精华内容
下载资源
问答
  • Hive SQL 窗口函数

    2020-01-31 16:46:22
    本文首发:大数据每日哔哔-Hive SQL 窗口函数 Hive窗口函数SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于...
        

    本文首发:大数据每日哔哔-Hive SQL 窗口函数

    Hive 的窗口函数

    在 SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是,有时候我们既要显示聚集前的数据,又要显示聚集后的数据,此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by子句之前。

    窗口函数 描述
    LAG() LAG()窗口函数返回分区中当前行之前行(可以指定第几行)的值。 如果没有行,则返回null。
    LEAD() LEAD()窗口函数返回分区中当前行后面行(可以指定第几行)的值。 如果没有行,则返回null。
    FIRST_VALUE FIRST_VALUE窗口函数返回相对于窗口中第一行的指定列的值。
    LAST_VALUE LAST_VALUE窗口函数返回相对于窗口中最后一行的指定列的值。

    LAG 和 LEAD 的用法:

    LAG | LEAD
    ( <col>, <line_num>, <DEFAULT> )
    OVER ( [ PARTITION BY ] [ ORDER BY ] )
    

    FIRST_VALUE 和 LAST_VALUE 的用法:

    FIRST_VALUE | LAST_VALUE
    ( <col>,<ignore nulls as boolean> ) OVER
    ( [ PARTITION BY ] [ ORDER BY ][ window_clause ] )
    

    下面举个例子,数据集如下:

    hive> select * from tmp_pv;
    OK
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-10  1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-11  5
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-12  7
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-13  3
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-14  2
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-15  4
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2019-02-16  4
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-10  2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-11  9
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-12  3
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-13  10
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-14  1
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-15  8
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2019-02-16  2
    Time taken: 0.102 seconds, Fetched: 14 row(s)
    

    <a name="Y1tyI"></a>

    LAG(col,n,default)

    与 partitioned by 结合使用,返回当前分区中,当前行之前的第 n 行对应的值。如果没有则默认换回 null。第一个参数为列名,第二个参数为当前行之前第n行(可选,默认为1),第三个参数为缺失时默认值(当前行之前第n行为NULL没有时,返回该默认值,如不指定,则为NULL)。

    为了比较每个用户浏览次数与前一天的浏览次数进行比较,查询返回当前浏览次数以及前一天的浏览数量。由于在2019-02-10之前没有浏览行为,前一天的浏览次数设置为0(不设置默认为NULL)。

    hive> select gid, dt, pv, lag(pv, 1, 0) over (partition by gid order by dt) as pre_pv from tmp_pv;
     
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10  1   0
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11  5   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12  7   5
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13  3   7
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14  2   3
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15  4   2
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16  4   4
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10  2   0
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11  9   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12  3   9
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13  10  3
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14  1   10
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15  8   1
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16  2   8
    Time taken: 11.783 seconds, Fetched: 14 row(s)
    

    <a name="V4ogf"></a>

    LEAD(col,n,default)

    与 LAG 函数相反。

    <a name="CA4fi"></a>

    FIRST_VALUE(col,布尔值)

    第一个参数是需要第一个值的列,第二个(可选)参数必须是默认为false的布尔值。如果设置为true,则跳过空值。

    hive> select gid,dt,pv,first_value(pv,true) over(partition by gid order by dt) as first_value from temp_pv; 
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-10  1   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-11  5   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-12  7   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-13  3   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-14  2   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-15  4   1
    0006D2BC-4DF9-4C0B-83AD-0183789E78D4    2017-02-16  4   1
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-10  2   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-11  9   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-12  3   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-13  10  2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-14  1   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-15  8   2
    993BD7AD-3B62-BA0C-15AE-A14B85921889    2017-02-16  2   2
    Time taken: 9.862 seconds, Fetched: 14 row(s)
    

    <a name="2f562"></a>

    LAST_VALUE(col,布尔值)

    与 FIRST_VALUE() 函数相反,这里就不进行演示了。

    <a name="cDqav"></a>

    over子句

    官方 OVER子句 包括几个部分:

    • 聚合函数(count, sum, min, max, avg)
    • OVER 子句
    • PARTITION BY 子句
    • ORDER BY 子句
    • WINDOW 子句

    结合具体的业务场景,SQL 语句如下:<br />

    ---1)201504月份的销售额
    select sum(amount) as total_amt
    from order_window 
    where substr(order_date,1,7)='2015-04'
    ;
    ---2)201504月份的订单明细与销售额
    select user_name, order_date, amount
          ,sum(amount) over() as total_amt
    from order_window
    where substr(order_date,1,7)='2015-04'
    ;
    ---3)客户的订单明细与月购买金额
    select user_name, order_date, amount
          ,sum(amount) over (partition by month(order_date)) month_amt
    from order_window
    ;
    ---4)客户的订单明细与累计购买金额
    select user_name, order_date, amount
          ,sum(amount) over (partition by month(order_date) order by order_date) month_add_amt
    from order_window
    ;
    ---5)不同窗口的销售额
    select 
         user_name
        ,order_date
        ,amount
        ,sum(amount) over() as sample1 --所有行相加
        ,sum(amount) over(partition by user_name) as sample2 --按name分组,组内数据相加
        ,sum(amount) over(partition by user_name order by order_date) as sample3 --按name分组,组内数据累加
        ,sum(amount) over(partition by user_name order by order_date rows between UNBOUNDED PRECEDING and current row) as sample4 --和sample3一样,由起点到当前行的聚合
        ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and current row) as sample5 --当前行和前面一行做聚合
        ,sum(amount) over(partition by user_name order by order_date rows between 1 PRECEDING and 1 FOLLOWING) as sample6 --当前行和前边一行及后面一行
        ,sum(amount) over(partition by user_name order by order_date rows between current row and UNBOUNDED FOLLOWING) as sample7 --当前行及后面所有行
    from order_window
    ;
    

    <a name="sWJSK"></a>

    windows子句

    带有窗口规范的OVER子句。窗口可以在WINDOW子句中单独定义。窗口规范支持如下格式:<br />

    关键字 说明
    PRECEDING 表示当前行之前的行
    UNBOUNDED PRECEDING 表示当前行之前无边界行,即第一行
    num PRECEDING 表示当前行之前第num行
    CURRENT ROW 表示当前行
    FOLLOWING 表示当前行后面的行
    UNBOUNDED FOLLOWING 表示当前行后面无边界行,即最后一行
    num FOLLOWING 表示当前行后面第num行

    <br />当缺少WINDOW子句并指定使用ORDER BY时,窗口规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,即从第一行到当前行。<br />当缺少ORDER BY和WINDOW子句时,窗口规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,即第一行到最后一行。<br />

    <a name="fn1nr"></a>

    参考

    展开全文
  • HiveSQL窗口函数

    2020-11-03 22:08:30
    HiveSQL窗口函数 1、掌握sum(),avg()用于累计计算的窗口函数; 2、掌握row_number(),rank()用于排序的窗口函数; 3、掌握ntile()用于分组查询的窗口函数; 4、掌握lag(),lead()偏移分析窗口函数 -- 2018年每月的...


    1、掌握sum(),avg()用于累计计算的窗口函数;
    2、掌握row_number(),rank()用于排序的窗口函数;
    3、掌握ntile()用于分组查询的窗口函数;
    4、掌握lag(),lead()偏移分析窗口函数

    1.累计计算窗口函数

    1).sum(…)over(…)

    截止某月的数值
    在这里插入图片描述

    user_trade列名 举例
    user_name Amy,Dennis
    piece 购买数量
    pay_amount 支付金额
    goods_category food,clothes,book,computer,electronics,shoes
    pay_time 132265412,时间戳
    dt partition,‘yyyy-mm-dd’

    求2018年每月的支付总额和当年累计支付总额

    select a.month,
    		a.pay_amount,
    		sum(a.pay_amount) over(order by a.month)
    from
    	(select month(dt) month,
    		sum(pay_amount) pay_amount
    	from user_trade
    	where year(dt)=2018
    	group by month(dt))a;
    

    在这里插入图片描述
    2017-2018年每月的支付总额和当年累积支付总额(如果用上面的方式会把之前月份的都汇总,无法实现)

    select a.year,
    		a.month,
    		a.pay_amount,
    		sum(a.pay_amount) over(partition by a.year order by a.month) -- over:指定累计计算的条件,这里须正确分组
    from 
    	(select year(dt) year,
    		month(dt) month,
    		sum(pay_amount) pay_amount
    		from user_trade
    		where year(dt) in (2017,2018)
    		group by year(dt),
    				month(dt))a;
    

    注:
    1.partition by 起到分组的作用;
    2.order by按照什么顺序进行累加,升序ASC,降序DESC,默认升序
    3.常见错误:分组没有限制正确(partition by 后多加了a.month)

    2).avg(…) over(…)

    2018年每个月的近三个月移动平均支付金额
    在这里插入图片描述

    select a.month,
    		a.pay_amount,
    		avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row)
    from 
    	(select month(dt)month,
    	sum(pay_amount)pay_amount
    	from user_trade
    	where year(dt)=2018
    	group by month(dt))a;
    

    说明:我们用rows between 2 preceding and current row来限制计算移动平均的平均范围,本语句含义是包含本行及前两行,这个就是我们题目中要求的近三月的写法

    3.语法总结:

    sum(…A…) over(partition by …B… order by…C…rows between…D1…and…D2…)
    avg(…A…) over(partition by …B… order by…C…rows between…D1…and…D2…)
    A:需要被加工的字段名称
    B:分组的字段名称
    C:排序的字段名称
    D:计算的行数范围
    rows between unbounded preceding and current row–包括本行和之前所有的行
    rows between current row and unbounded following–包括本行和之后所有的行
    rows between 3 preceding and current row --包括本行以内和前三行
    rows between 3 preceding and 1 following–从前三行到下一行(5行)
    拓展:
    max(…) over(partition by …order by … rows between… and…)
    min(…) over(partition by …order by … rows between… and…)

    2.分区排序窗口函数:

    这三个函数的作用都是返回相应规则的排序序号

    row_number() over(partition by …A…order by…B…) 1,2,3,4
    rank() over(partition by …A…order by…B…) 1,2,2,4
    dense_rank() over(partition by …A…order by…B…) 1,2,2,3

    2019年1月,用户购买商品品类数量的排名

    select user_name,
    	count(distinct goods_category),
    	row_number() over(order by count(distinct goods_category)),
    	rank() over(order by count(distinct goods_category)),
    	dense_rank() over(order by count(distinct goods_category))
    from user_trade
    where substr(dt,1,7)='2019-01'
    group by user_name;
    

    选择2019年支付金额排名在第10,20,30名的用户:

    select a.user_name,
    	a.pay_amount,
    	a.rank
    from
    	(select user_name,
    		sum(pay_amount)pay_amount,
    		rank() over(order by sum(pay_amount)desc)rank
    	from user_trade
    	where year(dt)=2019
    	group by user_name)a
    	where a.rank in (10,20,30);
    

    3.分组排序窗口函数

    ntile(n) over(…)

    ntile(n) over(partition by…A…order by…B…)
    n:切分的片数
    A:分组的字段名称
    B:排序的字段名称
    ntile(n),用于将分组数据按照顺序切分成n片,返回当前切片值
    ntile不支持row between,比如ntile(2) over(partition by …order by…rows between 3 preceding and current row)
    如果切片不均匀,默认增加第一个切片的分布

    将2019年1月份支付用户,按照支付金额分成5组:

    select user_name,
    	sum(pay_amount)pay_amount,
    	ntile(5) over(order by sum(pay_amount) desc)level
    from user_trade
    where substr(dt,1,7)='2019-01'
    group by user_name;
    

    选出2019年退款金额排名前10%的用户:

    user_refund列名 举例
    user_name Amy,Dennis
    refund_piece 退款件数
    refund_amount 退款金额
    refund_time 134854654,时间戳
    dt partition,‘yyyy-mm-dd’
    select a.user_name,
    		a.refund_amount,
    		a.level
    from
    	(select user_name,
    		sum(refund_amount)refund_amount,
    		ntile(10) over(order by sum(refund_amount)desc)level
    	from user_refund
    	where year(dt)=2019
    	group by user_name)a
    where a.level=1;
    

    4.偏移分析窗口函数

    lag(…)over(…),lead(…)over(…)

    lag和lead分析函数可以在通一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列。

    在实际应用中,若要用到取今天和昨天的某字段差值时,lag和lead函数的应用就显得尤为重要。当然,
    这种操作可以用表的自连接实现,但是lag和lead与left join、right join等自连接相比,效率更高,SQL更简洁。

    lag(exp_str,offset,defval) over(partition by …order by…)
    lead(exp_str,offset,defval) over(partition by …order by…)
    – exp_str是字段名称。
    – offset是偏移量,即是上1个或上N个的值,假设当前行在表中排在第5行,offset为3,则表示我们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。
    – defval默认值,当两个函数取上N/下N个值,当在表中从当前行位置向前数N行已经超出了表的范围时,lag()函数
    – 将defval这个参数值作为函数的返回值,若没有指定默认值,则返回null,那么在数学运算中,总要给一个默认值才不会出错。

    Alice和Alexander的各种时间偏移
    lag()实例:

    select user_name,
    		dt,
    		lag(dt,1,dt) over(partition by user_name order by dt),
    		lag(dt) over(partition by user_name order by dt),
    		lag(dt,2,dt) over(partition by user_name order by dt),
    		lag(dt,2) over(partition by user_name order by dt)
    from user_trade
    where dt>'0'
    		and user_name in ('Alice','Alexander');
    

    在这里插入图片描述
    lead()实例:

    select user_name,
    		dt,
    		lead(dt,1,dt) over(partition by user_name order by dt),
    		lead(dt) over(partition by user_name order by dt),
    		lead(dt,2,dt) over(partition by user_name order by dt),
    		lead(dt,2) over(partition by user_name order by dt)
    from user_trade
    where dt>'0'
    		and user_name in ('Alice','Alexander');
    

    在这里插入图片描述
    实例:支付时间间隔超过100天的用户数

    select count(distinct user_name)
    from
    	(select user_name,
    		dt, --选出每个人的支付日期
    		lead(dt) over(partition by user_name order by dt) lead_dt
    	from user_trade
    where dt>'0')a  --用的是全量,所以用>0
    	where a.lead_dt is not null and datediff(a.lead_dt,a.dt)>100; -- 大的写左边,小的写右边
    

    重点练习
    1.每个城市,不同性别,2018年支付金额最高的top3用户(使用user_trade和user_info两个表)

    user_info列名 举例
    user_id 10001,10002
    user_name Amy,Dennis
    sex [male,female]
    age [13,70]
    city beijing,shanghai
    fistactivetime 2019-04-19 15:40:00
    level [1,10]
    extra1 string类型:{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}
    extra2 map<string.string>类型: {“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}
    user_trade列名 举例
    user_name Amy,Dennis
    piece 购买数量
    price 价格
    pay_amount 支付金额
    goods_category food,clothes,book,computer,electronics,shoes
    pay_time 2412521561,时间戳
    dt partition,‘yyyy-mm-dd’
    select c.user_name,
    		c.city,
    		c.sex,
    		c.pay_amount,
    		c.rank
    from
    	(select a.user_name,
    		b.city,
    		b.sex,
    		a.pay_amount,
    		row_number() over(partition by b.city,b.sex order by a.pay_amount desc) rank 
    	from
    		(select user_name,
    			sum(pay_amount) pay_amount
    		from user_trade
    		where year(dt)=2018
    		group by user_name)a
    		left join user_info b on a.user_name=b.user_name)c 
    where c.rank<=3;
    -- 使用row_number是因为它可以精确限制可以具体出来多少行,使用dense_rank和rank都不行
    

    每个手机品牌退款金额前25%的用户(使用user_refund和user_info两个表)

    user_refund列名 举例
    user_name Amy,Dennis
    refund_piece 退款件数
    refund_amount 退款金额
    refund_time 123412521,时间戳
    dt partition,‘yyyy-mm-dd’
    select * 
    from 
    	(select a.user_name,
    			extra2['b.phonebrand'] as phonebrand,
    			a.refund_amount,
    			ntile(4) over(partition by extra2['b.phonebrand'] order by a.refund_amount desc)level
    			from
    				(select user_name,
    							sum(refund_amount) refund_amount
    				from user_refund
    				where dt>'0'
    				group by user_name) a
    				left join user_info b on a.user_name=b.user_name)c
    where c.level=1;
    
    展开全文
  • HiveSQL窗口函数.pdf

    2020-12-18 10:17:06
    HiveSQL窗口函数.pdf
  • hivesql窗口函数

    2021-06-27 17:24:43
    sum/avg/max/min+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合 –sum/avg/max/min(…) over( ) -------对表所有行求和 –sum/avg/max/min(…) over( order by … ) ---- 连续累积求和 –sum/avg/max/min(…...

    sum/avg/max/min/row_number/first_value/last_value+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
    –sum/avg/max/min/row_number/first_value/last_value(…) over( ) -------对表所有行operator
    –sum/avg/max/min/row_number/first_value/last_value(…) over( order by … ) ---- 连续累积operator
    –sum/avg/max/min/row_number/first_value/last_value(…) over( partition by… ) ---- 同组内所行operator
    –sum/avg/max/min/row_number/first_value/last_value(…) over( partition by… order by … ) ----在每个分组内,连续累积operator

    Window expression窗口表达式给我们提供了一种控制行范围的能力,比如向前2行,向后3行。
    语法如下:
    关键字是rows between,包括下面这几个选项

    • preceding:往前
    • following:往后
    • current row:当前行
    • unbounded:边界
    • unbounded preceding 表示从前面的起点
    • unbounded following:表示到后面的终点

    The OVER clause supports the following functions, but it does not support a window with them
    Rank, NTile, DenseRank, CumeDist, PercentRank.Lead and Lag functions.
    这些不支持rows between子句。

    —窗口表达式
    –第一行到当前行

    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime) as pv2
    from website_pv_info;

    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
    from website_pv_info;

    –向前3行至当前行
    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
    from website_pv_info;

    –向前3行 向后1行
    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
    from website_pv_info;

    –当前行至最后一行
    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
    from website_pv_info;

    –第一行到最后一行 也就是分组内的所有行
    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and unbounded following) as pv6
    from website_pv_info;

    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid ) as pv6
    from website_pv_info;
    ;

    展开全文
  • HIVE(3)-HIVESQL窗口函数

    2020-05-21 19:37:17
    HIVESQL窗口函数还是很好用的。MARK一下基本概念和语法。 1.累计计算:sum()、avg() 2.排序:row_number(),rank(),dense_rank()3.前百分之多少:ntile() 4.偏移分析:lag() ,lead() 1.累计计算:sum()、avg() ...

    HIVESQL窗口函数还是很好用的。MARK一下基本概念和语法。

    1.累计计算:sum()、avg()
    2.排序:row_number(),rank(),
    dense_rank() 
    3.前百分之多少:ntile()
    4.偏移分析:lag() ,lead()

     



    1.累计计算:sum()、avg()
      

    1.1sum()累计
    a)开始时间为0 第一条数据开始

     

    select a.month,
           a.pay_amount_1,                    --结果,非原始字段
           sum(a.pay_amount_1) over(order by a.month)--排序
    from (select month(dt) month,             --分类,按月提取值
                 sum(pay_amount) pay_amount_1 --汇总
         from user_trade
         where year(dt)=2018
         group by month(dt)) a;


    b)开始时间为当年(partition by 分片)
     

    select a.year,
           a.month,
           a.pay_amount_1,                    
           sum(a.pay_amount_1) over(partition by a.year order by a.month ASC)--partition by
    from (select year(dt) year,
                 month(dt) month,             
                 sum(pay_amount) pay_amount_1 
         from user_trade
         where year(dt) in(2017,2018)        --两个年份
         group by  year(dt),
                   month(dt)) a;

    c)容易出现的错误
     

    select a.year,
           a.month,
           a.pay_amount_1,                  
           sum(a.pay_amount_1) over(partition by a.year ,a.month order by a.month ASC)
    from (select year(dt) year,   -- a.month没有意义
                 month(dt) month,             
                 sum(pay_amount) pay_amount_1 
         from user_trade
         where year(dt) in(2017,2018)        --两个年份
         group by  year(dt),
                   month(dt)) a;

    1.2avg()平均
        3日平均,或者3月平均,一般就有移动平均值(每三日,每三月的平均)





     

    -- 求每个月的近三月移动平均支出金额--
    --1:month1
    --2:(month1+month2)/2
    --3.(month1+month2+month3)/3
    --4.(month2+month3+month4)/3
    select a.month,
           a.pay_amount_1,                    --结果,非原始字段
           avg(a.pay_amount_1) over(order by a.month rows between 2 preceding and current row)--2行之前 和当前行
    from (select  month(dt) month,             --分类,按月提取值
                  sum(pay_amount) pay_amount_1 
         from user_trade
         where year(dt)=2018       
         group by  month(dt)) a;


    2.排序:row_number(),rank(),dense_rank()
      row_number():123
      rank():113
      dense_rank():112


     


    2.1基础排序

    -- 1.1基础排序
    -- 购买商品品类数量的排名  
    select user_name,                      --
           count(distinct goods_category),--按goods_category排序
           row_number() over(order by count(distinct goods_category)),--1234
           rank() over(order by count(distinct goods_category)),      -- 113
           dense_rank() over(order by count(distinct goods_category)),--112
    from user_trade
    where substr(dt,1,7)='2019-01' --
    Group by user_name;            --用户名

    2.2 选出10.20.30 排名的用户
     

    --where  a.rank in(10,20,30)
    select a.user_name,            --1.显示字段          
           a.pay_amount,
           a.rank
     from (select user_name,        --2.子查询表
                  sum(pay_amount) pay_amount,
                  rank() over(order by sum(pay_amount)) rank
         from user_trade
         where year(dt)=2019          --时间    
         Group by user_name)a         -- a表选择
      where a.rank in(10,20,30);      --3.选择


    3.前百分之多少:ntile()
       


    3.1 注意点:整体分组 a)存在不能被整除的情况 b)整除的情况 c)不可以只分前25%,partition by的分组,不能用row between。

    --2019年1月,按支付金额分5组
    select user_name,                                        
           sum(pay_amount) pay_amount,
           ntile(5) over(order by sum(pay_amount)desc) level --分成5份
           from user_trade
           where year(dt,1,7)='2019-01'             
           Group by user_name;         

    3.2退款金额前10%的用户
         10%分10组以后的第一组

    --选出2019年退款金额排名前10%的用户
    select a.user_name
    from
    (select user_name,           
           sum(refund_amount) as refund_amount,
           ntile(10) over(order by sum(refund_amount)desc) level
      from user_trade
      where year(dt)=2019             
      group by user_name)a
    where a.level=1;    --level=1


    4.偏移分析:lag() ,lead()
     
     
      同一字段的前N行(lag),后N行(lead)

    exp_str:原始字段
    offset:偏移量
    defval:默认值

    --偏移 前一个
    
    select user_name,
           dt,                 --partition分组
           lag(dt,1,dt) over(partition by user_name order by dt),--前1个
           lag(dt) over(partition by user_name order by dt),  --前1个空值时返回dt
           lag(dt,2,dt) over(partition by user_name order by dt),--前2个
           lag(dt,2) over(partition by user_name order by dt)--前2个空值时返回dt
     from user_trade
     where dt>'0'
       and user_name in('ALice','Alexander');-- 按姓名自己排序
        
    -- 偏移 下一个
    
    select user_name,
           dt,            
           lead(dt,1,dt) over(partition by user_name order by dt),
           lead(dt) over(partition by user_name order by dt),  --后1个空值时返回空值
           lead(dt,2,dt) over(partition by user_name order by dt),
           lead(dt,2) over(partition by user_name order by dt)
     from user_trade
     where dt>'0'
       and user_name in('ALice','Alexander');      




     

    展开全文
  • Hive SQL窗口函数: 核心语法主框架: 8-Select 1 -From (left table) 3- (join_type)join(right_table) 2- On 4--Where 5-Group by 6- With 7-Having 9-Order by 10-Limit 1、 sum()、avg()用于累计窗口...
  • 利用窗口函数增加辅助列来计算,很明显本题窗口范围依旧是按照年和科目分组后的数据,我们可以利用分析函数max()对该窗口内的数据进行聚合求出每门课的最高成绩,作为辅助列。(辅助列往往是作为一种映射,一种对应...
  • hive sql 窗口函数与group by一起用

    千次阅读 2019-11-25 17:22:13
    窗口函数是写在select子句中的,而窗口函数开窗范围是基于后面的所有查询结果。。对每行结果进行开窗操作,然后每行都在这个窗口范围内执行聚合函数进行统计。统计结果作为这一行的一个字段值。   总结:窗口...
  • 比率:需要每个页面的pv/总的pv,这里用窗口函数sum() over() select t.date,t.p,t.uv,t.pv,round(t.pv/sum(t.pv) over(),3) from ( select to_date(at) date,page p,count(1) pv,count(distinct userid) uv ...
  • HIVE/SQL窗口函数

    2021-02-22 18:28:14
    面对这类需求,就需要使用sql的高级功能窗口函数了。 二.什么是窗口函数窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下:...
  • Hive窗口函数

    2020-01-31 16:54:30
    本文首发于:微信公众号【大数据每日哔哔,文章:Hive SQL 窗口函数SQL 中有一类函数叫做聚合函数,例如 sum()、avg()、max()、min() 等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是...
  • hivesql--窗口函数

    2021-01-28 11:44:57
    hivesql窗口函数 xmind获取地址:https://pan.baidu.com/s/15hegE_7LpWfKLrOVDBIs1w 提取码:skux
  • hive 窗口函数 分析函数
  • Hive窗口函数

    万次阅读 多人点赞 2018-09-20 00:01:35
    本文主要介绍hive中的窗口函数.hive中的窗口函数sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、...
  • hive sql窗口函数

    2020-03-12 18:53:57
    一、row_number 函数 区别于其它排序函数,相当于多了一个排序的列。 数据准备: name money ming 12 yang 23 ming 35 ming 54 yang 43 1、按照其中money 进行排序并增加一行。 select name,money,row_number() over...
  • hive常用窗口函数

    2020-04-16 17:28:25
    1 hive窗口函数 在平时的开发中常用那些hive函数?? 1、字符串操作函数?split、concat、ifnull、cast 2、聚合函数 : hive适用于分析,所以常用。 3、时间函数 : 数仓的特征随时间变化而变化,所以时间也特别多 ...
  • HiveSQL 窗口函数(开窗函数) 1.相关函数说明 OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。 CURRENT ROW:当前行 n PRECEDING:往前n行数据 n FOLLOWING:往后n行数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,735
精华内容 2,694
关键字:

hivesql窗口函数