门禁广告销售系统需求剖析 与 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 * 需求,用于返回真实的购买情况,或者退货情况。然后再写入流水表。