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 _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 _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, 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 _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