门禁广告销售系统需求剖析 与 PostgreSQL数据库实现

门禁广告销售系统需求剖析 与 PostgreSQL数据库实现

作者

digoal

日期

2016-11-24

标签

PostgreSQL , 门禁广告 , 数组 , 范围类型 , 抢购 , 排他约束 , 大盘分析 , 广告查询 , 火车票

背景

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

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

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

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

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

门禁广告销售系统需求分析

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

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

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

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

1. 门禁机属性设计

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

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

这个需求体现在查询（找到对应的设备），更新（将购票信息更新到设备中）。

同时，这里也很容易出现抢票的情况，例如多个广告主都勾选了同样的设备，并且购买了同样的位置相交的时间区间时，就会出现冲突。

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

阿里云RDS PostgreSQL bitpack介绍

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]  

数据库表设计

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

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

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

2. 售票表，每个位置一条记录，覆盖一个时间区间的售票情况，假设最小售卖单位为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分区。

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

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

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购买
);

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

create table customer(
  id int primary key, -- 广告主ID
  info text -- 信息，本例简化掉了
);

5. 广告表，表示广告的内容,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\0的个数，用于统计或查剩余广告位

等同于

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和这个数组得到成功退货的日期) ...

5. 新增门禁设备

insert menjin

insert menjin_sale

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

update menjin_sale

insert menjin_sale_log

问题分析

1. 抢购，例如多个广告主的购买位置冲突，虽然时间不冲突，但是属于同一行记录所以会有锁冲突。

解决方法skip locked，以及自治事务。

其他

1. skip locked 需求，跳过已经被锁的记录，可以加快购买速度，例如松散式购买。

自治事务，已更新的记录马上提交，减少长时间持锁，其他会话可以马上持有已释放的记录的锁，大大提高同一SITE的不同时段的购买并发。

2. returning * 需求，用于返回真实的购买情况，或者退货情况。然后再写入流水表。