如何高效将前7天的Error和Sales数据作为列展示在当日数据行

最优方案：使用窗口函数LAG() #

该方案适用于所有支持SQL窗口函数的引擎（MySQL 8.0+、PostgreSQL、Hive、Spark SQL、BigQuery等），仅需一次全表扫描即可完成计算，性能远高于多次自连接，代码简洁易维护。
示例代码如下：

SELECT
    Date,
    Error,
    Sales,
    -- 前1天数据
    LAG(Error, 1) OVER (ORDER BY Date) AS `Error-1`,
    LAG(Sales, 1) OVER (ORDER BY Date) AS `Sales-1`,
    -- 前2天数据
    LAG(Error, 2) OVER (ORDER BY Date) AS `Error-2`,
    LAG(Sales, 2) OVER (ORDER BY Date) AS `Sales-2`,
    -- 前3天数据
    LAG(Error, 3) OVER (ORDER BY Date) AS `Error-3`,
    LAG(Sales, 3) OVER (ORDER BY Date) AS `Sales-3`,
    -- 以此类推补充到前7天即可
    LAG(Error, 4) OVER (ORDER BY Date) AS `Error-4`,
    LAG(Sales, 4) OVER (ORDER BY Date) AS `Sales-4`,
    LAG(Error, 5) OVER (ORDER BY Date) AS `Error-5`,
    LAG(Sales, 5) OVER (ORDER BY Date) AS `Sales-5`,
    LAG(Error, 6) OVER (ORDER BY Date) AS `Error-6`,
    LAG(Sales, 6) OVER (ORDER BY Date) AS `Sales-6`,
    LAG(Error, 7) OVER (ORDER BY Date) AS `Error-7`,
    LAG(Sales, 7) OVER (ORDER BY Date) AS `Sales-7`
FROM sales_data
ORDER BY Date;

LAG(字段名, 偏移量, 可选默认值)的逻辑是按OVER子句中ORDER BY指定的顺序排序，取当前行向前偏移N行的对应字段值，没有匹配行时返回NULL，完全匹配需求。如果需要按店铺、区域等维度分别计算前N天数据，只需在OVER子句中增加PARTITION BY 维度字段即可。 #

兼容方案：单次自连接+行转列 #

如果使用的是不支持窗口函数的老旧SQL引擎（如MySQL 5.x），可以用该方案替代7次自连接，性能提升明显：

SELECT
    t1.Date,
    t1.Error,
    t1.Sales,
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 1 THEN t2.Error END) AS `Error-1`,
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 1 THEN t2.Sales END) AS `Sales-1`,
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 2 THEN t2.Error END) AS `Error-2`,
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 2 THEN t2.Sales END) AS `Sales-2`,
    -- 补充到前7天即可
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 7 THEN t2.Error END) AS `Error-7`,
    MAX(CASE WHEN DATEDIFF(t1.Date, t2.Date) = 7 THEN t2.Sales END) AS `Sales-7`
FROM sales_data t1
LEFT JOIN sales_data t2
ON t2.Date >= DATE_SUB(t1.Date, INTERVAL 7 DAY) AND t2.Date <= DATE_SUB(t1.Date, INTERVAL 1 DAY)
GROUP BY t1.Date, t1.Error, t1.Sales
ORDER BY t1.Date;

该方案仅做1次自连接，用范围条件关联当前行前7天的所有数据，再通过CASE语句+聚合函数把多行数据转为多列，避免了多次自连接的冗余开销。 #

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