| 门禁广告销售系统需求剖析 与 PostgreSQL数据库实现 原作者：digoal/德哥 创作时间：2016-11-24 22:06:52+08 |
doudou586 发布于2016-11-25 22:06:52
 评论: 0
 浏览: 4344
 顶: 593
 踩: 639
|
门禁广告销售系统需求剖析 与 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 * 需求，用于返回真实的购买情况，或者退货情况。然后再写入流水表。
请在登录后发表评论，否则无法保存。
