你真的懂hive窗口函数吗，如何开窗聚合？

目录

  • 1 窗口函数 Windowing functions
  • 2 OVER详解 The OVER clause
  • 2.1 标准聚合函数
  • 2.2 分析函数 Analytics functions
  • 2.3 OVER子句也支持聚合函数
  • 2.4 window clause 的另一种写法

1 窗口函数 Windowing functions

FIRST_VALUE(col, bool DEFAULT)

返回分组窗口内第一行col的值，DEFAULT默认为false，如果指定为true，则跳过NULL后再取值

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  NULL AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       FIRST_VALUE(col) over(partition by group_id order by col) as col_new
FROM tmp;
group_id col col_new
1 a a
1 b a
1 c a
2 NULL NULL
2 e NULL 
WITH tmp AS
(
  SELECT 1 AS group_id, NULL AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  NULL AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       FIRST_VALUE(col, true) over(partition by group_id order by col) as col_new
FROM tmp;
group_id col col_new
1 NULL NULL
1 b b
1 c b
2 NULL NULL
2 e e

LAST_VALUE(col, bool DEFAULT)

返回分组窗口内最后一行col的值，DEFAULT默认为false，如果指定为true，则跳过NULL后再取值

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  NULL AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LAST_VALUE(col) over(partition by group_id order by col desc) as col_new
FROM tmp;
group_id col col_new
1 c c
1 a a
1 NULL NULL
2 e e
2 d d 
WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  NULL AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LAST_VALUE(col, true) over(order by group_id,col desc rows between 1 preceding and 1 following) as col_new
FROM tmp;
group_id col col_new
1 c a
1 a a
1 NULL e
2 e d
2 d d

LEAD(col, n, DEFAULT)

返回分组窗口内往下第n行col的值，n默认为1，往下第n没有时返回DEFAULT（DEFAULT默认为NULL）

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LEAD(col) over(partition by group_id order by col) as col_new
FROM tmp;

等同于：

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LAST_VALUE(col) over(partition by group_id order by col rows between 1 FOLLOWING and 1 FOLLOWING) as col_new
FROM tmp;

返回结果都是：

group_id col col_new
1 a b
1 b c
1 c NULL
2 d e
2 e NULL 
WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LEAD(col, 2, 'z') over(partition by group_id order by col) as col_new
FROM tmp;

返回结果：

group_id col col_new
1 a c
1 b z
1 c z
2 d z
2 e z

LAG(col, n, DEFAULT)

返回分组窗口内往上第n行col的值，n默认为1，往上第n没有时返回DEFAULT（DEFAULT默认为NULL）

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LAG(col) over(partition by group_id order by col) as col_new
FROM tmp;

等同于：

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       FIRST_VALUE(col) over(partition by group_id order by col rows BETWEEN 1 PRECEDING and 1 PRECEDING) as col_new
FROM tmp;

返回结果都是：

group_id col col_new
1 a NULL
1 b a
1 c b
2 d NULL
2 e d 
WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'd' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       LAG(col, 2, 'zz') over(partition by group_id order by col) as col_new
FROM tmp;

返回结果：

group_id col col_new
1 a zz
1 b zz
1 c a
2 d zz
2 e zz

2 OVER详解 The OVER clause

FUNCTION(expr) OVER([PARTITION BY statement] [ORDER BY statement] [window clause])

  • FUNCTION：包括标准聚合函数（COUNT、SUM、MIN、MAX、AVG）和一些分析函数（RANK、ROW_NUMBER、DENSE_RANK等）
  • PARTITION BY：可以由一个或者多个列组成
  • ORDER BY：可以由一个或者多个列组成
  • window clause：(ROWS | RANGE) BETWEEN (UNBOUNDED PRECEDING | num PRECEDING | CURRENT ROW) AND (UNBOUNDED PRECEDING | num PRECEDING | CURRENT ROW)
  • 当 window clause 未指定时，默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即分组内第一行至当前行作为窗口
  • 当 window clause 和 ORDER BY 都未指定时，默认为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即分组内第一行至最后一行作为窗口

2.1 标准聚合函数

COUNT(expr) OVER()

返回窗口内行数

WITH tmp AS
(
  SELECT 1 AS group_id, 'a' AS col 
  UNION ALL SELECT 1 AS group_id,  'b' AS col 
  UNION ALL SELECT 1 AS group_id,  'c' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col 
  UNION ALL SELECT 2 AS group_id,  'e' AS col
)
SELECT group_id,
       col,
       count(col) over(partition by group_id) as cnt1,
       count(col) over(partition by group_id order by col) as cnt2,
       count(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as cnt3,
       count(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as cnt4
FROM tmp;
group_id col cnt1 cnt2 cnt3 cnt4
1 a 3 1 3 3
1 b 3 2 2 2
1 c 3 3 1 1
2 e 2 2 2 1
2 e 2 2 1 1

SUM(expr) OVER()

返回窗口内求和值

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
       col,
       SUM(col) over(partition by group_id) as sum1,
       SUM(col) over(partition by group_id order by col) as sum2,
       SUM(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as sum3,
       SUM(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as sum4
FROM tmp;
group_id col sum1 sum2 sum3 sum4
1 1 6 1 6 6
1 2 6 3 5 5
1 3 6 6 3 3
2 4 8 8 8 4
2 4 8 8 4 4

MIN(expr) OVER()

返回窗口内最小值

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       MIN(col) over(partition by group_id) as min1,
       MIN(col) over(partition by group_id order by col) as min2,
       MIN(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as min3
FROM tmp;
group_id col min1 min2 min3
1 1 1 1 1
1 2 1 1 2
1 3 1 1 3
2 4 4 4 4
2 5 4 4 5

MAX(expr) OVER()

返回窗口内最大值

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       MAX(col) over(partition by group_id) as max1,
       MAX(col) over(partition by group_id order by col) as max2,
       MAX(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as max3
FROM tmp;
group_id col max1 max2 max3
1 1 3 1 3
1 2 3 2 3
1 3 3 3 3
2 4 5 4 5
2 5 5 5 5

AVG(expr) OVER()

返回窗口内平均值

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
       col,
       AVG(col) over(partition by group_id) as avg1,
       AVG(col) over(partition by group_id order by col) as avg2,
       AVG(col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg3,
       AVG(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg4
FROM tmp;
group_id col avg1 avg2 avg3 avg4
1 1 2.0 1.0 2.0 2.0
1 2 2.0 1.5 2.5 2.5
1 3 2.0 2.0 3.0 3.0
2 4 4.0 4.0 4.0 4.0
2 4 4.0 4.0 4.0 4.0

2.2 分析函数 Analytics functions

RANK() OVER()

返回分组内排名（不支持自定义窗口）

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       RANK() over(partition by group_id order by col desc) as r
FROM tmp;
group_id col r
1 3 1
1 3 1
1 1 3
2 5 1
2 4 2

ROW_NUMBER() OVER()

返回分组内行号（不支持自定义窗口）

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       ROW_NUMBER() over(partition by group_id order by col desc) as r
FROM tmp;
group_id col r
1 3 1
1 3 2
1 1 3
2 5 1
2 4 2

DENSE_RANK() OVER()

返回分组内排名（排名相等不会留下空位，不支持自定义窗口）

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       DENSE_RANK() over(partition by group_id order by col desc) as r
FROM tmp;
group_id col r
1 3 1
1 3 1
1 1 2
2 5 1
2 4 2

CUME_DIST() OVER()

返回分组内累计分布值，即分组内小于(或者大于)等于当前值行数/分组内总行数

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       CUME_DIST() over(partition by group_id order by col asc) as d1,
       CUME_DIST() over(partition by group_id order by col desc) as d2
FROM tmp;
group_id col d1 d2
1 3 1.0 0.6666666666666666
1 3 1.0 0.6666666666666666
1 1 0.3333333333333333 1.0
2 5 1.0 0.5
2 4 0.5 1.0

PERCENT_RANK() OVER()

返回百分比排序值，即分组内当前行的RANK值-1/分组内总行数-1

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       RANK() over(partition by group_id order by col asc) as r1,
       PERCENT_RANK() over(partition by group_id order by col asc) as p1,
       RANK() over(partition by group_id order by col desc) as r2,
       PERCENT_RANK() over(partition by group_id order by col desc) as p2
FROM tmp;
group_id col r1 p1 r2 p2
1 3 2 0.5 1 0.0
1 3 2 0.5 1 0.0
1 1 1 0.0 3 1.0
2 5 2 1.0 1 0.0
2 4 1 0.0 2 1.0

NTILE(INTEGER x) OVER()

返回分区编号（将有序分区划分为x个组，称为bucket，并为分区中的每一行分配一个bucket编号）

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       col,
       NTILE(2) over(partition by group_id order by col asc) as bucket_id
FROM tmp;
group_id col bucket_id
1 1 1
1 3 1
1 3 2
1 3 2
2 4 1
2 5 2

2.3 OVER子句也支持聚合函数

Hive 2.1.0及之后版本，OVER子句也支持聚合函数，如：

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  5 AS col
)
SELECT group_id,
       RANK() over(order by sum(col) desc) as r
FROM tmp
group by group_id;

结果为：

group_id r
2 1
1 2

2.4 window clause 的另一种写法

将window子句写在from后面，在over后使用别名进行引用，如下:

WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
       col,
       AVG(col) over w1 as avg1,
       AVG(distinct col) over(partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following) as avg2
FROM tmp
WINDOW w1 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following);

结果为：

group_id col avg1 avg2
1 1 2.0 2.0
1 2 2.5 2.5
1 3 3.0 3.0
2 4 4.0 4.0
2 4 4.0 4.0 
WITH tmp AS
(
  SELECT 1 AS group_id, 1 AS col 
  UNION ALL SELECT 1 AS group_id,  2 AS col 
  UNION ALL SELECT 1 AS group_id,  3 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col 
  UNION ALL SELECT 2 AS group_id,  4 AS col
)
SELECT group_id,
       col,
       AVG(col) over w1 as avg1,
       AVG(distinct col) over w2 as avg2
FROM tmp
WINDOW w1 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following),
w2 AS (partition by group_id order by col rows between CURRENT ROW and UNBOUNDED following);

结果为：

group_id col avg1 avg2
1 1 2.0 2.0
1 2 2.5 2.5
1 3 3.0 3.0
2 4 4.0 4.0
2 4 4.0 4.0
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,921评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,635评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,393评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,836评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,833评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,685评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,043评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,694评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,671评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,670评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,779评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,424评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,027评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,984评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,214评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,108评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,517评论 2赞 343

推荐阅读更多精彩内容

  • pySpark 中文API (2)
    pyspark.sql模块 模块上下文 Spark SQL和DataFrames的重要类： pyspark.sql...
    mpro阅读 9,446评论 0赞 13
  • oracle学习笔记８: 分析函数
    分析函数，也称为窗口函数，通常被认为仅对数据仓库SQL有用。使用分析函数的查询，基于对数据行的分组来计算总量值。与...
    猫猫_tomluo阅读 3,305评论 3赞 18
  • Hive SQL 窗口函数
    本文首发：大数据每日哔哔-Hive SQL 窗口函数 Hive 的窗口函数 在 SQL 中有一类函数叫做聚合函数，...
    cuteximi_1995阅读 801评论 0赞 2
  • MySQL-窗口函数
    一般的商业数据库（其实也就是DB2，Oracle，SQL Server）都具备窗口函数这个功能，只不过名称不同，我...
    花讽院_和狆阅读 1,527评论 2赞 1
  • Hive开窗函数
    1. 介绍 普通聚合函数聚合的行集是组，开窗函数聚合的行集是窗口。因此，普通聚合函数每组（Group by）只有一...
    幸运猪x阅读 8,129评论 0赞 4