如何修改SQL语句让上周每日rideId非空请求统计显示0值天数

这个问题我之前也碰到过——原来的查询只会返回有符合条件请求的天数，要把空天数也列出来，核心思路是先生成过去7天的完整日期/星期序列，再把你的统计结果和这个序列做左连接，这样没有数据的天数就能自动填充count为0了。

下面以MySQL为例，给出修改后的完整SQL语句：

WITH RECURSIVE date_range AS (
    -- 起始日期：6天前（加上今天正好覆盖过去7天）
    SELECT 
        CURRENT_DATE - INTERVAL 6 DAY AS date_day,
        DAYNAME(CURRENT_DATE - INTERVAL 6 DAY) AS day_name,
        DAYOFWEEK(CURRENT_DATE - INTERVAL 6 DAY) AS day_num
    UNION ALL
    -- 递归生成后续每一天的日期、星期名、星期序号
    SELECT 
        date_day + INTERVAL 1 DAY,
        DAYNAME(date_day + INTERVAL 1 DAY),
        DAYOFWEEK(date_day + INTERVAL 1 DAY)
    FROM date_range
    WHERE date_day < CURRENT_DATE
)
SELECT 
    dr.day_name AS Day,
    -- 把null替换成0，确保无数据天数显示0
    COALESCE(r.request_count, 0) AS request_count
FROM date_range dr
-- 左连接原统计结果，保证所有日期都被保留
LEFT JOIN (
    -- 你的原统计逻辑，只是给count起了别名方便后续使用
    SELECT 
        DAYNAME(time) AS day_name,
        COUNT(*) AS request_count
    FROM requests
    WHERE time >= CURRENT_TIMESTAMP - INTERVAL 7 DAY
      AND rideId IS NOT NULL
    GROUP BY DAYNAME(time)
) r ON dr.day_name = r.day_name
-- 按星期序号排序，保证输出顺序符合日常习惯
ORDER BY dr.day_num;

关键部分解释： #

1. 递归CTE date_range：生成过去7天的完整日期序列，同时计算出每个日期对应的星期名称和星期序号，用来保证排序逻辑正确，不会出现星期按字母乱序的情况。
2. 左连接统计结果：原统计只返回有数据的星期，左连接后所有日期都会被保留，无数据的行对应的request_count会是null。
3. COALESCE函数：把null值替换为0，满足无请求天数显示0的需求。
4. 按星期序号排序：避免星期名称按字母排序（比如Friday会排在Monday前面），保证输出顺序是自然的星期顺序。

其他数据库适配提示： #

如果用的是PostgreSQL，生成日期序列可以用generate_series更简洁：

WITH date_range AS (
    SELECT
        current_date - generate_series(6, 0, -1) AS date_day,
        TRIM(to_char(date_day, 'Day')) AS day_name,
        extract(isodow from date_day) AS day_num
    FROM generate_series(current_date - interval '6 days', current_date, interval '1 day') AS date_day
)
SELECT
    dr.day_name AS Day,
    COALESCE(r.request_count, 0) AS request_count
FROM date_range dr
LEFT JOIN (
    SELECT
        TRIM(to_char(time, 'Day')) AS day_name,
        COUNT(*) AS request_count
    FROM requests
    WHERE time >= current_timestamp - interval '7 days'
      AND rideId IS NOT NULL
    GROUP BY TRIM(to_char(time, 'Day'))
) r ON dr.day_name = r.day_name
ORDER BY dr.day_num;

注意事项： #

• 要确保生成日期序列的时区和requests表中time字段的时区一致，避免日期匹配错误。
• 部分数据库的星期名称函数可能返回带空格的结果（比如PostgreSQL的to_char('Day')），记得用TRIM处理后再做连接。
• 如果想要自定义星期顺序（比如周一作为一周的第一天），可以调整排序用的day_num计算逻辑，比如MySQL中可以用(DAYOFWEEK(date_day) + 5) % 7 + 1来转换序号。

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