背景:
有些小游戏公司,对于新上线的关卡小游戏,会对每个关卡的用户,通关情况进行统计分布,调整关卡的难易程度、任务奖励等
一次赢: 经过本关没有输的记录,只有赢的记录,如果赢了一次,这一关是无法再重新玩的
先输后赢: 经过本关,输的记录在赢的记录前面,最终是赢的
一直输: 经过本关没有赢的记录,只有输的记录
对于这种一次赢、先输后赢、一直输,在数仓里面只是一个标签。
怎么打这种标签:
注:gamestatus='7’为赢的状态,gamestatus='8’为输的状态,
一次赢:
拿每个用户每个关卡赢的记录去关联输的记录,关联不上的代表这个用户在这个关卡一次赢
select
dateline,
apptypeid,
accid,
gameround,
fail_and_win_tag,
dt
from
(select
t1.dateline,
t1.apptypeid,
t1.accid,
t1.gameround,
case when (t2.accid is not null and t1.dateline<t2.dateline) or t2.accid is null then 1 end as fail_and_win_tag,
t1.dt
from
(select
dateline,
apptypeid,
accid,
gameround,
dt
from
(select
dateline,
apptypeid,
accid,
gameround,
row_number() over(distribute by apptypeid,accid,gameround sort by dateline asc) as rank,
dt
from dw_center.dwb_app_100091_game_user_srcqid
where dt='vardate' and gamestatus='7') t01
where t01.rank=1) t1
left join
(select
dateline,
apptypeid,
accid,
gameround,
dt
from
(select
dateline,
apptypeid,
accid,
gameround,
row_number() over(distribute by apptypeid,accid,gameround sort by dateline asc) as rank,
dt
from dw_center.dwb_app_100091_game_user_srcqid
where dt='vardate' and gamestatus='8') t02
where t02.rank=1) t2
on t1.apptypeid=t2.apptypeid and t1.accid=t2.accid and t1.gameround=t2.gameround and t1.dt=t2.dt) t3
where t3.fail_and_win_tag='1'
注:t2.accid is not null and t1.dateline<t2.dateline
是为了防止有的用户,赢了之后,又有输的记录。原则上没有这种,但是异常数据经常会发生,所以严谨一点总没有错
先输后赢、一直输:
拿每个用户每个关卡输的记录去关联赢的记录,如果关联不上,则这个用户在这个关卡没有赢的记录,如果关联上了,并且输的dateline<赢的dateliine
select
t1.dateline,
t1.apptypeid,
t1.accid,
t1.gameround,
--输的取最早一条,赢的数据取最早一条
--拿输的数据关联赢的数据,
--如果关联不上,说明没有赢的数据,代表一直输
--如果关联上了,赢的时间比输的大,说明赢的在输之后,代表先输后赢
case when t2.accid is not null and t1.dateline<t2.dateline then 2
when t2.accid is null then 3 end as fail_and_win_tag,
t1.dt
from
(select
dateline,
apptypeid,
accid,
gameround,
dt
from
(select
dateline,
apptypeid,
accid,
gameround,
row_number() over(distribute by apptypeid,accid,gameround sort by dateline asc) as rank,
dt
from dw_center.dwb_app_100091_game_user_srcqid
where dt='vardate' and gamestatus='8') t01
where t01.rank=1) t1
left join
(select
dateline,
apptypeid,
accid,
gameround,
dt
from
(select
dateline,
apptypeid,
accid,
gameround,
row_number() over(distribute by apptypeid,accid,gameround sort by dateline asc) as rank,
dt
from dw_center.dwb_app_100091_game_user_srcqid
where dt='vardate' and gamestatus='7') t02
where t02.rank=1) t2
on t1.apptypeid=t2.apptypeid and t1.accid=t2.accid and t1.gameround=t2.gameround and t1.dt=t2.dt) t3
where t3.fail_and_win_tag in ('2','3')
注:可以full join去做
有无下一关,怎么判断呢?
先给每个用户打一个最大关卡的标签,如果当前关卡等于最大关卡,代表无下一关,就停留在标签关卡。如果当前关卡小于最大关卡,代表有下一关。