mysql语句实现hive开窗效果

建表语句
create table t{
    id varchar(20),
    dates varchar(20),
    v_num int
}charset=utf8;

插入多行数据
insert into t values
("a","2020-01",5),
("b","2020-01",20"),
```
```
```
("a","2020-05",25),
("b","2020-05",30"),

需求：根据建表语句求出
用户  月份  当月访问次数  截至当月月最大访问次数  总访问次数

1、求出当月访问次数,并保存为中间表t1
create table t1 as
select 
    id,//用户
    dates,//月份
    sum(v_num) sum_month_num//当月访问次数
from t
group by id,dates
    
2、求出最大访问次数(自关联),并保存为中间表t2
create table t2 as
select
    b.id,//用户
    b.dates,//月份
    max(a.sum_month_num) max_month_num,//截至当月月最大访问次数
    sum(a.sum_month_num) sum_total_num//总访问次数
from t1 a
join t1 b
on a.id= b.id 
where a.dates <= b.dates   
group by b.id,b.dates

3、数据聚合
select
    t1.id,//用户
    t1.dates,//月份
    t1.sum_month_num,//当月访问次数
    t2.max_month_num,//截至当月月最大访问次数
    t2.sum_total_num//总访问次数
from t1
join t2
on t1.id = t2.id and t1.month = t2.month
    
 个人觉得hive中开窗统计可以基础数据上统计多个数据，这里基础数据可以是单表或多表join，自己构建数据源。

多个窗口数据来源一致，可以减少join次数，sql语句表达更为简单。

对于mysql来说实现复杂的聚合统计效果，需要先单独统计，然后join操作聚合多个统计结果，sql逻辑比较复杂，通常使用中间表将需求分层实现，而hive则可以一步到位。

在这个需求中难度较大是第二步中求截至到当月的月最大访问就次数，这种按照某个条件递进式比较，常常也是使用自关联的方式，然后加上给出的条件，是比较常规的方式，也可以移植到类似的这种需求，例如求截至到当月销售量，销售额等等。

hive实现这个需求

创建hive表    
create external table if not exists t(
    id String,
    v_num String 
)partitioned by(dates String)
row format delimited
fields terminated by '\t'

插入数据(顺便复习hive加载数据的方式)
加载本地文件到hive
load data local inpath 'opt/module/datas/t.txt' into tbale t;

加载HDFS文件到hive
load data inpath '/user/atguigu/hive/t.txt' into table t;

基本插入数据
insert into table t partition(dates='2020-09') values('a',20),('b',30);
    

select
    id,//用户
    dates,//月份
    sum_month_num,//当月访问次数
    max(sum_month_num) over(partition by id,dates rows between unbounded preceding and current row),//截至到当前月月最大访问量
    sum(sum_month_num) over(partition by id) sum_total_num//总访问次数
from(
    select
        id,
        sum(v_num) sum_month_num
    from t
    group by id,dates

)

在hive中使用开窗函数over可以指定数据的范围，就拿第二步中统计截至到当前月月最大访问量，可以先计算出每个月的数据，然后开窗统计指定数据的范围便能有效利用聚合函数。

 

在mysql中条件判断就是自关联月份大小比较，数据就是有序的，在hive中计算当月访问次数分组统计后日期之前大小默认就是有序的，不必再添加order by