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