SQL计算pwrDay表各指标环比差值及创建视图的方案咨询

问题原因 #

• 原有自关联方案错误：关联条件仅校验同月份下日期差值为1，跨月场景（如9月30日与10月1日）无法匹配上一行数据，存在缺失统计日时也会关联到错误的历史数据。
• LAG函数报错原因：当前使用的MySQL版本低于8.0，窗口函数是MySQL 8.0版本新增特性，低版本无法识别；且原有LAG写法参数错误，偏移量填0会取当前行数据，计算差值永远为0。

解决方案 #

方案1：兼容所有MySQL版本的自关联写法 #

通过子查询匹配当前日期的上一个存在的统计日期，不受跨月、缺统计日影响：

SELECT
    curr.jour,
    curr.pwrconsohp - IFNULL(prev.pwrconsohp, 0) AS deltaconsoHP,
    curr.pwrconsohc - IFNULL(prev.pwrconsohc, 0) AS deltaconsoHC,
    curr.pwrprod - IFNULL(prev.pwrprod, 0) AS deltaProd
FROM pwrDay curr
LEFT JOIN pwrDay prev 
ON prev.jour = (SELECT MAX(jour) FROM pwrDay WHERE jour < curr.jour)
ORDER BY curr.jour;

方案2：MySQL 8.0及以上版本的LAG函数正确写法 #

指定窗口排序规则，设置正确的偏移量和缺省值：

SELECT
    jour,
    pwrconsohp - LAG(pwrconsohp, 1, 0) OVER(ORDER BY jour) AS deltaconsoHP,
    pwrconsohc - LAG(pwrconsohc, 1, 0) OVER(ORDER BY jour) AS deltaconsoHC,
    pwrprod - LAG(pwrprod, 1, 0) OVER(ORDER BY jour) AS deltaProd
FROM pwrDay
ORDER BY jour;

两种方案都可以直接用于创建视图，计算结果完全一致。

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