Exemplo n.º 1
0
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
Exemplo n.º 2
0
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
Exemplo n.º 3
0
 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)],
     )
Exemplo n.º 4
0
 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])
Exemplo n.º 5
0
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