def get_query(self):
date_query, date_params = self._get_date_filter()
self.params.update(date_params)
prop_query, prop_params = self._get_prop_groups(
self._filter.property_groups)
self.params.update(prop_params)
person_query, person_params = self._get_person_query()
self.params.update(person_params)
groups_query, groups_params = self._get_groups_query()
self.params.update(groups_params)
entity_params, entity_format_params = get_entity_filtering_params(
entity=self._filter.entities[0],
team_id=self._team_id,
table_name=self.EVENT_TABLE_ALIAS)
self.params.update(entity_params)
return (
f"""
SELECT DISTINCT
person_id,
toDateTime(dateTrunc(%(interval)s, events.timestamp)) AS period,
person.created_at AS created_at
FROM events AS {self.EVENT_TABLE_ALIAS}
{self._get_distinct_id_query()}
{person_query}
{groups_query}
WHERE team_id = %(team_id)s
{entity_format_params["entity_query"]}
{date_query}
{prop_query}
""",
self.params,
)
def get_breakdown_prop_values(
filter: Filter,
entity: Entity,
aggregate_operation: str,
team_id: int,
limit: int = BREAKDOWN_VALUES_LIMIT,
extra_params={},
column_optimizer: Optional[EnterpriseColumnOptimizer] = None,
):
"""
Returns the top N breakdown prop values for event/person breakdown
e.g. for Browser with limit 3 might return ['Chrome', 'Safari', 'Firefox', 'Other']
"""
column_optimizer = column_optimizer or EnterpriseColumnOptimizer(
filter, team_id)
parsed_date_from, parsed_date_to, date_params = parse_timestamps(
filter=filter, team_id=team_id)
props_to_filter = filter.property_groups.combine_property_group(
PropertyOperatorType.AND, entity.property_groups)
outer_properties = column_optimizer.property_optimizer.parse_property_groups(
props_to_filter).outer
prop_filters, prop_filter_params = parse_prop_grouped_clauses(
team_id=team_id,
property_group=outer_properties,
table_name="e",
prepend="e_brkdwn",
person_properties_mode=PersonPropertiesMode.
USING_PERSON_PROPERTIES_COLUMN,
allow_denormalized_props=True,
)
entity_params, entity_format_params = get_entity_filtering_params(
entity=entity, team_id=team_id, table_name="e")
value_expression = _to_value_expression(filter.breakdown_type,
filter.breakdown,
filter.breakdown_group_type_index)
person_join_clauses = ""
person_join_params: Dict = {}
person_query = PersonQuery(filter,
team_id,
column_optimizer=column_optimizer,
entity=entity)
if person_query.is_used:
person_subquery, person_join_params = person_query.get_query()
person_join_clauses = f"""
INNER JOIN ({get_team_distinct_ids_query(team_id)}) AS pdi ON e.distinct_id = pdi.distinct_id
INNER JOIN ({person_subquery}) person ON pdi.person_id = person.id
"""
groups_join_condition, groups_join_params = GroupsJoinQuery(
filter, team_id, column_optimizer).get_join_query()
elements_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format(
value_expression=value_expression,
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
prop_filters=prop_filters,
aggregate_operation=aggregate_operation,
person_join_clauses=person_join_clauses,
groups_join_clauses=groups_join_condition,
**entity_format_params,
)
return sync_execute(
elements_query,
{
"key": filter.breakdown,
"limit": limit,
"team_id": team_id,
"offset": filter.offset,
**prop_filter_params,
**entity_params,
**person_join_params,
**groups_join_params,
**extra_params,
**date_params,
},
)[0][0]
def _get_entity_query(self) -> Tuple[str, Dict]:
entity_params, entity_format_params = get_entity_filtering_params(
self._entity, self._team_id, table_name=self.EVENT_TABLE_ALIAS)
return entity_format_params["entity_query"], entity_params
def get_breakdown_prop_values(
filter: Filter,
entity: Entity,
aggregate_operation: str,
team_id: int,
limit: int = 25,
extra_params={},
column_optimizer: Optional[ColumnOptimizer] = None,
):
"Returns the top N breakdown prop values for event/person breakdown"
parsed_date_from, parsed_date_to, date_params = parse_timestamps(
filter=filter, team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties + entity.properties,
team_id,
table_name="e",
prepend="e_brkdwn",
person_properties_mode=PersonPropertiesMode.EXCLUDE,
allow_denormalized_props=True,
)
entity_params, entity_format_params = get_entity_filtering_params(
entity, team_id, table_name="e")
if filter.breakdown_type == "person":
value_expression, _ = get_property_string_expr(
"person", cast(str, filter.breakdown), "%(key)s", "person_props")
elif filter.breakdown_type == "group":
value_expression, _ = get_property_string_expr(
"groups", cast(str, filter.breakdown), "%(key)s",
f"group_properties_{filter.breakdown_group_type_index}")
else:
value_expression, _ = get_property_string_expr(
"events", cast(str, filter.breakdown), "%(key)s", "properties")
person_join_clauses = ""
person_join_params: Dict = {}
person_query = ClickhousePersonQuery(filter,
team_id,
column_optimizer=column_optimizer,
entity=entity)
if person_query.is_used:
person_subquery, person_join_params = person_query.get_query()
person_join_clauses = f"""
INNER JOIN ({GET_TEAM_PERSON_DISTINCT_IDS}) AS pdi ON e.distinct_id = pdi.distinct_id
INNER JOIN ({person_subquery}) person ON pdi.person_id = person.id
"""
groups_join_condition, groups_join_params = GroupsJoinQuery(
filter, team_id, column_optimizer).get_join_query()
elements_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format(
value_expression=value_expression,
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
prop_filters=prop_filters,
aggregate_operation=aggregate_operation,
person_join_clauses=person_join_clauses,
groups_join_clauses=groups_join_condition,
**entity_format_params,
)
return sync_execute(
elements_query,
{
"key": filter.breakdown,
"limit": limit,
"team_id": team_id,
"offset": filter.offset,
**prop_filter_params,
**entity_params,
**person_join_params,
**groups_join_params,
**extra_params,
**date_params,
},
)[0][0]