def _parse(result: List) -> List: _, seconds_in_interval, _ = get_time_diff(filter.interval, filter.date_from, filter.date_to, team_id=team_id) time_range = enumerate_time_range(filter, seconds_in_interval) filter_params = filter.to_params() extra_params = { "entity_id": entity.id, "entity_type": entity.type, "entity_math": entity.math } parsed_params: Dict[str, str] = encode_get_request_params({ **filter_params, **extra_params }) return [{ "aggregated_value": result[0][0] if result and len(result) else 0, "days": time_range, "filter": filter_params, "persons": { "filter": extra_params, "url": f"api/projects/{team_id}/actions/people/?{urllib.parse.urlencode(parsed_params)}", }, }]
def _get_date_filter(self) -> Tuple[str, Dict]: date_filter = "" date_params: Dict[str, Any] = {} interval_annotation = get_trunc_func_ch(self._filter.interval) _, _, round_interval = get_time_diff(self._filter.interval or "day", self._filter.date_from, self._filter.date_to, team_id=self._team_id) _, parsed_date_to, date_params = parse_timestamps( filter=self._filter, team_id=self._team_id) parsed_date_from = date_from_clause(interval_annotation, round_interval) self.parsed_date_from = parsed_date_from self.parsed_date_to = parsed_date_to if self._entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]: date_filter = "{parsed_date_from_prev_range} {parsed_date_to}" format_params = get_active_user_params(self._filter, self._entity, self._team_id) self.active_user_params = format_params date_filter = date_filter.format(**format_params, parsed_date_to=parsed_date_to) else: date_filter = "{parsed_date_from} {parsed_date_to}".format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to) return date_filter, date_params
def _format_lifecycle_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]: date_from = filter.date_from if not date_from: date_from = get_earliest_timestamp(team_id) interval = filter.interval or "day" num_intervals, seconds_in_interval, _ = get_time_diff( interval, filter.date_from, filter.date_to, team_id) interval_increment, interval_string, sub_interval_string = self.get_interval( interval) trunc_func = get_trunc_func_ch(interval) event_query = "" event_params: Dict[str, Any] = {} 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) _, _, date_params = parse_timestamps(filter=filter, team_id=team_id) if entity.type == TREND_FILTER_TYPE_ACTIONS: try: action = entity.get_action() event_query, event_params = format_action_filter(action) except: return "", {}, self._parse_result(filter, entity) else: event_query = "event = %(event)s" event_params = {"event": entity.id} return ( LIFECYCLE_SQL.format( interval=interval_string, trunc_func=trunc_func, event_query=event_query, filters=prop_filters, sub_interval=sub_interval_string, GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS, ), { "team_id": team_id, "prev_date_from": (date_from - interval_increment).strftime("%Y-%m-%d{}".format( " %H:%M:%S" if filter.interval == "hour" or filter.interval == "minute" else " 00:00:00")), "num_intervals": num_intervals, "seconds_in_interval": seconds_in_interval, **event_params, **date_params, **prop_filter_params, }, self._parse_result(filter, entity), )
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 calculate_avg(self, filter: Filter, team: Team): parsed_date_from, parsed_date_to, _ = parse_timestamps(filter, team.pk) filters, params = parse_prop_clauses( filter.properties, team.pk, filter_test_accounts=filter.filter_test_accounts ) interval_notation = 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.pk ) entity_conditions, entity_params = entity_query_conditions(filter, team) if not entity_conditions: entity_conditions = ["event != '$feature_flag_called'"] # default conditino params = {**params, **entity_params} entity_query = " OR ".join(entity_conditions) avg_query = SESSIONS_NO_EVENTS_SQL.format( team_id=team.pk, date_from=parsed_date_from, date_to=parsed_date_to, filters=filters, sessions_limit="", entity_filter=f"AND ({entity_query})", ) per_period_query = AVERAGE_PER_PERIOD_SQL.format(sessions=avg_query, interval=interval_notation) null_sql = NULL_SQL.format( date_to=filter.date_to.strftime("%Y-%m-%d 00:00:00"), interval=interval_notation, num_intervals=num_intervals, seconds_in_interval=seconds_in_interval, ) final_query = AVERAGE_SQL.format(sessions=per_period_query, null_sql=null_sql) params = {**params, "team_id": team.pk} response = sync_execute(final_query, params) values = self.clean_values(filter, response) time_series_data = append_data(values, interval=filter.interval, math=None) scaled_data, _ = scale_time_series(time_series_data["data"]) time_series_data.update({"data": scaled_data}) # calculate average total = sum(val[1] for val in values) if total == 0: return [] valid_days = sum(1 if val[1] else 0 for val in values) overall_average = (total / valid_days) if valid_days else 0 result = self._format_avg(overall_average) time_series_data.update(result) return [time_series_data]
def calculate_avg(self, filter: Filter, team: Team): # format default dates if not filter._date_from: filter._date_from = relative_date_parse("-7d") if not filter._date_to: filter._date_to = timezone.now() parsed_date_from, parsed_date_to = parse_timestamps(filter) filters, params = parse_prop_clauses("uuid", filter.properties, team) interval_notation = get_interval_annotation_ch(filter.interval) num_intervals, seconds_in_interval = get_time_diff( filter.interval or "day", filter.date_from, filter.date_to) avg_query = SESSIONS_NO_EVENTS_SQL.format( team_id=team.pk, date_from=parsed_date_from, date_to=parsed_date_to, filters="{}".format(filters) if filter.properties else "", sessions_limit="", ) per_period_query = AVERAGE_PER_PERIOD_SQL.format( sessions=avg_query, interval=interval_notation) null_sql = NULL_SQL.format( date_to=(filter.date_to or timezone.now()).strftime("%Y-%m-%d 00:00:00"), interval=interval_notation, num_intervals=num_intervals, seconds_in_interval=seconds_in_interval, ) final_query = AVERAGE_SQL.format(sessions=per_period_query, null_sql=null_sql) params = {**params, "team_id": team.pk} response = sync_execute(final_query, params) values = self.clean_values(filter, response) time_series_data = append_data(values, interval=filter.interval, math=None) # calculate average total = sum(val[1] for val in values) if total == 0: return [] valid_days = sum(1 if val[1] else 0 for val in values) overall_average = (total / valid_days) if valid_days else 0 result = self._format_avg(overall_average) time_series_data.update(result) return [time_series_data]
def _get_funnel_trend_null_sql(self): interval_annotation = get_trunc_func_ch(self._filter.interval) num_intervals, seconds_in_interval, round_interval = get_time_diff( self._filter.interval or "day", self._filter.date_from, self._filter.date_to, team_id=self._team.id ) funnel_trend_null_sql = NULL_SQL_FUNNEL_TRENDS.format( interval=interval_annotation, seconds_in_interval=seconds_in_interval, num_intervals=num_intervals, date_to=self._filter.date_to.strftime("%Y-%m-%d %H:%M:%S"), ) return funnel_trend_null_sql
def get_query(self) -> str: steps_per_person_query = self.funnel_order.get_step_counts_without_aggregation_query( ) # expects multiple rows for same person, first event time, steps taken. self.params.update(self.funnel_order.params) num_intervals, seconds_in_interval, _ = get_time_diff( self._filter.interval or "day", self._filter.date_from, self._filter.date_to, team_id=self._team.pk) interval_method = get_trunc_func_ch(self._filter.interval) # How many steps must have been done to count for the denominator from_step = self._filter.funnel_from_step or 1 # How many steps must have been done to count for the numerator to_step = self._filter.funnel_to_step or len(self._filter.entities) reached_from_step_count_condition = f"steps_completed >= {from_step}" reached_to_step_count_condition = f"steps_completed >= {to_step}" query = f""" SELECT entrance_period_start, reached_from_step_count, reached_to_step_count, if(reached_from_step_count > 0, round(reached_to_step_count / reached_from_step_count * 100, 2), 0) AS conversion_rate FROM ( SELECT entrance_period_start, countIf({reached_from_step_count_condition}) AS reached_from_step_count, countIf({reached_to_step_count_condition}) AS reached_to_step_count FROM ( SELECT person_id, {interval_method}(timestamp) AS entrance_period_start, max(steps) AS steps_completed FROM ( {steps_per_person_query} ) GROUP BY person_id, entrance_period_start ) GROUP BY entrance_period_start ) data RIGHT JOIN ( SELECT {interval_method}(toDateTime('{self._filter.date_from.strftime(TIMESTAMP_FORMAT)}') + number * {seconds_in_interval}) AS entrance_period_start FROM numbers({num_intervals}) AS period_offsets ) fill USING (entrance_period_start) ORDER BY entrance_period_start ASC SETTINGS allow_experimental_window_functions = 1""" return query
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) 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", "team_id": team_id, "filters": prop_filters, "event_join": join_condition, "aggregate_operation": aggregate_operation, } entity_params, entity_format_params = self._populate_entity_params(entity) params = {**params, **entity_params} content_sql_params = {**content_sql_params, **entity_format_params} if filter.display in TRENDS_DISPLAY_BY_VALUE: agg_query = self._determine_single_aggregate_query(filter, entity) content_sql = agg_query.format(**content_sql_params) return ( content_sql, params, lambda result: [{"aggregated_value": result[0][0] if result and len(result) else 0}], ) else: content_sql = self._determine_trend_aggregate_query(filter, entity) content_sql = content_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_normal_result(filter)
def get_query(self) -> str: step_counts = self.get_step_counts_without_aggregation_query() # Expects multiple rows for same person, first event time, steps taken. self.params.update(self.funnel_order.params) reached_from_step_count_condition, reached_to_step_count_condition, _ = self.get_steps_reached_conditions( ) interval_method = get_trunc_func_ch(self._filter.interval) num_intervals, seconds_in_interval, _ = get_time_diff( self._filter.interval or "day", self._filter.date_from, self._filter.date_to, team_id=self._team.pk) breakdown_clause = self._get_breakdown_prop() query = f""" SELECT entrance_period_start, reached_from_step_count, reached_to_step_count, if(reached_from_step_count > 0, round(reached_to_step_count / reached_from_step_count * 100, 2), 0) AS conversion_rate {breakdown_clause} FROM ( SELECT entrance_period_start, countIf({reached_from_step_count_condition}) AS reached_from_step_count, countIf({reached_to_step_count_condition}) AS reached_to_step_count {breakdown_clause} FROM ( {step_counts} ) GROUP BY entrance_period_start {breakdown_clause} ) data RIGHT OUTER JOIN ( SELECT {interval_method}(toDateTime('{self._filter.date_from.strftime(TIMESTAMP_FORMAT)}') + number * {seconds_in_interval}) AS entrance_period_start {', breakdown_value as prop' if breakdown_clause else ''} FROM numbers({num_intervals}) AS period_offsets {'ARRAY JOIN (%(breakdown_values)s) AS breakdown_value' if breakdown_clause else ''} ) fill USING (entrance_period_start {breakdown_clause}) ORDER BY entrance_period_start ASC SETTINGS allow_experimental_window_functions = 1""" return query
def _format_breakdown_query(self, entity: Entity, filter: Filter, team: Team) -> List[Dict[str, Any]]: params = {"team_id": team.pk} inteval_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) action_query = "" action_params: Dict = {} top_elements_array = [] if entity.type == TREND_FILTER_TYPE_ACTIONS: action = Action.objects.get(pk=entity.id) action_query, action_params = format_action_filter(action) null_sql = NULL_BREAKDOWN_SQL.format( interval=inteval_annotation, seconds_in_interval=seconds_in_interval, num_intervals=num_intervals, date_to=((filter.date_to or timezone.now()) + timedelta(days=1)).strftime("%Y-%m-%d 00:00:00"), ) aggregate_operation, join_condition, math_params = self._process_math( entity) params = {**params, **math_params} if filter.breakdown_type == "cohort": breakdown = filter.breakdown if filter.breakdown and isinstance( filter.breakdown, list) else [] if "all" in breakdown: params = { **params, "event": entity.id, **action_params, } null_sql = NULL_SQL.format( interval=inteval_annotation, seconds_in_interval=seconds_in_interval, num_intervals=num_intervals, date_to=((filter.date_to or timezone.now()) + timedelta(days=1)).strftime("%Y-%m-%d 00:00:00"), ) conditions = BREAKDOWN_CONDITIONS_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="and uuid IN ({})".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", ) breakdown_query = BREAKDOWN_DEFAULT_SQL.format( null_sql=null_sql, conditions=conditions, event_join=join_condition, aggregate_operation=aggregate_operation, ) else: cohort_queries, cohort_ids = self._format_breakdown_cohort_join_query( breakdown, team) params = { **params, "values": cohort_ids, "event": entity.id, **action_params, } breakdown_filter = BREAKDOWN_COHORT_JOIN_SQL.format( cohort_queries=cohort_queries, parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="and uuid IN ({})".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", ) breakdown_query = BREAKDOWN_QUERY_SQL.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, ) elif filter.breakdown_type == "person": pass else: element_params = {**params, "key": filter.breakdown, "limit": 20} element_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to) try: top_elements_array_result = sync_execute( element_query, element_params) top_elements_array = top_elements_array_result[0][0] except: top_elements_array = [] params = { **params, "values": top_elements_array, "key": filter.breakdown, "event": entity.id, **action_params, } breakdown_filter = BREAKDOWN_PROP_JOIN_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="and uuid IN ({})".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", ) breakdown_query = BREAKDOWN_QUERY_SQL.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, ) try: result = sync_execute(breakdown_query, params) except: result = [] parsed_results = [] for idx, stats in enumerate(result): extra_label = self._determine_breakdown_label( idx, filter.breakdown_type, filter.breakdown, top_elements_array) label = "{} - {}".format(entity.name, extra_label) additional_values = { "label": label, "breakdown_value": filter.breakdown[idx] if isinstance(filter.breakdown, list) else filter.breakdown if filter.breakdown_type == "cohort" else top_elements_array[idx], } parsed_result = self._parse_response(stats, filter, additional_values) parsed_results.append(parsed_result) return parsed_results
def get_people( self, filter: Filter, team_id: int, target_date: datetime, lifecycle_type: str, request: Request, limit: int = 100, ): entity = filter.entities[0] date_from = filter.date_from if not date_from: date_from = get_earliest_timestamp(team_id) interval = filter.interval or "day" num_intervals, seconds_in_interval, _ = get_time_diff(interval, filter.date_from, filter.date_to, team_id=team_id) interval_increment, interval_string, sub_interval_string = self.get_interval( interval) trunc_func = get_trunc_func_ch(interval) event_query = "" event_params: Dict[str, Any] = {} _, _, date_params = parse_timestamps(filter=filter, team_id=team_id) if entity.type == TREND_FILTER_TYPE_ACTIONS: try: action = entity.get_action() event_query, event_params = format_action_filter(action) except: return [] else: event_query = "event = %(event)s" event_params = {"event": entity.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) result = sync_execute( LIFECYCLE_PEOPLE_SQL.format( interval=interval_string, trunc_func=trunc_func, event_query=event_query, filters=prop_filters, sub_interval=sub_interval_string, GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS, ), { "team_id": team_id, "prev_date_from": (date_from - interval_increment).strftime("%Y-%m-%d{}".format( " %H:%M:%S" if filter.interval == "hour" or filter.interval == "minute" else " 00:00:00")), "num_intervals": num_intervals, "seconds_in_interval": seconds_in_interval, **event_params, **date_params, **prop_filter_params, "status": lifecycle_type, "target_date": target_date.strftime("%Y-%m-%d{}".format( " %H:%M:%S" if filter.interval == "hour" or filter.interval == "minute" else " 00:00:00")), "offset": filter.offset, "limit": limit, }, ) people = get_persons_by_uuids(team_id=team_id, uuids=[p[0] for p in result]) people = people.prefetch_related( Prefetch("persondistinctid_set", to_attr="distinct_ids_cache")) from posthog.api.person import PersonSerializer return PersonSerializer(people, many=True).data
def _format_normal_query(self, entity: Entity, filter: Filter, team_id: int) -> List[Dict[str, Any]]: 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) parsed_date_from, parsed_date_to, _ = 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) aggregate_operation, join_condition, math_params = process_math(entity) params: Dict = {"team_id": team_id} params = {**params, **prop_filter_params, **math_params} content_sql_params = { "interval": interval_annotation, "timestamp": "timestamp", "team_id": team_id, "parsed_date_from": parsed_date_from, "parsed_date_to": parsed_date_to, "filters": prop_filters, "event_join": join_condition, "aggregate_operation": aggregate_operation, } entity_params, entity_format_params = self._populate_entity_params( entity) params = {**params, **entity_params} content_sql_params = {**content_sql_params, **entity_format_params} if filter.display == TRENDS_TABLE or filter.display == TRENDS_PIE: agg_query = self._determine_single_aggregate_query(filter, entity) content_sql = agg_query.format(**content_sql_params) try: result = sync_execute(content_sql, params) except: result = [] return [{ "aggregated_value": result[0][0] if result and len(result) else 0 }] else: content_sql = self._determine_trend_aggregate_query(filter, entity) content_sql = content_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) try: result = sync_execute(final_query, params) except: result = [] parsed_results = [] for _, stats in enumerate(result): parsed_result = parse_response(stats, filter) parsed_results.append(parsed_result) return parsed_results
def _format_breakdown_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]: # process params params: Dict[str, Any] = {"team_id": team_id} 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) _, 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, table_name="e", filter_test_accounts=filter.filter_test_accounts) aggregate_operation, _, math_params = process_math(entity) if entity.math == "dau" or filter.breakdown_type == "person": join_condition = EVENT_JOIN_PERSON_SQL else: join_condition = "" action_query = "" action_params: Dict = {} if entity.type == TREND_FILTER_TYPE_ACTIONS: action = Action.objects.get(pk=entity.id) action_query, action_params = format_action_filter(action) null_sql = NULL_BREAKDOWN_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"), ) params = { **params, **math_params, **prop_filter_params, **action_params, "event": entity.id, "key": filter.breakdown, **date_params, } breakdown_filter_params = { "parsed_date_from": date_from_clause(interval_annotation, round_interval), "parsed_date_to": parsed_date_to, "actions_query": "AND {}".format(action_query) if action_query else "", "event_filter": "AND event = %(event)s" if not action_query else "", "filters": prop_filters if props_to_filter else "", } breakdown_query = self._get_breakdown_query(filter) _params, _breakdown_filter_params = {}, {} if filter.breakdown_type == "cohort": _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_cohort_params( team_id, filter, entity) elif filter.breakdown_type == "person": _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_person_params( filter, team_id) else: _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_prop_params( filter, team_id) if len(_params["values"]) == 0: return "SELECT 1", {}, lambda _: [] params = {**params, **_params} breakdown_filter_params = { **breakdown_filter_params, **_breakdown_filter_params } if filter.display in TRENDS_DISPLAY_BY_VALUE: breakdown_filter = breakdown_filter.format( **breakdown_filter_params) content_sql = breakdown_query.format( breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, breakdown_value=breakdown_value, ) return content_sql, params, self._parse_single_aggregate_result( filter, entity) else: 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"), ) breakdown_filter = breakdown_filter.format( **breakdown_filter_params) breakdown_query = breakdown_query.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, ) return breakdown_query, params, self._parse_trend_result( filter, entity)
def get_query(self) -> Tuple[str, Dict, Callable]: interval_annotation = get_trunc_func_ch(self.filter.interval) num_intervals, seconds_in_interval, round_interval = get_time_diff( self.filter.interval, self.filter.date_from, self.filter.date_to, self.team_id) _, parsed_date_to, date_params = parse_timestamps(filter=self.filter, team_id=self.team_id) props_to_filter = self.filter.property_groups.combine_property_group( PropertyOperatorType.AND, self.entity.property_groups) outer_properties = self.column_optimizer.property_optimizer.parse_property_groups( props_to_filter).outer prop_filters, prop_filter_params = parse_prop_grouped_clauses( team_id=self.team_id, property_group=outer_properties, table_name="e", person_properties_mode=PersonPropertiesMode. USING_PERSON_PROPERTIES_COLUMN, ) aggregate_operation, _, math_params = process_math(self.entity) action_query = "" action_params: Dict = {} if self.entity.type == TREND_FILTER_TYPE_ACTIONS: action = self.entity.get_action() action_query, action_params = format_action_filter( team_id=self.team_id, action=action, table_name="e") self.params = { **self.params, **math_params, **prop_filter_params, **action_params, "event": self.entity.id, "key": self.filter.breakdown, **date_params, } breakdown_filter_params = { "parsed_date_from": date_from_clause(interval_annotation, round_interval), "parsed_date_to": parsed_date_to, "actions_query": "AND {}".format(action_query) if action_query else "", "event_filter": "AND event = %(event)s" if not action_query else "", "filters": prop_filters if props_to_filter.values else "", } _params, _breakdown_filter_params = {}, {} if self.filter.breakdown_type == "cohort": _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_cohort_params( ) else: _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_prop_params( "count(*)" if self.entity.math == "dau" else aggregate_operation, math_params, ) if len(_params["values"]) == 0: # If there are no breakdown values, we are sure that there's no relevant events, so instead of adjusting # a "real" SELECT for this, we only include the below dummy SELECT. # It's a drop-in replacement for a "real" one, simply always returning 0 rows. # See https://github.com/PostHog/posthog/pull/5674 for context. return ( "SELECT [now()] AS date, [0] AS data, '' AS breakdown_value LIMIT 0", {}, lambda _: [], ) person_join_condition, person_join_params = self._person_join_condition( ) groups_join_condition, groups_join_params = GroupsJoinQuery( self.filter, self.team_id, self.column_optimizer).get_join_query() self.params = { **self.params, **_params, **person_join_params, **groups_join_params } breakdown_filter_params = { **breakdown_filter_params, **_breakdown_filter_params } if self.filter.display in TRENDS_DISPLAY_BY_VALUE: breakdown_filter = breakdown_filter.format( **breakdown_filter_params) content_sql = BREAKDOWN_AGGREGATE_QUERY_SQL.format( breakdown_filter=breakdown_filter, person_join=person_join_condition, groups_join=groups_join_condition, aggregate_operation=aggregate_operation, breakdown_value=breakdown_value, ) time_range = enumerate_time_range(self.filter, seconds_in_interval) return ( content_sql, self.params, self._parse_single_aggregate_result(self.filter, self.entity, {"days": time_range}), ) else: breakdown_filter = breakdown_filter.format( **breakdown_filter_params) if self.entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]: active_user_params = get_active_user_params( self.filter, self.entity, self.team_id) conditions = BREAKDOWN_ACTIVE_USER_CONDITIONS_SQL.format( **breakdown_filter_params, **active_user_params) inner_sql = BREAKDOWN_ACTIVE_USER_INNER_SQL.format( breakdown_filter=breakdown_filter, person_join=person_join_condition, groups_join=groups_join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, conditions=conditions, GET_TEAM_PERSON_DISTINCT_IDS=get_team_distinct_ids_query( self.team_id), **active_user_params, **breakdown_filter_params, ) elif self.filter.display == TRENDS_CUMULATIVE and self.entity.math == "dau": inner_sql = BREAKDOWN_CUMULATIVE_INNER_SQL.format( breakdown_filter=breakdown_filter, person_join=person_join_condition, groups_join=groups_join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, **breakdown_filter_params, ) else: inner_sql = BREAKDOWN_INNER_SQL.format( breakdown_filter=breakdown_filter, person_join=person_join_condition, groups_join=groups_join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, ) breakdown_query = BREAKDOWN_QUERY_SQL.format( interval=interval_annotation, num_intervals=num_intervals, inner_sql=inner_sql, ) self.params.update({ "seconds_in_interval": seconds_in_interval, "num_intervals": num_intervals, }) return breakdown_query, self.params, self._parse_trend_result( self.filter, self.entity)
def _format_breakdown_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]: # process params params: Dict[str, Any] = {"team_id": team_id} 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) _, 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, table_name="e", filter_test_accounts=filter.filter_test_accounts) aggregate_operation, _, math_params = process_math(entity) if entity.math == "dau" or filter.breakdown_type == "person": join_condition = EVENT_JOIN_PERSON_SQL else: join_condition = "" action_query = "" action_params: Dict = {} if entity.type == TREND_FILTER_TYPE_ACTIONS: action = entity.get_action() action_query, action_params = format_action_filter(action, table_name="e") params = { **params, **math_params, **prop_filter_params, **action_params, "event": entity.id, "key": filter.breakdown, **date_params, } breakdown_filter_params = { "parsed_date_from": date_from_clause(interval_annotation, round_interval), "parsed_date_to": parsed_date_to, "actions_query": "AND {}".format(action_query) if action_query else "", "event_filter": "AND event = %(event)s" if not action_query else "", "filters": prop_filters if props_to_filter else "", } _params, _breakdown_filter_params = {}, {} if filter.breakdown_type == "cohort": _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_cohort_params( team_id, filter, entity) elif filter.breakdown_type == "person": ( _params, breakdown_filter, _breakdown_filter_params, breakdown_value, ) = self._breakdown_person_params( "count(*)" if entity.math == "dau" else aggregate_operation, entity, filter, team_id) else: ( _params, breakdown_filter, _breakdown_filter_params, breakdown_value, ) = self._breakdown_prop_params( "count(*)" if entity.math == "dau" else aggregate_operation, entity, filter, team_id) if len(_params["values"]) == 0: return "SELECT 1", {}, lambda _: [] params = {**params, **_params} breakdown_filter_params = { **breakdown_filter_params, **_breakdown_filter_params } if filter.display in TRENDS_DISPLAY_BY_VALUE: breakdown_filter = breakdown_filter.format( **breakdown_filter_params) content_sql = BREAKDOWN_AGGREGATE_QUERY_SQL.format( breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, breakdown_value=breakdown_value, ) time_range = enumerate_time_range(filter, seconds_in_interval) return content_sql, params, self._parse_single_aggregate_result( filter, entity, {"days": time_range}) else: breakdown_filter = breakdown_filter.format( **breakdown_filter_params) if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]: active_user_params = get_active_user_params( filter, entity, team_id) conditions = BREAKDOWN_ACTIVE_USER_CONDITIONS_SQL.format( **breakdown_filter_params, **active_user_params) inner_sql = BREAKDOWN_ACTIVE_USER_INNER_SQL.format( breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, conditions=conditions, GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS, **active_user_params, **breakdown_filter_params) else: inner_sql = BREAKDOWN_INNER_SQL.format( breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, ) breakdown_query = BREAKDOWN_QUERY_SQL.format( interval=interval_annotation, num_intervals=num_intervals, inner_sql=inner_sql, ) params.update({ "date_to": filter.date_to.strftime("%Y-%m-%d %H:%M:%S"), "seconds_in_interval": seconds_in_interval, "num_intervals": num_intervals, }) return breakdown_query, params, self._parse_trend_result( filter, entity)
def _format_breakdown_query(self, entity: Entity, filter: Filter, team: Team) -> List[Dict[str, Any]]: params = {"team_id": team.pk} 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) action_query = "" action_params: Dict = {} if entity.type == TREND_FILTER_TYPE_ACTIONS: action = Action.objects.get(pk=entity.id) action_query, action_params = format_action_filter(action) null_sql = NULL_BREAKDOWN_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"), ) params = {**params, **math_params, **prop_filter_params} top_elements_array = [] if filter.breakdown_type == "cohort": breakdown = filter.breakdown if filter.breakdown and isinstance( filter.breakdown, list) else [] if "all" in breakdown: params = {**params, "event": entity.id, **action_params} 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"), ) conditions = BREAKDOWN_CONDITIONS_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="AND {}".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", filters="{filters}".format( filters=prop_filters) if props_to_filter else "", ) breakdown_query = BREAKDOWN_DEFAULT_SQL.format( null_sql=null_sql, conditions=conditions, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, ) else: cohort_queries, cohort_ids, cohort_params = self._format_breakdown_cohort_join_query( breakdown, team) params = { **params, "values": cohort_ids, "event": entity.id, **action_params, **cohort_params } breakdown_filter = BREAKDOWN_COHORT_JOIN_SQL.format( cohort_queries=cohort_queries, parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="AND {}".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", filters="{filters}".format( filters=prop_filters) if props_to_filter else "", ) breakdown_query = BREAKDOWN_QUERY_SQL.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, ) elif filter.breakdown_type == "person": 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=""), ) top_elements_array = self._get_top_elements( elements_query, filter, team) params = { **params, "values": top_elements_array, "key": filter.breakdown, "event": entity.id, **action_params, } breakdown_filter = BREAKDOWN_PERSON_PROP_JOIN_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="AND {}".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", latest_person_sql=GET_LATEST_PERSON_SQL.format(query=""), ) breakdown_query = BREAKDOWN_QUERY_SQL.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, ) else: elements_query = TOP_ELEMENTS_ARRAY_OF_KEY_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to) top_elements_array = self._get_top_elements( elements_query, filter, team) params = { **params, "values": top_elements_array, "key": filter.breakdown, "event": entity.id, **action_params, } breakdown_filter = BREAKDOWN_PROP_JOIN_SQL.format( parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, actions_query="AND {}".format(action_query) if action_query else "", event_filter="AND event = %(event)s" if not action_query else "", filters="{filters}".format( filters=prop_filters) if props_to_filter else "", ) breakdown_query = BREAKDOWN_QUERY_SQL.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, ) try: result = sync_execute(breakdown_query, params) except: result = [] parsed_results = [] for idx, stats in enumerate(result): breakdown_value = stats[ 2] if not filter.breakdown_type == "cohort" else "" stripped_value = breakdown_value.strip('"') if isinstance( breakdown_value, str) else breakdown_value extra_label = self._determine_breakdown_label( idx, filter.breakdown_type, filter.breakdown, stripped_value) label = "{} - {}".format(entity.name, extra_label) additional_values = { "label": label, "breakdown_value": filter.breakdown[idx] if isinstance(filter.breakdown, list) else filter.breakdown if filter.breakdown_type == "cohort" else stripped_value, } parsed_result = self._parse_response(stats, filter, additional_values) parsed_results.append(parsed_result) return parsed_results
def _serialize_lifecycle(self, entity: Entity, filter: Filter, team_id: int) -> List[Dict[str, Any]]: date_from = filter.date_from if not date_from: date_from = get_earliest_timestamp(team_id) interval = filter.interval or "day" num_intervals, seconds_in_interval, _ = get_time_diff( interval, filter.date_from, filter.date_to, team_id) interval_increment, interval_string, sub_interval_string = self.get_interval( interval) trunc_func = get_trunc_func_ch(interval) event_query = "" event_params: Dict[str, Any] = {} props_to_filter = [*filter.properties, *entity.properties] prop_filters, prop_filter_params = parse_prop_clauses( props_to_filter, team_id) _, _, date_params = parse_timestamps(filter=filter, team_id=team_id) if entity.type == TREND_FILTER_TYPE_ACTIONS: try: action = Action.objects.get(pk=entity.id) event_query, event_params = format_action_filter(action) except: return [] else: event_query = "event = %(event)s" event_params = {"event": entity.id} result = sync_execute( LIFECYCLE_SQL.format( interval=interval_string, trunc_func=trunc_func, event_query=event_query, filters=prop_filters, sub_interval=sub_interval_string, ), { "team_id": team_id, "prev_date_from": (date_from - interval_increment).strftime("%Y-%m-%d{}".format( " %H:%M:%S" if filter.interval == "hour" or filter.interval == "minute" else " 00:00:00")), "num_intervals": num_intervals, "seconds_in_interval": seconds_in_interval, **event_params, **date_params, **prop_filter_params, }, ) res = [] for val in result: label = "{} - {}".format(entity.name, val[2]) additional_values = {"label": label, "status": val[2]} parsed_result = parse_response(val, filter, additional_values) res.append(parsed_result) return res
def _format_normal_query(self, entity: Entity, filter: Filter, team: Team) -> List[Dict[str, Any]]: inteval_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) prop_filters, prop_filter_params = parse_prop_clauses( "uuid", filter.properties, 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=inteval_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 filter.properties else "", event_join=join_condition, aggregate_operation=aggregate_operation, ) except: return [] else: content_sql = VOLUME_SQL.format( interval=inteval_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 filter.properties else "", event_join=join_condition, aggregate_operation=aggregate_operation, ) params = {**params, "event": entity.id} null_sql = NULL_SQL.format( interval=inteval_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 _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_id: int) -> 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_id) aggregate_operation, join_condition, math_params = process_math(entity) params: Dict = {"team_id": team_id} params = {**params, **prop_filter_params, **math_params} content_sql_params = { "interval": interval_annotation, "timestamp": "timestamp", "team_id": team_id, "parsed_date_from": parsed_date_from, "parsed_date_to": parsed_date_to, "filters": prop_filters, "event_join": join_condition, "aggregate_operation": aggregate_operation, } 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 content_sql_params = { **content_sql_params, "actions_query": action_query } except: return [] else: content_sql = VOLUME_SQL 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.strftime("%Y-%m-%d %H:%M:%S"), ) content_sql = content_sql.format(**content_sql_params) 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 = parse_response(stats, filter) parsed_results.append(parsed_result) return parsed_results
def _format_breakdown_query( self, entity: Entity, filter: Filter, breakdown: List, team_id: int ) -> List[Dict[str, Any]]: # process params params: Dict[str, Any] = {"team_id": team_id} 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 ) _, 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, table_name="e") aggregate_operation, _, math_params = process_math(entity) if entity.math == "dau" or filter.breakdown_type == "person": join_condition = EVENT_JOIN_PERSON_SQL else: join_condition = "" action_query = "" action_params: Dict = {} if entity.type == TREND_FILTER_TYPE_ACTIONS: action = Action.objects.get(pk=entity.id) action_query, action_params = format_action_filter(action) null_sql = NULL_BREAKDOWN_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"), ) params = { **params, **math_params, **prop_filter_params, **action_params, "event": entity.id, "key": filter.breakdown, **date_params, } breakdown_filter_params = { "parsed_date_from": date_from_clause(interval_annotation, round_interval), "parsed_date_to": parsed_date_to, "actions_query": "AND {}".format(action_query) if action_query else "", "event_filter": "AND event = %(event)s" if not action_query else "", "filters": prop_filters if props_to_filter else "", } breakdown_query = self._get_breakdown_query(filter, breakdown) _params, _breakdown_filter_params = {}, {} if filter.breakdown_type == "cohort": if "all" in breakdown: null_sql = NULL_SQL breakdown_filter = BREAKDOWN_CONDITIONS_SQL breakdown_value = "" else: _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_cohort_params( breakdown, team_id ) elif filter.breakdown_type == "person": _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_person_params( filter, team_id ) else: _params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_prop_params( filter, team_id ) params = {**params, **_params} breakdown_filter_params = {**breakdown_filter_params, **_breakdown_filter_params} if filter.display == TRENDS_TABLE or filter.display == TRENDS_PIE: breakdown_filter = breakdown_filter.format(**breakdown_filter_params) content_sql = breakdown_query.format( breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, breakdown_value=breakdown_value, ) result = sync_execute(content_sql, params) parsed_results = self._parse_single_aggregate_result(result, filter, entity, breakdown) return parsed_results else: 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"), ) breakdown_filter = breakdown_filter.format(**breakdown_filter_params) breakdown_query = breakdown_query.format( null_sql=null_sql, breakdown_filter=breakdown_filter, event_join=join_condition, aggregate_operation=aggregate_operation, interval_annotation=interval_annotation, breakdown_value=breakdown_value, ) try: result = sync_execute(breakdown_query, params) except: result = [] parsed_results = self._parse_trend_result(result, filter, entity, breakdown) return parsed_results