def _leads_period_unit_expr(period):
"""
:param period: - 'hour', 'day', 'week', 'moth', 'year'
:param date_to:
:return:
>>> _leads_period_unit_expr('hour')
TruncHour(F(created))
>>> _leads_period_unit_expr('day')
TruncDay(F(created))
>>> _leads_period_unit_expr('week')
Trunc(F(created))
>>> _leads_period_unit_expr('month')
TruncMonth(F(created))
>>> _leads_period_unit_expr('year')
TruncYear(F(created))
"""
if period == 'hour':
return TruncHour('created')
elif period == 'day':
return TruncDay('created')
elif period == 'week':
return Trunc('created', 'week')
elif period == 'month':
return TruncMonth('created')
else:
return TruncYear('created')
def _get_trunc_func(
self, subject: str, period: str
) -> Tuple[Union[TruncHour, TruncDay, TruncWeek, TruncMonth], str]:
if period == "Hour":
fields = """
FLOOR(DATE_PART('day', first_date - %s) * 24 + DATE_PART('hour', first_date - %s)) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date) * 24 + DATE_PART('hour', event_date - first_date)) AS date,
"""
return TruncHour(subject), fields
elif period == "Day":
fields = """
FLOOR(DATE_PART('day', first_date - %s)) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date)) AS date,
"""
return TruncDay(subject), fields
elif period == "Week":
fields = """
FLOOR(DATE_PART('day', first_date - %s) / 7) AS first_date,
FLOOR(DATE_PART('day', event_date - first_date) / 7) AS date,
"""
return TruncWeek(subject), fields
elif period == "Month":
fields = """
FLOOR((DATE_PART('year', first_date) - DATE_PART('year', %s)) * 12 + DATE_PART('month', first_date) - DATE_PART('month', %s)) AS first_date,
FLOOR((DATE_PART('year', event_date) - DATE_PART('year', first_date)) * 12 + DATE_PART('month', event_date) - DATE_PART('month', first_date)) AS date,
"""
return TruncMonth(subject), fields
else:
raise ValidationError(f"Period {period} is unsupported.")
def responses_per_day(self):
"""How many responses there have been per day"""
return (
self.responses.annotate(date=TruncDay("datetime"))
.values("date")
.annotate(count=Count("id"))
.order_by("date")
)
def trunc_func(
self, field_name: str
) -> Union[TruncHour, TruncDay, TruncWeek, TruncMonth]:
if self.interval == "hour":
return TruncHour(field_name)
elif self.interval == "day":
return TruncDay(field_name)
elif self.interval == "week":
return TruncWeek(field_name)
elif self.interval == "month":
return TruncMonth(field_name)
else:
raise ValidationError(f"{self.interval} not supported")
def get_of_picked_city_with_date_range(self, date_start, date_end, city_id=None):
temperature_kwargs = {
'downloaded__range': [f'{date_start}', f'{date_end}']
}
if not city_id:
temperature_kwargs['city__picked_city_related__isnull'] = False
else:
temperature_kwargs['city__exact'] = city_id
query_set = self.filter(
**temperature_kwargs
).values('id', 'city__name').annotate(
day=TruncDay('downloaded')
).order_by('day', 'city__name', '-temperature'
).distinct('day', 'city__name')
return query_set
def consumer_origin_by_period(user: User,
date_from: date,
date_to: date,
groups: list = None,
projects: list = None,
device_field='device_id',
label_type=None,
label_values=None,
os_groups=None,
browser_groups=None,
traffic_channels=None):
leads_qs = Lead.objects.filter(pixel__project__user=user)
leads_qs = _apply_lead_common_filters(leads_qs, date_from, date_to,
projects, label_type, label_values,
os_groups, browser_groups,
traffic_channels)
leads_qs = leads_qs.annotate(created_date=TruncDay('created'))
leads_qs = leads_qs.order_by('created')
groups_by_dates = _collect_device_frequency_groups_by_dates(
leads_qs, groups, device_field)
scale_period = get_scale_period(date_from, date_to)
return _collect_device_frequerncy_groups_by_period(groups_by_dates,
scale_period)
def most_played_daily(self, start=None, end=None):
return (self.plays(
start, end).annotate(day=TruncDay('timestamp')).values(
'radio_id', 'artist_name', 'title',
'day').annotate(count=Count('*')).order_by("-count"))