题目:你有一张table_a,需要计算2021年1月1日~6月30日各个内容分区前10%的up主明细填入table_b
tabel_a如下
tid(内容分区) | up_id | vv(单日播放量) | log_date(日期) |
---|---|---|---|
游戏 | 1 | 111 | 20200101 |
游戏 | 1 | 324 | 20200102 |
游戏 | 2 | 113 | 20200101 |
生活 | 5 | 112 | 20200101 |
table_b
tid(内容分区) | up_id | vv(累计播放量) | vv_rate(占分区播放量比例) |
---|---|---|---|
思路:(应该是对的,当然应该还有更好的方法,欢迎讨论)
1、log_date为string,需要转为为date,用于筛选时间
2、计算各个up主的累计播放量,计算各个分区总的播放量
3、计算vv_rate
4、对vv_rate其取10%
5、最后填入table_b
# 前三步可以一起做
select
distinct(tid) # 为了输出唯一值
,up_id
,sum(vv) over(partition by tid,up_id) as vv # 累计播放量
,sum(vv) over(partition by tid,up_id) /sum(vv) over(partition by tid) as vv_rate # 累计播放量占分区比例
from table_a
# 转码时间格式,筛选
where date_format(log_date,'%Y-%m-%d') between '2021-01' and '2021-06'
# 为了后续太杂乱,另这个表为a
# 利用ntile构造,排序10%
select
*,
ntile(10) over(partition by tid order by vv_rate desc) as sort_value # 利用ntile分析函数对vv_rate进行排序
from a
# 另这个表为b
select
tid
,up_id
,vv
,vv_rate
from b
where
sort_value = 1
# 另这个表为c,insert到table_b
insert into table_b
select * from c
# 当然需要你将这些abc写到一起
这个题还是比较有难度,10分钟能写出来的都是大佬!!!