如何获取最近连续6个周日/周一的对应业务统计数据

核心思路 #

• 先过滤出所有符合星期要求（周日/周一）的业务数据，按日期倒序排列后取前6条即可。要是存在某周对应日期无业务数据的情况，需要先生成连续的目标星期日期序列，再左关联业务表补全数据，避免出现行数不足6条的问题。
• 不同数据库判断星期的内置函数有区别：
  • MySQL 用DAYOFWEEK()，周日返回值为1，周一返回值为2
  • PostgreSQL 用EXTRACT(DOW FROM 日期字段)，周日返回值为0，周一返回值为1
  • SQL Server 用DATEPART(WEEKDAY, 日期字段)，默认配置下周日返回值为1，周一返回值为2

MySQL 可运行示例（以获取最近6个周日销售数据为例） #

SELECT 
    DATE_FORMAT(t.target_date, '%d-%b-%y') AS `date`,
    'Sunday' AS `Day`,
    IFNULL(s.sales, 0) AS sales
FROM (
    -- 生成最近6个周日的连续日期序列
    SELECT 
        DATE_SUB(CURDATE(), INTERVAL (DAYOFWEEK(CURDATE()) - 1 + 7 * n) DAY) AS target_date
    FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) nums
) t
LEFT JOIN 你的销售数据表 s ON t.target_date = s.统计日期
ORDER BY t.target_date DESC;

如果需要获取周一的数据，只需要把代码里的DAYOFWEEK(CURDATE()) - 1修改为DAYOFWEEK(CURDATE()) - 2即可。

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