PostgreSQL bit运算CASE - 最近7天消费金额大于N的用户bits
背景
有一个这样的数据结构
日期, 消费金额,userbitmaps。表示这一天这些设置为1的BIT位对应对用户消费了这么多金额。
需求,最近7天,消费大于多少的用户有哪些。
create table t_test (
dt date, -- 日期
amount int, -- 消费额度
users varbit -- 用户BITMAP
);
生成随机BIT的函数
create or replace function gen_rand_bit() returns bit(64) as $$
select (sqrt(random())::numeric*9223372036854775807*2-9223372036854775807::numeric)::int8::bit(64);
$$ language sql strict;
插入100万测试数据
insert into t_test
select current_date-(random()*1000)::int, random()*100, gen_rand_bit() from generate_series(1,1000000);
最近7天消费金额大于100的用户SQL如下:
select (concat(pos0,pos1,pos2,pos3))::varbit from
(
select
case when sum(amount*get_bit(users, 0)) > 100 then 1 else 0 end as pos0,
case when sum(amount*get_bit(users, 1)) > 100 then 1 else 0 end as pos1,
case when sum(amount*get_bit(users, 2)) > 100 then 1 else 0 end as pos2,
case when sum(amount*get_bit(users, 3)) > 100 then 1 else 0 end as pos3
-- 你需要补齐所有的BIT位,本例只测4个
from t_test
where
dt between current_date-7 and current_date
) t;
concat
--------
1111
(1 row)