上班打卡是一件很常见的事情，就像小时候的大宝广告，大宝天天见。

对于这种每天都要见的事物，也是广告主追逐的地盘，就好像电梯广告一样，门禁广告也悄然流行起来。

小小的门禁机，每天活跃的时间为上下班高峰，可能也就2小时左右的时间，通常广告不易过长，往往是很简短的广告，也不易放太多广告在门禁内，一般十几个已经足够了，否则2小时都不够轮播的。

今天我给大家分享一下门禁广告销售系统的需求，以及如何利用阿里云RDS for PostgreSQL特性(bitpack)来满足这样的需求。

回顾一下我之前写的 《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统》 , 性能无需多言，以一敌十。

首先分析一下门禁设备，通常一个门禁设备可以放十几条广告（前面分析了，多了也不合适）。

广告主根据他要投放的广告区域，选择可以投放广告的门禁设备。

我们可以把门禁想象成火车，一个门禁设备相当于一班火车，每天开一班，每班十几张票，票的有效期局限于购买日期当天。

有了这个假设的前提，就比较好操作了。

• 门禁机属性设计

编号，状态，区域属性，总共有多少位置，每个位置的已销售时间区间，维修窗口（指不能销售的时间区间）。

• 广告主查询指定区域可用的门禁，指定时间区间，进行购票。

这个需求体现在查询（找到对应的设备），更新（将购票信息更新到设备中）。同时，这里也很容易出现抢票的情况，例如多个广告主都勾选了同样的设备，并且购买了同样的位置相交的时间区间时，就会出现冲突。

• 统计需求，例如统计某个时间全局，已销售的数据，剩余的票数等。

get_bit (varbit, int, int) returns varbit

  从指定位置开始获取N个BIT位，返回varbit

  例如 get_bit('111110000011', 3, 5)   返回11000

 

(201611的版本还没有加入)

get_bit_array (varbit, int, int, int) returns int[]

  从指定位置开始获取N个BIT位，返回1|0的下标

  例如 get_bit('111110000011', 3, 5, 1)   返回11000的下标 array[3,4]

 

(201611的版本还没有加入)

get_bit_array (varbit, int, int[]) returns int[]

  查询指定下标的BIT位为0|1的，返回下标，超出不统计 

  例如 get_bit('111110000011', 1, array[1,5,6,7,10,11])   返回array[1,10,11]

 

set_bit_array (varbit, int, int, int[]) returns varbit 

  将指定位置的BIT设置为0|1，超出原始长度的部分填充0|1  

  例如 set_bit_array('111100001111', 0, 1, array[1,15]) 返回 1011000011111110   (设置为0, 超出补1)

 

(201611的版本还没有加入)

set_bit_array (varbit, int, int, int[], int) returns varbit 

  将指定位置的BIT设置为0|1，超出原始长度的部分填充0|1 , 设置N位即返回 

  例如 set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2) 返回 111111001111 (设置为1, 超出补0, 设置满2位即返回)

 

(201611的版本还没有加入)

set_bit_array_rec (varbit, int, int, int[]) returns (varbit,int[]) 

  将指定位置的BIT设置为0|1，超出原始长度的部分填充0|1 

  返回设置后的varbit

  同时返回(原来不是0|1)此次被设置为0|1的位置数组 

  例如 set_bit_array('111100001111', 0, 1, array[1,15]) 返回 1011000011111110   (设置为0, 超出补1)

  同时返回array[1,15]  (超出补位的不算)

 

(201611的版本还没有加入)

set_bit_array_rec (varbit, int, int, int[], int) returns (varbit,int[])

  将指定位置的BIT设置为0|1，超出原始长度的部分填充0|1 , 设置N位即返回 

  返回设置后的varbit

  同时返回(原来不是0|1)此次被设置为0|1的位置数组 

  例如 set_bit_array('111100001111', 1, 0, array[1,4,5,6,7], 2) 返回 111111001111 (设置为1, 超出补0, 设置满2位即返回)

  同时返回array[4,5]

 

