def stickiness(filtered_events: QuerySet, entity: Entity, filter: Filter, team_id: int) -> Dict[str, Any]: if not filter.date_to or not filter.date_from: raise ValueError("_stickiness needs date_to and date_from set") range_days = (filter.date_to - filter.date_from).days + 2 events = (filtered_events.filter(filter_events( team_id, filter, entity)).values("person_id").annotate(day_count=Count( functions.TruncDay("timestamp"), distinct=True)).filter( day_count__lte=range_days)) events_sql, events_sql_params = events.query.sql_with_params() aggregated_query = "select count(v.person_id), v.day_count from ({}) as v group by v.day_count".format( events_sql) aggregated_counts = execute_custom_sql(aggregated_query, events_sql_params) response: Dict[int, int] = {} for result in aggregated_counts: response[result[1]] = result[0] labels = [] data = [] for day in range(1, range_days): label = "{} day{}".format(day, "s" if day > 1 else "") labels.append(label) data.append(response[day] if day in response else 0) return { "labels": labels, "days": [day for day in range(1, range_days)], "data": data, "count": sum(data), }
def stickiness(self, entity: Entity, filter: Filter, team_id: int) -> Dict[str, Any]: if not filter.date_from: filter._date_from = (Event.objects.filter( team_id=team_id).order_by("timestamp")[0].timestamp.replace( hour=0, minute=0, second=0, microsecond=0).isoformat()) if not filter.date_to or not filter.date_from: raise ValueError("_stickiness needs date_to and date_from set") range_days = (filter.date_to - filter.date_from).days + 2 events = process_entity_for_events( entity=entity, team_id=team_id, order_by=None, ) events = events.filter(filter_events(team_id, filter, entity)) events = (events.filter(filter_events( team_id, filter, entity)).values("person_id").annotate(day_count=Count( functions.TruncDay("timestamp"), distinct=True)).filter( day_count__lte=range_days)) events_sql, events_sql_params = events.query.sql_with_params() aggregated_query = "select count(v.person_id), v.day_count from ({}) as v group by v.day_count".format( events_sql) counts = execute_custom_sql(aggregated_query, events_sql_params) return self.process_result(counts, range_days)
def _aggregate_by_day(self, action: Action, filters: Dict[Any, Any], request: request.Request): append: Dict[str, Any] = {} aggregates = Event.objects.filter_by_action(action)\ .filter(self._filter_events(request))\ .annotate(day=functions.TruncDay('timestamp'))\ .values('day')\ .annotate(count=Count('id'))\ .order_by() if filters.get('math') == 'dau': aggregates = aggregates.annotate( count=Count('distinct_id', distinct=True)) if len(aggregates) > 0: date_from, date_to = self._get_dates_from_request(request) if not date_from: date_from = aggregates[0]['day'].date() dates_filled = self._group_events_to_date(date_from=date_from, date_to=date_to, aggregates=aggregates) append = self._append_data(append, dates_filled) if request.GET.get('breakdown'): append['breakdown'] = self._breakdown( aggregates, breakdown_by=request.GET['breakdown']) return append
def _stickiness(self, filtered_events: QuerySet, entity: Entity, filter: Filter, team: Team) -> Dict[str, Any]: if not filter.date_to or not filter.date_from: raise ValueError('_stickiness needs date_to and date_from set') range_days = (filter.date_to - filter.date_from).days + 2 events = filtered_events\ .filter(self._filter_events(team, filter, entity))\ .values('person_id') \ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count__lte=range_days) events_sql, events_sql_params = events.query.sql_with_params() aggregated_query = 'select count(v.person_id), v.day_count from ({}) as v group by v.day_count'.format(events_sql) aggregated_counts = self._execute_custom_sql(aggregated_query, events_sql_params) response: Dict[int, int] = {} for result in aggregated_counts: response[result[1]] = result[0] labels = [] data = [] for day in range(1, range_days): label = '{} day{}'.format(day, 's' if day > 1 else '') labels.append(label) data.append(response[day] if day in response else 0) return { 'labels': labels, 'days': [day for day in range(1, range_days)], 'data': data, 'count': sum(data) }
def _calculate_people(events: QuerySet): shown_as = request.GET.get("shown_as") if shown_as is not None and shown_as == "Stickiness": stickiness_days = int(request.GET["stickiness_days"]) events = (events.values("person_id").annotate(day_count=Count( functions.TruncDay("timestamp"), distinct=True)).filter( day_count=stickiness_days)) else: events = events.values("person_id").distinct() if (request.GET.get("breakdown_type") == "cohort" and request.GET.get("breakdown_value") != "all"): events = events.filter( Exists( CohortPeople.objects.filter( cohort_id=int(request.GET["breakdown_value"]), person_id=OuterRef("person_id"), ).only("id"))) people = Person.objects.filter( team=team, id__in=[p["person_id"] for p in events[0:100]]) people = people.prefetch_related( Prefetch("persondistinctid_set", to_attr="distinct_ids_cache")) return serialize_people(people=people, request=request)
def _calculate_people(events: QuerySet): shown_as = request.GET.get('shown_as') if shown_as is not None and shown_as == 'Stickiness': stickiness_days = int(request.GET['stickiness_days']) events = events\ .values('person_id')\ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count=stickiness_days) else: events = events.values('person_id').distinct() if request.GET.get('breakdown_type') == 'cohort' and request.GET.get('breakdown_value') != 'all': events = events.filter(Exists( CohortPeople.objects.filter( cohort_id=int(request.GET['breakdown_value']), person_id=OuterRef('person_id') ).only('id') )) people = Person.objects\ .filter(team=team, id__in=[p['person_id'] for p in events[0:100]]) people = people.prefetch_related(Prefetch('persondistinctid_set', to_attr='distinct_ids_cache')) return self._serialize_people( people=people, request=request )
def _serialize_action(self, action: Action, filters: Dict[Any, Any], request: request.Request, date_from: datetime, date_to: datetime) -> Dict: append = { 'action': { 'id': action.pk, 'name': action.name }, 'label': action.name, 'count': 0, 'breakdown': [] } aggregates = Event.objects.filter_by_action(action)\ .filter(**self._filter_events(request))\ .filter(timestamp__gte=date_from)\ .annotate(day=functions.TruncDay('timestamp'))\ .values('day')\ .annotate(count=Count('id'))\ .order_by() if filters.get('math') == 'dau': aggregates = aggregates.annotate(count=Count('distinct_id', distinct=True)) if len(aggregates) > 0: dates_filled = self._group_events_to_date(date_from=date_from, aggregates=aggregates, steps=(date_to - date_from).days) values = [value[0] for key, value in dates_filled.iterrows()] append['labels'] = [key.strftime('%-d %B') for key, value in dates_filled.iterrows()] append['data'] = values append['count'] = sum(values) if request.GET.get('breakdown'): append['breakdown'] = self._breakdown(aggregates, breakdown_by=request.GET['breakdown']) return append
def _aggregate_by_day(self, filtered_events: QuerySet, filters: Dict[Any, Any], request: request.Request) -> Dict[str, Any]: append: Dict[str, Any] = {} aggregates = filtered_events\ .filter(self._filter_events(request))\ .annotate(day=functions.TruncDay('timestamp'))\ .values('day')\ .annotate(count=Count('id'))\ .order_by() aggregates = self._process_math(aggregates, filters) if len(aggregates) > 0: date_from, date_to = self._get_dates_from_request(request) if not date_from: date_from = aggregates[0]['day'].date() dates_filled = self._group_events_to_date(date_from=date_from, date_to=date_to, aggregates=aggregates) append = self._append_data(append, dates_filled) if request.GET.get('breakdown'): append = self._breakdown(append, filtered_events, filters, request, breakdown_by=request.GET['breakdown']) return append
def people(self, request: request.Request, *args: Any, **kwargs: Any) -> Response: actions = self.get_queryset() actions = actions.filter(deleted=False) actions_list = [] for action in actions: events = Event.objects.filter_by_action(action, order_by=None).filter(self._filter_events(request)) if request.GET.get('shown_as', 'Volume') == 'Volume': events = events.values('person_id').distinct() elif request.GET['shown_as'] == 'Stickiness': stickiness_days = int(request.GET['stickiness_days']) events = events\ .values('person_id')\ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count=stickiness_days) people = Person.objects\ .filter(team=self.request.user.team_set.get(), id__in=[p['person_id'] for p in events[0:100]]) actions_list.append(self._serialize_people( action=action, people=people, request=request )) return Response(actions_list)
def _stickiness(self, filtered_events: QuerySet, filters: Dict[Any, Any], request: request.Request) -> Dict[str, Any]: date_from, date_to = self._get_dates_from_request(request) range_days = (date_to - date_from).days + 2 events = filtered_events\ .filter(self._filter_events(request))\ .values('person_id') \ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count__lte=range_days) events_sql, events_sql_params = events.query.sql_with_params() aggregated_query = 'select count(v.person_id), v.day_count from ({}) as v group by v.day_count'.format( events_sql) aggregated_counts = self._execute_custom_sql(aggregated_query, events_sql_params) response: Dict[int, int] = {} for result in aggregated_counts: response[result[1]] = result[0] labels = [] data = [] for day in range(1, range_days): label = '{} day{}'.format(day, 's' if day > 1 else '') labels.append(label) data.append(response[day] if day in response else 0) return { 'labels': labels, 'days': [day for day in range(1, range_days)], 'data': data }
def stats(self, last): qs = self.filter(start_time__gte=now() - timedelta(days=last)) qs = qs.annotate( day=dbfunc.TruncDay('start_time'), month=dbfunc.TruncMonth('start_time'), year=dbfunc.TruncYear('start_time'), ) return OrderedDict(day=self._get_history_stats_by(qs, 'day'), month=self._get_history_stats_by(qs, 'month'), year=self._get_history_stats_by(qs, 'year'))
def _get_interval_annotation(self, key: str) -> Dict[str, Any]: map: Dict[str, Any] = { 'minute': functions.TruncMinute('timestamp'), 'hour': functions.TruncHour('timestamp'), 'day': functions.TruncDay('timestamp'), 'week': functions.TruncWeek('timestamp'), 'month': functions.TruncMonth('timestamp'), } func = map.get(key) if func is None: return {'day': map.get('day')} # default return { key: func }
def get_interval_annotation(key: str) -> Dict[str, Any]: map: Dict[str, Any] = { "minute": functions.TruncMinute("timestamp"), "hour": functions.TruncHour("timestamp"), "day": functions.TruncDay("timestamp"), "week": functions.TruncWeek("timestamp"), "month": functions.TruncMonth("timestamp"), } func = map.get(key) if func is None: return {"day": map.get("day")} # default return {key: func}
def _calculate_people(events: QuerySet): shown_as = request.GET.get('shown_as') if shown_as is not None and shown_as == 'Stickiness': stickiness_days = int(request.GET['stickiness_days']) events = events\ .values('person_id')\ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count=stickiness_days) else: events = events.values('person_id').distinct() people = Person.objects\ .filter(team=team, id__in=[p['person_id'] for p in events[0:100]]) return self._serialize_people(people=people, request=request)
def get_interval_annotation(key: str) -> Dict[str, Any]: map: Dict[str, Any] = { "minute": functions.TruncMinute("timestamp"), "hour": functions.TruncHour("timestamp"), "day": functions.TruncDay("timestamp"), "week": functions.TruncWeek( ExpressionWrapper(F("timestamp") + datetime.timedelta(days=1), output_field=DateTimeField()) ), "month": functions.TruncMonth("timestamp"), } func = map.get(key) if func is None: return {"day": map.get("day")} # default return {key: func}
def _calculate_people(entity: Entity, events: QuerySet): if request.GET.get('shown_as', 'Volume') == 'Volume': events = events.values('person_id').distinct() elif request.GET['shown_as'] == 'Stickiness': stickiness_days = int(request.GET['stickiness_days']) events = events\ .values('person_id')\ .annotate(day_count=Count(functions.TruncDay('timestamp'), distinct=True))\ .filter(day_count=stickiness_days) people = Person.objects\ .filter(team=team, id__in=[p['person_id'] for p in events[0:100]]) return self._serialize_people(entity=entity, people=people, request=request)
def get_group_queryset(self, period_step, start, end): query = self.get_span_queryset(period_step, start, end) ann_kwargs = {} if period_step == models.STEP_DAY: ann_kwargs['_date'] = db_functions.TruncDay( self.date_field, output_field=DateField()) ann_kwargs['_month'] = db_functions.TruncMonth(self.date_field) ann_kwargs['_year'] = db_functions.TruncYear(self.date_field) if period_step == models.STEP_MONTH: ann_kwargs['_date'] = db_functions.TruncMonth( self.date_field, output_field=DateField()) ann_kwargs['_year'] = db_functions.TruncYear(self.date_field) if period_step == models.STEP_YEAR: ann_kwargs['_date'] = db_functions.TruncYear( self.date_field, output_field=DateField()) return query.annotate(**ann_kwargs).values(*ann_kwargs.keys())
def set_user_scores(apps, schema_editor): from accounts.models import User UserTPScore = apps.get_model("pootle_score.UserTPScore") scorelogs = apps.get_model("pootle_statistics.ScoreLog").objects.exclude( user__username__in=User.objects.META_USERS) scorelogs = scorelogs.annotate( day=functions.TruncDay("creation_time")).values( "day", "user", "submission__translation_project").annotate( score=Sum("score_delta"), suggested=Sum( Case( When( action_code=TranslationActionCodes.SUGG_ADDED, then='wordcount'), default=0, output_field=IntegerField())), translated=Sum( Case( When( translated_wordcount__isnull=False, then='translated_wordcount'), default=0, output_field=IntegerField())), reviewed=Sum( Case( When( action_code__in=[ TranslationActionCodes.SUGG_REVIEWED_ACCEPTED, TranslationActionCodes.REVIEWED, TranslationActionCodes.EDITED], translated_wordcount__isnull=True, then='wordcount'), default=0, output_field=IntegerField()))) UserTPScore.objects.bulk_create( UserTPScore( date=score["day"], user_id=score["user"], tp_id=score["submission__translation_project"], score=score["score"], reviewed=score["reviewed"], suggested=score["suggested"], translated=score["translated"]) for score in scorelogs.iterator())
def trends(self, request: request.Request, *args: Any, **kwargs: Any) -> Response: actions = self.get_queryset() actions = actions.filter(deleted=False) actions_list = [] steps = int(request.GET.get('days', 7)) date_from = datetime.date.today() - relativedelta(days=steps) date_to = datetime.date.today() for action in actions: append = { 'action': { 'id': action.pk, 'name': action.name }, 'label': action.name, 'count': 0, 'breakdown': [] } aggregates = self._filter_events(Event.objects.filter_by_action(action), request=request)\ .filter(timestamp__gte=date_from)\ .annotate(day=functions.TruncDay('timestamp'))\ .values('day')\ .annotate(count=Count('id'))\ .order_by() if len(aggregates) > 0: dates_filled = self._group_events_to_date( date_from=date_from, aggregates=aggregates, steps=steps) values = [value[0] for key, value in dates_filled.iterrows()] append['labels'] = [ key.strftime('%-d %B') for key, value in dates_filled.iterrows() ] append['data'] = values append['count'] = sum(values) if request.GET.get('breakdown'): append['breakdown'] = self._breakdown( aggregates, breakdown_by=request.GET['breakdown']) actions_list.append(append) return Response(actions_list)
def _stickiness(self, action: Action, filters: Dict[Any, Any], request: request.Request): events = Event.objects.filter_by_action(action)\ .filter(self._filter_events(request))\ .annotate(day=functions.TruncDay('timestamp'))\ .annotate(distinct_person_day=Concat('person_id', 'day', output_field=TextField()))\ .order_by('distinct_person_day')\ .distinct('distinct_person_day') date_from, date_to = self._get_dates_from_request(request) people: Dict[int, int] = {} for event in events: if not people.get(event.person_id): people[event.person_id] = 0 people[event.person_id] += 1 labels = [] data = [] for day in range(1, (date_to - date_from).days + 2): label = '{} day{}'.format(day, 's' if day > 1 else '') labels.append(label) data.append( len([key for key, value in people.items() if value == day])) return {'labels': labels, 'data': data}
def trunc_day(self): """ Truncates date or datetime at a whole day. """ return functions.TruncDay(self._name)