选择TOP 3值及其所属的组和值

Question

我有一个这样的表

<表类> 用户 组 价值 tbody><<tr>G10.9G20.8G30.3G40.7BG10.9BG20.8BG30.7CG10.9CG20.8

此操作称为pivot，可通过以下方式执行:

• 使用ROW_NUMBER窗口函数
选择每个组的排名
• 为每个新字段
提取组和值
• 聚合每个用户的值

WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY user_ ORDER BY VALUE DESC) AS rn
FROM tab
)
SELECT user_, 
MAX(CASE WHEN rn = 1 THEN GROUP_ END) AS first_G,
MAX(CASE WHEN rn = 1 THEN VALUE_ END) AS first_G_val,
MAX(CASE WHEN rn = 2 THEN GROUP_ END) AS second_G,
MAX(CASE WHEN rn = 2 THEN VALUE_ END) AS second_G_val,
MAX(CASE WHEN rn = 3 THEN GROUP_ END) AS third_G,
MAX(CASE WHEN rn = 3 THEN VALUE_ END) AS third_G_val
FROM cte
GROUP BY user_