bit_count (varbit, int, int, int) returns int

  从第n位开始，统计N个BIT位中有多少个0|1，如果N超出长度，则只计算已经存在的。  

  例如 bit_count('1111000011110000', 1, 5, 4) 返回 1  (0001)

 

bit_count (varbit, int) returns int 

  统计整个bit string中1|0的个数。  

  例如 bit_count('1111000011110000', 1) 返回 8

 

(201611的版本还没有加入)

bit_count_array (varbit, int, int[]) returns int  

  统计指定位置bit string中1|0的个数。  

  例如 bit_count('1111000011110000', 1, array[1,2,7,8]) 返回 3 

 

bit_fill (int, int) returns varbit 

  填充指定长度的0 或 1

  例如 bit_fill(0,10) 返回 '0000000000'

 

(201611的版本还没有加入)

bit_rand (int, int, float) returns varbit  

  填充指定长度的随机BIT，并指定1或0的随机比例  

  例如 bit_rand(10, 1, 0.3) 可能返回 '0101000001'  

 

bit_posite (varbit, int, boolean) returns int[]  

  返回 1|0 的位置信息，下标从0开始计数, true时正向返回，false时反向返回    

  例如 bit_posite ('11110010011', 1, true) 返回 [0,1,2,3,6,9,10]  

       bit_posite ('11110010011', 1, false) 返回 [10,9,6,3,2,1,0]

 

bit_posite (varbit, int, int, boolean) returns int[]  

  返回 1|0 的位置信息，下标从0开始计数，true时正向返回，false时反向返回，返回N个为止  

  例如 bit_posite ('11110010011', 1, 3, true) 返回 [0,1,2]  

       bit_posite ('11110010011', 1, 3, false) 返回 [10,9,6]

根据前面提供的信息，我们结合业务，设计表。

• 属性表，每个门禁一条记录，描述门禁的各种属性，例如区域，编号，状态，总票数。

create table menjin ( 

  id int primary key, -- 编号

  loc point, -- 区域, 位置 (建议使用PostGIS插件, 使用geometry类型)

  state int, -- 状态

  sits int not null -- 票数(例如15)

);

插入初始记录，假设有1万门禁设备，每个门禁15个广告位置。

insert into menjin select generate_series(1,10000), ('('||trunc(random()*10000)||','||trunc(random()*100000)||')')::point, 1, 15;

 

INSERT 0 10000

• 售票表，每个位置一条记录，覆盖一个时间区间的售票情况，假设最小售卖单位为1个位置1天的跨度。

使用BIT表示时间，offset表示第一个BIT的时间。20年只需要7200个BIT，所需空间约908字节。

create table menjin_sale(

  id serial8 primary key, -- 主键, 可用来设置ad lock，防止并发更新

  menjin_id int, -- 门禁ID

  menjin_sitid smallint, -- 位置ID

  crt_time timestamp, -- 创建时间

  mod_time timestamp, -- 记录修改时间

  offset_date date, -- 偏移量, 表示第一个比特位的日期

  sale_window varbit, -- 已销售时间区间，以及维护时间窗口范围，每个BIT代表1天，1表示已销售，0表示未销售  

  sale_window_arr int[] -- 最后一次成功设置的sale_window下标, 表示成功购买|退货的位置

); 

插入初始记录，如果有新的设备进来，添加到售票表。

insert into menjin_sale(menjin_id,menjin_sitid,crt_time,offset_date,sale_window) select id, generate_series(1,sits), now(), current_date, bit_fill(0,7200) from menjin;

 

INSERT 0 150000

如果将来的门禁设备很多(例如超过1亿时)，可以对表进行分区，如按menjin_id分区。

• 流水/对账表，用来存储每次的销售记录，当广告主购买广告时，在售卖表里只是更新了一条记录，但是售卖表没有记录是哪个广告主买的，所以我们需要一张另外的流水表来记录。

向门禁推送广告时，也需要查询这张表，表示用户的真实购买流水。

create table menjin_sale_log(

  id serial8 primary key, -- 日志主键

  customer_id int, -- 客户编号

  ads_id int, -- 广告ID

  menjin_id int, -- 门禁编号

  menjin_sitid smallint, -- 位置编号

  crt_time timestamp, -- 创建时间，即交易时间

  shop_window daterange, -- 购买/退货的窗口/区间  

  state int, -- 操作是否成功

  sale_or_back int -- 退货or购买

);

