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 = Action.objects.get(pk=entity.id)
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,
),
{
"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 get_count(self, action: Action) -> Optional[int]:
if self.context.get("view") and self.context["view"].action != "list":
query, params = format_action_filter(action)
if query == "":
return None
return sync_execute("SELECT count(1) FROM events WHERE team_id = %(team_id)s AND {}".format(query), params)[
0
][0]
return None
def _format_stickiness_query(self, entity: Entity, filter: Filter,
team: Team) -> Optional[Dict[str, Any]]:
if not filter.date_to or not filter.date_from:
raise ValueError("_stickiness needs date_to and date_from set")
range_days = (filter.date_to - filter.date_from).days + 2
parsed_date_from, parsed_date_to = parse_timestamps(filter=filter)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team)
params: Dict = {"team_id": team.pk}
params = {**params, **prop_filter_params}
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
action_query, action_params = format_action_filter(action)
if action_query == "":
return None
params = {**params, **action_params}
content_sql = STICKINESS_ACTIONS_SQL.format(
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 "",
)
else:
content_sql = STICKINESS_SQL.format(
team_id=team.pk,
event=entity.id,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters="{filters}".format(
filters=prop_filters) if filter.properties else "",
)
aggregated_counts = sync_execute(content_sql, params)
response: Dict[int, int] = {}
for result in aggregated_counts:
response[result[1]] = result[0]
labels = []
data = []
for day in range(1, range_days):
label = "{} day{}".format(day, "s" if day > 1 else "")
labels.append(label)
data.append(response[day] if day in response else 0)
return {
"labels": labels,
"days": [day for day in range(1, range_days)],
"data": data,
"count": sum(data),
}
def query_action(action: Action) -> Optional[List]:
formatted_query, params = format_action_filter(action, "")
query = ACTION_QUERY.format(action_filter=formatted_query)
if query:
return sync_execute(query, {"team_id": action.team_id, **params})
return None
def query_action(action: Action) -> Optional[List]:
formatted_query, params = format_action_filter(action, "", 0)
query = ACTION_QUERY.format(action_filter=formatted_query)
if query:
return sync_execute(query, params)
return None
def _format_entity_filter(entity: Entity) -> Tuple[str, Dict]:
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
action_query, params = format_action_filter(action)
entity_filter = "AND {}".format(action_query)
else:
entity_filter = "AND event = %(event)s"
params = {"event": entity.id}
return entity_filter, params
def _query_events_list(self,
filter: Filter,
team: Team,
request: Request,
long_date_from: bool = False,
limit: int = 100) -> List:
limit += 1
limit_sql = "LIMIT %(limit)s"
conditions, condition_params = determine_event_conditions(
team,
{
"after": (now() - timedelta(days=1)).isoformat(),
"before": (now() + timedelta(seconds=5)).isoformat(),
**request.GET.dict(),
},
long_date_from,
)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
if request.GET.get("action_id"):
try:
action = Action.objects.get(pk=request.GET["action_id"],
team_id=team.pk)
except Action.DoesNotExist:
return []
if action.steps.count() == 0:
return []
action_query, params = format_action_filter(action)
prop_filters += " AND {}".format(action_query)
prop_filter_params = {**prop_filter_params, **params}
if prop_filters != "":
return sync_execute(
SELECT_EVENT_WITH_PROP_SQL.format(conditions=conditions,
limit=limit_sql,
filters=prop_filters),
{
"team_id": team.pk,
"limit": limit,
**condition_params,
**prop_filter_params
},
)
else:
return sync_execute(
SELECT_EVENT_WITH_ARRAY_PROPS_SQL.format(conditions=conditions,
limit=limit_sql),
{
"team_id": team.pk,
"limit": limit,
**condition_params
},
)
def format_person_query(cohort: Cohort) -> Tuple[str, Dict[str, Any]]:
filters = []
params: Dict[str, Any] = {}
if cohort.is_static:
return (
"person_id IN (SELECT person_id FROM {} WHERE cohort_id = %(cohort_id)s AND team_id = %(team_id)s)"
.format(PERSON_STATIC_COHORT_TABLE),
{
"cohort_id": cohort.pk,
"team_id": cohort.team_id
},
)
or_queries = []
for group_idx, group in enumerate(cohort.groups):
if group.get("action_id"):
action = Action.objects.get(pk=group["action_id"],
team_id=cohort.team.pk)
action_filter_query, action_params = format_action_filter(
action, prepend="_{}_action".format(group_idx))
date_query: str = ""
date_params: Dict[str, str] = {}
if group.get("days"):
date_query, date_params = parse_action_timestamps(
int(group.get("days")))
extract_person = "SELECT distinct_id FROM events WHERE team_id = %(team_id)s {date_query} AND {query}".format(
query=action_filter_query, date_query=date_query)
params = {**params, **action_params, **date_params}
filters.append("distinct_id IN (" + extract_person + ")")
elif group.get("properties"):
from ee.clickhouse.models.property import prop_filter_json_extract
filter = Filter(data=group)
query = ""
for idx, prop in enumerate(filter.properties):
filter_query, filter_params = prop_filter_json_extract(
prop=prop,
idx=idx,
prepend="{}_{}_{}_person".format(cohort.pk, group_idx,
idx))
params = {**params, **filter_params}
query += filter_query
or_queries.append(query.replace("AND ", "", 1))
if len(or_queries) > 0:
query = "AND ({})".format(" OR ".join(or_queries))
filters.append("person_id IN {}".format(
GET_LATEST_PERSON_ID_SQL.format(query=query)))
joined_filter = " OR ".join(filters)
return joined_filter, params
def _get_condition(self, target_entity: Entity, table: str, prepend: str = "") -> Tuple[str, Dict]:
if target_entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=target_entity.id)
action_query, params = format_action_filter(action, prepend=prepend, use_loop=False)
condition = action_query
elif target_entity.type == TREND_FILTER_TYPE_EVENTS:
condition = "{}.event = %({}_event)s".format(table, prepend)
params = {"{}_event".format(prepend): target_entity.id}
else:
condition = "{}.event = %({}_event)s".format(table, prepend)
params = {"{}_event".format(prepend): "$pageview"}
return condition, params
def _get_entity_query(event_id: Optional[str], action_id: Optional[int],
team_id: int,
group_idx: int) -> Tuple[str, Dict[str, str]]:
if event_id:
return "event = %(event)s", {"event": event_id}
elif action_id:
action = Action.objects.get(pk=action_id, team_id=team_id)
action_filter_query, action_params = format_action_filter(
action, prepend="_{}_action".format(group_idx))
return action_filter_query, action_params
else:
raise ValidationError("Cohort query requires action_id or event_id")
def _get_events_for_action(action: Action) -> List[MockEvent]:
formatted_query, params = format_action_filter(team_id=action.team_id, action=action, prepend="")
query = f"""
SELECT
events.uuid,
events.distinct_id
FROM events
WHERE {formatted_query}
AND events.team_id = %(team_id)s
ORDER BY events.timestamp DESC
"""
events = sync_execute(query, {"team_id": action.team_id, **params})
return [MockEvent(str(uuid), distinct_id) for uuid, distinct_id in events]
def _format_entity_filter(self, entity: Entity) -> Tuple[str, Dict]:
if entity.type == TREND_FILTER_TYPE_ACTIONS:
try:
action = Action.objects.get(pk=entity.id)
action_query, params = format_action_filter(action)
entity_filter = "AND {}".format(action_query)
except Action.DoesNotExist:
raise ValueError("This action does not exist")
else:
entity_filter = "AND event = %(event)s"
params = {"event": entity.id}
return entity_filter, params
def _populate_entity_params(self, entity: Entity) -> Tuple[Dict, Dict]:
params, content_sql_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 = {**action_params}
content_sql_params = {"actions_query": action_query}
except:
raise ValueError("Action does not exist")
else:
params = {"event": entity.id}
return params, content_sql_params
def list(self, request: Request, *args: Any, **kwargs: Any) -> Response:
team = request.user.team
assert team is not None
filter = Filter(request=request)
if request.GET.get("after"):
filter._date_from = request.GET["after"]
if request.GET.get("before"):
filter._date_to = request.GET["before"]
limit = "LIMIT 101"
conditions, condition_params = determine_event_conditions(request.GET.dict())
prop_filters, prop_filter_params = parse_prop_clauses(filter.properties, team.pk)
if request.GET.get("action_id"):
action = Action.objects.get(pk=request.GET["action_id"])
if action.steps.count() == 0:
return Response({"next": False, "results": []})
action_query, params = format_action_filter(action)
prop_filters += " AND {}".format(action_query)
prop_filter_params = {**prop_filter_params, **params}
if prop_filters != "":
query_result = sync_execute(
SELECT_EVENT_WITH_PROP_SQL.format(conditions=conditions, limit=limit, filters=prop_filters),
{"team_id": team.pk, **condition_params, **prop_filter_params},
)
else:
query_result = sync_execute(
SELECT_EVENT_WITH_ARRAY_PROPS_SQL.format(conditions=conditions, limit=limit),
{"team_id": team.pk, **condition_params},
)
result = ClickhouseEventSerializer(
query_result[0:100], many=True, context={"people": self._get_people(query_result, team),},
).data
if len(query_result) > 100:
path = request.get_full_path()
reverse = request.GET.get("orderBy", "-timestamp") != "-timestamp"
next_url: Optional[str] = request.build_absolute_uri(
"{}{}{}={}".format(
path,
"&" if "?" in path else "?",
"after" if reverse else "before",
query_result[99][3].strftime("%Y-%m-%dT%H:%M:%S.%fZ"),
)
)
else:
next_url = None
return Response({"next": next_url, "results": result})
def count(self, request: Request, **kwargs) -> Response: # type: ignore
action = self.get_object()
query, params = format_action_filter(action)
if query == "":
return Response({"count": 0})
results = sync_execute(
"SELECT count(1) FROM events WHERE team_id = %(team_id)s AND {}".
format(query),
{
"team_id": action.team_id,
**params
},
)
return Response({"count": results[0][0]})
def populate_entity_params(entity: Entity) -> Tuple[Dict, Dict]:
params, content_sql_params = {}, {}
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
action_query, action_params = format_action_filter(action)
params = {**action_params}
content_sql_params = {
"entity_query":
"AND {action_query}".format(action_query=action_query)
}
else:
content_sql_params = {"entity_query": "AND event = %(event)s"}
params = {"event": entity.id}
return params, content_sql_params
def _get_entity_query(self, entity: Entity):
prepend = self._event_query_type
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
action_query, params = format_action_filter(action,
prepend=prepend,
use_loop=False)
condition = action_query
elif entity.type == TREND_FILTER_TYPE_EVENTS:
condition = f"{self.EVENT_TABLE_ALIAS}.event = %({prepend}_event)s"
params = {f"{prepend}_event": entity.id}
else:
condition = f"{self.EVENT_TABLE_ALIAS}.event = %({prepend}_event)s"
params = {f"{prepend}_event": PAGEVIEW_EVENT}
return condition, params
def entity_query_conditions(filter: Filter, team: Team) -> Tuple[List[str], Dict]:
entity_conditions = []
params: Dict[str, Any] = {}
for index, entity in enumerate(filter.entities):
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
action_query, action_params = format_action_filter(action, prepend=f"action_{index}")
entity_conditions.append(action_query)
params = {**params, **action_params}
else:
event_query, event_params = event_entity_to_query(entity, team, prepend=f"event_{index}")
entity_conditions.append(event_query)
params = {**params, **event_params}
return entity_conditions, params
def _build_steps_query(self, entity: Entity, index: int) -> str:
filters = self._build_filters(entity, index)
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
for action_step in action.steps.all():
self.params["events"].append(action_step.event)
action_query, action_params = format_action_filter(action, "step_{}".format(index))
if action_query == "":
return ""
self.params.update(action_params)
content_sql = "{actions_query} {filters}".format(actions_query=action_query, filters=filters,)
else:
self.params["events"].append(entity.id)
content_sql = "event = '{event}' {filters}".format(event=entity.id, filters=filters)
return content_sql
def stickiness(self, entity: Entity, filter: StickinessFilter,
team_id: int) -> Dict[str, Any]:
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team_id)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team_id,
filter_test_accounts=filter.filter_test_accounts)
trunc_func = get_trunc_func_ch(filter.interval)
params: Dict = {"team_id": team_id}
params = {
**params,
**prop_filter_params, "num_intervals": filter.total_intervals
}
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
action_query, action_params = format_action_filter(action)
if action_query == "":
return {}
params = {**params, **action_params}
content_sql = STICKINESS_ACTIONS_SQL.format(
team_id=team_id,
actions_query=action_query,
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
filters=prop_filters,
trunc_func=trunc_func,
GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS,
)
else:
content_sql = STICKINESS_SQL.format(
team_id=team_id,
event=entity.id,
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
filters=prop_filters,
trunc_func=trunc_func,
GET_TEAM_PERSON_DISTINCT_IDS=GET_TEAM_PERSON_DISTINCT_IDS,
)
counts = sync_execute(content_sql, params)
return self.process_result(counts, filter)
def get_count(self, action: Action) -> Optional[int]:
if self.context.get("view") and self.context["view"].action != "list":
query, params = format_action_filter(action)
if query == "":
return None
try:
return sync_execute(
"SELECT count(1) FROM events WHERE team_id = %(team_id)s AND {}"
.format(query),
{
"team_id": action.team_id,
**params
},
)[0][0]
except Exception as e:
capture_exception(e)
return None
return None
def count(self, request: request.Request, **kwargs) -> Response:
action = self.get_object()
query, params = format_action_filter(team_id=action.team_id, action=action)
if query == "":
return Response({"count": 0})
results = sync_execute(
"SELECT count(1) FROM events WHERE team_id = %(team_id)s AND timestamp < %(before)s AND timestamp > %(after)s AND {}".format(
query
),
{
"team_id": action.team_id,
"before": now().strftime("%Y-%m-%d %H:%M:%S.%f"),
"after": (now() - relativedelta(months=3)).strftime("%Y-%m-%d %H:%M:%S.%f"),
**params,
},
)
return Response({"count": results[0][0]})
def _query_events_list(
self, filter: Filter, team: Team, request: request.Request, long_date_from: bool = False, limit: int = 100
) -> List:
limit += 1
limit_sql = "LIMIT %(limit)s"
order = "DESC" if self._parse_order_by(self.request)[0] == "-timestamp" else "ASC"
conditions, condition_params = determine_event_conditions(
team,
{
"after": (now() - timedelta(days=1)).isoformat(),
"before": (now() + timedelta(seconds=5)).isoformat(),
**request.GET.dict(),
},
long_date_from,
)
prop_filters, prop_filter_params = parse_prop_grouped_clauses(
team_id=team.pk, property_group=filter.property_groups, has_person_id_joined=False
)
if request.GET.get("action_id"):
try:
action = Action.objects.get(pk=request.GET["action_id"], team_id=team.pk)
except Action.DoesNotExist:
return []
if action.steps.count() == 0:
return []
action_query, params = format_action_filter(team_id=team.pk, action=action)
prop_filters += " AND {}".format(action_query)
prop_filter_params = {**prop_filter_params, **params}
if prop_filters != "":
return sync_execute(
SELECT_EVENT_BY_TEAM_AND_CONDITIONS_FILTERS_SQL.format(
conditions=conditions, limit=limit_sql, filters=prop_filters, order=order
),
{"team_id": team.pk, "limit": limit, **condition_params, **prop_filter_params},
)
else:
return sync_execute(
SELECT_EVENT_BY_TEAM_AND_CONDITIONS_SQL.format(conditions=conditions, limit=limit_sql, order=order),
{"team_id": team.pk, "limit": limit, **condition_params},
)
def get_entity_filtering_params(
entity: Entity,
team_id: int,
table_name: str = "",
*,
person_properties_mode: PersonPropertiesMode = PersonPropertiesMode.USING_PERSON_PROPERTIES_COLUMN,
) -> Tuple[Dict, Dict]:
params: Dict[str, Any] = {}
content_sql_params: Dict[str, str]
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
action_query, action_params = format_action_filter(
team_id=team_id, action=action, table_name=table_name, person_properties_mode=person_properties_mode,
)
params.update(action_params)
content_sql_params = {"entity_query": f"AND {action_query}"}
else:
params["event"] = entity.id
content_sql_params = {"entity_query": f"AND event = %(event)s"}
return params, content_sql_params
def format_filter_query(cohort: Cohort) -> Tuple[str, Dict]:
filters = []
params: Dict[str, Any] = {}
for group in cohort.groups:
if group.get("action_id"):
action = Action.objects.get(pk=group["action_id"], team_id=cohort.team.pk)
action_filter_query, action_params = format_action_filter(action)
extract_person = "SELECT distinct_id FROM events WHERE uuid IN ({query})".format(query=action_filter_query)
params = {**params, **action_params}
filters.append("(" + extract_person + ")")
elif group.get("properties"):
filter = Filter(data=group)
prop_filter = filter.format_ch(team_id=cohort.team.pk)
extract_distinct_id = "SELECT distinct_id FROM person_distinct_id WHERE person_id IN ({query})".format(
query=PERSON_PROPERTY_FILTER_SQL.format(filters=prop_filter)
)
filters.append("(" + extract_distinct_id + ")")
separator = " OR distinct_id IN "
joined_filter = separator.join(filters)
person_id_query = CALCULATE_COHORT_PEOPLE_SQL.format(query=joined_filter)
return person_id_query, params
def format_filter_query(cohort: Cohort) -> Tuple[str, Dict[str, Any]]:
filters = []
params: Dict[str, Any] = {}
for group_idx, group in enumerate(cohort.groups):
if group.get("action_id"):
action = Action.objects.get(pk=group["action_id"],
team_id=cohort.team.pk)
action_filter_query, action_params = format_action_filter(action)
extract_person = "SELECT distinct_id FROM events WHERE uuid IN ({query})".format(
query=action_filter_query)
params = {**params, **action_params}
filters.append("(" + extract_person + ")")
elif group.get("properties"):
filter = Filter(data=group)
for idx, prop in enumerate(filter.properties):
prepend = "{}_cohort_group_{}".format(cohort.pk, group_idx)
arg = "v{}_{}".format(prepend, idx)
operator_clause, value = get_operator(prop, arg)
prop_filters = "(ep.key = %(k{prepend}_{idx})s) AND {operator_clause}".format(
idx=idx, operator_clause=operator_clause, prepend=prepend)
clause = GET_DISTINCT_IDS_BY_PROPERTY_SQL.format(
filters=prop_filters,
negation="NOT "
if prop.operator and "not" in prop.operator else "")
filters.append("(" + clause + ")")
params.update({
"k{}_{}".format(prepend, idx): prop.key,
arg: value
})
separator = " OR distinct_id IN "
joined_filter = separator.join(filters)
person_id_query = CALCULATE_COHORT_PEOPLE_SQL.format(query=joined_filter)
return person_id_query, params
def _build_step_query(self, entity: Entity, index: int,
entity_name: str) -> str:
filters = self._build_filters(entity, index)
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
for action_step in action.steps.all():
self.params[entity_name].append(action_step.event)
action_query, action_params = format_action_filter(
action, "{}_step_{}".format(entity_name, index))
if action_query == "":
return ""
self.params.update(action_params)
content_sql = "{actions_query} {filters}".format(
actions_query=action_query,
filters=filters,
)
else:
self.params[entity_name].append(entity.id)
event_param_key = f"{entity_name}_event_{index}"
self.params[event_param_key] = entity.id
content_sql = f"event = %({event_param_key})s {filters}"
return content_sql
def _build_steps_query(self, entity: Entity, index: int) -> str:
parsed_date_from, parsed_date_to = parse_timestamps(
filter=self._filter)
is_first_step = (
"timestamp <> toDateTime(0)" if index == 0 else
"step_{prev_step} <> toDateTime(0) AND timestamp >= step_{prev_step}"
.format(prev_step=index - 1))
filters = self._build_filters(entity, index)
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
action_query, action_params = format_action_filter(
action, "step_{}".format(index))
if action_query == "":
return ""
self.params.update(action_params)
content_sql = STEP_ACTION_SQL.format(
team_id=self._team.pk,
actions_query=action_query,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters=filters,
step=index,
is_first_step=is_first_step,
)
else:
content_sql = STEP_EVENT_SQL.format(
team_id=self._team.pk,
event=entity.id,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters=filters,
step=index,
is_first_step=is_first_step,
)
return content_sql
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_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)
