def get_breakdown_event_prop_values(filter: Filter,
entity: Entity,
aggregate_operation: str,
team_id: int,
limit: int = 25):
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team_id,
table_name="e",
filter_test_accounts=filter.filter_test_accounts,
)
entity_params, entity_format_params = populate_entity_params(entity)
elements_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format(
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
prop_filters=prop_filters,
aggregate_operation=aggregate_operation,
**entity_format_params,
)
top_elements_array = _get_top_elements(
filter=filter,
team_id=team_id,
query=elements_query,
params={
**prop_filter_params,
**entity_params
},
limit=limit,
)
return top_elements_array
def _breakdown_person_params(self, aggregate_operation: str,
entity: Entity, filter: Filter, team_id: int):
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team_id,
table_name="e",
filter_test_accounts=filter.filter_test_accounts)
person_prop_filters, person_prop_params = parse_prop_clauses(
[prop for prop in filter.properties if prop.type == "person"],
team_id,
table_name="e",
filter_test_accounts=filter.filter_test_accounts,
is_person_query=True,
)
entity_params, entity_format_params = populate_entity_params(entity)
elements_query = TOP_PERSON_PROPS_ARRAY_OF_KEY_SQL.format(
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
latest_person_sql=GET_LATEST_PERSON_SQL.format(query=""),
prop_filters=prop_filters,
person_prop_filters=person_prop_filters,
aggregate_operation=aggregate_operation,
latest_distinct_id_sql=GET_LATEST_PERSON_DISTINCT_ID_SQL,
**entity_format_params)
top_elements_array = self._get_top_elements(elements_query,
filter,
team_id,
params={
**prop_filter_params,
**person_prop_params,
**entity_params
})
params = {
"values": [*top_elements_array, "none"],
}
breakdown_filter_params = {
"latest_person_sql": GET_LATEST_PERSON_SQL.format(query=""),
}
return (
params,
BREAKDOWN_PERSON_PROP_JOIN_SQL,
breakdown_filter_params,
"value",
None if filter.offset else NONE_BREAKDOWN_PERSON_PROP_JOIN_SQL,
)
def get_breakdown_person_prop_values(filter: Filter,
entity: Entity,
aggregate_operation: str,
team_id: int,
limit: int = 25):
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team_id,
table_name="e",
filter_test_accounts=filter.filter_test_accounts,
)
person_prop_filters, person_prop_params = parse_prop_clauses(
[prop for prop in filter.properties if prop.type == "person"],
team_id,
filter_test_accounts=filter.filter_test_accounts,
is_person_query=True,
prepend="person",
)
entity_params, entity_format_params = populate_entity_params(entity)
elements_query = TOP_PERSON_PROPS_ARRAY_OF_KEY_SQL.format(
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
latest_person_sql=GET_LATEST_PERSON_SQL.format(query=""),
prop_filters=prop_filters,
person_prop_filters=person_prop_filters,
aggregate_operation=aggregate_operation,
GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS,
**entity_format_params,
)
top_elements_array = _get_top_elements(
filter=filter,
team_id=team_id,
query=elements_query,
params={
**prop_filter_params,
**person_prop_params,
**entity_params
},
limit=limit,
)
return top_elements_array
def _breakdown_prop_params(self, aggregate_operation: str, entity: Entity,
filter: Filter, team_id: int):
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team_id,
table_name="e",
filter_test_accounts=filter.filter_test_accounts)
entity_params, entity_format_params = populate_entity_params(entity)
elements_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format(
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
prop_filters=prop_filters,
aggregate_operation=aggregate_operation,
**entity_format_params)
top_elements_array = self._get_top_elements(elements_query,
filter,
team_id,
params={
**prop_filter_params,
**entity_params
})
params = {
"values": [*top_elements_array, "none"],
}
return (
params,
BREAKDOWN_PROP_JOIN_SQL,
{},
"JSONExtractRaw(properties, %(key)s)",
None if filter.offset else NONE_BREAKDOWN_PROP_JOIN_SQL,
)
def _get_entity_query(self) -> Tuple[str, Dict]:
entity_params, entity_format_params = populate_entity_params(
self._entity)
return entity_format_params["entity_query"], entity_params
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, 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_params, entity_format_params = populate_entity_params(entity)
content_sql_params = {**content_sql_params, **entity_format_params}
params = {**params, **entity_params}
if filter.display in TRENDS_DISPLAY_BY_VALUE:
content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
**content_sql_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)
else:
content_sql = VOLUME_SQL.format(**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)
