table1
| id | city | view_count |
|---|---|---|
| 1123 | 1,2,3 | 100 |
| 212123 | 2,3,4 | 110 |
| 212123 | 2,3 | 120 |
table2
| city_id | city_name |
|---|---|
| 1 | 北京 |
| 2 | 上海 |
| 3 | 广州 |
| ... | ... |
- 写个sql把表1的id换成中文的list.
select collect_set(city_name), id
from
(
table2 join
select
id,city_id from table1 later view explode(city) **tmptable** as city_id
on table2.city_id = tmptable.city_id
) innert
group by id
2、取出来各个班级前三名平均分和姓名
row_number() over(order by xxx) 简单根据xxx排序
row_number() over(partition by xxxa order by xxxb) 按照xxxa分组，分组内按照xxxb排序
sum(amount) over(partition by name ordre by month)
ref:
explode语法：https://yq.aliyun.com/articles/654743
hive sql优化：https://cloud.tencent.com/developer/article/1423060
练习题：
https://www.jianshu.com/p/faee67f104f7
https://blog.csdn.net/qq_43081842/article/details/102832142
