I have a Django model with the following structure:
class BBPerformance(models.Model):
marketcap_change = models.FloatField(verbose_name="marketcap change", null=True, blank=True)
bb_change = models.FloatField(verbose_name="bestbuy change", null=True, blank=True)
created_at = models.DateTimeField(verbose_name="created at", auto_now_add=True)
updated_at = models.DateTimeField(verbose_name="updated at", auto_now=True)
I would like to have an Avg aggregate function on objects for every 3 days.
for example I write a queryset that do this aggregation for each day or with something like TruncDay function.
queryset = BBPerformance.objects.annotate(day=TruncDay('created_at')).values('day').annotate(marketcap_avg=Avg('marketcap_change'),bb_avg=Avg('bb_change')
How can I have a queryset of the aggregated value with 3-days interval and the index of the second day of that interval?
The following should work, although it's slightly ugly.
If you get the difference in days between each row's date and the min date you can then take the Mod of this difference to work out how many days you need to shift to get "middle" date. This middle date can then be grouped on using a values query
import datetime
from django.db.models import F, IntegerField, Avg, Min, DurationField, DateField
from django.db.models.functions import Cast, Mod, Extract
BBPerformance.objects.order_by(
'created_at'
).annotate(
diff=F('created_at__date') - BBPerformance.objects.aggregate(min=Min('created_at__date'))['min']
).annotate(
diff_days=Cast(Extract('diff', 'days'), output_field=IntegerField())
).annotate(
to_shift=Mod('diff_days', 3) - 1
).annotate(
grouped_date=Cast(F('created_at__date') - Cast(F('to_shift') * datetime.timedelta(days=1), output_field=DurationField()), output_field=DateField())
).order_by(
'grouped_date'
).values(
'grouped_date'
).annotate(
marketcap_avg=Avg('marketcap_change'),
bb_avg=Avg('bb_change')
)