SQLite按时间间隔分组数据：如何包含无订单日期？

你的问题核心是原查询仅从存在订单的数据中分组，所以无法返回无订单的日期。要解决这个问题，我们需要先生成指定时间范围内的所有日期（以天为单位），再将这个日期序列与你的订单统计结果做左连接，最后用COALESCE将无订单的统计值替换为0。

下面分不同数据库给出具体实现：

1. MySQL（8.0及以上支持递归CTE） #

首先用递归CTE生成目标时间范围内每天的起始毫秒时间戳，再左连接订单表统计数量：

WITH RECURSIVE date_ranges AS (
    -- 起始日期：2020-06-01 00:00:00 对应的毫秒时间戳
    SELECT 1590969600000 AS day_start
    UNION ALL
    SELECT day_start + 86400000  -- 增加一天的毫秒数
    FROM date_ranges
    -- 结束日期：2020-06-30 23:59:59 对应的毫秒时间戳
    WHERE day_start + 86400000 <= 1593388799000
)
SELECT 
    dr.day_start AS gap,
    COALESCE(SUM(o.quantity), 0) AS totalOrdersBetweenInterval
FROM date_ranges dr
LEFT JOIN Orders o 
    -- 匹配当天的订单：order_time >= 当天起始，且 < 下一天起始
    ON o.order_time >= dr.day_start 
    AND o.order_time < dr.day_start + 86400000
GROUP BY dr.day_start
ORDER BY dr.day_start ASC;

2. PostgreSQL #

PostgreSQL可以用generate_series直接生成日期序列，再转换为毫秒时间戳：

SELECT 
    dr.day_start AS gap,
    COALESCE(SUM(o.quantity), 0) AS totalOrdersBetweenInterval
FROM (
    SELECT 
        -- 将日期转换为毫秒时间戳：timestamp转bigint是秒，乘1000转毫秒
        generate_series(
            '2020-06-01'::timestamp, 
            '2020-06-30'::timestamp, 
            '1 day'
        )::bigint * 1000 AS day_start
) dr
LEFT JOIN Orders o 
    ON o.order_time >= dr.day_start 
    AND o.order_time < dr.day_start + 86400000
GROUP BY dr.day_start
ORDER BY dr.day_start ASC;

3. 通用思路（适用于不支持CTE的数据库） #

如果你的数据库不支持递归CTE或generate_series，可以创建一个日期维度表（提前存储所有可能的日期对应的时间戳），然后用这个表左连接订单表进行统计：

SELECT 
    dt.day_start AS gap,
    COALESCE(SUM(o.quantity), 0) AS totalOrdersBetweenInterval
FROM date_dimension dt
LEFT JOIN Orders o 
    ON o.order_time >= dt.day_start 
    AND o.order_time < dt.day_start + 86400000
WHERE dt.day_start >= 1590969600000 
  AND dt.day_start <= 1593388799000
GROUP BY dt.day_start
ORDER BY dt.day_start ASC;

关键说明 #

• COALESCE(SUM(o.quantity), 0)：当某一天没有订单时，SUM(o.quantity)会返回NULL，用COALESCE将其替换为0，满足你的需求。
• 时间范围匹配：用order_time >= day_start AND order_time < day_start + 86400000比原查询的取模方式更直观，也避免了时间戳取模可能出现的边界问题。

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