예제 #1
0
    def calculate_avg(self, filter: Filter, team: Team):

        parsed_date_from, parsed_date_to, _ = parse_timestamps(filter, team.pk)

        filters, params = parse_prop_clauses(
            filter.properties, team.pk, filter_test_accounts=filter.filter_test_accounts
        )

        interval_notation = get_trunc_func_ch(filter.interval)
        num_intervals, seconds_in_interval, _ = get_time_diff(
            filter.interval or "day", filter.date_from, filter.date_to, team.pk
        )

        entity_conditions, entity_params = entity_query_conditions(filter, team)
        if not entity_conditions:
            entity_conditions = ["event != '$feature_flag_called'"]  # default conditino

        params = {**params, **entity_params}
        entity_query = " OR ".join(entity_conditions)

        avg_query = SESSIONS_NO_EVENTS_SQL.format(
            team_id=team.pk,
            date_from=parsed_date_from,
            date_to=parsed_date_to,
            filters=filters,
            sessions_limit="",
            entity_filter=f"AND ({entity_query})",
        )
        per_period_query = AVERAGE_PER_PERIOD_SQL.format(sessions=avg_query, interval=interval_notation)

        null_sql = NULL_SQL.format(
            date_to=filter.date_to.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)
        scaled_data, _ = scale_time_series(time_series_data["data"])
        time_series_data.update({"data": scaled_data})
        # 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]
예제 #2
0
    def _total_volume_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]:

        interval_annotation = get_trunc_func_ch(filter.interval)
        num_intervals, seconds_in_interval, _ = get_time_diff(
            filter.interval or "day", filter.date_from, filter.date_to, team_id=team_id
        )
        aggregate_operation, join_condition, math_params = process_math(entity)

        trend_event_query = TrendsEventQuery(
            filter=filter,
            entity=entity,
            team_id=team_id,
            should_join_distinct_ids=True
            if join_condition != "" or entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]
            else False,
        )
        event_query, event_query_params = trend_event_query.get_query()

        content_sql_params = {
            "aggregate_operation": aggregate_operation,
            "timestamp": "e.timestamp",
            "interval": interval_annotation,
        }
        params: Dict = {"team_id": team_id}
        params = {**params, **math_params, **event_query_params}

        if filter.display in TRENDS_DISPLAY_BY_VALUE:
            content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(event_query=event_query, **content_sql_params)
            time_range = enumerate_time_range(filter, seconds_in_interval)

            return (
                content_sql,
                params,
                lambda result: [
                    {"aggregated_value": result[0][0] if result and len(result) else 0, "days": time_range}
                ],
            )
        else:

            if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
                content_sql = ACTIVE_USER_SQL.format(
                    event_query=event_query,
                    **content_sql_params,
                    parsed_date_to=trend_event_query.parsed_date_to,
                    parsed_date_from=trend_event_query.parsed_date_from,
                    **trend_event_query.active_user_params
                )
            else:
                content_sql = VOLUME_SQL.format(event_query=event_query, **content_sql_params)

            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"),
            )
            final_query = AGGREGATE_SQL.format(null_sql=null_sql, content_sql=content_sql)
            return final_query, params, self._parse_total_volume_result(filter)
예제 #3
0
    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]
예제 #4
0
    def _total_volume_query(self, entity: Entity, filter: Filter,
                            team_id: int) -> Tuple[str, Dict, Callable]:
        trunc_func = get_trunc_func_ch(filter.interval)
        interval_func = get_interval_func_ch(filter.interval)
        aggregate_operation, join_condition, math_params = process_math(entity)

        trend_event_query = TrendsEventQuery(
            filter=filter,
            entity=entity,
            team_id=team_id,
            should_join_distinct_ids=True if join_condition != ""
            or entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE] else False,
        )
        event_query, event_query_params = trend_event_query.get_query()

        content_sql_params = {
            "aggregate_operation": aggregate_operation,
            "timestamp": "e.timestamp",
            "interval": trunc_func,
        }
        params: Dict = {"team_id": team_id}
        params = {**params, **math_params, **event_query_params}

        if filter.display in TRENDS_DISPLAY_BY_VALUE:
            content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
                event_query=event_query, **content_sql_params)

            return (content_sql, params,
                    self._parse_aggregate_volume_result(
                        filter, entity, team_id))
        else:

            if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
                content_sql = ACTIVE_USER_SQL.format(
                    event_query=event_query,
                    **content_sql_params,
                    parsed_date_to=trend_event_query.parsed_date_to,
                    parsed_date_from=trend_event_query.parsed_date_from,
                    **trend_event_query.active_user_params,
                )
            elif filter.display == TRENDS_CUMULATIVE and entity.math == "dau":
                cumulative_sql = CUMULATIVE_SQL.format(event_query=event_query)
                content_sql = VOLUME_SQL.format(event_query=cumulative_sql,
                                                **content_sql_params)
            else:
                content_sql = VOLUME_SQL.format(event_query=event_query,
                                                **content_sql_params)

            null_sql = NULL_SQL.format(trunc_func=trunc_func,
                                       interval_func=interval_func)
            params["interval"] = filter.interval
            final_query = AGGREGATE_SQL.format(null_sql=null_sql,
                                               content_sql=content_sql)
            return final_query, params, self._parse_total_volume_result(
                filter, entity, team_id)
