I have a django model that is "DailyReport" of the companies sale I want to find out company sale change over the previous day. the model that i define is like that:

class DailyReport(models.Model):
    company = models.CharField(max_length=50)
    sale = models.IntegerField()
    date = models.DateField()

How can i figure out this issue to add new column for every report that represent change rate over the previous day

CodePudding user response：

Use the Lag window function to annotate each row with the previous sale amount for that company.

Then use another annotation to calculate the difference between the current and previous sale

from django.db.models import Window, F
from django.db.models.functions import Lag

DailyReport.objects.annotate(
    prev_val=Window(
        expression=Lag('sale', default=0),
        partition_by=['company'],
        order_by=F('date').asc(),
    )
).annotate(
    diff=F('sale') - F('prev_val')
)

CodePudding user response：

Assuming you can have only one record of a company for each day, you can create a model property:

@property
def previous_day_sale(self):
  date = self.date 
  dr = DailyReport.objects.filter(company=self.company, date=date-timedelta(days=1)
  if dr:
    return dr.first().sale - self.sale

CodePudding user response：

You may need to override the save method, but you will have to cover all edge cases.

class DailyReport(models.Model):
    company = models.CharField(max_length=50)
    sale = models.IntegerField()
    date = models.DateField()
    sale_over_previous_day = models.IntegerField()
    
    def save(self, *args, **kwargs):
        previous_day_sale_object = DailyReport.objects.filter(company=self.company, date=date-timedelta(days=1))
        if previous_day_sale_object:
            previous_day_sale = previous_day_sale_object[0].sale
        else:
            previous_day_sale = 0
        self.sale_over_previous_day = self.sale - previous_day_sale
        super(DailyReport, self).save(*args, **kwargs)