首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >对()求和,包括分组依据和前面的

问对()求和,包括分组依据和前面的
EN

Stack Overflow用户
提问于 2019-07-14 00:41:20
回答 1查看 477关注 0票数 0

我有一个运行良好的代码--只根据手机使用的日期。

代码语言:javascript
复制
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天的总和,但是否有可能包括第二个变量,例如,我想查看日期,按费率计划这些用法?

就像这样

代码语言:javascript
复制
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, d

just date结果

代码语言:javascript
复制
date  usage  last30sum
1/1   1       1
1/2   1       2
....
1/20  1       30

如果我有源数据

代码语言:javascript
复制
date line     rateplan        usage  
1/1  phone1   10gbplan        1
1/1  phone2   unlimited       2                
1/2  phone3   10gbplan        1                
....
1/30 phone200 10gbplan        1                

我想看到解决问题的方法

代码语言:javascript
复制
date plan        totalusage   rolling_30
1/2  10gbplan   1            4+ sum(28 days before 1/2)

你能按日期分组吗,unl可以得到最近30天的数据,unl,a,b,c只表示其他分组,可以按设备型号或区域分组。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-07-14 01:55:53

我通过添加分区来解决这个问题。

代码语言:javascript
复制
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 Table
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/57021036

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档