行转列且有序

 1 select 
 2   user_id, 
 3   arr[0],
 4   arr[1],
 5   arr[2]
 6 from(
 7     select 
 8       user_id,
 9       sort_array(collect_list(order_id)) as arr
10     from( 
11         SELECT 
12             t3.*, 
13             ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY cnt DESC) rn
14         FROM (
15           select 
16               user_id, 
17               order_id , 
18               count(1) cnt 
19           from 
20               t2 
21           group by 
22               user_id, order_id
23         ) t3
24       )t4
25     WHERE 
26         t4.rn <= 3 
27     group by 
28         user_id

29 ) t5;

例二：实现如下功能：

select
uid, 
max(case when d = 20220101 then num else 0 end) as d1,
max(case when d = 20220102 then num else 0 end) as d2
from (
select 
20220101 as d,
1111 as uid,
10 as num

union all 
select
20220101 as d,
222 as uid,
6 as num

union all 
select
20220102 as d,
1111 as uid,
8 as num

union all 
select
20220102 as d,
222 as uid,
16 as num
) t1
group by uid;

posted on 2021-04-09 20:17 大鹏的鸿鹄之志阅读(66) 评论(0) 编辑收藏举报