SQL查询获取去年同周几日期 实现销售数据对比

核心逻辑 #

你需要匹配的是年份差为1、星期几完全一致、且日期差最接近365天的对应日期，比如你举例的2021年1月6日为周二，对应的2020年同周二的最近日期就是2020年1月5日，完全符合需求。

实现步骤 #

• 首先给销售表的日期字段计算星期几标识，不同数据库的星期计算函数略有差异，注意周起始规则（部分数据库默认周日为一周第一天，可根据业务调整偏移量）
• 关联两年的销售数据，关联条件同时满足日期年份差为1、两天的星期几标识完全相等
• 若需要排除极端偏差的匹配结果，可以额外加限制条件ABS(DATEDIFF(本年日期, 去年日期)) BETWEEN 358 AND 371，保证两个日期的差在±7天范围内

注意：如果业务需要排除春节、国庆等移动节假日的影响，可以额外在WHERE条件中加入节假日排除规则即可。

MySQL 示例代码 #

假设你的销售表名为sales，包含字段销售日期(sale_date)、销售额(sale_amount)，参考实现如下：

SELECT 
    curr.sale_date AS 本年日期,
    DAYNAME(curr.sale_date) AS 星期,
    curr.sale_amount AS 本年销售额,
    prev.sale_date AS 去年同周几日期,
    prev.sale_amount AS 去年同周几销售额,
    (curr.sale_amount - prev.sale_amount) AS 同比差值,
    CONCAT(ROUND((curr.sale_amount - prev.sale_amount)/prev.sale_amount * 100, 2), '%') AS 同比增长率
FROM sales curr
LEFT JOIN sales prev 
    ON YEAR(curr.sale_date) = YEAR(prev.sale_date) + 1
    AND DAYOFWEEK(curr.sale_date) = DAYOFWEEK(prev.sale_date)
    AND ABS(DATEDIFF(curr.sale_date, prev.sale_date)) BETWEEN 358 AND 371
WHERE YEAR(curr.sale_date) = 2021 -- 替换为你要查询的年份
ORDER BY curr.sale_date;

其他数据库适配修改点 #

• SQL Server：将DAYOFWEEK()替换为DATEPART(WEEKDAY, sale_date)，DATEDIFF()语法改为DATEDIFF(day, prev.sale_date, curr.sale_date)
• PostgreSQL：将DAYOFWEEK()替换为EXTRACT(DOW FROM sale_date)，YEAR()替换为EXTRACT(YEAR FROM sale_date)

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