Hive从入门到放弃——玩一玩Hive的数据分析开窗函数（十五）

最新推荐文章于 2022-08-24 10:57:23 发布
最新推荐文章于 2022-08-24 10:57:23 发布
阅读量489 收藏
点赞数 1
分类专栏： Hadoop Hive 文章标签： hive 开窗函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/LXWalaz1s1s/article/details/106564439
版权

背 景

  Hive顶着大数据数据仓库的头衔，那么数据分析是在常用不过的功能了，所以，而数据分析钟，聚合函数和开窗函数是最重要的两环，聚合函数在博客Hive从入门到放弃——Hive 用户内置函数简介（十一）已经介绍，而开窗函数，因为器运用的灵活性，这里单独介绍一下；
  有些新手会混淆聚合函数和开窗函数的区别，聚合函数：目是是按照某些维度聚会，计算需求需要的度量值的统计指标；开窗函数:选取的维度本身有不同的值，这些值作为一个分组，计算这些组内的度量值的统计指标；如一个记录全校不同班级的语文成绩的数据表，聚合函数基本用来统计全校平均分，总分，最高分，最低分等，每个班平均分，总分，最高分，最低分等；而开窗函数不仅可以做到校平均分，总分，最高分，最低分等，每个班平均分，总分，最高分，最低分等，还可以每班成绩排序，全校成绩排序，从一班开始累计人数等分析，更好的实现不同维度组的度量值的统计指标，下面进入开窗函数的正题；

数据表准备

  这里我们新建一个表ods_sale_order_producttion_amount（ods产品每天销售总额），其中建表语句，数据加载，数据预览如下；

-- DDL建表
CREATE TABLE `ods_sale_order_producttion_amount`(
  `month_key` string COMMENT '月份', 
  `date_key` string COMMENT '日期', 
  `production_amount` decimal(18,2) COMMENT '产品总值')
COMMENT 'ods产品每天销售总额'
PARTITIONED BY ( 
  `event_year` int, 
  `event_week` int, 
  `event_day` string, 
  `event_hour` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://dw-test-cluster/hive/warehouse/ods/sale/ods_sale_order_producttion_amount'
TBLPROPERTIES ('parquet.compression'='snappy')

-- 装入数据
insert into ods_sale_order_producttion_amount partition(event_year=2020,event_week=30,event_day='20200731',event_hour='00')
select  '202005','20200501',199000.00
union all 
select  '202005','20200502',185000.00
union all 
select  '202005','20200503',199000.00
union all 
select  '202005','20200504',138500.00
union all 
select  '202005','20200505',196540.00
union all 
select  '202005','20200506',138500.00
union all 
select  '202005','20200507',159840.00
union all 
select  '202005','20200508',189462.00
union all
select  '202005','20200509',200000.00
union all
select  '202005','20200510',198540.00
union all 
select  '202006','20200601',189000.00
union all 
select  '202006','20200602',185000.00
union all 
select  '202006','20200603',189000.00
union all 
select  '202006','20200604',158500.00
union all 
select  '202006','20200605',200140.00
union all 
select  '202006','20200606',158500.00
union all 
select  '202006','20200607',198420.00
union all 
select  '202006','20200608',158500.00
union all
select  '202006','20200609',200100.00
union all
select  '202006','20200610',135480.00

-- 数据预览
month_key       date_key        production_amount       event_year      event_week      event_day       event_hour
202005  20200501        199000.00       2020    30      20200731        00
202005  20200502        185000.00       2020    30      20200731        00
202005  20200503        199000.00       2020    30      20200731        00
202005  20200504        138500.00       2020    30      20200731        00
202005  20200505        196540.00       2020    30      20200731        00
202005  20200506        138500.00       2020    30      20200731        00
202005  20200507        159840.00       2020    30      20200731        00
202005  20200508        189462.00       2020    30      20200731        00
202005  20200509        200000.00       2020    30      20200731        00
202005  20200510        198540.00       2020    30      20200731        00
202006  20200601        189000.00       2020    30      20200731        00
202006  20200602        185000.00       2020    30      20200731        00
202006  20200603        189000.00       2020    30      20200731        00
202006  20200604        158500.00       2020    30      20200731        00
202006  20200605        200140.00       2020    30      20200731        00
202006  20200606        158500.00       2020    30      20200731        00
202006  20200607        198420.00       2020    30      20200731        00
202006  20200608        158500.00       2020    30      20200731        00
202006  20200609        200100.00       2020    30      20200731        00
202006  20200610        135480.00       2020    30      20200731        00
Time taken: 0.233 seconds, Fetched: 20 row(s)

开窗分组排序函数

  • ROW_NUMBER:
    用法：ROW_NUMBER() OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的排名结果，即使排序列的值一样，组内排名也有先后，最终的组内排名值等于最终的组内参与排名的行总数；
  • RANK
    用法：RANK() OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的排名结果，排序列的值一样，组内排名一样，但是下一个排名会跳过上组排名重复的个数，可能出现间断的名次，即最终的组内排名值等于最终的组内参与排名的行总数；
  • DENSE_RANK
    用法：DENSE_RANK() OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的排名结果，排序列的值一样，组内排名一样，但是下一个排名不会跳过上组排名重复的个数，即最终的组内排名小于等于最终的组内参与排名的行总数；
  • CUME_DIST
    用法：CUME_DIST() OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的排名百分比占比，取值为数学区间（0,1](大于0，小于等于1)，排序列的值一样，组内百分比排名一样，值的计算上等于小于等于该列组内RANK()值的个数/组内总行数，，即最终的组内排名百分比总是等于1.0；
  • PERCENT_RANK
    用法：PERCENT_RANK() OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的排名百分比占比，取值为数学区间[0,1](大于等于0，小于等于1)，而且百分比排名总是从0开始排的，排序列的值一样，组内百分比排名一样，而且一定是从等于0开始往后排的，值的计算上等于(组内当前行的RANK值-1)/(组内总行数-1)，即最终的组内排名百分比不一定等于1.0,当组内最大的RANK值也发生了重复的时候，则不会等于1.0，否则等于1.0；
  • NTILE
    用法：NTILE(num) OVER([PARTITION BY col1.col2,…] order by col1.col2,… ASC|DESC)
    返回值：用于将分组数据按照顺序切分成num片，返回根据分区列的每一组数据按照排序列的顺序或者倒序排序的结果平均的分配到num个切片中，如果分配不均匀，如将组内的10行数按分组排序规则分配给7个切片，则余数为3，这三个余下的分片会平均从较小的分片里面开始平摊，即分片1,2,3能分到两行数据，剩下的能分到一行，返回的值时该行数据对应的分片值，分片从1开始计算；
    运用：常用来获取1/n时间段内的最大，最小收益等，如按照时间分组排序，平均分成3份，获取每组内前m段时间内的最好的业绩，则只需要取到NTILE()处理后的返回值的小于等于m即可；
    ROW_NUMBER() ,RANK() ,DENSE_RANK(),CUME_DIST()，PERCENT_RANK()，NTILE() 的Hive Cli实现效果：如下；
