最码农 最码农
  • 首页
  • 动态广场
  • 精选栏目
  • 闲言碎语
  • 左邻右里
  • 笔记屋
  • 注册
  • 登录
首页 › 大数据 › Hive 窗口函数(开窗函数)

Hive 窗口函数(开窗函数)

Cosy
2月前大数据阅读 95

目录

Hive

开窗函数

1)相关函数说明

OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的改变而变化。

  • CURRENT ROW:当前行
  • n PRECEDING:往前n行数据
  • n FOLLOWING:往后n行数据
  • UNBOUNDED:起点,
    • UNBOUNDED PRECEDING 表示从前面的起点,
    • UNBOUNDED FOLLOWING表示到后面的终点
  • LAG(col,n,default_val):往前第n行数据
  • LEAD(col,n, default_val):往后第n行数据
  • NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。

2)数据准备:name,orderdate,cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

3)需求

  • 需求1:
    • 查询在2017年4月份购买过的顾客及总人数
  • 需求2:
    • 查询顾客的购买明细及所有顾客的月购买总额
    • 查询顾客的购买明细及每个顾客的月购买总额
  • 需求3:
    • 将每个顾客的cost按照日期进行累加
    • 将所有顾客的cost按照日期进行累加
  • 需求4:
    • 求所有顾客的购买明细及按照日期进行排序后
    • 求所有顾客的cost 第一行 到 当前行 累加和
    • 求所有顾客的cost 上一行 到 当前行 的累加和
    • 求所有顾客的cost 上一行 到 下一行 的累加和
    • 求所有顾客的cost 当前行 到 下一行 的累加和
    • 求所有顾客的cost 当前行 到 最后一行 的累加和
  • 需求5:
    • 查询每个顾客上次的购买时间 及 下一次的购买时间
  • 需求6:
    • 查询前20%时间的订单信息

4)创建hive表并导入数据


create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/home/hadoop/data/business.txt" into table business;

5)按需求查询数据

  • 需求1:
    • 查询在2017年4月份购买过的顾客及总人数

select 
   t2.name, count(t2.name) over()
from 
(select
  distinct(t1.name)
from 
  (select
  name, orderdate, cost 
from 
  business 
where substring(orderdate,0,7) = '2017-04')t1) t2;
Hive 窗口函数(开窗函数)-最码农
  • 需求2:
    • 查询顾客的购买明细及所有顾客的月购买总额

select
  name, 
  orderdate, 
  cost , 
  sum(cost) over(partition by substring(orderdate,0,7))  month_cost
from
  business;
Hive 窗口函数(开窗函数)-最码农
  • 需求2:
    • 查询顾客的购买明细及每个顾客的月购买总额

select
  name, 
  orderdate,
  cost, 
  sum(cost) over(partition by name, substring(orderdate,0,7)) name_month_cost
from 
  business;
Hive 窗口函数(开窗函数)-最码农

需求3:

  • 将每个顾客的cost按照日期进行累加

select 
  name, 
  orderdate,
  cost,
  sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) lj
from 
  business; 
Hive 窗口函数(开窗函数)-最码农
  • 需求3:
    • 将所有顾客的cost按照日期进行累加

select 
  name, 
  orderdate,
  cost,
  sum(cost) over(order by orderdate ) lj
from 
  business;
Hive 窗口函数(开窗函数)-最码农
  • 需求4:
    • 求所有顾客的购买明细及按照日期进行排序后
    • 求所有顾客的cost 第一行 到 当前行 累加和
    • 求所有顾客的cost 上一行 到 当前行 的累加和
    • 求所有顾客的cost 上一行 到 下一行 的累加和
    • 求所有顾客的cost 当前行 到 下一行 的累加和
    • 求所有顾客的cost 当前行 到 最后一行 的累加和

select
  name,
  orderdate,
  cost,
  sum(cost) over(order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) f_c,
  sum(cost) over(order by orderdate rows between 1 PRECEDING and CURRENT ROW ) p_c,
  sum(cost) over(order by orderdate rows between 1 PRECEDING and 1 FOLLOWING ) p_n,
  sum(cost) over(order by orderdate rows between CURRENT ROW and 1 FOLLOWING ) c_n,
  sum(cost) over(order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING ) c_l
from
  business;
Hive 窗口函数(开窗函数)-最码农
  • 需求5:
    • 查询每个顾客上次的购买时间 及 下一次的购买时间

select
   name,
   cost, 
   orderdate c_orderdate,
   lag(orderdate ,1 ,'1970-01-01') over(partition by name  order by orderdate) p_orderdate,
   lead(orderdate ,1 ,'9999-01-01') over(partition by name  order by orderdate) p_orderdate
from 
  business;
Hive 窗口函数(开窗函数)-最码农
  • 需求6:
    • 查询前20%时间的订单信息

select 
  t1.name, 
  t1.orderdate,
  t1.cost ,
  t1.gid
from 
(select
  name, 
  orderdate,
  cost, 
  ntile(5) over(order by orderdate ) gid
from 
  business) t1
where t1.gid = 1;
Hive 窗口函数(开窗函数)-最码农

5)总结

  • over():会为每条数据都开启一个窗口。默认的窗口大小就是当前数据集的大小。
  • over(partition by ….):会按照指定的字段进行分区, 将分区字段的值相同的数据划分到相同的区。每个区中的每条数据都会开启一个窗口。每条数据的窗口大小默认为当前分区数据集的大小。
  • over(order by ….):会在窗口中按照指定的字段对数据进行排序,会为每条数据都开启一个窗口,默认的窗口大小为从数据集开始到当前行。
  • over(partition by …. order by ….):会按照指定的字段进行分区,将分区字段的值相同的数据划分到相同的区,在每个区中会按照指定的字段进行排序,会为每条数据都开启一个窗口,默认的窗口大小为当前分区中从数据集开始到当前行。
  • over(partition by … order by … rows between … and ….):指定每条数据的窗口大小。

6)关键字整理

  • order by:全局排序 或者 窗口函数中排序.
  • distribute by:分区
  • sort by:区内排序
  • cluster by:分区排序
  • partition by:窗口函数中分区
  • partitioned by:建表指定分区字段
  • clustered by:建表指定分桶字段

Hive 教程
赞(0) 收藏(0)
Hive 行转列与列转行
上一篇
Hive Rank排名函数
下一篇
再想想
暂无评论
随 机 推 荐
Hive 行转列与列转行
Hive 常用函数整理
Flume 自定义Sink
Flume 入门案例 – 实时监控单个追加文件
从Hadoop框架讨论大数据生态
HDFS 概述
MapReduce框架原理-InputFormat数据输入
Hadoop 序列化
  • 0
  • 0
介绍

我们致力于打造一个原创的计算机相关技术的博客网站,旨在为访客提供一个优质的计算机技术教程交流平台。网站开辟了很多于计算机相关的栏目,并且收集了不少实用资源,同时也鼓励欢迎访客一起分享、交流、学习。

灵魂推荐
Veer图库 数码荔枝
栏目标题
首页 动态广场 精选栏目 闲言碎语 左邻右里 笔记屋
Copyright © 2021-2023 最码农. 苏ICP备20033168号
  • 首页
  • 动态广场
  • 精选栏目
  • 闲言碎语
  • 左邻右里
  • 笔记屋
# 教程 # # Hadoop # # Hive # # Flume # # 人工智能 #
Cosy
即使世界毁灭,也总有回光返照的那一刻
117
文章
3
评论
432
喜欢