• 广告主信息表，用来记录广告主的信息，具体略。

create table customer(

  id int primary key, -- 广告主ID

  info text -- 信息，本例简化掉了

);

• 广告表，表示广告的内容,ID等，具体内容略。

create table ads_info(

  id int primary key,

  info text

);

1. 查询剩余广告位或已销售广告位

给定时间区间，BIT为0的有多少个。

create or replace function get_bit_count(

  offset_date date, -- 偏移量

  begin_date date, -- 开始日期

  end_date date, -- 结束日期

  bb varbit, -- 销售比特

  salebit int -- 0,1 分别表示统计未销售或已销售情况

) returns int as $$

  select bit_count(bb, salebit, begin_date-offset_date, end_date-begin_date+1);

$$ language sql strict;

2. 查询指定位置的BIT为1�的个数，用于统计或查剩余广告位

等同于：

bit_count_array (varbit, int, int[]) returns int  

  统计指定位置bit string中1|0的个数。  

  例如 bit_count('1111000011110000', 1, array[1,2,7,8]) 返回 3 

create or replace function get_bit_count_arr(

  varbit, -- 需要计算的BIT

  int, -- 0/1

  int[] -- 数组，即下标

) returns int as $$

declare

  res int :=0;

  pos int ;

begin

  foreach pos in array $3

  loop

    select case when get_bit($1, pos) = $2 then res+1 else res end into res; 

  end loop;

  return res;

end;

$$ language plpgsql strict;

 

 

postgres=# select get_bit_count_arr('111000111',1,array[1,4,0]);

 get_bit_count_arr 

-------------------

                 2

(1 row)

 

统计连续的时间区间可以使用阿里云RDS PostgreSQL提供的这个FUNCTION

bit_count (varbit, int, int, int) returns int

  从第n位开始，统计N个BIT位中有多少个0|1，如果N超出长度，则只计算已经存在的。  

  例如 bit_count('1111000011110000', 1, 5, 4) 返回 1  (0001)

3. 查询指定位置BIT为1/0的下标，可以用来表示本次真实购买到的日期

等同于：

get_bit_array (varbit, int, int[]) returns int[]

  查询指定下标的BIT位为0|1的，返回下标，超出不统计 

  例如 get_bit('111110000011', 1, array[1,5,6,7,10,11])   返回array[1,10,11]

create or replace function get_bit_pos_arr(

  varbit, -- 需要计算的BIT

  int, -- 0/1

  int[] -- 数组，即下标

) returns int[] as $$

declare

  res int[] := $3;

  pos int ;

begin

  foreach pos in array $3

  loop

    if get_bit($1, pos) <> $2 then 

      res := array_remove(res, pos);

    end if;

  end loop;

  return res;

end;

$$ language plpgsql strict;

 

 

postgres=# select get_bit_pos_arr('111000111000', 0, array[0,1,2,3,4,5]);

 get_bit_pos_arr 

-----------------

 {3,4,5}

(1 row)

根据不同的业务需求，设计一套伪代码。

1. 广告主提供区域信息，时间段信息，返回可购买的广告位信息。

select t1.menjin_id, t1.menjin_sitid, get_bit_count(t1.offset_date, 开始日期, 结束日期, 0) as 可购买天数

from 

  menjin_sale t1 join menjin t2 on (t2.id=t1.menjin_id) 

  where t2.loc <@ '电子栅栏' ; 

UI展示，可购买的总时长、可购买的位置总数、每个位置的可购买时长。

2. 选中时间，门禁SITID，购买广告。

select * from menjin_sale where id in (选定IDs) for update skip locked.  -- 返回实际锁定的IDs

 

-- 原子操作，不需要担心已设置为1的位置继续被设置

 

update menjin_sale set sale_window=(set_bit_array_rec(sale_window, 1, 0, array[100,101,102,103,....])).varbit_res, sale_window_arr=(set_bit_array_rec(sale_window, 1, 0, array[100,101,102,103,....])).arr_res   -- 以数组的方式指定需要购买的时间窗口（根据OFFSET_DATE换算成的BIT下标）  

