Using Lag() when a partition is missing-CodePudding

I'm using lag() to be able to correctly show the Year on Year Sales difference. However, since some categories are missing sales in certain months, the lag will not go to the correct month.

I use the following code currently:

SELECT extract(YEAR_MONTH from processed_at) as 'Year_Month',
product_type,
sum(`Revenue`) as 'Revenue',
lag(sum(`Revenue`),12) over (partition by product_type order by extract(year_month from processed_at)) as 'YoY Revenue'
FROM dashboard
GROUP by 1,2
ORDER by 1 desc;

E.g., the table below will not show the 2022_01 YoY Gloves revenue correctly(we'd like to see the number 1180 as per 2021_01 sales), because Gloves have not made any sales in one of the previous months. Thus it will not be able to correctly count back to 2021_01 (12 months back as set by the lag())

 ------------ -------------- --------- ------------- 
| Year_Month | Product_Type | Revenue | YoY Revenue |
 ------------ -------------- --------- ------------- 
| 202201     | Gloves       | 180     | 1500        |
 ------------ -------------- --------- ------------- 
| 202201     | Jackets      | 210     | 3900        |
 ------------ -------------- --------- ------------- 
| 202201     | Pants        | 310     | 1820        |
 ------------ -------------- --------- ------------- 
| 202112     | Jackets      | 500     | 600         |
 ------------ -------------- --------- ------------- 
| 202112     | Pants        | 600     | 700         |
 ------------ -------------- --------- ------------- 
| 202101     | Gloves       | 1180    | 1600        |
 ------------ -------------- --------- ------------- 
| 202101     | Jackets      | 3900    | 4000        |
 ------------ -------------- --------- ------------- 
| 202101     | Pants        | 1820    | 1900        |
 ------------ -------------- --------- -------------

Would there be a clever way to show a product_type each month even if it has not made any sales and cannot be grouped by for that month?

(Writing this in MySQL)

CodePudding user response：

You need in something like:

WITH 
cte1 AS ( SELECT DISTINCT Year_Month 
          FROM table ),
cte2 AS ( SELECT DISTINCT Product_Type 
          FROM table )
SELECT Year_Month, 
       Product_Type, 
       COALESCE(table.Revenue, 0) Revenue, 
       COALESCE(table.YoY, 0) YoY
FROM cte1
CROSS JOIN cte2
NATURAL JOIN table

This will fill your 'holes' (adjust the values for absent rows if zeros are not safe). After this you may calcuilate everything which you need.