制作网站时，您需要多少创新创意产品设计方案？

请人做网站要多少,创新创意产品设计方案,广州哪里做网站,如保做网站赢利目录 1 题目2 建表语句3 题解 1 题目 有用户账户表#xff0c;包含年份#xff0c;用户id和值,请按照年份分组#xff0c;取出值前两小和前两大对应的用户id#xff0c;需要保持值最小和最大的用户id排首位。 样例数据 ------------------------- | year | user_id | v… 目录 1 题目2 建表语句3 题解 1 题目 有用户账户表包含年份用户id和值,请按照年份分组取出值前两小和前两大对应的用户id需要保持值最小和最大的用户id排首位。 样例数据 ------------------------- | 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 | -------------------------期望结果 ----------------------------------------- | year | max2_user_list | min2_user_list | ----------------------------------------- | 2022 | A,C | B,C | | 2023 | B,A | C,D | -----------------------------------------2 建表语句 --建表语句 create table if not exists t_amount (year string,user_id string,value bigint )ROW FORMAT DELIMITEDFIELDS TERMINATED BY ,STORED AS orc;--插入数据insert into t_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)3 题解 1row_number函数根据年份分组value正排和倒排得到两个序列 select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rn from t_amount执行结果 ---------------------------------------- | user_id | year | value | desc_rn | rn | ---------------------------------------- | B | 2022 | 10 | 3 | 1 | | C | 2022 | 20 | 2 | 2 | | A | 2022 | 30 | 1 | 3 | | C | 2023 | 20 | 4 | 1 | | D | 2023 | 30 | 3 | 2 | | A | 2023 | 40 | 2 | 3 | | B | 2023 | 50 | 1 | 4 | ----------------------------------------2根据年份分组取出value最大user_id第二大user_id最小user_id第二小user_id 根据年份分组取出每年最大、第二大最小、第二小用户ID。使用 if 对desc_rnrn进行判断对符合条件的数据取出 user_id其他去null然后使用聚合函数取出结果。 select year,max(if(desc_rn 1, user_id, null)) as max1_user_id,max(if(desc_rn 2, user_id, null)) as max2_user_id,max(if(rn 1, user_id, null)) as min1_user_id,max(if(rn 2, user_id, null)) as min2_user_id from (select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rnfrom t_amount) t1 group by year执行结果 ------------------------------------------------------------------- | year | max1_user_id | max2_user_id | min1_user_id | min2_user_id | ------------------------------------------------------------------- | 2022 | A | C | B | C | | 2023 | B | A | C | D | -------------------------------------------------------------------3按照顺序拼接得到最终结果 按照题目要求进行字符拼接 拼接max1_user_id、max2_user_id为max2_list拼接min1_user_id、min2_user_id为min2_list; select year,concat(max(if(desc_rn 1, user_id, null)), ,,max(if(desc_rn 2, user_id, null))) as max2_user_list,concat(max(if(rn 1, user_id, null)), ,,max(if(rn 2, user_id, null))) as min2_user_list from (select user_id, year, value, row_number() over (partition by year order by value desc) as desc_rn, row_number() over (partition by year order by value) as rnfrom t_amount) t1 group by year执行结果 ----------------------------------------- | year | max2_user_list | min2_user_list | ----------------------------------------- | 2022 | A,C | B,C | | 2023 | B,A | C,D | -----------------------------------------