如何编写SQL查询统计每个订单日期往前30天内的用户订单总数

方案1：窗口函数实现（推荐，性能更高，适配MySQL8.0+、Spark SQL、Hive、PostgreSQL等多数支持标准SQL的数据库） #

核心用滑动窗口帧统计同用户30天内的订单量，代码如下：

SELECT 
    user_id,
    order_id,
    order_date,
    previous_order_date,
    `30_rolling_days`,
    COUNT(order_id) OVER (
        PARTITION BY user_id 
        ORDER BY UNIX_TIMESTAMP(order_date) 
        RANGE BETWEEN 2592000 PRECEDING AND CURRENT ROW
    ) AS count_orders_in_the_last_30days
FROM user_orders
ORDER BY user_id, order_date;

注：2592000是30天对应的秒数（30243600），如果是PostgreSQL数据库，可以直接用日期类型的窗口帧：

-- PostgreSQL适配版本
SELECT 
    user_id,
    order_id,
    order_date,
    previous_order_date,
    "30_rolling_days",
    COUNT(order_id) OVER (
        PARTITION BY user_id 
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
    ) AS count_orders_in_the_last_30days
FROM user_orders
ORDER BY user_id, order_date;

逻辑说明 #

• PARTITION BY user_id 按用户ID分组，仅统计同一个用户的订单
• ORDER BY order_date 把同一用户的所有订单按下单时间升序排序
• 窗口帧范围设置为「当前订单时间往前30天 ~ 当前订单时间」，统计范围内的订单总数，和你给出的样例输出完全匹配，比如2019-09-24的订单往前30天是2019-08-25，2019-08-16的订单不在统计范围内，最终计数为3，符合预期。

方案2：自关联实现（兼容所有支持关联操作的数据库，适合不支持窗口函数的低版本数据库） #

SELECT 
    a.user_id,
    a.order_id,
    a.order_date,
    a.previous_order_date,
    a.`30_rolling_days`,
    COUNT(b.order_id) AS count_orders_in_the_last_30days
FROM user_orders a
LEFT JOIN user_orders b 
    ON a.user_id = b.user_id
    AND b.order_date BETWEEN DATE_SUB(a.order_date, INTERVAL 30 DAY) AND a.order_date
GROUP BY a.user_id, a.order_id, a.order_date, a.previous_order_date, a.`30_rolling_days`
ORDER BY a.user_id, a.order_date;

内容的提问来源于stack exchange，提问作者layal