hive 开窗函数
·
一、窗口函数核心概念
窗口函数(Window Function)和普通聚合函数(sum/max)的核心区别：
普通聚合函数：group by 会将多行聚合为一行，丢失明细；
窗口函数：不缩减行数，为每行计算一个"窗口范围内"的聚合/排序结果，保留明细。
语法结构:
函数名(列) OVER (
[PARTITION BY 分区列] --按哪些列分组(类似group by，不合并行) 省略则全量数据为一组
[ORDER BY 排序列] --窗口内的排序规则
[ROWS/RANGE BETWEEN 边界1 AND 边界2] --窗口的行范围(可选)
) as 别名
PARTITION BY：必选(无则全局为一个分区），将数据分成多个"窗口"；
ORDER BY：可选，定义窗口内的行顺序；
ROWS/RANGE：可选，定义窗口的物理/逻辑行范围（比如"当前行的前3行到当前行"）。
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW -- 前1行到当前行
rows between unbounded preceding and current row) -- 从组内第一行到当前行
-- 常用边界：unbounded preceding(第一行)、n preceding(前n行)、current row(当前行)
-- 不写，默认窗口是 range between unbounded preceding and current row(会把排序相同日期的行视为一个逻辑范围导致结果不准)
-- 常用边界:unbounded preceding(第一行)、n preceding(前n行)、current row(当前行)
--窗口范围关键字说明:防止相同
--CURRENT ROW:当前行
--n preceding:前n行
--n following:后n行
--unbounded preceding:分区的第一行
--unbounded following:分区的最后一行
2.三大函数分类
2.1 聚合类窗口函数
在窗口内做聚合(sum/max/min/avg/count），数仓中常用来计算累计值、环比、分组内总计。
--组内累积/全局聚合
sum()、avg()、count()、max()、min()
collect_list()、collect_set()
示例：
create table tmp.tmp_orders as
select 1001 as user_id,1 as levels,'2022-01-01' as order_date,10 as amount
union all
select 1001 as user_id,1 as levels,'2022-01-05' as order_date,46 as amount
union all
select 1001 as user_id,1 as levels,'2022-01-05' as order_date,40 as amount
union all
select 1001 as user_id,1 as levels,'2022-01-05' as order_date,35 as amount
union all
select 1001 as user_id,1 as levels,'2022-02-03' as order_date,23 as amount
union all
select 1001 as user_id,1 as levels,'2022-01-01' as order_date,15 as amount
union all
select 1002 as user_id,1 as levels,'2022-01-08' as order_date,80 as amount
union all
select 1002 as user_id,1 as levels,'2022-01-08' as order_date,90 as amount
union all
select 1002 as user_id,1 as levels,'2022-01-09' as order_date,20 as amount;
select user_id,levels,order_date,amount from tmp.tmp_orders;
user_id levels order_date amount
1001 1 2022-01-01 10
1001 1 2022-01-05 46
1001 1 2022-01-05 40
1001 1 2022-01-05 35
1001 1 2022-02-03 23
1001 1 2022-01-01 15
1002 1 2022-01-08 80
1002 1 2022-01-08 90
1002 1 2022-01-09 20
select user_id,levels,order_date,amount
,sum(amount) over(partition by user_id order by order_date asc) as amount1
,sum(amount) over(partition by user_id) as amount2
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2
1001 1 2022-01-01 15 25 169
1001 1 2022-01-01 10 25 169
1001 1 2022-01-05 46 146 169
1001 1 2022-01-05 40 146 169
1001 1 2022-01-05 35 146 169
1001 1 2022-02-03 23 169 169
1002 1 2022-01-08 80 170 190
1002 1 2022-01-08 90 170 190
1002 1 2022-01-09 20 190 190
over(partition by dept order by salary
rows between unbounded preceding and current row) -- 从组内第一行到当前行
-- 不写，默认窗口是 range between unbounded preceding and current row(会把排序相同日期的行视为一个逻辑范围导致结果不准)
-- 常用边界:unbounded preceding(第一行)、n preceding(前n行)、current row(当前行)
窗口范围关键字说明:防止相同
CURRENT ROW:当前行
n preceding:前n行
n following:后n行
unbounded preceding:分区的第一行
unbounded following:分区的最后一行
select user_id,levels,order_date,amount
,sum(amount) over(partition by user_id order by order_date asc) as amount1
,sum(amount) over(partition by user_id order by order_date asc rows between unbounded preceding and current row) as amount2
,sum(amount) over(partition by user_id) as amounts
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2 amounts
1001 1 2022-01-01 15 25 15 169
1001 1 2022-01-01 10 25 25 169
1001 1 2022-01-05 46 146 71 169
1001 1 2022-01-05 40 146 111 169
1001 1 2022-01-05 35 146 146 169
1001 1 2022-02-03 23 169 169 169
1002 1 2022-01-08 80 170 80 190
1002 1 2022-01-08 90 170 170 190
1002 1 2022-01-09 20 190 190 190
select user_id,levels,order_date,amount
,sum(amount) over(partition by user_id order by order_date asc) as amount1
,sum(amount) over(partition by user_id order by order_date asc rows between unbounded preceding and current row) as amount2
,sum(amount) over(partition by user_id order by order_date asc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as amount3
,sum(amount) over(partition by user_id,order_date order by order_date asc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as amount4
,sum(amount) over(partition by user_id,order_date order by order_date asc rows between 1 preceding and 1 following) as amount5
,sum(amount) over(partition by user_id) as amounts
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2 amount3 amount4 amount5 amounts
1001 1 2022-01-01 15 25 15 25 25 25 169
1001 1 2022-01-01 10 25 25 25 25 25 169
1001 1 2022-01-05 46 146 71 146 121 86 169
1001 1 2022-01-05 40 146 111 146 121 121 169
1001 1 2022-01-05 35 146 146 146 121 75 169
1001 1 2022-02-03 23 169 169 169 23 23 169
1002 1 2022-01-08 80 170 80 170 170 170 190
1002 1 2022-01-08 90 170 170 170 170 170 190
1002 1 2022-01-09 20 190 190 190 20 20 190
select user_id,levels,order_date,amount
,sum(amount) over(partition by user_id order by order_date asc) as ord_sum_amounts --排序求和
,sum(amount) over(partition by user_id) as sum_amounts --求和
,avg(amount) over(partition by user_id order by order_date asc) as ord_avg_amounts --排序求均
,avg(amount) over(partition by user_id) as avg_amounts --求均
,count(amount) over(partition by user_id order by order_date asc) as ord_count_amounts --排序求数量
,count(amount) over(partition by user_id) as count_amounts --求数量
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2 amount3 amount4 amount5 amount6
1001 1 2022-01-01 15 25 169 12.5 28.1667 2 6
1001 1 2022-01-01 10 25 169 12.5 28.1667 2 6
1001 1 2022-01-05 46 146 169 29.2 28.1667 5 6
1001 1 2022-01-05 40 146 169 29.2 28.1667 5 6
1001 1 2022-01-05 35 146 169 29.2 28.1667 5 6
1001 1 2022-02-03 23 169 169 28.1667 28.1667 6 6
1002 1 2022-01-08 80 170 190 85 63.3333 2 3
1002 1 2022-01-08 90 170 190 85 63.3333 2 3
1002 1 2022-01-09 20 190 190 63.3333 63.3333 3 3
select user_id,levels,order_date,amount
,max(amount) over(partition by user_id order by order_date asc) as amount1 --排序求最大
,max(amount) over(partition by user_id) as amount2 --求最大
,max(amount) over(partition by user_id rows between unbounded preceding and current row) as amount3 --求最大
,min(amount) over(partition by user_id order by order_date asc) as amount4 --排序求最小
,min(amount) over(partition by user_id) as amount5 --求最小
,min(amount) over(partition by user_id rows between unbounded preceding and current row) as amount6 --求最小
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2 amount3 amount4 amount5 amount6
1001 1 2022-01-01 15 15 46 15 10 10 15
1001 1 2022-01-01 10 15 46 15 10 10 10
1001 1 2022-01-05 46 46 46 46 10 10 10
1001 1 2022-01-05 40 46 46 46 10 10 10
1001 1 2022-01-05 35 46 46 46 10 10 10
1001 1 2022-02-03 23 46 46 46 10 10 10
1002 1 2022-01-08 80 90 90 80 80 20 80
1002 1 2022-01-08 90 90 90 90 80 20 80
1002 1 2022-01-09 20 90 90 90 20 20 20
-- 开窗使用collect_list collect_set 受hive版本、执行引擎影响，建议使用mr模式，不要用tez和spark
select user_id,levels,order_date,amount
,collect_list(order_date) over(partition by user_id order by order_date asc) as order_date1 --排序求相同
,collect_list(order_date) over(partition by user_id) as order_date2 --求相同
from tmp.tmp_orders;
user_id levels order_date amount order_date1 order_date2
1001 1 2022-01-01 15 ["2022-01-01","2022-01-01"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1001 1 2022-01-01 10 ["2022-01-01","2022-01-01"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1001 1 2022-01-05 46 ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1001 1 2022-01-05 40 ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1001 1 2022-01-05 35 ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1001 1 2022-02-03 23 ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"] ["2022-01-01","2022-01-01","2022-01-05","2022-01-05","2022-01-05","2022-02-03"]
1002 1 2022-01-08 80 ["2022-01-08","2022-01-08"] ["2022-01-08","2022-01-08","2022-01-09"]
1002 1 2022-01-08 90 ["2022-01-08","2022-01-08"] ["2022-01-08","2022-01-08","2022-01-09"]
1002 1 2022-01-09 20 ["2022-01-08","2022-01-08","2022-01-09"] ["2022-01-08","2022-01-08","2022-01-09"]
select user_id,levels,order_date,amount
,collect_set(order_date) over(partition by user_id order by order_date asc) as order_date3 --排序求去重相同
,collect_set(order_date) over(PARTITION BY user_id) as order_date4 --求去重相同
from tmp.tmp_orders;
user_id levels order_date amount order_date3 order_date4
1001 1 2022-01-01 15 ["2022-01-01"] ["2022-01-01","2022-01-05","2022-02-03"]
1001 1 2022-01-01 10 ["2022-01-01"] ["2022-01-01","2022-01-05","2022-02-03"]
1001 1 2022-01-05 46 ["2022-01-01","2022-01-05"] ["2022-01-01","2022-01-05","2022-02-03"]
1001 1 2022-01-05 40 ["2022-01-01","2022-01-05"] ["2022-01-01","2022-01-05","2022-02-03"]
1001 1 2022-01-05 35 ["2022-01-01","2022-01-05"] ["2022-01-01","2022-01-05","2022-02-03"]
1001 1 2022-02-03 23 ["2022-01-01","2022-01-05","2022-02-03"] ["2022-01-01","2022-01-05","2022-02-03"]
1002 1 2022-01-08 80 ["2022-01-08"] ["2022-01-08","2022-01-09"]
1002 1 2022-01-08 90 ["2022-01-08"] ["2022-01-08","2022-01-09"]
1002 1 2022-01-09 20 ["2022-01-08","2022-01-09"] ["2022-01-08","2022-01-09"]
2.2 排序类窗口函数(最常用)
用于给每行生成排序序号，数仓中常用来取TopN、去重、标记最新记录。
--连续排序:不重复排名：1,2,3,4(按值排序)
row_number() over(partition by dept order by salary desc) as rn
--跳跃排序:相同值同排名，跳过后续序号：1,1,3
rank()
--密集排序:相同值同排名，不跳过：1,1,2
dense_rank()
--百分比排名
percent_rank()
select user_id,levels,order_date,amount
,row_number() over(partition by user_id order by order_date asc) as ranks1 --排序连续排序
,row_number() over(partition by user_id) as ranks2 --连续排序
,rank() over(partition by user_id order by order_date asc) as ranks3 --排序跳跃排序
,rank() over(partition by user_id) as ranks4 --跳跃排序
,DENSE_RANK() over(partition by user_id order by order_date asc) as ranks5 --排序密集排序
,dense_rank() over(partition by user_id) as ranks6 --密集排序
,percent_rank() over(partition by user_id order by order_date asc) as ranks7 --排序百分比排名
,percent_rank() over(partition by user_id) as ranks8 --百分比排名
,percent_rank() over(order by order_date asc) as ranks8 --百分比排名全
from tmp.tmp_orders;
user_id levels order_date amount ranks1 ranks2 ranks3 ranks4 ranks5 ranks6 ranks7 ranks8
1001 1 2022-01-01 15 1 1 1 1 1 1 0 0
1001 1 2022-01-01 10 2 2 1 1 1 1 0 0
1001 1 2022-01-05 46 3 3 3 1 2 1 0.4 0.25
1001 1 2022-01-05 40 4 4 3 1 2 1 0.4 0.25
1001 1 2022-01-05 35 5 5 3 1 2 1 0.4 0.25
1001 1 2022-02-03 23 6 6 6 1 3 1 1 1
1002 1 2022-01-08 80 1 1 1 1 1 1 0 0.625
1002 1 2022-01-08 90 2 2 1 1 1 1 0 0.625
1002 1 2022-01-09 20 3 3 3 1 2 1 1 0.875
2.3 偏移类窗口函数(前后行类跨行访问)
用于取当前行的上/下N行数据，数仓中常用来计算环比（如本月vs上月）、同比。
--上一行/下一行的值
lag(字段,offset,default) over(...) --lag(列,n,默认值) 取当前行的前n行数据(n默认1，默认值可选)
lead(字段,offset,default) over(...) --lead(列,n,默认值) 取当前行的后n行数据(n默认1，默认值可选)
--分组第一行/最后一行的值
first_value(字段) over(...)
last_value(字段) over(...)
select user_id,levels,order_date,amount
,lag(amount,1) over(partition by user_id order by order_date asc) as amount1 --排序上一行按日期，无默认返回null
,lag(amount,1,0) over(partition by user_id order by order_date asc) as amount2 --排序上一行按日期，默认0
,lag(amount,2,0) over(partition by user_id) as amount3 --排序上一行按日期
,lead(amount,1) over(partition by user_id order by order_date asc) as amount4 --排序下一行按日期，无默认返回null
,lead(amount,1,0) over(partition by user_id order by order_date asc) as amount5 --排序下一行按日期，默认0
,lead(amount,2,0) over(partition by user_id) as amount6 --排序下一行按日期
,first_value(amount) over(partition by user_id order by order_date asc) as amount7 --排序分组第一行
,first_value(amount) over(partition by user_id) as amount8 --分组第一行
,last_value(amount) over(partition by user_id order by order_date asc) as amount9 --排序分组最后一行
,last_value(amount) over(partition by user_id order by order_date asc rows between unbounded preceding and current row) as amount91 --排序分组最后一行
,last_value(amount) over(partition by user_id) as amount10 --分组最后一行
from tmp.tmp_orders;
user_id levels order_date amount amount1 amount2 amount3 amount4 amount5 amount6 amount7 amount8 amount9 amount91 amount10
1001 1 2022-01-01 15 0 0 10 10 46 15 15 10 15 23
1001 1 2022-01-01 10 15 15 0 46 46 40 15 15 10 10 23
1001 1 2022-01-05 46 10 10 15 40 40 35 15 15 35 46 23
1001 1 2022-01-05 40 46 46 10 35 35 23 15 15 35 40 23
1001 1 2022-01-05 35 40 40 46 23 23 0 15 15 35 35 23
1001 1 2022-02-03 23 35 35 40 0 0 15 15 23 23 23
1002 1 2022-01-08 80 0 0 90 90 20 80 80 90 80 20
1002 1 2022-01-08 90 80 80 0 20 20 0 80 80 90 90 20
1002 1 2022-01-09 20 90 90 80 0 0 80 80 20 20 20
3.窗口子句(指定范围 近N行)
over(partition by dept order by salary
rows between unbounded preceding and current row) -- 从组内第一行到当前行
-- 常用边界：unbounded preceding(第一行)、n preceding(前n行)、current row(当前行)
窗口范围关键字说明：
CURRENT ROW：当前行
n PRECEDING：前n行
n FOLLOWING：后n行
UNBOUNDED PRECEDING：分区的第一行
UNBOUNDED FOLLOWING：分区的最后一行
4. 典型应用场景
分组TopN：row_number + where过滤
同比环比：lag计算与上一期差值
累计统计：sum(amt) over(partition by date order by time)
去重聚合：collect_set收集组内唯一值
4.1 计算当月/上月/当年
SELECT
mon,
-- 当月累计
SUM(day_amt) OVER (PARTITION BY mon ORDER BY mon ROWS UNBOUNDED PRECEDING) AS month_cum,
-- 当年累计
SUM(day_amt) OVER (PARTITION BY SUBSTR(mon,1,4) ORDER BY mon ROWS UNBOUNDED PRECEDING) AS year_cum,
-- 上月累计（通过 LAG 获取上一月的总额）
LAG(mon_amt, 1) OVER (ORDER BY mon) AS prev_month_total
FROM (
SELECT
SUBSTR(order_date,1,7) AS mon,
SUM(amount) AS day_amt,
SUM(SUM(amount)) OVER (PARTITION BY SUBSTR(order_date,1,7)) AS mon_amt -- 计算当月总额
FROM tmp.tmp_orders
GROUP BY SUBSTR(order_date,1,7)
) t;
mon month_cum year_cum prev_month_total
2022-01 336 336 0
2022-02 23 359 336
4.2 多维拉链表初始化
create table tmp.tmp_customer as
select 1001 as user_id,1 as levels,'1234' as mobile,'A' as addr,20260101 as dates
union all
select 1001 as user_id,1 as levels,'1234' as mobile,'A' as addr,20260102 as dates
union all
select 1001 as user_id,1 as levels,'1234' as mobile,'A' as addr,20260103 as dates
union all
select 1001 as user_id,1 as levels,'1235' as mobile,'A' as addr,20260104 as dates
union all
select 1001 as user_id,1 as levels,'1235' as mobile,'A' as addr,20260105 as dates
union all
select 1001 as user_id,1 as levels,'1235' as mobile,'B' as addr,20260106 as dates
union all
select 1001 as user_id,2 as levels,'1235' as mobile,'B' as addr,20260107 as dates
union all
select 1002 as user_id,3 as levels,'2345' as mobile,'C' as addr,20260104 as dates
union all
select 1002 as user_id,3 as levels,'2345' as mobile,'C' as addr,20260105 as dates
union all
select 1002 as user_id,3 as levels,'2346' as mobile,'B' as addr,20260106 as dates
union all
select 1002 as user_id,2 as levels,'2346' as mobile,'B' as addr,20260107 as dates;
--标记变化点（任一属性不同于上一行）
create table tmp.tmp_customer1 as
select a.user_id as user_id,a.levels as levels,a.mobile as mobile,a.addr as addr,a.dates as dates
,a.prev_levels as prev_levels,a.prev_mobile as prev_mobile,a.prev_addr as prev_addr
,(CASE WHEN (a.prev_levels IS NULL AND a.levels IS NOT NULL) OR (a.prev_levels IS NOT NULL AND a.levels IS NULL) OR (a.prev_levels != a.levels) OR
(a.prev_mobile IS NULL AND a.mobile IS NOT NULL) OR (a.prev_mobile IS NOT NULL AND a.mobile IS NULL) OR (a.prev_mobile != a.mobile) OR
(a.prev_addr IS NULL AND a.addr IS NOT NULL) OR (a.prev_addr IS NOT NULL AND a.addr IS NULL) OR (a.prev_addr != a.addr)
THEN 1 ELSE 0 END) AS change_flag
from (
select user_id,levels,mobile,addr,dates
--字段较多 使用concat_ws哈希比较或者md5
,lag(levels) over(PARTITION BY user_id ORDER BY dates asc) AS prev_levels
,lag(mobile,1) over(PARTITION BY user_id ORDER BY dates asc) AS prev_mobile
,lag(addr) over(PARTITION BY user_id ORDER BY dates asc) AS prev_addr
from tmp.tmp_customer
) a ;
user_id levels mobile addr dates prev_levels prev_mobile prev_addr change_flag
1001 1 1234 A 20260101 1
1001 1 1234 A 20260102 1 1234 A 0
1001 1 1234 A 20260103 1 1234 A 0
1001 1 1235 A 20260104 1 1234 A 1
1001 1 1235 A 20260105 1 1235 A 0
1001 1 1235 B 20260106 1 1235 A 1
1001 2 1235 B 20260107 1 1235 B 1
1002 3 2345 C 20260104 1
1002 3 2345 C 20260105 3 2345 C 0
1002 3 2346 B 20260106 3 2345 C 1
1002 2 2346 B 20260107 3 2346 B 1
--生成版本组ID(对变化标记累加)
create table tmp.tmp_customer2 as
SELECT user_id,levels,mobile,addr,dates
-- 使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 否则会把排序相同日期的行视为一个逻辑范围导致结果不准,当前场景用默认的order by不写后面也不影响
,SUM(change_flag) OVER (PARTITION BY user_id ORDER BY dates asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS version_id
FROM tmp.tmp_customer1;
user_id levels mobile addr dates version_id
1001 1 1234 A 20260101 1
1001 1 1234 A 20260102 1
1001 1 1234 A 20260103 1
1001 1 1235 A 20260104 2
1001 1 1235 A 20260105 2
1001 1 1235 B 20260106 3
1001 2 1235 B 20260107 4
1002 3 2345 C 20260104 1
1002 3 2345 C 20260105 1
1002 3 2346 B 20260106 2
1002 2 2346 B 20260107 3
--按版本组聚合，取起止日期
create table tmp.tmp_customer3 as
SELECT user_id,levels,mobile,addr
,MIN(dates) AS start_date
,LEAD(MIN(dates),1,'99999999') over(PARTITION BY user_id ORDER BY MIN(dates) asc) AS end_date
,replace(date_add(from_unixtime(unix_timestamp(cast(LEAD(MIN(dates),1,'29991231') over(PARTITION BY user_id ORDER BY MIN(dates) asc) as string),'yyyyMMdd'),'yyyy-MM-dd'),-1),'-','') as end_date1
FROM tmp.tmp_customer2
GROUP BY user_id,levels,mobile,addr,version_id;
user_id levels mobile addr start_date end_date end_date1
1001 1 1234 A 20260101 20260104 20260103
1001 1 1235 A 20260104 20260106 20260105
1001 1 1235 B 20260106 20260107 20260106
1001 2 1235 B 20260107 99999999 29991230
1002 3 2345 C 20260104 20260106 20260105
1002 3 2346 B 20260106 20260107 20260106
1002 2 2346 B 20260107 99999999 29991230
5.实例
对指定数据排序后row_number
create table tmp.test_row_number as
select '115' as task_id,'3' as operation_id,'0' as operation_order union all
select '115' as task_id,'1' as operation_id,'1' as operation_order union all
select '115' as task_id,'2' as operation_id,'2' as operation_order union all
select '115' as task_id,'4' as operation_id,'3' as operation_order union all
select '115' as task_id,'5' as operation_id,'4' as operation_order union all
select '115' as task_id,'9' as operation_id,'5' as operation_order union all
select '115' as task_id,'6' as operation_id,'6' as operation_order union all
select '115' as task_id,'7' as operation_id,'7' as operation_order union all
select '115' as task_id,'11' as operation_id,'8' as operation_order union all
select '115' as task_id,'12' as operation_id,'9' as operation_order union all
select '115' as task_id,'13' as operation_id,'10' as operation_order union all
select '115' as task_id,'14' as operation_id,'11' as operation_order union all
select '115' as task_id,'15' as operation_id,'12' as operation_order union all
select '115' as task_id,'18' as operation_id,'13' as operation_order union all
select '115' as task_id,'16' as operation_id,'14' as operation_order union all
select '115' as task_id,'8' as operation_id,'15' as operation_order union all
select '115' as task_id,'10' as operation_id,'16' as operation_order union all
select '115' as task_id,'17' as operation_id,'17' as operation_order union all
select '115' as task_id,'19' as operation_id,'18' as operation_order union all
select '115' as task_id,'20' as operation_id,'19' as operation_order;
select t.task_id as task_id
,concat_ws(',', collect_list(t.task_id_rank) over(partition by t.task_id order by t.rn)) as operation_id
,row_number() over(PARTITION BY t.task_id ORDER BY t.rn desc ) as rn
from
(
select task_id
,concat(task_id,':',operation_id) as task_id_rank
,row_number() over(partition by task_id order by CAST(operation_order AS INT) asc) as rn
,operation_order as operation_order
from tmp.test_row_number
) t
select a.task_id as task_id
,a.operation_id as operation_id
from (
select t.task_id as task_id
,concat_ws(',', collect_list(t.task_id_rank) over(partition by t.task_id order by t.rn)) as operation_id
,row_number() over(PARTITION BY t.task_id ORDER BY t.rn desc ) as rn
from
(
select task_id
,concat(task_id,':',operation_id) as task_id_rank
,row_number() over(partition by task_id order by CAST(operation_order AS INT) asc) as rn
,operation_order as operation_order
from tmp.test_row_number
) t
) a where a.rn = 1;
北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台
更多推荐
18
0- 0
已为社区贡献5条内容
所有评论(0)