目录
FIRST_VALUE(col, bool DEFAULT)
FUNCTION(expr) OVER([PARTITION BY statement] [ORDER BY statement] [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 |
---|