hive> set hive.cli.print.header=true;
hive> select
    >
    >         ROW_NUMBER() OVER(PARTITION BY month_key order by production_amount DESC)   row_number_result
    >        ,RANK() OVER(PARTITION BY month_key order by production_amount DESC)         rank_result
    >        ,DENSE_RANK() OVER(PARTITION BY month_key order by production_amount DESC)   dense_rank_result
    >        ,month_key
    >        ,date_key
    >        ,production_amount
    > from ods_sale_order_producttion_amount where event_day='20200731';
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200721163446_108a897b-ddf1-46ae-9bd8-a81a2afec8c9
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0123, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0123/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0123
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-21 16:35:03,990 Stage-1 map = 0%,  reduce = 0%
2020-07-21 16:35:12,456 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.76 sec
2020-07-21 16:35:17,720 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.95 sec
MapReduce Total cumulative CPU time: 6 seconds 950 msec
Ended Job = job_1592876386879_0123
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 6.95 sec   HDFS Read: 14952 HDFS Write: 970 SUCCESS
Total MapReduce CPU Time Spent: 6 seconds 950 msec
OK
Time taken: 32.507 seconds, Fetched: 20 row(s)

  为了方便看结果，我把结果拉到表格内来格式规范点，看起来舒服点，具体结果如表1；

表1 ROW_NUMBER() ,RANK() ,DENSE_RANK(),CUME_DIST()，PERCENT_RANK()，NTILE()排序结果
row_number_resultrank_resultdense_rank_resultcume_dist_resultpercent_rank_resultntile2_resultntile3_resultntile4_resultntile7_resultmonth_keydate_keyproduction_amount
1110.10.0111120200620200605200140
2220.20.1111111111111111111120200620200609200100
3330.30.2222222222222222111220200620200607198420
4440.50.3333333333333333112220200620200601189000
5440.50.3333333333333333122320200620200603189000
6650.60.5555555555555556222320200620200602185000
7760.90.6666666666666666223420200620200604158500
8760.90.6666666666666666233520200620200606158500
9760.90.6666666666666666234620200620200608158500
101071.01.0234720200620200610135480
1110.10.0111120200520200509200000
2220.30.1111111111111111111120200520200501199000
3220.30.1111111111111111111220200520200503199000
4430.40.3333333333333333112220200520200510198540
5540.50.4444444444444444122320200520200505196540
6650.60.5555555555555556222320200520200508189462
7760.70.6666666666666666223420200520200502185000
8870.80.7777777777777778233520200520200507159840
9981.00.8888888888888888234620200520200504138500
10981.00.8888888888888888234720200520200506138500

开窗基本集合函数

  • COUNT
    用法：COUNT(*) OVER([PARTITION BY col1.col2,…] )
    返回值：返回根据分区列的每一组数据的总行数；
  • SUM
    用法：SUM(coln) OVER([PARTITION BY col1.col2,…] )
    返回值：返回根据分区列的每一组数据的关于coln的汇总结果；
  • MIN
    用法：MIN(coln) OVER([PARTITION BY col1.col2,…] )
    返回值：返回根据分区列的每一组数据的关于coln的最小值；
  • MAX
    用法：MAX(coln) OVER([PARTITION BY col1.col2,…] )
    返回值：返回根据分区列的每一组数据的关于coln的最大值；
  • AVG
    用法：AVG(coln) OVER([PARTITION BY col1.col2,…] )
    返回值：返回根据分区列的每一组数据的关于coln的平均值；
    COUNT,SUM,MIN.MAX,AVG的Hive Cli实现：如下；
