行转列且有序

 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;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/pengpenghuhu/p/14638659.html