时间戳

首页 > 解决方案 > 过去 7 天的滚动总和，如何包括缺失的日期 - postresql

sql - 过去 7 天的滚动总和，如何包括缺失的日期 - postresql

问题描述

我试图得到这样的东西，但我只有前两列：

dates          sales     rolling_sum7days           
01-01-2019     1         1
02-01-2019     3         4
03-01-2019     5         9
04-01-2019     2        11
05-01-2019     7        18
06-01-2019     8        26
08-01-2019    10        35
09-01-2019     1        32
10-01-2019     8        39

我想出了这个，但还没有找到处理缺失值的方法，例如 07-01-2019 的销售额

SELECT dates 
sum(sales) over(order by dates ROWS BETWEEN 6 preceding AND CURRENT ROW) 
from table

我应该纠正什么？发现了一些类似的问题，但他们没有解决这个问题。例如：这个

标签： sqlpostgresql

解决方案

演示：db<>小提琴

SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT OUTER JOIN 
    generate_series('2019-01-01', '2019-01-11', interval '1 day') gs
    ON gs = mt.dates

要填充特定范围内的缺失日期，您可以使用generate_series()此日期范围并进行外部连接。

如果您不想修复 gs 参数，那么当然，您可以在之前计算它们，例如从表中获取 MIN 和 MAX：

演示：db<>小提琴

WITH date_bounds AS (
    SELECT min(dates), max(dates) FROM mytable
)
SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT OUTER JOIN 
    generate_series(
         (SELECT min FROM date_bounds), 
         (SELECT max FROM date_bounds), 
         interval '1 day'
    ) gs
    ON gs = mt.dates

推荐阅读