예제 #5
0
파일: normal.py 프로젝트: ungps/posthog
    def _normal_query(self, entity: Entity, filter: Filter, team_id: int) -> Tuple[str, Dict, Callable]:

        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=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)

        aggregate_operation, join_condition, math_params = process_math(entity)

        params: Dict = {"team_id": team_id}
        params = {**params, **prop_filter_params, **math_params, **date_params}
        content_sql_params = {
            "interval": interval_annotation,
            "parsed_date_from": date_from_clause(interval_annotation, round_interval),
            "parsed_date_to": parsed_date_to,
            "timestamp": "timestamp",
            "team_id": team_id,
            "filters": prop_filters,
            "event_join": join_condition,
            "aggregate_operation": aggregate_operation,
        }

        entity_params, entity_format_params = self._populate_entity_params(entity)
        params = {**params, **entity_params}
        content_sql_params = {**content_sql_params, **entity_format_params}

        if filter.display in TRENDS_DISPLAY_BY_VALUE:
            agg_query = self._determine_single_aggregate_query(filter, entity)
            content_sql = agg_query.format(**content_sql_params)

            return (
                content_sql,
                params,
                lambda result: [{"aggregated_value": result[0][0] if result and len(result) else 0}],
            )
        else:
            content_sql = self._determine_trend_aggregate_query(filter, entity)
            content_sql = content_sql.format(**content_sql_params)

            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"),
            )
            final_query = AGGREGATE_SQL.format(null_sql=null_sql, content_sql=content_sql)
            return final_query, params, self._parse_normal_result(filter)
예제 #6
0
    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
예제 #7
0
    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
예제 #8
0
    def _normal_query(self, entity: Entity, filter: Filter,
                      team_id: int) -> Tuple[str, Dict, Callable]:

        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=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,
            filter_test_accounts=filter.filter_test_accounts)

        aggregate_operation, join_condition, math_params = process_math(entity)

        params: Dict = {"team_id": team_id}
        params = {**params, **prop_filter_params, **math_params, **date_params}
        content_sql_params = {
            "interval":
            interval_annotation,
            "parsed_date_from":
            date_from_clause(interval_annotation, round_interval),
            "parsed_date_to":
            parsed_date_to,
            "timestamp":
            "timestamp",
            "filters":
            prop_filters,
            "event_join":
            join_condition,
            "aggregate_operation":
            aggregate_operation,
            "entity_query":
            "AND {actions_query}" if entity.type == TREND_FILTER_TYPE_ACTIONS
            else "AND event = %(event)s",
        }

        entity_params, entity_format_params = self._populate_entity_params(
            entity)
        params = {**params, **entity_params}

        if filter.display in TRENDS_DISPLAY_BY_VALUE:
            content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
                **content_sql_params).format(**entity_format_params)
            time_range = self._enumerate_time_range(filter,
                                                    seconds_in_interval)

            return (
                content_sql,
                params,
                lambda result: [{
                    "aggregated_value":
                    result[0][0] if result and len(result) else 0,
                    "days":
                    time_range
                }],
            )
        else:

            if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
                sql_params = get_active_user_params(filter, entity, team_id)
                content_sql = ACTIVE_USER_SQL.format(
                    **content_sql_params,
                    **sql_params).format(**entity_format_params)
            else:
                # entity_format_params depends on format clause from content_sql_params
                content_sql = VOLUME_SQL.format(**content_sql_params).format(
                    **entity_format_params)

            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"),
            )
            final_query = AGGREGATE_SQL.format(null_sql=null_sql,
                                               content_sql=content_sql)
            return final_query, params, self._parse_normal_result(filter)
