作者
digoal
日期
2018-04-17
标签
PostgreSQL , bit , case when
背景
有一个这样的数据结构
日期, 消费金额，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(amountget_bit(users, 0)) > 100 then 1 else 0 end as pos0,
case when sum(amountget_bit(users, 1)) > 100 then 1 else 0 end as pos1,
case when sum(amountget_bit(users, 2)) > 100 then 1 else 0 end as pos2,
case when sum(amountget_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)
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
