digoal
2016-11-24
PostgreSQL , 门禁广告 , 数组 , 范围类型 , 抢购 , 排他约束 , 大盘分析 , 广告查询 , 火车票
上班打卡是一件很常见的事情，就像小时候的大宝广告，大宝天天见。
对于这种每天都要见的事物，也是广告主追逐的地盘，就好像电梯广告一样，门禁广告也悄然流行起来。
小小的门禁机，每天活跃的时间为上下班高峰，可能也就2小时左右的时间，通常广告不易过长，往往是很简短的广告，也不易放太多广告在门禁内，一般十几个已经足够了，否则2小时都不够轮播的。
今天我给大家分享一下门禁广告销售系统的需求，以及如何利用阿里云RDS for PostgreSQL特性(bitpack)来满足这样的需求。
回顾一下我之前写的 《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统》 , 性能无需多言，以一敌十。
首先分析一下门禁设备，通常一个门禁设备可以放十几条广告（前面分析了，多了也不合适）。
广告主根据他要投放的广告区域，选择可以投放广告的门禁设备。
我们可以把门禁想象成火车，一个门禁设备相当于一班火车，每天开一班，每班十几张票，票的有效期局限于购买日期当天。
有了这个假设的前提，就比较好操作了。
1. 门禁机属性设计
编号，状态，区域属性，总共有多少位置，每个位置的已销售时间区间，维修窗口（指不能销售的时间区间）。
2. 广告主查询指定区域可用的门禁，指定时间区间，进行购票。
这个需求体现在查询（找到对应的设备），更新（将购票信息更新到设备中）。
同时，这里也很容易出现抢票的情况，例如多个广告主都勾选了同样的设备，并且购买了同样的位置相交的时间区间时，就会出现冲突。
3. 统计需求，例如统计某个时间全局，已销售的数据，剩余的票数等。
根据前面提供的信息，我们结合业务，设计表。
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 100002. 售票表，每个位置一条记录，覆盖一个时间区间的售票情况，假设最小售卖单位为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_sale6. 添加或修改门禁设备的维护窗口
update menjin_sale insert menjin_sale_log1. 抢购，例如多个广告主的购买位置冲突，虽然时间不冲突，但是属于同一行记录所以会有锁冲突。
解决方法skip locked，以及自治事务。
1. skip locked 需求，跳过已经被锁的记录，可以加快购买速度，例如松散式购买。
自治事务，已更新的记录马上提交，减少长时间持锁，其他会话可以马上持有已释放的记录的锁，大大提高同一SITE的不同时段的购买并发。
2. returning * 需求，用于返回真实的购买情况，或者退货情况。然后再写入流水表。
相关资源：python入门教程(PDF版)