select userid,singerid
from (
 select a.userid,b.singerid_list
 from 播放流水 a left join (
  select scid,concat_ws(‘,’,collect_list(singerid)) singerid_list from 歌曲歌手关系表 group by scid --合唱歌曲的歌手放在一个singerid_list中
 ) b on a.scid=b.scid
) x lateral view explode(split(singerid_list,‘,’)) xs as singerid --关联完后再爆开singerid_list

–步骤1只需执行一次，所有n只需执行一次步骤2，只要步骤1足够完善，可以支持后续多种步骤2规则拓展。如不用01表示，用活跃度表示，逗号隔开。
步骤1，建历史累计表，用active_his字段记录userid的历史每天活跃情况，0代表不活跃，1代表活跃，最右一位代表当前一天：
dt userid active_his
2023-01-10 1 1111111111111111111111111111111111111111
2023-01-10 2 1100000110000011000001100000110000011000
2023-01-10 3 101
insert overwrite table active_his_table partition (dt=‘today’)
select userid,concat(max(active_his),max(today_flag)) active_his --字符串拼接（太长可截取某个最大长度，避免无限拼接）
from (
 select userid,active_his,‘0’ today_flag from active_his_table where dt=‘yesterday’ --昨日累计结果
union all
 select userid,‘’ active_his,‘1’ today_flag from 活跃流水 where dt=‘today’ --今日增量
) x group by userid

步骤2，用正则表达式匹配，性能非常高效，哪怕有些过分的逻辑，也可制定强大的正则表达式。有新需求拓展，加一个正则表达式可立即出数：
select count(1) from active_his_table where dt=‘today’ and active_his rlike ‘1.{23}(?=0*1).{7}$’ --（30天前的活跃用户，在近7天内还活跃）

步骤1：圈定前后2天维表变更部分
cache table c1 as (select userid,年龄段,sum(if(dt=昨天,-1,1)) flag_adddel from 用户画像 where dt in (昨天,今天) group by userid,年龄段 having flag_adddel<>0)
步骤2：算出历史统计表的变更部分，c1可以走广播
insert table c2 as (select b.年龄段,sum(flag_adddel) pv_adddel from 历史流水 a inner join c1 b on a.userid=b.userid group by b.年龄段)
步骤3：结合旧的统计表和变更部分，算出新的历史统计表
insert table 统计表 partition (dt=今天) select 年龄段,sum(pv) from (select 年龄段,pv from 统计表 where dt=昨天 union all select 年龄段,pv_adddel pv from c2) x group by 年龄段
步骤4：新的统计表不含今天流水，很多时候，还需要补上今天流水
insert table 统计表 partition (dt=今天) select 年龄段,sum(pv) from (select b.年龄段,count(1) pv from 今天流水 a inner join 用户画像 b on a.userid=b.userid and b.dt=今天 group by b.年龄段 union all select 年龄段,pv from 统计表 where dt=今天) x group by 年龄段

1. 80%play_count为1，重复率太高。抽样1%，创建辅助排序字段：play_count+3位随机小数，求得0.01-0.99共100个分位数，耗时4分钟
create table tmp_dac.t_zkp_order_percentile_rand_spark as
select
percentile(play_count, 0.01) p1,percentile(play_count, 0.02) p2,…,percentile(play_count, 0.99) p99
from (select play_count+cast(rand() as decimal(4,3)) play_count from (select play_count from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘2023-09-29’ and rand()<=0.01) x) x;

2. 按分位数求分组，组内排序，耗时8分钟
create table tmp_dac.t_zkp_testorder2_spark as
select userid,scid,play_count,grp,row_number() over(partition by grp order by play_count desc) rn,0 final_rn from (
select userid,scid,play_count,case 
when play_count2<1.012 then 0 when play_count2<1.024 then 1 … when play_count2<7.439 then 98 
else 99
end grp from (
select userid,scid,play_count,play_count+rand() play_count2 from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘2023-09-29’
) x
) x;

3. 组间排序累加，耗时9分钟
cache table c1 as (select grp,sum© over(order by grp)-c acc_rn from (select grp,count(1) c from tmp_dac.t_zkp_testorder2_spark group by grp) b);
insert overwrite table tmp_dac.t_zkp_testorder2_spark
select userid,scid,play_count,a.grp,rn,acc_rn+rn final_rn from tmp_dac.t_zkp_testorder2_spark a
inner join c1 b on a.grp=b.grp;