hive> set hive.cli.print.header=true;
hive> select month_key,date_key,production_amount
    >       ,count(3) over(partition by month_key) as `每月份数`
    >       ,sum(production_amount) OVER(partition by month_key) as `每月总和`
    >       ,min(production_amount) OVER(partition by month_key) as `每月最小收入`
    >       ,max(production_amount) OVER(partition by month_key) as `每月最大收入`
    >       ,avg(production_amount) OVER(partition by month_key) as `每月平均收入`
    > from dw.ods_sale_order_producttion_amount
    > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729105357_e57ed4d0-f210-431c-8889-5a138cbb0032
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0139, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0139/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0139
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 10:54:10,089 Stage-1 map = 0%,  reduce = 0%
2020-07-29 10:54:19,185 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.93 sec
2020-07-29 10:54:25,548 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.35 sec
MapReduce Total cumulative CPU time: 8 seconds 350 msec
Ended Job = job_1592876386879_0139
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 8.35 sec   HDFS Read: 18912 HDFS Write: 1807 SUCCESS
Total MapReduce CPU Time Spent: 8 seconds 350 msec
OK
month_key       date_key        production_amount       每月份数        每月总和        每月最小收入    每月最大收入    每月平均收入
202005  20200501        199000.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200510        198540.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200509        200000.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200508        189462.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200507        159840.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200506        138500.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200505        196540.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200504        138500.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200503        199000.00       10      1804382.00      138500.00       200000.00       180438.200000
202005  20200502        185000.00       10      1804382.00      138500.00       200000.00       180438.200000
202006  20200610        135480.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200609        200100.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200608        158500.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200607        198420.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200606        158500.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200605        200140.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200604        158500.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200603        189000.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200602        185000.00       10      1772640.00      135480.00       200140.00       177264.000000
202006  20200601        189000.00       10      1772640.00      135480.00       200140.00       177264.000000
Time taken: 29.364 seconds, Fetched: 20 row(s)
  • LEAD
    用法：LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值,第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
    返回值：回根据分区列的每一组统计窗口内往下第n行值；
  • LAG
    用法：LAG(col,n,DEFAULT)，用于统计窗口内往上第n行值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
    返回值：返回根据分区列的每一组统计窗口内往上第n行值；
  • FIRST_VALUE
    用法：FIRST_VALUE(coln) over (partition by col1,col2 order by col1,col2)
    返回值：返回根据分区排序列的每一组统计窗口内第1行值；
  • LAST_VALUE
    用法：LAST_VALUE(coln) over (partition by col1,col2 order by col1,col2)
    返回值：返回根据分区排序列的每一组统计窗口内截止到当前行最后一行值；
    LEAD，LAG，FIRST_VALUE，LAST_VALUE的Hive Cli实现:
hive> set hive.cli.print.header=true;
hive> select month_key,date_key,production_amount
    >       ,FIRST_VALUE(production_amount) OVER(partition by month_key order by date_key) as `月初数据`
    >       ,LAST_VALUE(production_amount) OVER(partition by month_key)  as `总月末数据`
    >       ,LAST_VALUE(production_amount) OVER(partition by month_key order by date_key)  as `截至当前月末数据`
    >       ,LAG(date_key)  OVER(partition by month_key order by date_key)             as `LAG默认参数`
    >       ,LAG(date_key,1)  OVER(partition by month_key order by date_key) as `LAG取date_key值向上移动1位`
    >       ,LAG(date_key,2,'19000101')  OVER(partition by month_key order by date_key) as `LAG取date_key值向上移动2位，取到null用19000101代替`
    >       ,LEAD(date_key)   OVER(partition by month_key order by date_key)               as `LEAD默认参数`
    >       ,LEAD(date_key,1)  OVER(partition by month_key order by date_key)   as `LEAD取date_key值向上移动1位`
    >       ,LEAD(date_key,2,'19000101')   OVER(partition by month_key order by date_key)  as `LEAD取date_key值向上移动1位，取到null用19000101代替`
    > from dw.ods_sale_order_producttion_amount
    > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729113052_9e57c0fe-5c46-4589-9406-f394787885bc
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0144, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0144/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0144
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 11:30:59,931 Stage-1 map = 0%,  reduce = 0%
2020-07-29 11:31:06,206 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.27 sec
2020-07-29 11:31:12,498 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 7.62 sec
MapReduce Total cumulative CPU time: 7 seconds 620 msec
Ended Job = job_1592876386879_0144
Launching Job 2 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0145, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0145/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0145
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2020-07-29 11:31:24,159 Stage-2 map = 0%,  reduce = 0%
2020-07-29 11:31:30,442 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 2.7 sec
2020-07-29 11:31:36,698 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 4.97 sec
MapReduce Total cumulative CPU time: 4 seconds 970 msec
Ended Job = job_1592876386879_0145
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.62 sec   HDFS Read: 20982 HDFS Write: 2084 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.97 sec   HDFS Read: 21609 HDFS Write: 2499 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 590 msec
OK
month_key       date_key        production_amount       月初数据        总月末数据      截至当前月末数据        lag默认参数     lag取date_key值向上移动1位      lag取date_key值向上移动2位，取到null用19000101代替   lead默认参数    lead取date_key值向上移动1位     lead取date_key值向上移动1位，取到null用19000101代替
202005  20200501        199000.00       199000.00       185000.00       199000.00       NULL    NULL    19000101        20200502        20200502        20200503
202005  20200510        198540.00       199000.00       185000.00       198540.00       20200509        20200509        20200508        NULL    NULL    19000101
202005  20200509        200000.00       199000.00       185000.00       200000.00       20200508        20200508        20200507        20200510        20200510        19000101
202005  20200508        189462.00       199000.00       185000.00       189462.00       20200507        20200507        20200506        20200509        20200509        20200510
202005  20200507        159840.00       199000.00       185000.00       159840.00       20200506        20200506        20200505        20200508        20200508        20200509
202005  20200506        138500.00       199000.00       185000.00       138500.00       20200505        20200505        20200504        20200507        20200507        20200508
202005  20200505        196540.00       199000.00       185000.00       196540.00       20200504        20200504        20200503        20200506        20200506        20200507
202005  20200504        138500.00       199000.00       185000.00       138500.00       20200503        20200503        20200502        20200505        20200505        20200506
202005  20200503        199000.00       199000.00       185000.00       199000.00       20200502        20200502        20200501        20200504        20200504        20200505
202005  20200502        185000.00       199000.00       185000.00       185000.00       20200501        20200501        19000101        20200503        20200503        20200504
202006  20200610        135480.00       189000.00       189000.00       135480.00       20200609        20200609        20200608        NULL    NULL    19000101
202006  20200609        200100.00       189000.00       189000.00       200100.00       20200608        20200608        20200607        20200610        20200610        19000101
202006  20200608        158500.00       189000.00       189000.00       158500.00       20200607        20200607        20200606        20200609        20200609        20200610
202006  20200607        198420.00       189000.00       189000.00       198420.00       20200606        20200606        20200605        20200608        20200608        20200609
202006  20200606        158500.00       189000.00       189000.00       158500.00       20200605        20200605        20200604        20200607        20200607        20200608
202006  20200605        200140.00       189000.00       189000.00       200140.00       20200604        20200604        20200603        20200606        20200606        20200607
202006  20200604        158500.00       189000.00       189000.00       158500.00       20200603        20200603        20200602        20200605        20200605        20200606
202006  20200603        189000.00       189000.00       189000.00       189000.00       20200602        20200602        20200601        20200604        20200604        20200605
202006  20200602        185000.00       189000.00       189000.00       185000.00       20200601        20200601        19000101        20200603        20200603        20200604
202006  20200601        189000.00       189000.00       189000.00       189000.00       NULL    NULL    19000101        20200602        20200602        20200603
Time taken: 45.744 seconds, Fetched: 20 row(s)

