def _run_formula_query(self, filter: Filter, team_id: int): letters = [chr(65 + i) for i in range(0, len(filter.entities))] queries = [] for entity in filter.entities: sql, params, _ = self._get_sql_for_entity(filter, entity, team_id) # type: ignore queries.append(substitute_params(sql, params)) breakdown_value = (", sub_A.breakdown_value" if filter.breakdown_type == "cohort" else ", trim(BOTH '\"' FROM sub_A.breakdown_value)") is_aggregate = filter.display in [TRENDS_TABLE, TRENDS_PIE] sql = """SELECT {date_select} arrayMap(({letters_select}) -> {formula}, {selects}) {breakdown_value} FROM ({first_query}) as sub_A {queries} """.format( date_select="'' as date," if is_aggregate else "sub_A.date,", letters_select=", ".join(letters), formula=filter. formula, # formula is properly escaped in the filter # Need to wrap aggregates in arrays so we can still use arrayMap selects=", ".join([ ("[sub_{}.data]" if is_aggregate else "sub_{}.data").format( letters[i]) for i in range(0, len(filter.entities)) ]), breakdown_value=breakdown_value if filter.breakdown else "", first_query=queries[0], queries="".join([ "FULL OUTER JOIN ({query}) as sub_{letter} ON sub_A.breakdown_value = sub_{letter}.breakdown_value " .format(query=query, letter=letters[i + 1]) for i, query in enumerate(queries[1:]) ]) if filter.breakdown else "".join([ " CROSS JOIN ({}) as sub_{}".format(query, letters[i + 1]) for i, query in enumerate(queries[1:]) ]), ) result = sync_execute(sql) response = [] for item in result: additional_values: Dict[str, Any] = { "label": self._label(filter, item, team_id), } if is_aggregate: additional_values["data"] = [] additional_values["aggregated_value"] = item[1][0] else: additional_values["data"] = [ round(number, 2) if not math.isnan(number) and not math.isinf(number) else 0.0 for number in item[1] ] if filter.display == TRENDS_CUMULATIVE: additional_values["data"] = list( accumulate(additional_values["data"])) additional_values["count"] = float(sum(additional_values["data"])) response.append(parse_response(item, filter, additional_values)) return response
def _parse(result: List) -> List: parsed_results = [] for _, stats in enumerate(result): parsed_result = parse_response(stats, filter) parsed_results.append(parsed_result) return parsed_results
def _parse(result: List) -> List: parsed_results = [] for idx, stats in enumerate(result): additional_values = self._breakdown_result_descriptors(stats[2], filter, entity) parsed_result = parse_response(stats, filter, additional_values) parsed_results.append(parsed_result) return sorted(parsed_results, key=lambda x: 0 if x.get("breakdown_value") != "all" else 1)
def _parse(result: List) -> List: 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 _parse_trend_result( self, result, filter: Filter, entity: Entity, breakdown: Union[str, List[Union[str, int]], None] ): parsed_results = [] for idx, stats in enumerate(result): breakdown_value = stats[2] if not filter.breakdown_type == "cohort" else "" additional_values = self._breakdown_result_descriptors(breakdown_value, idx, filter, entity, breakdown) parsed_result = parse_response(stats, filter, additional_values) parsed_results.append(parsed_result) return parsed_results
def _parse(result: List) -> List: parsed_results = [] for _, stats in enumerate(result): parsed_result = parse_response(stats, filter) parsed_result.update({ "persons_urls": self._get_persons_url(filter, entity, team_id, parsed_result["days"]) }) parsed_results.append(parsed_result) parsed_result.update({"filter": filter.to_dict()}) return parsed_results
def _parse(result: List) -> List: parsed_results = [] for idx, stats in enumerate(result): result_descriptors = self._breakdown_result_descriptors(stats[2], filter, entity) parsed_result = parse_response(stats, filter, result_descriptors) parsed_result.update( { "persons_urls": self._get_persons_url( filter, entity, self.team_id, parsed_result["days"], result_descriptors["breakdown_value"] ) } ) parsed_results.append(parsed_result) parsed_result.update({"filter": filter.to_dict()}) return sorted(parsed_results, key=lambda x: 0 if x.get("breakdown_value") != "all" else 1)
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_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_id: int) -> List[Dict[str, Any]]: # process params params: Dict[str, Any] = {"team_id": team_id} 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, table_name="e") aggregate_operation, join_condition, math_params = 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).strftime("%Y-%m-%d %H:%M:%S"), ) params = { **params, **math_params, **prop_filter_params, **action_params, "event": entity.id, "key": filter.breakdown, } top_elements_array = [] breakdown_filter_params = { "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": prop_filters if props_to_filter else "", } if filter.breakdown_type == "cohort": breakdown = filter.breakdown if filter.breakdown and isinstance( filter.breakdown, list) else [] if "all" in breakdown: null_sql = NULL_SQL breakdown_filter = BREAKDOWN_CONDITIONS_SQL breakdown_query = BREAKDOWN_DEFAULT_SQL else: cohort_queries, cohort_ids, cohort_params = self._format_breakdown_cohort_join_query( breakdown, team_id) params = {**params, "values": cohort_ids, **cohort_params} breakdown_filter = BREAKDOWN_COHORT_JOIN_SQL breakdown_filter_params = { **breakdown_filter_params, "cohort_queries": cohort_queries } breakdown_query = BREAKDOWN_QUERY_SQL 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_id) params = { **params, "values": top_elements_array, } breakdown_filter = BREAKDOWN_PERSON_PROP_JOIN_SQL breakdown_filter_params = { **breakdown_filter_params, "latest_person_sql": GET_LATEST_PERSON_SQL.format(query=""), } breakdown_query = BREAKDOWN_QUERY_SQL 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_id) params = { **params, "values": top_elements_array, } breakdown_filter = BREAKDOWN_PROP_JOIN_SQL breakdown_query = BREAKDOWN_QUERY_SQL 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, ) 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 = parse_response(stats, filter, additional_values) parsed_results.append(parsed_result) return parsed_results
def _run_formula_query(self, filter: Filter, team_id: int): letters = [chr(65 + i) for i in range(0, len(filter.entities))] queries = [] params: Dict[str, Any] = {} for idx, entity in enumerate(filter.entities): sql, entity_params, _ = self._get_sql_for_entity(filter, entity, team_id) # type: ignore sql = sql.replace("%(", f"%({idx}_") entity_params = {f"{idx}_{key}": value for key, value in entity_params.items()} queries.append(sql) params = {**params, **entity_params} breakdown_value = ( ", sub_A.breakdown_value" if filter.breakdown_type == "cohort" else ", trim(BOTH '\"' FROM sub_A.breakdown_value)" ) is_aggregate = filter.display in TRENDS_DISPLAY_BY_VALUE sql = """SELECT {date_select} arrayMap(({letters_select}) -> {formula}, {selects}) {breakdown_value} {max_length} FROM ({first_query}) as sub_A {queries} """.format( date_select="'' as date," if is_aggregate else "sub_A.date,", letters_select=", ".join(letters), formula=filter.formula, # formula is properly escaped in the filter # Need to wrap aggregates in arrays so we can still use arrayMap selects=", ".join( [ (f"[sub_{letter}.data]" if is_aggregate else f"arrayResize(sub_{letter}.data, max_length, 0)") for letter in letters ] ), breakdown_value=breakdown_value if filter.breakdown else "", max_length="" if is_aggregate else ", arrayMax([{}]) as max_length".format(", ".join(f"length(sub_{letter}.data)" for letter in letters)), first_query=queries[0], queries="".join( [ "FULL OUTER JOIN ({query}) as sub_{letter} ON sub_A.breakdown_value = sub_{letter}.breakdown_value ".format( query=query, letter=letters[i + 1] ) for i, query in enumerate(queries[1:]) ] ) if filter.breakdown else "".join( [" CROSS JOIN ({}) as sub_{}".format(query, letters[i + 1]) for i, query in enumerate(queries[1:])] ), ) result = sync_execute(sql, params) response = [] for item in result: additional_values: Dict[str, Any] = { "label": self._label(filter, item, team_id), } if is_aggregate: additional_values["data"] = [] additional_values["aggregated_value"] = item[1][0] else: additional_values["data"] = [ round(number, 2) if not math.isnan(number) and not math.isinf(number) else 0.0 for number in item[1] ] if filter.display == TRENDS_CUMULATIVE: additional_values["data"] = list(accumulate(additional_values["data"])) additional_values["count"] = float(sum(additional_values["data"])) response.append(parse_response(item, filter, additional_values)) return response
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