1. play_count为1-1亿的随机整数，重复率太低。抽样1%，创建辅助排序字段：play_count后四位置为0，求得0.01-0.99共100个分位数，耗时2分钟
create table tmp_dac.t_zkp_order_percentile_spark_rand as
select
percentile(play_count, 0.01) p1,percentile(play_count, 0.02) p2,…,percentile(play_count, 0.99) p99
from (select int(play_count/10000)*10000 play_count from (select play_count from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘rand’ and rand()<=0.01) x) x

2. 按分位数求分组，组内排序，耗时11分钟
select userid,scid,play_count,grp,row_number() over(partition by grp order by play_count desc) rn,0 final_rn from (
select userid,scid,play_count,case 
when play_count2<990000.0 then 0 when play_count2<2000000.0 then 1 … when play_count2<9.899E7 then 98 
else 99
end grp from (
select userid,scid,play_count,play_count+rand() play_count2 from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘rand’
) x
) x;

3. 组间排序累加，耗时10分钟
cache table c1 as (select grp,sum© over(order by grp)-c acc_rn from (select grp,count(1) c from tmp_dac.t_zkp_testorder2_spark_rand group by grp) b);
insert overwrite table tmp_dac.t_zkp_testorder2_spark_rand
select userid,scid,play_count,a.grp,rn,acc_rn+rn final_rn from tmp_dac.t_zkp_testorder2_spark_rand a
inner join c1 b on a.grp=b.grp;

1. 60%play_count为1，重复率太高。抽样0.1%，创建辅助排序字段：play_count+3位随机小数，求得0.01-0.99共100个分位数，耗时4小时
create table tmp_dac.t_zkp_order_percentile_rand_spark_2month as
select
percentile(play_count, 0.01) p1,percentile(play_count, 0.02) p2,…,percentile(play_count, 0.99) p99
from (select play_count+cast(rand() as decimal(4,3)) play_count from (select play_count from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘2month’ and rand()<=0.001) x) x;

2. 按分位数求分组，组内排序，耗时3小时
create table tmp_dac.t_zkp_testorder2_spark_2month as
select userid,scid,play_count,grp,row_number() over(partition by grp order by play_count desc) rn,0 final_rn from (
select userid,scid,play_count,case 
when play_count2<1.016 then 0 when play_count2<1.032 then 1 … when play_count2<27.303 then 98 
else 99
end grp from (
select userid,scid,play_count,play_count+rand() play_count2 from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘2month’
) x
) x;

3. 组间排序累加，耗时5小时
cache table c1 as (select grp,sum© over(order by grp)-c acc_rn from (select grp,count(1) c from tmp_dac.t_zkp_testorder2_spark_2month group by grp) b);
insert overwrite table tmp_dac.t_zkp_testorder2_spark_2month
select userid,scid,play_count,a.grp,rn,acc_rn+rn final_rn from tmp_dac.t_zkp_testorder2_spark_2month a
inner join c1 b on a.grp=b.grp;

1. play_count为1-1亿的随机整数，重复率太低。抽样0.1%，创建辅助排序字段：play_count后四位置为0，求得0.01-0.99共100个分位数，耗时2小时
create table tmp_dac.t_zkp_order_percentile_spark_2monthrand as
select
percentile(play_count, 0.01) p1,percentile(play_count, 0.02) p2,…,percentile(play_count, 0.99) p99
from (select int(play_count/10000)*10000 play_count from (select play_count from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘rand2’ and rand()<=0.001) x) x;

2. 按分位数求分组，组内排序，耗时4小时
create table tmp_dac.t_zkp_testorder2_spark_2monthrand as
select userid,scid,play_count,grp,row_number() over(partition by grp order by play_count desc) rn,0 final_rn from (
select userid,scid,play_count,case 
when play_count2<1000000.0 then 0 when play_count2<1990000.0 then 1 … when play_count2<9.899E7 then 98 
else 99
end grp from (
select userid,scid,play_count,play_count+rand() play_count2 from tmp_dac.oa_bi_profile_play_scid_userid_d where dt=‘rand2’
) x
) x

3. 组间排序累加，耗时3小时
cache table c1 as (select grp,sum© over(order by grp)-c acc_rn from (select grp,count(1) c from tmp_dac.t_zkp_testorder2_spark_2monthrand group by grp) b);
insert overwrite table tmp_dac.t_zkp_testorder2_spark_2monthrand
select userid,scid,play_count,a.grp,rn,acc_rn+rn final_rn from tmp_dac.t_zkp_testorder2_spark_2monthrand a
inner join c1 b on a.grp=b.grp;