20180417_02.md

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

作者

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.