MySQL滑动窗口计算【该日的过去7日售出商品种类数】

目录

• tips
• over()语法的基础知识
• rows的语法
• mysql中写过去七日滑动窗口的范式
  • mysql不支持 COUNT(distinct product_id) OVER () 这样的语法，postgre支持
  • 一种替代是使用self-join

tips

如果你over()里有RANGE BETWEEN INTERVAL 7 DAY PRECEDING，那么order by后面接的应该是数字或者日期不能是字符串

over()语法的基础知识

--- 举例
COUNT(distinct product_id) OVER (
            PARTITION BY shop_id 
            ORDER BY date(event_time_date)
            RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW 
        )

COUNT(distinct product_id) OVER (
            PARTITION BY shop_id 
            ORDER BY date(event_time_date)
            rows between 2 preceding and current row
        )

rows的语法

rows between 2 preceding and current row #取当前行和前面两行

rows between unbounded preceding and current row #包括本行和之前所有的行

rows between current row and unbounded following #包括本行和之后所有的行

rows between 3 preceding and current row #包括本行和前面三行

rows between 3 preceing and 1 following #从前面三行和下面一行，总共五行

注意如果只关心国庆3天的数据，先滑动窗口写法得到一个临时表，再where。顺序不要反了。

mysql中写过去七日滑动窗口的范式

mysql不支持 COUNT(distinct product_id) OVER () 这样的语法，postgre支持

SELECT 
        shop_id,
        date(event_time_date),  
        COUNT(distinct product_id) OVER (
            PARTITION BY shop_id 
            ORDER BY date(event_time_date)
            RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
        ) AS unique_products_sold_in_7_days
    FROM 
        total_info

一种替代是使用self-join

SELECT 
    t1.shop_id,
    date(t1.event_time_date) AS event_date,
    COUNT(DISTINCT t2.product_id) AS unique_products_sold_in_7_days
FROM
    total_info t1
JOIN
    total_info t2
    ON t1.shop_id = t2.shop_id
    AND date(t2.event_time_date) BETWEEN date(t1.event_time_date) - INTERVAL 7 DAY AND date(t1.event_time_date)
GROUP BY
    t1.shop_id,
    event_date