def content_summary_from_content_timeseries(org,
content_item_ids=[],
num_hours=24):
"""
Rollup content-timseries metrics into summaries.
Optimize this query by only updating content items
which have had updates to their metrics in the last X hours.
"""
# just use this to generate a giant timeseries select with computed
# metrics.
ts = QueryContentMetricTimeseries(org, content_item_ids, unit=None)
ts.compute = False
metrics, ss = _summary_select(org.content_timeseries_metric_rollups)
qkw = {
'select_statements': ss,
'metrics': metrics,
'org_id': org.id,
'last_updated': (dates.now() - timedelta(hours=num_hours)).isoformat(),
'ts_query': ts.query,
}
q = """SELECT upsert_content_metric_summary({org_id}, content_item_id, metrics::text)
FROM (
SELECT
content_item_id,
(SELECT row_to_json(_) from (SELECT {metrics}) as _) as metrics
FROM (
SELECT
content_item_id,
{select_statements}
FROM ({ts_query}) zzzz
WHERE zzzz.content_item_id in (
SELECT
distinct(content_item_id)
FROM content_metric_timeseries
WHERE updated > '{last_updated}'
)
GROUP BY content_item_id
) t1
) t2
""".format(**qkw)
db.session.execute(q)
db.session.commit()
return True
def content_summary_from_content_timeseries(org, content_item_ids=[], num_hours=24):
"""
Rollup content-timseries metrics into summaries.
Optimize this query by only updating content items
which have had updates to their metrics in the last X hours.
"""
# just use this to generate a giant timeseries select with computed
# metrics.
ts = QueryContentMetricTimeseries(org, content_item_ids, unit=None)
ts.compute = False
metrics, ss = _summary_select(org.content_timeseries_metric_rollups)
qkw = {
'select_statements': ss,
'metrics': metrics,
'org_id': org.id,
'last_updated': (dates.now() - timedelta(hours=num_hours)).isoformat(),
'ts_query': ts.query,
}
q = """SELECT upsert_content_metric_summary({org_id}, content_item_id, metrics::text)
FROM (
SELECT
content_item_id,
(SELECT row_to_json(_) from (SELECT {metrics}) as _) as metrics
FROM (
SELECT
content_item_id,
{select_statements}
FROM ({ts_query}) zzzz
WHERE zzzz.content_item_id in (
SELECT
distinct(content_item_id)
FROM content_metric_timeseries
WHERE updated > '{last_updated}'
)
GROUP BY content_item_id
) t1
) t2
""".format(**qkw)
db.session.execute(q)
db.session.commit()
return True
def org_timeseries_from_content_timeseries(org,
content_item_ids=[],
num_hours=24):
"""
Rollup content timeseries => org timeseries.
"""
# summarize the content timeseries table
content_ts = QueryContentMetricTimeseries(org,
org.content_item_ids,
unit='hour',
group_by_id=False)
content_ts.compute = False
# select statements.
metrics, ss = _summary_select(org.timeseries_metric_rollups)
qkw = {
'org_id': org.id,
'metrics': metrics,
'select_statements': ss,
'ts_query': content_ts.query
}
# generate the query
q = \
"""SELECT upsert_org_metric_timeseries({org_id}, datetime, metrics::text)
FROM (
SELECT
datetime,
(SELECT row_to_json(_) from (SELECT {metrics}) as _) as metrics
FROM (
SELECT
datetime,
{select_statements}
FROM ({ts_query}) zzzz
GROUP BY datetime
) t1
) t2
""".format(**qkw)
db.session.execute(q)
db.session.commit()
return True
def org_timeseries_from_content_timeseries(org, content_item_ids=[], num_hours=24):
"""
Rollup content timeseries => org timeseries.
"""
# summarize the content timeseries table
content_ts = QueryContentMetricTimeseries(org, org.content_item_ids,
unit='hour', group_by_id=False)
content_ts.compute = False
# select statements.
metrics, ss = _summary_select(org.timeseries_metric_rollups)
qkw = {
'org_id': org.id,
'metrics': metrics,
'select_statements': ss,
'ts_query': content_ts.query
}
# generate the query
q = \
"""SELECT upsert_org_metric_timeseries({org_id}, datetime, metrics::text)
FROM (
SELECT
datetime,
(SELECT row_to_json(_) from (SELECT {metrics}) as _) as metrics
FROM (
SELECT
datetime,
{select_statements}
FROM ({ts_query}) zzzz
GROUP BY datetime
) t1
) t2
""".format(**qkw)
db.session.execute(q)
db.session.commit()
return True