拟某款抽卡手游的6月份封测报告

# 时间权值
ll=pd.Series(date_list).dt.dayofweek
week_val=ll.values # 星期几
week_index=ll.index.values # 对应位置
for indx,val in zip(week_index,week_val):
    if val in(6,5,4):
        rights[indx]=(np.random.choice([1,2,3,4,5],1,p=[0.1,0.3,0.3,0.2,0.1]))
    
target_index=np.argwhere((date_list>=ver_date_start) & (date_list<=ver_date_end))

# 各段年龄段的权值
rights=np.concatenate([right1,right2,right3,right4,right5,right6])
rights=rights/sum(rights)
age_list=np.random.choice(range(8,101),len(amount_list),p=rights)

# ……其他字段类似
```

# 抽卡行为权值
# gacha 用户抽取次数应符合正态分布
# mean用户平均抽取
pay_usr_num=usr_cnt-int(usr_cnt*free_usr_rate) # 付费用户数
# 按照标准正态分布的模板 mean=0 std=1
scaled_normal = np.random.normal(0, 1, pay_usr_num)  
# 缩放和平移这个分布到标准分布的期望的范围  
mean=mean_times # mean_times 平均抽取次数 std_dev 标准差
gacha = mean*10 + std_dev * scaled_normal # 避免出现负数先放大10倍
gacha=gacha/10 # 还原原来大小
gacha = [max(x,1) for x in gacha] # 付费用户至少1次消费
gacha = np.round(gacha).astype(int)

# 由表tb_detail_cos的uid字段和datetime_comsume字段为入参生成表格
# 生成 4.5%的封号人员
# 为了避免与事实表冲突，封禁期间内产生了交易，设置解封日期都在充值日期之后
# 封号天数会根据封禁理由产生权值变化：


def usr_status(mnt,df,df_uid,df_datetime):
    tags={'不当言论':{3:20,7:10,90:2,360:0,360*10:0},
      '使用游戏漏洞谋益':{3:0,7:20,90:10,360:5,360*10:1},
      '使用第三方插件':{3:5,7:5,90:10,360:20,360*10:2},
      '非法账号':{3:0,7:0,90:0,360:10,360*10:20}}
    # 各tag权值
    tags_right=[20,2,8,1]
    tags_right=[right/sum(tags_right) for right in tags_right]
    # 被banned的tag
    ban_reason=[]
    
    re_df=df[[df_uid,df_datetime]].groupby(df_uid).max()
    usr=list(re_df.index)
    tmpdatetime=re_df.values.flatten()
    
    # 封禁列表
    num=len(usr)
    ban_num=round(num*0.045+0.01)
    idx_ban=list(np.random.choice(range(num),ban_num,replace=False))
    punish_days=np.zeros(num)
    
    
    for idx in range(len(tmpdatetime)):
        if tmpdatetime[idx]<'1970-01-01':
            if idx in idx_ban:
                first_monthday=f'2024-{mnt:0>2}-01'
                date_list=pd.date_range(first_monthday,pd.Timestamp(first_monthday)+pd.offsets.MonthEnd(0))
                datelt=list(pd.date_range(first_monthday,pd.Timestamp(first_monthday)+pd.offsets.MonthEnd(0)))
                day_random=np.random.choice(datelt).strftime('%Y-%m-%d')
                
                 # 获取tag
                tag=np.random.choice(list(tags.keys()),p=tags_right)
                ban_reason.append(tag)
                
                # 处罚天数与释放日期计算
                tag_right=list(tags[tag].values())
                tag_right=[right/sum(tag_right) for right in tag_right]
                days_ban=np.random.choice(list(tags[tag].keys()),p=tag_right)
                # 修改天数与日期
                punish_days[idx]=days_ban
                tmpdatetime[idx]=(pd.Timestamp(day_random)+pd.DateOffset(days=int(days_ban))).strftime('%Y-%m-%d')
                
            else:
                ban_reason.append('-')
                tmpdatetime[idx]='1970-01-01'
        else:
            if idx in idx_ban:  
                # 获取tag
                tag=np.random.choice(list(tags.keys()),p=tags_right)
                ban_reason.append(tag)

                # 处罚天数权值计算
                tag_right=list(tags[tag].values())
                tag_right=[right/sum(tag_right) for right in tag_right]
                days_ban=np.random.choice(list(tags[tag].keys()),p=tag_right)
                # 计算释放日期
                punish_days[idx]=days_ban
                tmpdatetime[idx]=(pd.Timestamp(tmpdatetime[idx])+pd.DateOffset(days=int(days_ban))).strftime('%Y-%m-%d')
                
            else:
                tmpdatetime[idx]=pd.Timestamp(tmpdatetime[idx]).strftime('%Y-%m-%d')
                ban_reason.append('-')
     
    usr_status=pd.DataFrame({
    'uid':usr,
    'punish_days':punish_days,
    'release_day':tmpdatetime,
    'reason_tag':ban_reason
    })
    
    return usr_status

create table if not exists usr_info_list(
uid string,
phone string,
city string,
gender string,
age int,
levels int,
usr_amount float,
forums string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
    "input.regex" = "([^\\t]*)\\t([^\\t]*)\\t([^\\t]*)\\t([^\\t]*)\\t([^\\t]*)\\t([^\\t]*)\\t([^\\t]*)\\t\\[([^\\t]*)\\]"
    );
 
create table if not exists detail_usr_comsume_list(
    uid string,
    consume float,
    usr_datetime string
)row format delimited
fields terminated by '\t';

 
create table if not exists usr_org_st(
    uid string,
    phone string,
    punish_days int,
    release_day date,
    reason_tag string
)row format delimited
fields terminated by '\t';

create table if not exists usr_st_tb_add(
    uid string,
    phone string,
    punish_days int,
    release_day date,
    reason_tag string
)
row format delimited
fields terminated by '\t'; 

load data inpath "hdfs://node_main:8020/user/tmp/proj4/usr_info_list.tsv" into table usr_info_list;
load data inpath "hdfs://node_main:8020/user/tmp/proj4/detail_usr_comsume_list.tsv" into table detail_usr_comsume_list;
load data inpath "hdfs://node_main:8020/user/tmp/proj4/usr_org_st.tsv" into table usr_org_st;
load data inpath "hdfs://node_main:8020/user/tmp/proj4/usr_st_tb_add.tsv" into table usr_st_tb_add;

export HADOOP_HEAPSIZE_MAX=6144
export HADOOP_HEAPSIZE_MIN=4096

export HIVE_HEAPSIZE=2048

<property>
    <name>hive.server2.active.passive.ha.enable</name>
    <value>true</value>
</property>

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.groupby.skewindata=true;
set hive.stats.column.autogather=false; 

create table general_tb as
select *,(usr_cnt-pay_cnt) as not_pay_cnt
from(
select sum(usr_amount) as GMV,
       avg(usr_amount) avg_amount,
count(uid) usr_cnt,
sum(if(age>17,1,0)) as adult_cnt,
count(if(usr_amount>0,1,NULL)) as pay_cnt
from usr_info_list
)t;

-- 日期
create table comsume_per_date as
select usr_date,
       if(dayofweek(usr_date)=1,7,dayofweek(usr_date)-1)as week_day,
       sum(consume) as day_consume
from(
    select uid,consume,date(usr_datetime) as usr_date
    from detail_usr_comsume_list
    where consume >0
         )t
group by usr_date
order by usr_date asc;
-- 时间
create table comsume_per_hour as
select hour(usr_datetime) as hour,count(*) as cnt,
round(avg(consume),1) as avg_consume,
sum(consume) as sum_consume
from detail_usr_comsume_list
where consume>0
group by hour(usr_datetime);

create table gender_info as
select gender,count(*),
       round(avg(usr_amount),1) as avg_amount,
       round(sum(usr_amount),1 )as sum_amount
from usr_info_list_PT
group by gender;

create table if not exists ages_info as
select age_tag,count(uid) as usr_cnt,
       round(avg(usr_amount),1) as avg_amount_per_tag,
       sum(usr_amount) as sum_amount_per_tag
from(
select
case when age < 16 then '少年' when age between 16 and 21 then '青少年'
when age between 22 and 35 then '年轻工薪青年'
when age between 36 and 50 then '中年'
else '老年' end as age_tag,usr_amount,uid
from usr_info_list_PT
        )t
group by age_tag;

create table level_info as
select levels,count(*)as lv_cnt,
round(avg(usr_amount),1) as avg_amount,
sum(usr_amount) as sum_amount
from usr_info_list
group by levels;

select city,count(uid),sum(usr_amount) as avg_amount,round(avg(usr_amount),1) as sum_amount
,sum(if(gender='男',1,0)) as male_cnt,sum(if(gender='女',1,0)) as female_cnt
from usr_info_list_pt
group by city;

create table forums_info as
select parts,
       sum(if(gender='男',1,0)) as male,sum(if(gender='女',1,0)) as female,
       count(city) city_cnt,
       round(avg(age),1) as avg_age,
       round(avg(levels),1) as avg_level,
       round(avg(usr_amount),1) as avg_amount,
       round(sum(usr_amount),1 )as sum_amount
from usr_info_list_PT lateral view explode(split(forums," ")) fm as parts
group by parts;

-- 更新6月份表格
create table usr_st_tb_tmp as
select a.uid,a.phone,a.punish_days,a.release_day,a.reason_tag
from usr_st_tb_add a
union
select /*+STREAMTABLE(b)*/ b.uid,
    b.phone,
    if(c.punish_days>0,c.punish_days,b.punish_days),
    if(c.release_day>'1970-01-01',c.release_day,b.release_day),
    if(c.reason_tag<>'-',c.reason_tag,b.reason_tag)
from usr_org_st b left join usr_st_tb_add c
using(uid);

-- 覆盖更新至状态表
insert overwrite table usr_st_tb 
select *
from usr_st_tb_tmp

更新日 = CALCULATE(sum(comsume_per_date[day_consume]),FILTER(comsume_per_date, comsume_per_date[usr_date] in {DATE(2024,06,14),DATE(2024,06,15),DATE(2024,06,16)}))
更新日之外 = CALCULATE(sum(comsume_per_date[day_consume]),FILTER(comsume_per_date,not comsume_per_date[usr_date] in {DATE(2024,06,14),DATE(2024,06,15),DATE(2024,06,16)}))
工作日消费 = CALCULATE([更新日之外],'comsume_per_date'[week_day] in {4,3,2,1})
周末消费 = CALCULATE([更新日之外],'comsume_per_date'[week_day] in {5,6,7})
add_rate = [周末消费]/[工作日消费]

# 封禁比例
banned_rate = CALCULATE(SUM('usr_status_info'[tag_cnt]),filter(all('usr_status_info'),[reason_tag]<>"-")) /CALCULATE(sum('usr_status_info'[tag_cnt]),all('usr_status_info'))