如果今天的结果为空，则显示昨天的总数

Question

我的代码是一段时间内累积的总收益。如果某一天是空的(当天没有收入)，我需要它来显示前一天的总数。CASE WHEN (今天为空)，昨日数据否则今日合计

我不确定这个问题的语法是什么。

select distinct
 date_trunc('day',admit_date) as admit_date,
 revenue,
 sum(revenue) over(order by admit_date) as running_rev
 from dailyrev
 order by admit_date 

预期结果：

Day 1: $100
Day 2: $200
Day 3: (no data so show Day 2 data) $200

Answer 1

也许这就是你需要的：

SELECT admit_date,
       prev_revs[cardinality(prev_revs)] AS adj_revenue,
       sum(prev_revs[cardinality(prev_revs)])
          OVER (ORDER BY admit_date) AS running_sum
FROM (SELECT date_trunc('day', admit_date) AS admit_date,
             array_remove(array_agg(revenue)
                             OVER (order by admit_date),
                          NULL) AS prev_revs
      FROM dailyrev) AS q
ORDER BY admit_date;

不幸的是，PostgreSQL还不支持IGNORE NULLS子句，那么它会更简单。

Answer 2

我不确定这是不是你想要的，但是试试这个：

SELECT
    gs.date::date AS admit_date,
    (SELECT revenue FROM dailyrev WHERE admit_date::date = gs.date) AS revenue,
    (SELECT SUM(revenue) FROM dailyrev WHERE admit_date::date <= gs.date) AS accumulated_total
FROM
    generated_series(
        (SELECT MIN(admit_date::date) FROM dailyrev),
        (SELECT MAX(admit_date::date) FROM dailyrev),
        INTERVAL '1 day'
    ) gs
ORDER BY gs.date::date;

是的，它看起来不是那么好，但是..