def _retrieve_people(self, target_entity: Entity, filter: StickinessFilter,
team: Team) -> ReturnDict:
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team.pk)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
entity_sql, entity_params = self._format_entity_filter(
entity=target_entity)
trunc_func = get_trunc_func_ch(filter.interval)
params: Dict = {
"team_id": team.pk,
**prop_filter_params,
"stickiness_day": filter.selected_interval,
**entity_params,
"offset": filter.offset,
}
content_sql = STICKINESS_PEOPLE_SQL.format(
entity_filter=entity_sql,
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
filters=prop_filters,
trunc_func=trunc_func,
)
people = sync_execute(
PEOPLE_SQL.format(
content_sql=content_sql,
query="",
latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")),
params,
)
return ClickhousePersonSerializer(people, many=True).data
def _calculate_entity_people(self, team: Team, entity: Entity,
filter: Filter):
parsed_date_from, parsed_date_to = parse_timestamps(filter=filter)
prop_filters, prop_filter_params = parse_prop_clauses(
"uuid", filter.properties, team)
entity_sql, entity_params = self._format_entity_filter(entity=entity)
params: Dict = {
"team_id": team.pk,
**prop_filter_params,
**entity_params, "offset": filter.offset
}
content_sql = PERSON_TREND_SQL.format(
entity_filter=entity_sql,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters="{filters}".format(
filters=prop_filters) if filter.properties else "",
breakdown_filter="",
)
people = sync_execute(
PEOPLE_THROUGH_DISTINCT_SQL.format(content_sql=content_sql),
params)
serialized_people = ClickhousePersonSerializer(people, many=True).data
return serialized_people
def _calculate_stickiness_entity_people(self, team: Team, entity: Entity,
filter: Filter,
stickiness_day: int):
parsed_date_from, parsed_date_to = parse_timestamps(filter=filter)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
entity_sql, entity_params = self._format_entity_filter(entity=entity)
params: Dict = {
"team_id": team.pk,
**prop_filter_params,
"stickiness_day": stickiness_day,
**entity_params,
"offset": filter.offset,
}
content_sql = STICKINESS_PEOPLE_SQL.format(
entity_filter=entity_sql,
parsed_date_from=(parsed_date_from or ""),
parsed_date_to=(parsed_date_to or ""),
filters="{filters}".format(
filters=prop_filters) if filter.properties else "",
)
people = sync_execute(
PEOPLE_SQL.format(
content_sql=content_sql,
query="",
latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")),
params,
)
serialized_people = ClickhousePersonSerializer(people, many=True).data
return serialized_people
def _retrieve_people(self, filter: RetentionFilter, team: Team):
period = filter.period
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
trunc_func = get_trunc_func_ch(period)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
returning_entity = filter.returning_entity if filter.selected_interval > 0 else filter.target_entity
target_query, target_params = self._get_condition(filter.target_entity,
table="e")
target_query_formatted = "AND {target_query}".format(
target_query=target_query)
return_query, return_params = self._get_condition(returning_entity,
table="e",
prepend="returning")
return_query_formatted = "AND {return_query}".format(
return_query=return_query)
reference_event_query = (REFERENCE_EVENT_UNIQUE_SQL
if is_first_time_retention else
REFERENCE_EVENT_SQL).format(
target_query=target_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
)
reference_date_from = filter.date_from
reference_date_to = filter.date_from + filter.period_increment
date_from = filter.date_from + filter.selected_interval * filter.period_increment
date_to = date_from + filter.period_increment
result = sync_execute(
RETENTION_PEOPLE_SQL.format(
reference_event_query=reference_event_query,
target_query=return_query_formatted,
filters=prop_filters),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_start_date":
reference_date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_end_date":
reference_date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"offset":
filter.offset,
**target_params,
**return_params,
**prop_filter_params,
},
)
serialized = ClickhousePersonSerializer(result, many=True).data
return serialized
def retrieve(self, request, pk=None):
if not endpoint_enabled(CH_PERSON_ENDPOINT, request.user.distinct_id):
return super().retrieve(request, pk)
qres = sync_execute(PEOPLE_SQL.format(content_sql=[pk]), {"offset": 0})
res = ClickhousePersonSerializer(qres[0]).data if len(qres) > 0 else []
return Response(res)
def list(self, request):
if not endpoint_enabled(CH_PERSON_ENDPOINT, request.user.distinct_id):
return super().list(request)
team = self.request.user.team_set.get()
filtered = self._ch_filter_request(self.request, team)
results = ClickhousePersonSerializer(filtered, many=True).data
return Response({"results": results})
def retrieve_stickiness_people(target_entity: Entity, filter: StickinessFilter, team: Team) -> ReturnDict:
content_sql, params = _process_content_sql(target_entity, filter, team)
people = sync_execute(
PEOPLE_SQL.format(content_sql=content_sql, query="", latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")),
params,
)
return ClickhousePersonSerializer(people, many=True).data
def calculate_entity_people(team: Team, entity: Entity, filter: Filter):
content_sql, params = _process_content_sql(team, entity, filter)
people = sync_execute(
PEOPLE_THROUGH_DISTINCT_SQL.format(
content_sql=content_sql, latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")
),
params,
)
serialized_people = ClickhousePersonSerializer(people, many=True).data
return serialized_people
def _calculate_entity_people(self, team: Team, entity: Entity,
filter: Filter):
parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter,
team_id=team.pk)
entity_sql, entity_params = format_entity_filter(entity=entity)
person_filter = ""
person_filter_params: Dict[str, Any] = {}
if filter.breakdown_type == "cohort" and filter.breakdown_value != "all":
cohort = Cohort.objects.get(pk=filter.breakdown_value)
person_filter, person_filter_params = format_filter_query(cohort)
person_filter = "AND distinct_id IN ({})".format(person_filter)
elif (filter.breakdown_type == "person"
and isinstance(filter.breakdown, str)
and isinstance(filter.breakdown_value, str)):
person_prop = Property(
**{
"key": filter.breakdown,
"value": filter.breakdown_value,
"type": "person"
})
filter.properties.append(person_prop)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
params: Dict = {
"team_id": team.pk,
**prop_filter_params,
**entity_params, "offset": filter.offset
}
content_sql = PERSON_TREND_SQL.format(
entity_filter=f"AND {entity_sql}",
parsed_date_from=parsed_date_from,
parsed_date_to=parsed_date_to,
filters=prop_filters,
breakdown_filter="",
person_filter=person_filter,
)
people = sync_execute(
PEOPLE_THROUGH_DISTINCT_SQL.format(
content_sql=content_sql,
latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")),
{
**params,
**person_filter_params
},
)
serialized_people = ClickhousePersonSerializer(people, many=True).data
return serialized_people
def by_distinct_id(self, request):
if not endpoint_enabled(CH_PERSON_ENDPOINT, request.user.distinct_id):
result = super().get_by_distinct_id(request)
return Response(result)
distinct_id = str(request.GET["distinct_id"])
result = sync_execute(
PEOPLE_THROUGH_DISTINCT_SQL.format(content_sql=[distinct_id]),
{"offset": 0})
res = ClickhousePersonSerializer(
result[0]).data if len(result) > 0 else []
return Response(res)
def calculate_entity_people(team: Team, entity: Entity, filter: Filter):
content_sql, params = _process_content_sql(team, entity, filter)
people = sync_execute(
(PEOPLE_SQL if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE] else
PEOPLE_THROUGH_DISTINCT_SQL).format(
content_sql=content_sql,
latest_person_sql=GET_LATEST_PERSON_SQL.format(query=""),
latest_distinct_id_sql=GET_LATEST_PERSON_DISTINCT_ID_SQL,
),
params,
)
serialized_people = ClickhousePersonSerializer(people, many=True).data
return serialized_people
def _retrieve_people_in_period(self, filter: RetentionFilter, team: Team):
period = filter.period
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
trunc_func = get_trunc_func_ch(period)
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
returning_entity = filter.returning_entity if filter.selected_interval > 0 else filter.target_entity
target_query, target_params = self._get_condition(filter.target_entity,
table="e")
target_query_formatted = "AND {target_query}".format(
target_query=target_query)
return_query, return_params = self._get_condition(returning_entity,
table="e",
prepend="returning")
return_query_formatted = "AND {return_query}".format(
return_query=return_query)
first_event_sql = (REFERENCE_EVENT_UNIQUE_PEOPLE_PER_PERIOD_SQL
if is_first_time_retention else
REFERENCE_EVENT_PEOPLE_PER_PERIOD_SQL).format(
target_query=target_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
)
default_event_query = (
DEFAULT_REFERENCE_EVENT_UNIQUE_PEOPLE_PER_PERIOD_SQL
if is_first_time_retention else
DEFAULT_REFERENCE_EVENT_PEOPLE_PER_PERIOD_SQL).format(
target_query=target_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
)
date_from = filter.date_from + filter.selected_interval * filter.period_increment
date_to = filter.date_to
query_result = sync_execute(
RETENTION_PEOPLE_PER_PERIOD_SQL.format(
returning_query=return_query_formatted,
filters=prop_filters,
first_event_sql=first_event_sql,
first_event_default_sql=default_event_query,
trunc_func=trunc_func,
),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"offset":
filter.offset,
"limit":
100,
"period":
period,
**target_params,
**return_params,
**prop_filter_params,
},
)
people_dict = {}
all_people = sync_execute("SELECT * FROM person WHERE id IN %(uuids)s",
{"uuids": [val[0] for val in query_result]})
for person in all_people:
people_dict.update(
{person[0]: ClickhousePersonSerializer(person).data})
result = self.process_people_in_period(filter, query_result,
people_dict)
return result