LAG和LEAD默认的第二个参数是1，第三个参数是null
OVER(partition by col1 order by col2),加了更细力度order by的列后，就可以做到截止到目前列的数据，这个在扩展中可以详细讲；

开窗基本集合函数扩展

  另一种情况的运用，就是累计每个月截止到目前行的业绩总和，平均业绩，最大业绩等等，这里以求总和为例子，就是我们经常说的MTD业绩，如按照业绩从小到大排列，求截至目前行的总值，具体如下图所示：
在这里插入图片描述

图1 根据RANGE或者ROWS累计开窗

  这里可以结合RANGE或者ROWS的来使用，RANGE和ROWS还是有点区别的，只不过说这个列子不明显，可参考下图的一个列子，RANGE会把相同partition和order by的值显示为一样的最终结果，但是ROWS不会，Hive Cli代码和效果如下；


hive> set hive.cli.print.header=true;
hive> select month_key,date_key,production_amount
  >      ,sum(production_amount) OVER(partition by month_key order by production_amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `RANGE每月总和`
  >      ,sum(production_amount) OVER(partition by month_key order by production_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `ROWS每月总和`
  >
  >      ,min(production_amount) OVER(partition by month_key order by production_amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `RANGE每月最小收入`
  >      ,max(production_amount) OVER(partition by month_key order by production_amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `RANGE每月最大收入`
  >      ,avg(production_amount) OVER(partition by month_key order by production_amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `RANGE每月平均收入`
  >      ,min(production_amount) OVER(partition by month_key order by production_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `ROWS每月最小收入`
  >      ,max(production_amount) OVER(partition by month_key order by production_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `ROWS每月最大收入`
  >      ,avg(production_amount) OVER(partition by month_key order by production_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as `ROWS每月平均收入`
  > from dw.ods_sale_order_producttion_amount
  > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729151835_4e818a96-4447-4fad-aae5-8e14371e2ba6
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0152, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0152/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0152
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 15:18:43,001 Stage-1 map = 0%,  reduce = 0%
2020-07-29 15:18:53,853 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.91 sec
2020-07-29 15:19:01,158 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 8.56 sec
MapReduce Total cumulative CPU time: 8 seconds 560 msec
Ended Job = job_1592876386879_0152
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 8.56 sec   HDFS Read: 22462 HDFS Write: 2646 SUCCESS
Total MapReduce CPU Time Spent: 8 seconds 560 msec
OK
month_key       date_key        production_amount       range每月总和   rows每月总和    range每月最小收入       range每月最大收入       range每月平均收入       rows每月最小收入        rows每月最大收入    rows每月平均收入
202005  20200504        138500.00       277000.00       138500.00       138500.00       138500.00       138500.000000   138500.00       138500.00       138500.000000
202005  20200506        138500.00       277000.00       277000.00       138500.00       138500.00       138500.000000   138500.00       138500.00       138500.000000
202005  20200507        159840.00       436840.00       436840.00       138500.00       159840.00       145613.333333   138500.00       159840.00       145613.333333
202005  20200502        185000.00       621840.00       621840.00       138500.00       185000.00       155460.000000   138500.00       185000.00       155460.000000
202005  20200508        189462.00       811302.00       811302.00       138500.00       189462.00       162260.400000   138500.00       189462.00       162260.400000
202005  20200505        196540.00       1007842.00      1007842.00      138500.00       196540.00       167973.666667   138500.00       196540.00       167973.666667
202005  20200510        198540.00       1206382.00      1206382.00      138500.00       198540.00       172340.285714   138500.00       198540.00       172340.285714
202005  20200501        199000.00       1604382.00      1405382.00      138500.00       199000.00       178264.666667   138500.00       199000.00       175672.750000
202005  20200503        199000.00       1604382.00      1604382.00      138500.00       199000.00       178264.666667   138500.00       199000.00       178264.666667
202005  20200509        200000.00       1804382.00      1804382.00      138500.00       200000.00       180438.200000   138500.00       200000.00       180438.200000
202006  20200610        135480.00       135480.00       135480.00       135480.00       135480.00       135480.000000   135480.00       135480.00       135480.000000
202006  20200604        158500.00       610980.00       293980.00       135480.00       158500.00       152745.000000   135480.00       158500.00       146990.000000
202006  20200606        158500.00       610980.00       452480.00       135480.00       158500.00       152745.000000   135480.00       158500.00       150826.666667
202006  20200608        158500.00       610980.00       610980.00       135480.00       158500.00       152745.000000   135480.00       158500.00       152745.000000
202006  20200602        185000.00       795980.00       795980.00       135480.00       185000.00       159196.000000   135480.00       185000.00       159196.000000
202006  20200601        189000.00       1173980.00      984980.00       135480.00       189000.00       167711.428571   135480.00       189000.00       164163.333333
202006  20200603        189000.00       1173980.00      1173980.00      135480.00       189000.00       167711.428571   135480.00       189000.00       167711.428571
202006  20200607        198420.00       1372400.00      1372400.00      135480.00       198420.00       171550.000000   135480.00       198420.00       171550.000000
202006  20200609        200100.00       1572500.00      1572500.00      135480.00       200100.00       174722.222222   135480.00       200100.00       174722.222222
202006  20200605        200140.00       1772640.00      1772640.00      135480.00       200140.00       177264.000000   135480.00       200140.00       177264.000000
Time taken: 26.882 seconds, Fetched: 20 row(s)

   rows和range后面的常用参数如下表2：

表2 rows和range后面的常用参数解析
UNBOUNDED PRECEDINGThe window starts at the first row of the partition.
UNBOUNDED FOLLOWINGThe window ends at the last row of the partition.
CURRENT ROWwindow begins at the current row or ends at the current row
n PRECEDING or n FOLLOWINGThe window starts or ends n rows before or after the current row.for example,ROWS BETWEEN Unbounded preceding AND 1 Preceding 1,means that the window goes from the first row of the partition to the row that stands (in the ordered set) immediatly before the current row…

OLAP开窗函数

Hive开窗函数也支持OLAP，根据不同维度上钻和下钻的指标统计，达到数据分析的效果，常用的OLAP函数如下；

  • GROUPING SETS GROUPING__ID
    返回值：在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL GROUPING__ID，表示结果属于哪一个分组集合；
    Hive Cli语句和效果：如下；
hive> set hive.cli.print.header=true;
hive> select month_key,date_key
    >       ,sum(production_amount) `生产总值`
    >       ,GROUPING__ID
    > from     dw.ods_sale_order_producttion_amount
    > group by  month_key,date_key
    > grouping sets(month_key,date_key)
    > order by GROUPING__ID
    > ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729154417_180a7883-ed66-49e3-a52e-bdebfbf61507
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0156, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0156/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0156
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 15:44:24,616 Stage-1 map = 0%,  reduce = 0%
2020-07-29 15:44:32,981 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.61 sec
2020-07-29 15:44:38,197 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 7.62 sec
MapReduce Total cumulative CPU time: 7 seconds 620 msec
Ended Job = job_1592876386879_0156
Launching Job 2 out of 2
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0157, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0157/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0157
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2020-07-29 15:44:49,818 Stage-2 map = 0%,  reduce = 0%
2020-07-29 15:44:56,078 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 2.34 sec
2020-07-29 15:45:02,288 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 4.51 sec
MapReduce Total cumulative CPU time: 4 seconds 510 msec
Ended Job = job_1592876386879_0157
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.62 sec   HDFS Read: 11977 HDFS Write: 796 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.51 sec   HDFS Read: 7133 HDFS Write: 877 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 130 msec
OK
month_key       date_key        生产总值        grouping__id
202005  NULL    1804382.00      1
202006  NULL    1772640.00      1
NULL    20200601        189000.00       2
NULL    20200610        135480.00       2
NULL    20200609        200100.00       2
NULL    20200608        158500.00       2
NULL    20200607        198420.00       2
NULL    20200606        158500.00       2
NULL    20200605        200140.00       2
NULL    20200604        158500.00       2
NULL    20200603        189000.00       2
NULL    20200602        185000.00       2
NULL    20200510        198540.00       2
NULL    20200509        200000.00       2
NULL    20200508        189462.00       2
NULL    20200507        159840.00       2
NULL    20200506        138500.00       2
NULL    20200505        196540.00       2
NULL    20200504        138500.00       2
NULL    20200503        199000.00       2
NULL    20200502        185000.00       2
NULL    20200501        199000.00       2
Time taken: 45.797 seconds, Fetched: 22 row(s)

结果分析：统计不同层次维度的度量值，第1列是按照month_key进行分组，第2列是按照date_key进行分组，统计各自的生产总值，注意GROUPING__ID表组别，是两个_隔开，根据grouping sets(month_key,date_key)来标识顺序，1是代表month_key，2是代表date_key。

  • CUBE
    返回值：根据GROUP BY的维度的所有组合进行聚合；
    Hive Cli语句和效果：如下；
hive> set hive.cli.print.header=true;
hive> select month_key,date_key
    >       ,sum(production_amount) `生产总值`
    >       ,GROUPING__ID
    > from     dw.ods_sale_order_producttion_amount
    > group by  month_key,date_key
    > WITH CUBE
    > ORDER BY GROUPING__ID;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729155154_4b3bf262-89b1-4834-a6b9-a8ed0a9eafda
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0159, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0159/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0159
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 15:52:14,628 Stage-1 map = 0%,  reduce = 0%
2020-07-29 15:52:20,895 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.17 sec
2020-07-29 15:52:27,147 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 7.0 sec
MapReduce Total cumulative CPU time: 7 seconds 0 msec
Ended Job = job_1592876386879_0159
Launching Job 2 out of 2
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0161, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0161/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0161
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2020-07-29 15:53:01,847 Stage-2 map = 0%,  reduce = 0%
2020-07-29 15:53:08,105 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 2.92 sec
2020-07-29 15:53:14,350 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 5.13 sec
MapReduce Total cumulative CPU time: 5 seconds 130 msec
Ended Job = job_1592876386879_0161
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.0 sec   HDFS Read: 12026 HDFS Write: 1599 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 5.13 sec   HDFS Read: 7936 HDFS Write: 1708 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 130 msec
OK
month_key       date_key        生产总值        grouping__id
202005  20200501        199000.00       0
202006  20200610        135480.00       0
202006  20200608        158500.00       0
202006  20200607        198420.00       0
202006  20200606        158500.00       0
202006  20200605        200140.00       0
202006  20200604        158500.00       0
202006  20200603        189000.00       0
202006  20200602        185000.00       0
202006  20200601        189000.00       0
202006  20200609        200100.00       0
202005  20200510        198540.00       0
202005  20200509        200000.00       0
202005  20200508        189462.00       0
202005  20200507        159840.00       0
202005  20200506        138500.00       0
202005  20200505        196540.00       0
202005  20200504        138500.00       0
202005  20200503        199000.00       0
202005  20200502        185000.00       0
202005  NULL    1804382.00      1
202006  NULL    1772640.00      1
NULL    20200610        135480.00       2
NULL    20200609        200100.00       2
NULL    20200608        158500.00       2
NULL    20200607        198420.00       2
NULL    20200606        158500.00       2
NULL    20200605        200140.00       2
NULL    20200604        158500.00       2
NULL    20200603        189000.00       2
NULL    20200602        185000.00       2
NULL    20200601        189000.00       2
NULL    20200510        198540.00       2
NULL    20200509        200000.00       2
NULL    20200508        189462.00       2
NULL    20200507        159840.00       2
NULL    20200506        138500.00       2
NULL    20200505        196540.00       2
NULL    20200504        138500.00       2
NULL    20200503        199000.00       2
NULL    20200502        185000.00       2
NULL    20200501        199000.00       2
NULL    NULL    3577022.00      3
Time taken: 80.686 seconds, Fetched: 43 row(s)

结果分析：根据GROUP BY的维度的所有组合进行聚合，统计不同维度的值；

  • ROLLUP
    返回值：是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合，即只会返回group by 后面第一个维度的所有聚合情况；
    Hive Cli语句和效果：如下，这里分别以month_key，date_key写在group by第一个，就一目了然了；
-- month_key在最左侧
hive> set hive.cli.print.header=true;
hive> select month_key,date_key
    >       ,sum(production_amount) `生产总值`
    >       ,GROUPING__ID
    > from     dw.ods_sale_order_producttion_amount
    > group by  month_key,date_key
    > WITH ROLLUP
    > ORDER BY GROUPING__ID;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729155836_b7c2669b-9763-469f-8337-03cc5b71f68e
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0163, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0163/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0163
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 15:58:56,098 Stage-1 map = 0%,  reduce = 0%
2020-07-29 15:59:03,410 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.12 sec
2020-07-29 15:59:08,635 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 7.25 sec
MapReduce Total cumulative CPU time: 7 seconds 250 msec
Ended Job = job_1592876386879_0163
Launching Job 2 out of 2
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0165, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0165/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0165
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2020-07-29 15:59:42,370 Stage-2 map = 0%,  reduce = 0%
2020-07-29 15:59:48,660 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 2.13 sec
2020-07-29 15:59:53,878 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 4.1 sec
MapReduce Total cumulative CPU time: 4 seconds 100 msec
Ended Job = job_1592876386879_0165
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.25 sec   HDFS Read: 12022 HDFS Write: 959 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.1 sec   HDFS Read: 7296 HDFS Write: 988 SUCCESS
Total MapReduce CPU Time Spent: 11 seconds 350 msec
OK
month_key       date_key        生产总值        grouping__id
202006  20200610        135480.00       0
202006  20200609        200100.00       0
202006  20200608        158500.00       0
202006  20200607        198420.00       0
202006  20200606        158500.00       0
202006  20200605        200140.00       0
202006  20200604        158500.00       0
202006  20200603        189000.00       0
202006  20200602        185000.00       0
202006  20200601        189000.00       0
202005  20200510        198540.00       0
202005  20200509        200000.00       0
202005  20200508        189462.00       0
202005  20200507        159840.00       0
202005  20200506        138500.00       0
202005  20200505        196540.00       0
202005  20200504        138500.00       0
202005  20200503        199000.00       0
202005  20200502        185000.00       0
202005  20200501        199000.00       0
202005  NULL    1804382.00      1
202006  NULL    1772640.00      1
NULL    NULL    3577022.00      3
Time taken: 78.751 seconds, Fetched: 23 row(s)

--date_key在最左侧

hive> set hive.cli.print.header=true;
hive> select month_key,date_key
    >       ,sum(production_amount) `生产总值`
    >       ,GROUPING__ID
    > from     dw.ods_sale_order_producttion_amount
    > group by  date_key,month_key
    > WITH ROLLUP
    > ORDER BY GROUPING__ID;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20200729160309_ad0a688d-edd1-4017-ab14-3407c90d2054
Total jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0166, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0166/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0166
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-07-29 16:03:19,204 Stage-1 map = 0%,  reduce = 0%
2020-07-29 16:03:25,534 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.87 sec
2020-07-29 16:03:30,752 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.77 sec
MapReduce Total cumulative CPU time: 6 seconds 770 msec
Ended Job = job_1592876386879_0166
Launching Job 2 out of 2
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1592876386879_0167, Tracking URL = http://dw-test-cluster-007:8088/proxy/application_1592876386879_0167/
Kill Command = /usr/local/tools/hadoop/current//bin/hadoop job  -kill job_1592876386879_0167
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2020-07-29 16:03:41,661 Stage-2 map = 0%,  reduce = 0%
2020-07-29 16:03:47,991 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 3.47 sec
2020-07-29 16:03:53,245 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 5.59 sec
MapReduce Total cumulative CPU time: 5 seconds 590 msec
Ended Job = job_1592876386879_0167
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 6.77 sec   HDFS Read: 11941 HDFS Write: 1539 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 5.59 sec   HDFS Read: 7876 HDFS Write: 1638 SUCCESS
Total MapReduce CPU Time Spent: 12 seconds 360 msec
OK
month_key       date_key        生产总值        grouping__id
202006  20200610        135480.00       0
202006  20200609        200100.00       0
202006  20200608        158500.00       0
202006  20200607        198420.00       0
202006  20200606        158500.00       0
202006  20200605        200140.00       0
202006  20200604        158500.00       0
202006  20200603        189000.00       0
202006  20200602        185000.00       0
202006  20200601        189000.00       0
202005  20200510        198540.00       0
202005  20200509        200000.00       0
202005  20200508        189462.00       0
202005  20200507        159840.00       0
202005  20200506        138500.00       0
202005  20200505        196540.00       0
202005  20200504        138500.00       0
202005  20200503        199000.00       0
202005  20200502        185000.00       0
202005  20200501        199000.00       0
NULL    20200505        196540.00       1
NULL    20200510        198540.00       1
NULL    20200610        135480.00       1
NULL    20200502        185000.00       1
NULL    20200609        200100.00       1
NULL    20200509        200000.00       1
NULL    20200608        158500.00       1
NULL    20200504        138500.00       1
NULL    20200607        198420.00       1
NULL    20200508        189462.00       1
NULL    20200606        158500.00       1
NULL    20200605        200140.00       1
NULL    20200507        159840.00       1
NULL    20200604        158500.00       1
NULL    20200503        199000.00       1
NULL    20200603        189000.00       1
NULL    20200506        138500.00       1
NULL    20200602        185000.00       1
NULL    20200501        199000.00       1
NULL    20200601        189000.00       1
NULL    NULL    3577022.00      3
Time taken: 44.948 seconds, Fetched: 41 row(s)

结果分析：以最左侧的维度为主，从该维度进行层级聚合，即只会返回group by 后面第一个维度的所有聚合情况；

开窗高级集合函数

  窗口函数还支持复杂的数学分析函数，如相关和线性回归函数，标准差和方差函数等，由于不太常用，这里就不一一列举了，给一个传送门，用到的时候可以去参考函数形式，改写成HiveQL就行了,传送门：复杂窗口函数；

╭⌒若隐_RowYet——大数据
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
hive窗口函数
01-09
hive窗口系列函数
Hive SQL 分析函数
程序员小陶的大数据分享
01-31 673
本文首发于：大数据每日哔哔-Hive 分析函数 Hive 分析函数 应用场景 （1）用于分区排序<br />（2）Top N<br />（3）层次查询 <a name="kFMpa"></a> 常用分析函数 分析函数 描述 RANK 返回数据项在分区中的排名。排名值序列可能会有间隔 DENSE_RANK 返回数据项在...
第六章 扩展：开窗函数
抓住人生的每一分每一秒，胜过虚度中的每一年每一月！！！
05-22 3789
概述 https://www.cnblogs.com/qiuting/p/7880500.html ●介绍 开窗函数的引入是为了既显示聚集前的数据，又显示聚集后的数据。即在每一行的最后一列添加聚合函数的结果。 开窗用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用 GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 ●聚合函数和开窗函数 聚合函数是将多行变成一行，count,avg.... 开窗函数是将一行变成多行..
揭开sql server分区的神秘面纱
LXWalaz1s1s的博客
05-27 233
首先普及下sql server数据库的三种常见文件类型：   .mdf 全称（primary data file）   主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。    .ndf全称（Secondary data files）   次要数据文件是可选...
Hive从入门到放弃——Hive表DML的艺术性（八）
LXWalaz1s1s的博客
06-21 399
背 景   Hive的DML其实和MySQL的差别不多，当然局限于select层面， 基本的select操作    jion操作    insert操作    load操作   
HIVE从入门到精通.pdf
01-17
### HIVE从入门到精通知识点概述 #### 一、Hive简介 - **背景与需求**：随着商业智能领域数据量的急剧增加，传统的数据仓库解决方案成本高昂，难以满足需求。Hadoop作为一种流行且开源的MapReduce实现，在Yahoo、...
hive开窗函数知识点总结.txt
05-24
工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每...
hive+python数据分析入门
02-26
举个例子,当年没有数据库的时候,人们编程来操作文件系统,这相当于我们编写mapreduce来分析数据后来有了数据库,再没人操作文件系统了(除非有其它需求),而是直接使用sql和一些语言(php,java,python)来操作数据....
基于 Hive的数据分析案例 -MM聊天软件数据分析
06-16
基于 Hive 的数据分析案例 -MM 聊天软件数据分析 本资源摘要信息主要介绍了基于 Hive 的数据分析案例，通过对 MM 聊天软件的数据进行统计分析，了解用户行为，实现精准的用户画像，并为公司的发展决策提供精确的...
大数据之路之Linux篇
LXWalaz1s1s的博客
03-27 5657
为什么要学习Linux 1. 工作需要，从事IT工作或多或少都要设计Linux； 2. 迟早老子会有钱，要买一台苹果Mac坐在星巴克追剧，那你会发现，Mac的命令行模式竟然和Linux惊人的相识，我每次用到Mac命令行操作都是直接网上直接copy的，不知道啥意思，这是我一个做设计的朋友跟我吐槽的，嘿嘿，就怕哪天你copy了个`rm -rf *` 3.每次看美国大片，发现那些电脑高手都在一个黑框框里啪啦啪啦的敲键盘，他们在敲啥呢？想不想成为他们一样的高手？
【吴恩达机器学习-笔记整理】OCR，滑动窗口，数据扩增，上限分析，计算机视觉
怪&的个人博客
08-24 1303
一、什么是OCR 步骤 二、滑动窗口 以较简单的人行检测为例 切割出人区域 文字检测： 字符分割 三、获取大量数据和人工数据 人工数据合成 ==机器学习问题必做：== 四、天花板分析：下一步工作的pipeline
JavaSE基础——异常机制
LXWalaz1s1s的博客
04-16 602
曾有人说过：“一个程序项目内30%的代码就能实现功能，剩下70%的代码都在检查异常、增加约束……”本文就总结下Java的异常机制。
brew结合iTerm2花式玩转MacOS软件管理的终端指令
LXWalaz1s1s的博客
03-29 2621
为什么你的Mac终端能语法高亮、高端智能提示、风骚的指定风格、有趣的fuck插件(shell指令报错怎么办？fuck一下！)，还有张陌上人如玉，公子世无双的嵩嵩（老婆最爱）的背景板等等，因为我利用国内镜像安装brew，结合iTerm2花式玩转MacOS的软件管理呀，想学呀？我教你呀！
ElasticSearch7.9.2安装指南（二）
LXWalaz1s1s的博客
10-07 1576
安装版本配置 操作系统：CentOS7 ES版本：7.9.2 ES集群节点数：3个[node1,node2,node3] ES集群Master节点集群：[node1,node2] JDK版本：本人使用的是JDK 8；ES建议使用JDK 11,且如果不是JDK 11的话，启动的时候会有警告，但是不会报错，只是简单的警告而已，问题不大，可以忽略。 官网下载 官网下载：传送门地址   具体如图1，这里我们选择Linux的tar包模式下载，这个看个人习惯，ElasticSearch 也是支持rpm和docke
Hive调优实践
_朝和
08-25 6192
Hive调优实践
大数据与数据仓库入门到精通
09-20
本课程以CDH作为大数据平台，详细介绍CDH平台各个组件在生产环境的应用及开发，并结合实际的业务场景，离线数仓，实时数仓，构建企业核心的数据架构。在实际的工作当中，大数据架构，运维或者开发人员会与多个公司团队合作，ETL团队，爬虫团队，算法团队，运营团队等等，指导大家如何与个个团队打交道，提升工作效率。减少团队之间不愉快的沟通。希望学习者最好从事过数据库相关工作，有一些 JAVA开发基础，或者有其他工作经验，想学习大数据及数据仓库的同学，对于没有工作经验，或者对开发，数据完全小白的同学，建议先了解相关知识再学习。本课程的宗旨只有一条，任何学习完本课程的同学，都能熟悉企业主流的数据架构，都有能力维护一个中等HADOOP集群，也就是1P左右的数据的集群或者多个集群。
Hive SQL主要开窗函数用法介绍
weixin_43025027的博客
04-24 1588
在开窗函数出现之前，存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。 下面通过一些简单的需求示例介绍主要的开窗函数。 本例的数据源： select * from wx_tmp1; 需求1：要在源表中，增加两列，全国总的gmv和各城市的gmv占比。 select *,sum(gmv) over() as all_gmv, gmv/sum(gmv) ove
hive开窗函数大全
ffddnp的博客
05-12 1739
-- 窗口函数sum,max,min,avg SELECT cookieid, createtime, pv, SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED...
【Hive】用sort_array和collet_list对一组数字进行排序，取最大值或最小值
一往无前
06-22 6537
"value" :" {-15, -56, -75, -32, -61}" 取最大值或最小值 select sort_array(collect_list(cast value as double)) lateral view explode
Hive数据仓库详解：从入门到精通
“大数据技术之Hive - 副本.doc”文档...Hive是大数据分析领域的一个重要工具，尤其适合对实时性要求不高的批量数据处理场景。尽管存在一定的局限性，但其易用性和与SQL的兼容性使其在数据仓库和分析领域有广泛的应用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

╭⌒若隐_RowYet——大数据

谢谢小哥哥，小姐姐的巨款

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值