累計金額到current_date

基表

   month     id    sales  cumulative_sales
2021-01-01  33205   10           10
2021-02-01  33205   15           25

根據上面的基表，即使沒有銷售，我也想添加到當月的更多行。

預期表

   month     id    sales cumulative_sales
2021-01-01  33205   10           10
2021-02-01  33205   15           25
2021-03-01  33205    0           25
2021-04-01  33205    0           25
2021-05-01  33205    0           25
.........
2021-11-01  33205    0           25

我的查詢停止于

select month, id, sales, 
sum(sales) over (partition by id 
                 order by month 
                 rows between unbounded preceding and current row) as cumulative_sales
from base_table

uj5u.com熱心網友回復：

這有效。假設該month列被限制為僅包含“本月的第一天”日期。使用所需的硬編碼開始日期，或使用另一個 CTE 從base_table以下位置獲取最早日期：

with base_table as (
    select *
    from (values
     ('2021-01-01'::date,33205,10)
    ,('2021-02-01'      ,33205,15)
    ,('2021-01-01'      ,12345,99)
    ,('2021-04-01'      ,12345,88)
         ) dat("month",id,sales)
)
select cal.dt::date
      ,list.id
      ,coalesce(dat.sales,0) as sales
      ,coalesce(sum(dat.sales) over (partition by list.id order by cal.dt),0) as cumulative_sales
from Generate_Series('2020-06-01' /* use desired start date here */,current_date,'1 month') cal(dt)
     cross join (select distinct id from base_table) list
     left join base_table dat on dat."month" = cal.dt and dat.id = list.id
;

結果：

| dt         | id    | sales | cumulative_sales |
 ------------ ------- ------- ------------------ 
| 2020-06-01 | 12345 |     0 |                0 |
| 2020-07-01 | 12345 |     0 |                0 |
| 2020-08-01 | 12345 |     0 |                0 |
| 2020-09-01 | 12345 |     0 |                0 |
| 2020-10-01 | 12345 |     0 |                0 |
| 2020-11-01 | 12345 |     0 |                0 |
| 2020-12-01 | 12345 |     0 |                0 |
| 2021-01-01 | 12345 |    99 |               99 |
| 2021-02-01 | 12345 |     0 |               99 |
| 2021-03-01 | 12345 |     0 |               99 |
| 2021-04-01 | 12345 |    88 |              187 |
| 2021-05-01 | 12345 |     0 |              187 |
| 2021-06-01 | 12345 |     0 |              187 |
| 2021-07-01 | 12345 |     0 |              187 |
| 2021-08-01 | 12345 |     0 |              187 |
| 2021-09-01 | 12345 |     0 |              187 |
| 2021-10-01 | 12345 |     0 |              187 |
| 2021-11-01 | 12345 |     0 |              187 |
| 2020-06-01 | 33205 |     0 |                0 |
| 2020-07-01 | 33205 |     0 |                0 |
| 2020-08-01 | 33205 |     0 |                0 |
| 2020-09-01 | 33205 |     0 |                0 |
| 2020-10-01 | 33205 |     0 |                0 |
| 2020-11-01 | 33205 |     0 |                0 |
| 2020-12-01 | 33205 |     0 |                0 |
| 2021-01-01 | 33205 |    10 |               10 |
| 2021-02-01 | 33205 |    15 |               25 |
| 2021-03-01 | 33205 |     0 |               25 |
| 2021-04-01 | 33205 |     0 |               25 |
| 2021-05-01 | 33205 |     0 |               25 |
| 2021-06-01 | 33205 |     0 |               25 |
| 2021-07-01 | 33205 |     0 |               25 |
| 2021-08-01 | 33205 |     0 |               25 |
| 2021-09-01 | 33205 |     0 |               25 |
| 2021-10-01 | 33205 |     0 |               25 |
| 2021-11-01 | 33205 |     0 |               25 |

標籤：sql PostgreSQL

上一篇：計算列中的不同值

下一篇：如何在查詢中添加輔助列？