예제 #9
0
파일: normal.py 프로젝트: sjain07/posthog
    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
예제 #10
0
    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
예제 #11
0
    def _total_volume_query(self, entity: Entity, filter: Filter,
                            team: Team) -> Tuple[str, Dict, Callable]:
        trunc_func = get_trunc_func_ch(filter.interval)
        interval_func = get_interval_func_ch(filter.interval)
        aggregate_operation, join_condition, math_params = process_math(
            entity, team)

        trend_event_query = TrendsEventQuery(
            filter=filter,
            entity=entity,
            team=team,
            should_join_distinct_ids=True if join_condition != "" or
            (entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]
             and not team.aggregate_users_by_distinct_id) else False,
        )
        event_query, event_query_params = trend_event_query.get_query()

        content_sql_params = {
            "aggregate_operation": aggregate_operation,
            "timestamp": "e.timestamp",
            "interval": trunc_func,
        }
        params: Dict = {"team_id": team.id}
        params = {**params, **math_params, **event_query_params}

        if filter.display in TRENDS_DISPLAY_BY_VALUE:
            content_sql = VOLUME_TOTAL_AGGREGATE_SQL.format(
                event_query=event_query, **content_sql_params)

            return (content_sql, params,
                    self._parse_aggregate_volume_result(
                        filter, entity, team.id))
        else:

            if entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
                content_sql = ACTIVE_USER_SQL.format(
                    event_query=event_query,
                    **content_sql_params,
                    parsed_date_to=trend_event_query.parsed_date_to,
                    parsed_date_from=trend_event_query.parsed_date_from,
                    aggregator="distinct_id"
                    if team.aggregate_users_by_distinct_id else "person_id",
                    **trend_event_query.active_user_params,
                )
            elif filter.display == TRENDS_CUMULATIVE and entity.math == "dau":
                cumulative_sql = CUMULATIVE_SQL.format(event_query=event_query)
                content_sql = VOLUME_SQL.format(event_query=cumulative_sql,
                                                **content_sql_params)
            else:
                content_sql = VOLUME_SQL.format(event_query=event_query,
                                                **content_sql_params)

            null_sql = NULL_SQL.format(trunc_func=trunc_func,
                                       interval_func=interval_func)
            params["interval"] = filter.interval

            # If we have a smoothing interval > 1 then add in the sql to
            # handling rolling average. Else just do a sum. This is possibly an
            # nessacary optimization.
            if filter.smoothing_intervals > 1:
                smoothing_operation = f"""
                    AVG(SUM(total))
                    OVER (
                        ORDER BY day_start
                        ROWS BETWEEN {filter.smoothing_intervals - 1} PRECEDING
                        AND CURRENT ROW
                    )"""
            else:
                smoothing_operation = "SUM(total)"

            final_query = AGGREGATE_SQL.format(
                null_sql=null_sql,
                content_sql=content_sql,
                smoothing_operation=smoothing_operation,
                aggregate="count"
                if filter.smoothing_intervals < 2 else "floor(count)",
            )
            return final_query, params, self._parse_total_volume_result(
                filter, entity, team.id)
예제 #12
0
    def _format_normal_query(self, entity: Entity, filter: Filter,
                             team_id: int) -> List[Dict[str, Any]]:

        interval_annotation = get_trunc_func_ch(filter.interval)
        num_intervals, seconds_in_interval = get_time_diff(filter.interval
                                                           or "day",
                                                           filter.date_from,
                                                           filter.date_to,
                                                           team_id=team_id)
        parsed_date_from, parsed_date_to, _ = 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)

        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,
        }

        entity_params, entity_format_params = self._populate_entity_params(
            entity)
        params = {**params, **entity_params}
        content_sql_params = {**content_sql_params, **entity_format_params}

        if filter.display == TRENDS_TABLE or filter.display == TRENDS_PIE:
            agg_query = self._determine_single_aggregate_query(filter, entity)
            content_sql = agg_query.format(**content_sql_params)

            try:
                result = sync_execute(content_sql, params)
            except:
                result = []

            return [{
                "aggregated_value":
                result[0][0] if result and len(result) else 0
            }]
        else:
            content_sql = self._determine_trend_aggregate_query(filter, entity)
            content_sql = content_sql.format(**content_sql_params)

            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"),
            )
            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