当年/月/日累计指标数据初始化

 

-- 当年/月/日累计指标数据初始化
with data_day as (
    select 'a' name,'2016-07-01' as day, 1  as value union all
    select 'a' name,'2017-07-01' as day, 2  as value union all
    select 'a' name,'2018-05-01' as day, 3  as value union all
    select 'a' name,'2018-06-01' as day, 4  as value union all
    select 'a' name,'2018-07-01' as day, 5  as value union all
    select 'a' name,'2018-07-02' as day, 6  as value union all
    select 'a' name,'2018-07-03' as day, 7  as value union all
    select 'a' name,'2018-08-01' as day, 8  as value union all
    select 'a' name,'2018-08-03' as day, 9  as value union all
    select 'a' name,'2018-09-01' as day, 10 as value union all
    select 'a' name,'2018-09-02' as day, 11 as value union all
    select 'a' name,'2018-09-03' as day, 12 as value
)
,cal as (
    select '2018-07-01' as day union all
    select '2018-07-02' as day union all
    select '2018-07-03' as day union all
    select '2018-08-01' as day union all
    select '2018-08-02' as day union all
    select '2018-08-03' as day union all
    select '2018-09-02' as day union all
    select '2018-09-03' as day
)
select
     t2.name
    ,t1.day
    ,sum(case when t1.day = t2.day                         then value end) as value_d -- 当日数量
    ,sum(case when trunc(t1.day,'MM') = trunc(t2.day,'MM') then value end) as value_m -- 当月累计
    ,sum(case when trunc(t1.day,'YY') = trunc(t2.day,'YY') then value end) as value_y -- 当年累计
    ,sum(value)                                                            as value_t -- 全量累计
from cal t1
join data_day t2
    on 1 = 1
where t2.day <= t1.day
group by
     t2.name
    ,t1.day
order by
     t2.name
    ,t1.day
;

 

+-------+-------------+----------+----------+----------+----------+--+
| name  |     day     | value_d  | value_m  | value_y  | value_t  |
+-------+-------------+----------+----------+----------+----------+--+
| a     | 2018-07-01  | 5        | 5        | 12       | 15       |
| a     | 2018-07-02  | 6        | 11       | 18       | 21       |
| a     | 2018-07-03  | 7        | 18       | 25       | 28       |
| a     | 2018-08-01  | 8        | 8        | 33       | 36       |
| a     | 2018-08-02  | NULL     | 8        | 33       | 36       |
| a     | 2018-08-03  | 9        | 17       | 42       | 45       |
| a     | 2018-09-02  | 11       | 21       | 63       | 66       |
| a     | 2018-09-03  | 12       | 33       | 75       | 78       |
+-------+-------------+----------+----------+----------+----------+--+

 

select
     day
    ,sum(value) over(partition by trunc(day,'MM') order by day asc) as value_asc1                                              -- 当月累计
    ,sum(value) over(partition by trunc(day,'MM') order by day rows between unbounded preceding and current row) as value_asc2 -- 当月累计
from (
    select '2018-08-01' as day ,100 as value union all
    select '2018-08-03' as day ,100 as value
) t
;

 

+-------------+-------------+-------------+--+
|     day     | value_asc1  | value_asc2  |
+-------------+-------------+-------------+--+
| 2018-08-01  | 100         | 100         |
| 2018-08-03  | 200         | 200         |
+-------------+-------------+-------------+--+