コード例 #1
0
    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
コード例 #2
0
ファイル: actions.py プロジェクト: bowo-anakdesa/posthog
    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
コード例 #3
0
    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
コード例 #4
0
    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
コード例 #5
0
    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)
コード例 #6
0
    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})
コード例 #7
0
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
コード例 #8
0
ファイル: actions.py プロジェクト: well-balanced/posthog
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
コード例 #9
0
    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
コード例 #10
0
    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)
コード例 #11
0
ファイル: actions.py プロジェクト: vishivish18/posthog
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
コード例 #12
0
    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