def get_aggregate_total(query: QuerySet, entity: Entity) -> int:
entity_total = 0
if entity.math == "dau":
_query, _params = query.query.sql_with_params()
with connection.cursor() as cursor:
cursor.execute(
"SELECT count(DISTINCT person_id) FROM ({}) as aggregates".
format(_query), _params)
entity_total = cursor.fetchall()[0][0]
elif entity.math in MATH_TO_AGGREGATE_FUNCTION:
query = query.annotate(math_prop=Cast(
RawSQL('"posthog_event"."properties"->>%s', (
entity.math_property, )),
output_field=FloatField(),
))
query = query.extra(
where=[
'jsonb_typeof("posthog_event"."properties"->%s) = \'number\''
],
params=[entity.math_property],
)
_query, _params = query.query.sql_with_params()
with connection.cursor() as cursor:
agg_func = MATH_TO_AGGREGATE_STRING[entity.math].format(
math_prop="math_prop")
cursor.execute(
"SELECT {} FROM ({}) as aggregates".format(agg_func, _query),
(_params))
entity_total = cursor.fetchall()[0][0]
else:
entity_total = len(query)
return entity_total
def process_math(query: QuerySet, entity: Entity) -> QuerySet:
math_to_aggregate_function = {
"sum": Sum,
"avg": Avg,
"min": Min,
"max": Max
}
if entity.math == "dau":
# In daily active users mode count only up to 1 event per user per day
query = query.annotate(count=Count("person_id", distinct=True))
elif entity.math in math_to_aggregate_function:
# Run relevant aggregate function on specified event property, casting it to a double
query = query.annotate(
count=math_to_aggregate_function[entity.math](Cast(
RawSQL('"posthog_event"."properties"->>%s', (
entity.math_property, )),
output_field=FloatField(),
)))
# Skip over events where the specified property is not set or not a number
# It may not be ideally clear to the user what events were skipped,
# but in the absence of typing, this is safe, cheap, and frictionless
query = query.extra(
where=[
'jsonb_typeof("posthog_event"."properties"->%s) = \'number\''
],
params=[entity.math_property],
)
return query
def filter_by_url(self, action_step: ActionStep, subquery: QuerySet):
if not action_step.url:
return subquery
url_exact = action_step.url_matching == ActionStep.EXACT
return subquery.extra(
where=["properties ->> '$current_url' {} %s".format("=" if url_exact else "LIKE")],
params=[action_step.url if url_exact else "%{}%".format(action_step.url)],
)
def filter_by_url(self, action_step: ActionStep, subquery: QuerySet):
if not action_step.url:
return subquery
if action_step.url_matching == ActionStep.EXACT:
where, param = "properties->>'$current_url' = %s", action_step.url
elif action_step.url_matching == ActionStep.REGEX:
where, param = "properties->>'$current_url' ~ %s", action_step.url
else:
where, param = "properties->>'$current_url' LIKE %s", f"%{action_step.url}%"
return subquery.extra(where=[where], params=[param])
def field_sort_queryset(queryset: QuerySet,
items: Iterable,
sort_field: str = 'id'):
"""
field排序
:param queryset:
:param items:
:param sort_field:
:return:
"""
assert issubclass(queryset._queryset_class, QuerySet), ('传入的不是QuerySet')
if items:
sku_ids = ','.join([str(i) for i in items])
field_sql = f"FIELD(`{sort_field}`,{sku_ids})"
queryset = queryset.extra(select={'field_sql': field_sql},
where=[f'id IN ({sku_ids})'],
order_by=['field_sql'])
else:
queryset = queryset.filter(id__in=[])
return queryset
