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]