Peewee / SQLite Query for 7 Day Rolling Average

Question

I have a table of daily sessions and units sold for each product

ProductId (char) | Date | Sessions | UnitsSold
sku01 | 2017-02-01 | 21 | 4
sku01 | 2017-02-02 | 14 | 3
sku01 | 2017-02-03 | 13 | 3
sku01 | 2017-02-06 | 14 | 2
sku01 | 2017-02-07 | 5 | 1
sku01 | 2017-02-09 | 2 | 0
sku02 | 2017-02-02 | 16 | 10
sku02 | 2017-02-03 | 16 | 10
sku02 | 2017-02-14 | 30 | 23
...

I'm trying to generate a report for each product that gives the last 7 days rolling average for each day for the sessions and units sold values

So for example the output for sku01 would be:

Date | SessionsRollingAvg | UnitsSoldRollingAvg
2017-02-01 | sra1 | usra1
2017-02-02 | sra2 | usra2
...

where:

sra1 = (sum of Sessions 2017-02-01 to 2017-02-07 for sku01) / 7
usra1 = (sum of UnitsSold 2017-02-01 to 2017-02-07 for sku01) / 7
sra2 = (sum of Sessions 2017-02-02 to 2017-02-08 for sku01) / 7
usra2 = (sum of UnitsSold 2017-02-02 to 2017-02-08 for sku01) / 7

So far I found this article that mentions multiple ways to do it with Postgres, MySQL. But how can I do it with the Peewee ORM?

score 1 · Answer 1 · edited May 23 '17 at 12:34

Start your query from your products class then you can specify a join condition to select the sessions you want. See this answer Is it possible to make sql join on several fields using peewee python ORM?

You can then use fn and group_by to create your average.

Something like this more or less matches the queries described in your article:

from peewee import fn

cond = (
    (MySessions.product_id == MyProduct.id) & 
    (MySessions.date.between(7daysago, now))
)

query = (MyProduct
         .select(MyProduct, fn.avg(MyTable.Sessions).alias('session_avg')
         .join(MySessions, on=cond)
         .group_by(MyProduct)

Peewee / SQLite Query for 7 Day Rolling Average

1 Answers1