python - Query the most recent row with a lag function

Question

I am trying to make a list of all the Result's with the most recent related Batch.date, while also filtering only negative numbers when comparing Result.price to the second most recent Result using fn.LAG.

This is what I have so far using peewee with sqlite3:

    difference = Result.price - fn.LAG(Result.price, 1).over(partition_by= [Itinerary.length, Result.dest,
                                                             Itinerary.period, Batch.departure], order_by = Batch.date.asc())
        
    fd = (Result
            .select(Batch.departure.alias("departure"),
                    Batch.date.alias("date"),
                    Itinerary.period.alias("period"),
                    Itinerary.length.alias("length"),
                    Result.price.alias("price"),
                    Result.dest.alias("dest"),
                    difference.alias('diff'))
            .join(Batch)
            .switch(Result)
            .join (Itinerary))

    get_all = (Result
                .select(fd.c.departure, fd.c.date, fd.c.period,
                        fd.c.length, fd.c.price, fd.c.dest, fd.c.diff)
                .from_(fd)
                .where(fd.c.diff < 0))

My models are:

class BaseModel(Model):
    class Meta:
        database = db

class Batch(BaseModel):
    departure = CharField()
    date = DateField(default=date.today())

class Itinerary(BaseModel):
    period = CharField()
    length = CharField()

class Result(BaseModel):
    dest = CharField()
    price = IntegerField()
    batch = ForeignKeyField(Batch, backref='search')
    itin = ForeignKeyField(Itinerary, backref='result')

I'm pretty lost at the moment.. I couldn't get fn.MAX and a group_by() to work in conjunction with the difference calculation. I think I might need to make a second sub-query?

question from:https://stackoverflow.com/questions/65865514/query-the-most-recent-row-with-a-lag-function

Answer 1

Waitting for answers