用HiveQL解决问题,表名:amount 表字段及数据 year | user_id | value 2022 A 30 2022 B 10 2022 C 20 2023 A 40 2023 B 50 2023 C 20 2023 D 30 请写一段SQL,按year分组取value前两小和前两大时对应的user_id字段,注意:需保持value最小、最大的user_id排首位 得到如下结果: year | min_user_id | max_user_id 2022 B,C A,C 2023 C,D B,A
时间: 2024-01-28 07:03:41 浏览: 107
以下是一段HiveQL,按year分组取value前两小和前两大时对应的user_id字段:
```
SELECT
year,
CONCAT_WS(",", COLLECT_LIST(CASE WHEN rank_num <= 2 THEN user_id ELSE NULL END)) as min_user_id,
CONCAT_WS(",", COLLECT_LIST(CASE WHEN rank_num_desc <= 2 THEN user_id ELSE NULL END)) as max_user_id
FROM
(SELECT
year,
user_id,
value,
ROW_NUMBER() OVER(PARTITION BY year ORDER BY value ASC, user_id ASC) as rank_num,
ROW_NUMBER() OVER(PARTITION BY year ORDER BY value DESC, user_id ASC) as rank_num_desc
FROM
amount
) t
WHERE
rank_num <= 2 OR rank_num_desc <= 2
GROUP BY
year
ORDER BY
year ASC;
```
解释:
1. 内部查询使用窗口函数 ROW_NUMBER 对每个年份中的数据按 value 升序或降序排序,并为每个用户分配排名。
2. 外部查询使用 CONCAT_WS 函数按 year 分组,对每个年份中排名前两位的最小和最大值进行聚合。在 CONCAT_WS 函数中,使用 CASE WHEN 子句对排名小于等于 2 的用户进行筛选,并使用逗号分隔符将 user_id 进行连接。
3. WHERE 子句用于筛选排名小于等于 2 的数据。
4. 最后使用 ORDER BY 对结果按 year 升序排序。
阅读全文