where 

id in (.....) -- 指定的第一步for update得到的门禁广告位IDs 

returning sale_window_arr  -- 返回成功购买到的下标(即时间)

; 

 

-- set_bit_array设置位置为100,101,102,103,...的BIT=1, 超出原varbit长度的部分设置为0(即未售卖状态).  

 

 

-- 插入流水

insert menjin_sale_log ... sale_window_arr(转换为时间) ....  

 

-- 根据menjin_sale.id, menjin_sale.sale_window_arr 展示给用户实际购买到的广告位信息

3. 松散式购买，买到指定数量即可。

第2步为精确购买，如果指定的范围有些已经被卖出了，则购买不到。

用户可以给定一个区间，需要购买多少个广告位接口。 （这个需求有点像卖火车票，你没法选座，只能选车次，购买几张）

指定时间区间，指定广告位的ID范围（如某区域中的所有ID，或者用户勾选的ID），总共买多少个广告（一个广告位一天算一个）。

这样的购买手段很好调度，买到指定数量即可。

for rec in cursor select * from menjin_sale where id in (指定的ID区间) for update skip locked 

loop

  update menjin_sale set sale_window=(set_bit_array_rec(sale_window, 1, 0, array[100,101,102,103,....]), N).varbit_res, sale_window_arr=(set_bit_array_rec(sale_window, 1, 0, array[100,101,102,103,....]), N).arr_res   -- 以数组的方式指定需要购买的时间窗口（根据OFFSET_DATE换算成的BIT下标） 

  -- N指定要购买的广告位个数

  where 

  current of rec 

  returning sale_window_arr  -- 返回成功购买到的下标(即时间)

  ;  

 

  when count(array_length(sale_window_arr)) 累加 >= 指定数量 return;  

  else 

    计算下一个N多大（保证不会超出购买的总数）

 

end loop;

4. 根据时间段，区域，统计已售卖的广告位，剩余的广告位。

已售卖广告统计：

维度

维度内 总位置

维度内 已售卖位置个数

维度内 每个位置已售卖时长

 

使用RDS PostgreSQL功能

  get_bit_count(t1.offset_date, 开始日期, 结束日期, 1) 

剩余卖广告统计：

维度

维度内 总位置

维度内 每个位置的未售卖时长

 

使用RDS PostgreSQL功能

  get_bit_count(t1.offset_date, 开始日期, 结束日期, 0) 

5. 退广告位，这个需求可以砍掉吧。

-- 原子操作，不需要担心已设置为1的位置继续被设置

 

update menjin_sale set sale_window=(set_bit_array_rec(sale_window, 0, 0, array[100,101,102,103,....])).varbit_res, sale_window_arr=(set_bit_array_rec(sale_window, 0, 0, array[100,101,102,103,....])).arr_res   -- 以数组的方式指定需要退回的时间窗口（根据OFFSET_DATE换算成的BIT下标） 

where 

  id in (.....) -- 指定的门禁广告位ID  

  returning sale_window_arr  -- 返回成功退货的下标(即时间)   

;

 

-- 设置位置为100,101,102,103,...的BIT=0, 超出原varbit长度的部分设置为0(即未售卖状态).  

 

 

-- 插入流水

insert menjin_sale_log ... sale_window_arr(根据menjin_sale.offset_date和这个数组得到成功退货的日期) ...

6. 新增门禁设备

insert menjin

 

insert menjin_sale

7. 添加或修改门禁设备的维护窗口

update menjin_sale

 

insert menjin_sale_log

抢购，例如多个广告主的购买位置冲突，虽然时间不冲突，但是属于同一行记录所以会有锁冲突。解决方法skip locked，以及自治事务。

• skip locked 需求，跳过已经被锁的记录，可以加快购买速度，例如松散式购买。自治事务，已更新的记录马上提交，减少长时间持锁，其他会话可以马上持有已释放的记录的锁，大大提高同一SITE的不同时段的购买并发。
• returning * 需求，用于返回真实的购买情况，或者退货情况。然后再写入流水表。