时间戳

首页 > 解决方案 > SUM OVER () 与 Group by 和前面

sql - SUM OVER () 与 Group by 和前面

问题描述

我有一个运行良好的代码 - 只有电话使用日期。

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, d

刚刚日期的结果

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 只是表示其他分组方式，可能是按设备型号或区域。

标签： sqlsumhiveqlwindow-functions

解决方案

我通过添加分区来解决

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

推荐阅读