PostgreSQL bit运算CASE - 最近7天消费金额大于N的用户bits

标签

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(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)