一、题目

有用户账户表：包含年份，用户id和余额。请按照年份分组，取出余额前两小和前两大对应的用户id。**注意：**需要保持余额最小和最大的用户id排首位。

二、思路

1、计算每年 每个用户余额的升序排名和降序排名。升序排名前两名就是余额前两小的用户，降序排名前两名就是前两大的用户。可以使用row_number实现。

2、取每年余额最小的两个用户。可以使用collect_list实现行转列

3、取每年余额最大的两个用户。

4、最后使用年份关联。

三、解答

with tab_detail as (
  -- 1、计算每年 每个用户余额的升序排名和降序排名。升序排名前两名就是余额前两小的用户，降序排名前两名就是前两大的用户。可以使用row_number实现。
  select 
    year,
    user_id,
    value,
    row_number() over(partition by year order by value) asc_rn, -- 升序
    row_number() over(partition by year order by value desc) dasc_rn -- 降序
  from user_account_amount
)

select 
  t11.year,
  userids_min,
  userids_max
from (
  -- 2、取每年余额最小的两个用户。可以使用collect_list实现行转列
  select 
    year,
    concat_ws(',', collect_list(user_id)) userids_min
  from (
    select
      year,
      user_id
    from tab_detail
    where asc_rn <= 2
    order by asc_rn
  ) t01
  group by year
) t11

left join (
  -- 3、取每年余额最大的两个用户。
  select 
    year,
    concat_ws(',', collect_list(user_id)) userids_max 
  from (
    select
      year,
      user_id
    from tab_detail
    where dasc_rn <= 2
    order by dasc_rn
  ) t01
  group by year
) t12
on t11.year = t12.year

结果：

数据

# 建表
--建表语句
create table if not exists user_account_amount(
    year    string,
    user_id string,
    value   bigint
) comment '查询前2大和前2小用户并有序拼接'
stored as parquet
;
 
--插入数据
insert into user_account_amount(year, user_id, value)
values ('2022', 'A', 30),
       ('2022', 'B', 10),
       ('2022', 'C', 20),
       ('2023', 'A', 40),
       ('2023', 'B', 50),
       ('2023', 'C', 20),
       ('2023', 'D', 30);

注：题目获取自网络