def _total_volume_query(self, entity: Entity, filter: Filter,
team_id: int) -> Tuple[str, Dict, Callable]:
trunc_func = get_trunc_func_ch(filter.interval)
interval_func = get_interval_func_ch(filter.interval)
aggregate_operation, join_condition, math_params = process_math(entity)
trend_event_query = TrendsEventQuery(
filter=filter,
entity=entity,
team_id=team_id,
should_join_distinct_ids=True if join_condition != ""
or entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE] else False,
)
event_query, event_query_params = trend_event_query.get_query()
content_sql_params = {
"aggregate_operation": aggregate_operation,
"timestamp": "e.timestamp",
"interval": trunc_func,
}
params: Dict = {"team_id": team_id}
params = {**params, **math_params, **event_query_params}
if filter.display in TRENDS_DISPLAY_BY_VALUE:
content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
event_query=event_query, **content_sql_params)
return (content_sql, params,
self._parse_aggregate_volume_result(
filter, entity, team_id))
else:
if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
content_sql = ACTIVE_USER_SQL.format(
event_query=event_query,
**content_sql_params,
parsed_date_to=trend_event_query.parsed_date_to,
parsed_date_from=trend_event_query.parsed_date_from,
**trend_event_query.active_user_params,
)
elif filter.display == TRENDS_CUMULATIVE and entity.math == "dau":
cumulative_sql = CUMULATIVE_SQL.format(event_query=event_query)
content_sql = VOLUME_SQL.format(event_query=cumulative_sql,
**content_sql_params)
else:
content_sql = VOLUME_SQL.format(event_query=event_query,
**content_sql_params)
null_sql = NULL_SQL.format(trunc_func=trunc_func,
interval_func=interval_func)
params["interval"] = filter.interval
final_query = AGGREGATE_SQL.format(null_sql=null_sql,
content_sql=content_sql)
return final_query, params, self._parse_total_volume_result(
filter, entity, team_id)
def _total_volume_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]:
interval_annotation = get_trunc_func_ch(filter.interval)
num_intervals, seconds_in_interval, _ = get_time_diff(
filter.interval or "day", filter.date_from, filter.date_to, team_id=team_id
)
aggregate_operation, join_condition, math_params = process_math(entity)
trend_event_query = TrendsEventQuery(
filter=filter,
entity=entity,
team_id=team_id,
should_join_distinct_ids=True
if join_condition != "" or entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]
else False,
)
event_query, event_query_params = trend_event_query.get_query()
content_sql_params = {
"aggregate_operation": aggregate_operation,
"timestamp": "e.timestamp",
"interval": interval_annotation,
}
params: Dict = {"team_id": team_id}
params = {**params, **math_params, **event_query_params}
if filter.display in TRENDS_DISPLAY_BY_VALUE:
content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(event_query=event_query, **content_sql_params)
time_range = enumerate_time_range(filter, seconds_in_interval)
return (
content_sql,
params,
lambda result: [
{"aggregated_value": result[0][0] if result and len(result) else 0, "days": time_range}
],
)
else:
if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
content_sql = ACTIVE_USER_SQL.format(
event_query=event_query,
**content_sql_params,
parsed_date_to=trend_event_query.parsed_date_to,
parsed_date_from=trend_event_query.parsed_date_from,
**trend_event_query.active_user_params
)
else:
content_sql = VOLUME_SQL.format(event_query=event_query, **content_sql_params)
null_sql = NULL_SQL.format(
interval=interval_annotation,
seconds_in_interval=seconds_in_interval,
num_intervals=num_intervals,
date_to=filter.date_to.strftime("%Y-%m-%d %H:%M:%S"),
)
final_query = AGGREGATE_SQL.format(null_sql=null_sql, content_sql=content_sql)
return final_query, params, self._parse_total_volume_result(filter)
def _normal_query(self, entity: Entity, filter: Filter,
team_id: int) -> Tuple[str, Dict, Callable]:
interval_annotation = get_trunc_func_ch(filter.interval)
num_intervals, seconds_in_interval, round_interval = get_time_diff(
filter.interval or "day",
filter.date_from,
filter.date_to,
team_id=team_id)
_, parsed_date_to, date_params = parse_timestamps(filter=filter,
team_id=team_id)
props_to_filter = [*filter.properties, *entity.properties]
prop_filters, prop_filter_params = parse_prop_clauses(
props_to_filter,
team_id,
filter_test_accounts=filter.filter_test_accounts)
aggregate_operation, join_condition, math_params = process_math(entity)
params: Dict = {"team_id": team_id}
params = {**params, **prop_filter_params, **math_params, **date_params}
content_sql_params = {
"interval":
interval_annotation,
"parsed_date_from":
date_from_clause(interval_annotation, round_interval),
"parsed_date_to":
parsed_date_to,
"timestamp":
"timestamp",
"filters":
prop_filters,
"event_join":
join_condition,
"aggregate_operation":
aggregate_operation,
"entity_query":
"AND {actions_query}" if entity.type == TREND_FILTER_TYPE_ACTIONS
else "AND event = %(event)s",
}
entity_params, entity_format_params = self._populate_entity_params(
entity)
params = {**params, **entity_params}
if filter.display in TRENDS_DISPLAY_BY_VALUE:
content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
**content_sql_params).format(**entity_format_params)
time_range = self._enumerate_time_range(filter,
seconds_in_interval)
return (
content_sql,
params,
lambda result: [{
"aggregated_value":
result[0][0] if result and len(result) else 0,
"days":
time_range
}],
)
else:
if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
sql_params = get_active_user_params(filter, entity, team_id)
content_sql = ACTIVE_USER_SQL.format(
**content_sql_params,
**sql_params).format(**entity_format_params)
else:
# entity_format_params depends on format clause from content_sql_params
content_sql = VOLUME_SQL.format(**content_sql_params).format(
**entity_format_params)
null_sql = NULL_SQL.format(
interval=interval_annotation,
seconds_in_interval=seconds_in_interval,
num_intervals=num_intervals,
date_to=filter.date_to.strftime("%Y-%m-%d %H:%M:%S"),
)
final_query = AGGREGATE_SQL.format(null_sql=null_sql,
content_sql=content_sql)
return final_query, params, self._parse_normal_result(filter)
def _format_normal_query(self, entity: Entity, filter: Filter,
team: Team) -> List[Dict[str, Any]]:
interval_annotation = get_interval_annotation_ch(filter.interval)
num_intervals, seconds_in_interval = get_time_diff(
filter.interval or "day", filter.date_from, filter.date_to)
parsed_date_from, parsed_date_to = parse_timestamps(filter=filter)
props_to_filter = [*filter.properties, *entity.properties]
prop_filters, prop_filter_params = parse_prop_clauses(
props_to_filter, team)
aggregate_operation, join_condition, math_params = self._process_math(
entity)
params: Dict = {"team_id": team.pk}
params = {**params, **prop_filter_params, **math_params}
if entity.type == TREND_FILTER_TYPE_ACTIONS:
try:
action = Action.objects.get(pk=entity.id)
action_query, action_params = format_action_filter(action)
params = {**params, **action_params}
content_sql = VOLUME_ACTIONS_SQL.format(
interval=interval_annotation,
timestamp="timestamp",
team_id=team.pk,
actions_query=action_query,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters="{filters}".format(
filters=prop_filters) if props_to_filter else "",
event_join=join_condition,
aggregate_operation=aggregate_operation,
)
except:
return []
else:
content_sql = VOLUME_SQL.format(
interval=interval_annotation,
timestamp="timestamp",
team_id=team.pk,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters="{filters}".format(
filters=prop_filters) if props_to_filter else "",
event_join=join_condition,
aggregate_operation=aggregate_operation,
)
params = {**params, "event": entity.id}
null_sql = NULL_SQL.format(
interval=interval_annotation,
seconds_in_interval=seconds_in_interval,
num_intervals=num_intervals,
date_to=((filter.date_to
or timezone.now())).strftime("%Y-%m-%d %H:%M:%S"),
)
final_query = AGGREGATE_SQL.format(null_sql=null_sql,
content_sql=content_sql)
try:
result = sync_execute(final_query, params)
except:
result = []
parsed_results = []
for _, stats in enumerate(result):
parsed_result = self._parse_response(stats, filter)
parsed_results.append(parsed_result)
return parsed_results
def _total_volume_query(self, entity: Entity, filter: Filter,
team: Team) -> Tuple[str, Dict, Callable]:
trunc_func = get_trunc_func_ch(filter.interval)
interval_func = get_interval_func_ch(filter.interval)
aggregate_operation, join_condition, math_params = process_math(
entity, team)
trend_event_query = TrendsEventQuery(
filter=filter,
entity=entity,
team=team,
should_join_distinct_ids=True if join_condition != "" or
(entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]
and not team.aggregate_users_by_distinct_id) else False,
)
event_query, event_query_params = trend_event_query.get_query()
content_sql_params = {
"aggregate_operation": aggregate_operation,
"timestamp": "e.timestamp",
"interval": trunc_func,
}
params: Dict = {"team_id": team.id}
params = {**params, **math_params, **event_query_params}
if filter.display in TRENDS_DISPLAY_BY_VALUE:
content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
event_query=event_query, **content_sql_params)
return (content_sql, params,
self._parse_aggregate_volume_result(
filter, entity, team.id))
else:
if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
content_sql = ACTIVE_USER_SQL.format(
event_query=event_query,
**content_sql_params,
parsed_date_to=trend_event_query.parsed_date_to,
parsed_date_from=trend_event_query.parsed_date_from,
aggregator="distinct_id"
if team.aggregate_users_by_distinct_id else "person_id",
**trend_event_query.active_user_params,
)
elif filter.display == TRENDS_CUMULATIVE and entity.math == "dau":
cumulative_sql = CUMULATIVE_SQL.format(event_query=event_query)
content_sql = VOLUME_SQL.format(event_query=cumulative_sql,
**content_sql_params)
else:
content_sql = VOLUME_SQL.format(event_query=event_query,
**content_sql_params)
null_sql = NULL_SQL.format(trunc_func=trunc_func,
interval_func=interval_func)
params["interval"] = filter.interval
# If we have a smoothing interval > 1 then add in the sql to
# handling rolling average. Else just do a sum. This is possibly an
# nessacary optimization.
if filter.smoothing_intervals > 1:
smoothing_operation = f"""
AVG(SUM(total))
OVER (
ORDER BY day_start
ROWS BETWEEN {filter.smoothing_intervals - 1} PRECEDING
AND CURRENT ROW
)"""
else:
smoothing_operation = "SUM(total)"
final_query = AGGREGATE_SQL.format(
null_sql=null_sql,
content_sql=content_sql,
smoothing_operation=smoothing_operation,
aggregate="count"
if filter.smoothing_intervals < 2 else "floor(count)",
)
return final_query, params, self._parse_total_volume_result(
filter, entity, team.id)