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