账号 | 活跃日期 |
G01 | 2022-03-01 |
G02 | 2022-03-01 |
G01 | 2022-03-02 |
G01 | 2022-03-03 |
G02 | 2022-03-03 |
G02 | 2022-03-04 |
G02 | 2022-03-31 |
G01 | 2022-03-29 |
G01 | 2022-03-30 |
G01 | 2022-03-31 |
int是4个子节,一个子节8位,4个子节32位,数字位有31位,第一位为符号位(0是正1是负数)
G01:【0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1】
G02:【0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,1】
通过转换我们得知
G01 = 2^31+2^30+2^29+2^2+2^1+2^0
G02 = 2^31+2^3+2^2+2^0
通过推理得知,
如果我在1号登陆,那么就是 2^('2022-03-01'-'2022-03-01') = 1 即 1号=1
如果我在2号登陆,那么就是 2^('2022-03-02'-'2022-03-01') =2 即 2号=2
如果我在3号登陆,那么就是 2^('2022-03-03'-'2022-03-01') = 4 即 3号=4
如果我在4号登陆,那么就是 2^('2022-03-04'-'2022-03-01') = 8 即 4号=8
如果我在31号登陆,那么就是 2^('2022-03-31'-'2022-03-01') 即 31号=2^30
得出的规律为 ,dt号登陆那么就是
POWER(2, datediff(dt, trunc(dt,'MM')))
每个人的bitmap 就可以构建出来了
drop table if EXISTS dws.mall_app_dau_bitmp;
create table dws.mall_app_dau_bitmp (
guid string,
bitval bigint
)
partitioned by ( dt string)
insert into dws.mall_app_dau_bitmp partition(dt='2022-02-13')
select
nvl(ld.guid,td.guid) as guid,
if(
-- 如果这个人之前没登陆过
ld.bitval is null,
-- 计算这个人登陆日期距离本月1号的差然后^2
POWER(2, datediff(dt, trunc(dt,'MM') )),
-- 如果这个人之前登陆过,那要看今天有没有登陆,如果登陆需要加上 今天距离本月1号的差然后^2 否则加上0
-- 为了保障超出int导致符号改变 需要与上int最大值
-- 这样就能得到今天的intval
(ld.bitval + if(td.guid is null,0,cast(POWER(2, datediff(dt, trunc(dt,'MM') )) as bigint)))&2147483647
)
from (
select guid,bitval
from dws.mall_app_dau_bitmp
where dt ='2022-02-12'
) ld
full join (
select guid,dt
from dws.mall_app_dau
where dt ='2022-02-13'
) td
on ld.guid = td.guid
使用
select guid,bitval,lpad(bin(bitval),32,0) from dws.mall_app_dau_bitmp where dt='2022-02-13'