我有一个运行良好的代码--只根据手机使用的日期。
select date,
data,
SUM(data) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) data_30,
texts,
SUM(tests) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) text_30,
voice,
SUM(voice) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) voice_30,
wifi,
SUM(wifi)
OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) wifi_30
FROM Table我只是想弄清楚如何使用前30天的总和,但是否有可能包括第二个变量,例如,我想查看日期,按费率计划这些用法?
就像这样
select date,
plan, b, c, d,
data,
SUM(data) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) data_30,
texts,
SUM(tests) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) text_30,
voice,
SUM(voice) OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) voice_30,
wifi,
SUM(wifi)
OVER (ORDER BY date asc
ROWS between 30 PRECEDING and current row) wifi_30
FROM Table
group by date, plan, b, c, djust date结果
date usage last30sum
1/1 1 1
1/2 1 2
....
1/20 1 30如果我有源数据
date line rateplan usage
1/1 phone1 10gbplan 1
1/1 phone2 unlimited 2
1/2 phone3 10gbplan 1
....
1/30 phone200 10gbplan 1 我想看到解决问题的方法
date plan totalusage rolling_30
1/2 10gbplan 1 4+ sum(28 days before 1/2)你能按日期分组吗,unl可以得到最近30天的数据,unl,a,b,c只表示其他分组,可以按设备型号或区域分组。
发布于 2019-07-14 01:55:53
我通过添加分区来解决这个问题。
select date,
plan, b, c, d,
data,
SUM(data) OVER (partition by plan, b, c, d
ORDER BY date asc
ROWS between 30 PRECEDING and current row) data_30,
texts,
SUM(tests) OVER (partition by plan, b, c, d
ORDER BY date asc
ROWS between 30 PRECEDING and current row) text_30,
voice,
SUM(voice) OVER (partition by plan, b, c, d
ORDER BY date asc
ROWS between 30 PRECEDING and current row) voice_30,
wifi,
SUM(wifi)
OVER (partition by plan, b, c, d
ORDER BY date asc
ROWS between 30 PRECEDING and current row) wifi_30
FROM Tablehttps://stackoverflow.com/questions/57021036
复制相似问题