数据透视 - 商场(如沃尔玛)选址应用

10 minute read

背景

人群透视是商业与数据结合的案例之一,比如大型商场的选址,可与分析的数据包括车流、人流量等等。

结合数据可以更深入的分析人群的组成结构,消费能力等等,给大型商场的选址带来更多的参考价值。

pic

那么如何使用数据库透视人群数据呢?

pic

场景构建

1. 人群属性表

记载了每个人的各个属性段落,比如收入、车龄、固定资产等等。如下

create table people(  
  id serial8 primary key,  -- 用户ID  
  c1 int2, -- 年龄分段, 假设分5个档, 使用0,1,2,3,4表示  
  c2 int2, -- 个人收入分段, 假设分3个档, 使用0,1,2表示  
  c3 int2, -- 车龄分段, 假设分5个档, 使用0,1,2,3,4表示  
  c4 int2, -- 家庭收入分段, 假设分3个档, 使用0,1,2表示  
  c5 int2, -- 固定资产分段, 假设分3个档, 使用0,1,2表示  
  c6 int2  -- 存款分段, 假设分3个档, 使用0,1,2表示  
);  

2. 人群动态轨迹

记录的是人群的活动位置或轨迹

使用PostgreSQL PostGIS插件,可以很方便的记录轨迹数据,并且支持GIST索引,可以快速的根据某个区域或范围搜索对应的人群。

create table people_loc(  
  id int8,  -- 用户ID  
  -- loc geometry,  -- 位置  
  crt_time timestamp  -- 时间  
);  

生成测试数据

1. 生成1000万人群的测试数据, 其中车龄为4, 年龄段为4的不插入,制造一些空洞。

insert into people (c1,c2,c3,c4,c5,c6)  
select   
mod((random()*10)::int,4),  
mod((random()*10)::int,3),  
mod((random()*10)::int,4),  
mod((random()*10)::int,3),  
mod((random()*10)::int,3),  
mod((random()*10)::int,3)  
from generate_series(1,10000000);  
  
postgres=# select * from people limit 10;  
 id | c1 | c2 | c3 | c4 | c5 | c6   
----+----+----+----+----+----+----  
  1 |  2 |  1 |  3 |  0 |  1 |  2  
  2 |  0 |  0 |  1 |  0 |  1 |  0  
  3 |  2 |  1 |  0 |  2 |  0 |  2  
  4 |  1 |  0 |  0 |  0 |  1 |  2  
  5 |  3 |  2 |  2 |  1 |  2 |  1  
  6 |  1 |  2 |  0 |  0 |  1 |  1  
  7 |  2 |  1 |  0 |  1 |  0 |  0  
  8 |  1 |  1 |  0 |  1 |  0 |  2  
  9 |  3 |  0 |  3 |  1 |  2 |  1  
 10 |  3 |  2 |  2 |  0 |  2 |  1  
(10 rows)  

2. 生成1000万人群轨迹数据

insert into people_loc (id, crt_time)  
select random()*10000000, now()+format('%L', (500000-random()*1000000))::interval  
-- 或 select random()*10000000, now()+(''||(500000-random()*1000000))::interval  
from generate_series(1,10000000);  
  
  
postgres=# select * from people_loc  limit 10;  
   id    |          crt_time            
---------+----------------------------  
 7278581 | 2017-03-05 16:35:13.828435  
 3456421 | 2017-03-07 09:08:26.853477  
  976602 | 2017-03-04 18:47:49.176176  
 1996929 | 2017-03-11 08:46:31.955573  
 6590325 | 2017-03-11 14:48:55.231263  
 7252414 | 2017-03-04 08:17:28.731733  
 8763332 | 2017-03-01 15:37:11.57363  
 9426083 | 2017-03-11 17:51:46.474757  
 4399781 | 2017-03-05 08:07:45.962599  
 9049432 | 2017-03-09 14:10:42.211882  
(10 rows)  

数据透视

1. 选择人群

以某个点为中心、或者根据某个闭环区域,圈一部分人群,(采用PostGIS)

这里不举例GIS(跟兴趣的童鞋可以使用PostGIS测试一下,性能杠杠的),我直接以时间为度量直接圈人。

select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date;  

有人可能要问,如果这个时间段,同一个人出现了多条轨迹,怎么处理呢?

这里使用了IN,PostgreSQL 的优化器很强大,JOIN时数据库会自动聚合,不必在这里GROUP BY,原理可参考如下文章。

《聊一下PostgreSQL优化器 - in里面有重复值时PostgreSQL如何处理?》

2. 数据透视

PostgreSQL的SQL兼容性非常强大,对于数据透视,可以使用grouping sets, cube, rollup等语法。

《GROUPING SETS, CUBE and ROLLUP》

select c1,c2,c3,c4,c5,c6,count(*) cnt  
from   
people  
where id in (  
  select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date  
)  
GROUP BY GROUPING SETS (c1,c2,c3,c4,c5,c6,());  
  
 c1 | c2 | c3 | c4 | c5 | c6 |   cnt     
----+----+----+----+----+----+---------  
    |  0 |    |    |    |    |  555530  
    |  1 |    |    |    |    |  555525  
    |  2 |    |    |    |    |  475596  
    |    |    |    |    |    | 1586651  
    |    |    |  0 |    |    |  554079  
    |    |    |  1 |    |    |  555864  
    |    |    |  2 |    |    |  476708  
    |    |    |    |    |  0 |  554738  
    |    |    |    |    |  1 |  554843  
    |    |    |    |    |  2 |  477070  
    |    |    |    |  0 |    |  554552  
    |    |    |    |  1 |    |  555073  
    |    |    |    |  2 |    |  477026  
  0 |    |    |    |    |    |  396349  
  1 |    |    |    |    |    |  475616  
  2 |    |    |    |    |    |  397502  
  3 |    |    |    |    |    |  317184  
    |    |  0 |    |    |    |  396947  
    |    |  1 |    |    |    |  475504  
    |    |  2 |    |    |    |  395852  
    |    |  3 |    |    |    |  318348  
(21 rows)  

更多透视用法参考cube, rollup, grouping sets用法。

目前PostgreSQL, HybridDB, Greenplum都支持以上语法。

3. 结果转换

使用WITH语法,将以上结果进行转换

with tmp as (  
select c1,c2,c3,c4,c5,c6,count(*) cnt  
from   
people  
where id in (  
  select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date  
)  
GROUP BY GROUPING SETS (c1,c2,c3,c4,c5,c6,())  
)  
select case   
when c1 is not null then 'c1_'||c1   
when c2 is not null then 'c2_'||c2   
when c3 is not null then 'c3_'||c3   
when c4 is not null then 'c4_'||c4   
when c5 is not null then 'c5_'||c5   
when c6 is not null then 'c6_'||c6   
else 'cnt' end AS col,  
t1.cnt as private,  
t2.cnt as all,  
t1.cnt::float8/t2.cnt as ratio  
from tmp t1, (select cnt from tmp where tmp.c1 is null and tmp.c2 is null and tmp.c3 is null and tmp.c4 is null and tmp.c5 is null and tmp.c6 is null) t2  
;  
  
 col  | private |   all   |         ratio            
------+---------+---------+------------------------  
 c2_0 |  555530 | 1586651 | 0.35012740672019240526  
 c2_1 |  555525 | 1586651 | 0.35012425542857250901  
 c2_2 |  475596 | 1586651 | 0.29974833785123508572  
 cnt  | 1586651 | 1586651 | 1.00000000000000000000  
 c4_0 |  554079 | 1586651 | 0.34921290189209851442  
 c4_1 |  555864 | 1586651 | 0.35033791300040147455  
 c4_2 |  476708 | 1586651 | 0.30044918510750001103  
 c6_0 |  554738 | 1586651 | 0.34962824212760083976  
 c6_1 |  554843 | 1586651 | 0.34969441925161866094  
 c6_2 |  477070 | 1586651 | 0.30067733862078049930  
 c5_0 |  554552 | 1586651 | 0.34951101407934069937  
 c5_1 |  555073 | 1586651 | 0.34983937866613388830  
 c5_2 |  477026 | 1586651 | 0.30064960725452541233  
 c1_0 |  396349 | 1586651 | 0.24980225645085151051  
 c1_1 |  475616 | 1586651 | 0.29976094301771467071  
 c1_2 |  397502 | 1586651 | 0.25052894429839958504  
 c1_3 |  317184 | 1586651 | 0.19990785623303423374  
 c3_0 |  396947 | 1586651 | 0.25017915092859110163  
 c3_1 |  475504 | 1586651 | 0.29969035408542899478  
 c3_2 |  395852 | 1586651 | 0.24948901806383382357  
 c3_3 |  318348 | 1586651 | 0.20064147692214608001  
(21 rows)  
  
Time: 8466.507 ms  

perf report

# Events: 8K cycles  
#  
# Overhead   Command       Shared Object                                               Symbol  
# ........  ........  ..................  ...................................................  
#  
     6.29%  postgres  postgres            [.] comparetup_heap  
            |  
            --- comparetup_heap  
               |            
               |--41.84%-- (nil)  
               |            
               |--33.36%-- 0x1  
               |            
               |--8.44%-- 0x23e8e  
               |            
               |--8.43%-- 0x2  
               |            
                --7.93%-- 0x3  
  
     5.16%  postgres  postgres            [.] slot_deform_tuple.lto_priv.1138  
            |  
            --- slot_deform_tuple.lto_priv.1138  
  
     3.82%  postgres  postgres            [.] mergeprereadone  
            |  
            --- mergeprereadone  
  
     3.79%  postgres  postgres            [.] qsort_ssup  
            |  
            --- qsort_ssup  
  
     3.51%  postgres  postgres            [.] tuplesort_gettuple_common.lto_priv.1348  
            |  
            --- tuplesort_gettuple_common.lto_priv.1348  
               |            
               |--32.14%-- 0x1  
               |            
               |--22.28%-- 0x2  
               |            
               |--18.95%-- (nil)  
               |            
               |--11.41%-- 0x10  
               |            
               |--5.72%-- 0x3  
               |            
               |--1.91%-- 0x3d84d9  
               |            
               |--1.91%-- 0xef259  
               |            
               |--1.91%-- get_select_query_def.lto_priv.1324  
               |            
               |--1.91%-- 0x95c9af  
               |            
                --1.88%-- 0x3a0e54  

4. left join 补缺(可选)

对于空洞值,如果你要补齐的话,使用left join即可

select * from (values ('c1_0'),('c1_1'),('c1_2'),('c1_3'),('c1_4'),('c2_0'),('c2_1'),('c2_2'),('c3_0'),('c3_1'),('c3_2'),('c3_3'),('c3_4'),('c4_0'),('c4_1'),('c4_2'),('c5_0'),('c5_1'),('c5_2'),('c6_0'),('c6_1'),('c6_2')) t (col);  
  
  
 col    
------  
 c1_0  
 c1_1  
 c1_2  
 c1_3  
 c1_4  
 c2_0  
 c2_1  
 c2_2  
 c3_0  
 c3_1  
 c3_2  
 c3_3  
 c3_4  
 c4_0  
 c4_1  
 c4_2  
 c5_0  
 c5_1  
 c5_2  
 c6_0  
 c6_1  
 c6_2  
(22 rows)  

补缺如下

with tmp as (  
select c1,c2,c3,c4,c5,c6,count(*) cnt  
from   
people  
where id in (  
  select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date  
)  
GROUP BY GROUPING SETS (c1,c2,c3,c4,c5,c6,())  
),  
tmp2 as (  
select case   
when c1 is not null then 'c1_'||c1   
when c2 is not null then 'c2_'||c2   
when c3 is not null then 'c3_'||c3   
when c4 is not null then 'c4_'||c4   
when c5 is not null then 'c5_'||c5   
when c6 is not null then 'c6_'||c6   
else 'cnt' end AS col,  
t1.cnt as private,  
t2.cnt as all,  
t1.cnt::float8/t2.cnt as ratio  
from tmp t1, (select cnt from tmp where tmp.c1 is null and tmp.c2 is null and tmp.c3 is null and tmp.c4 is null and tmp.c5 is null and tmp.c6 is null) t2  
)  
select t1.col,coalesce(t2.ratio,0) ratio from (values ('c1_0'),('c1_1'),('c1_2'),('c1_3'),('c1_4'),('c2_0'),('c2_1'),('c2_2'),('c3_0'),('c3_1'),('c3_2'),('c3_3'),('c3_4'),('c4_0'),('c4_1'),('c4_2'),('c5_0'),('c5_1'),('c5_2'),('c6_0'),('c6_1'),('c6_2'))   
t1 (col)   
left join tmp2 t2  
on (t1.col=t2.col)  
order by t1.col;   
  
  
 col  |         ratio            
------+------------------------  
 c1_0 | 0.24980225645085151051  
 c1_1 | 0.29976094301771467071  
 c1_2 | 0.25052894429839958504  
 c1_3 | 0.19990785623303423374  
 c1_4 |                      0  
 c2_0 | 0.35012740672019240526  
 c2_1 | 0.35012425542857250901  
 c2_2 | 0.29974833785123508572  
 c3_0 | 0.25017915092859110163  
 c3_1 | 0.29969035408542899478  
 c3_2 | 0.24948901806383382357  
 c3_3 | 0.20064147692214608001  
 c3_4 |                      0  
 c4_0 | 0.34921290189209851442  
 c4_1 | 0.35033791300040147455  
 c4_2 | 0.30044918510750001103  
 c5_0 | 0.34951101407934069937  
 c5_1 | 0.34983937866613388830  
 c5_2 | 0.30064960725452541233  
 c6_0 | 0.34962824212760083976  
 c6_1 | 0.34969441925161866094  
 c6_2 | 0.30067733862078049930  
(22 rows)  

5. 行列变换(可选)

如果要将以上数据,多行转换为单行,可以使用tablefunc插件,PostgreSQL玩法巨多哦。

https://www.postgresql.org/docs/9.6/static/tablefunc.html

create extension tablefunc;  
  
select * from   
crosstab($$  
with tmp as (  
select c1,c2,c3,c4,c5,c6,count(*) cnt  
from   
people  
where id in (  
  select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date  
)  
GROUP BY GROUPING SETS (c1,c2,c3,c4,c5,c6,())  
),  
tmp2 as (  
select case   
when c1 is not null then 'c1_'||c1   
when c2 is not null then 'c2_'||c2   
when c3 is not null then 'c3_'||c3   
when c4 is not null then 'c4_'||c4   
when c5 is not null then 'c5_'||c5   
when c6 is not null then 'c6_'||c6   
else 'cnt' end AS col,  
t1.cnt as private,  
t2.cnt as all,  
t1.cnt::float8/t2.cnt as ratio  
from tmp t1, (select cnt from tmp where tmp.c1 is null and tmp.c2 is null and tmp.c3 is null and tmp.c4 is null and tmp.c5 is null and tmp.c6 is null) t2  
)  
select 'row'::text , t1.col,coalesce(t2.ratio,0) ratio from (values ('c1_0'),('c1_1'),('c1_2'),('c1_3'),('c1_4'),('c2_0'),('c2_1'),('c2_2'),('c3_0'),('c3_1'),('c3_2'),('c3_3'),('c3_4'),('c4_0'),('c4_1'),('c4_2'),('c5_0'),('c5_1'),('c5_2'),('c6_0'),('c6_1'),('c6_2'))   
t1 (col)   
left join tmp2 t2  
on (t1.col=t2.col)  
order by t1.col  
$$  
)  
as  
(  
row text,  
c1_0 float8,  
c1_1 float8,  
c1_2 float8,  
c1_3 float8,  
c1_4 float8,  
c2_0 float8,  
c2_1 float8,  
c2_2 float8,  
c3_0 float8,  
c3_1 float8,  
c3_2 float8,  
c3_3 float8,  
c3_4 float8,  
c4_0 float8,  
c4_1 float8,  
c4_2 float8,  
c5_0 float8,  
c5_1 float8,  
c5_2 float8,  
c6_0 float8,  
c6_1 float8,  
c6_2 float8  
);  
  
  
 row |          c1_0          |          c1_1          |          c1_2          |          c1_3          | c1_4 |          c2_0          |          c2_1          |          c2_2          |          c3_0          |          c3_1            
|          c3_2          |          c3_3          | c3_4 |          c4_0          |          c4_1          |          c4_2          |          c5_0          |          c5_1          |          c5_2          |          c6_0          |      
      c6_1          |          c6_2            
-----+------------------------+------------------------+------------------------+------------------------+------+------------------------+------------------------+------------------------+------------------------+------------------------  
+------------------------+------------------------+------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+----  
--------------------+------------------------  
 row | 0.24980225645085151051 | 0.29976094301771467071 | 0.25052894429839958504 | 0.19990785623303423374 |    0 | 0.35012740672019240526 | 0.35012425542857250901 | 0.29974833785123508572 | 0.25017915092859110163 | 0.29969035408542899478   
| 0.24948901806383382357 | 0.20064147692214608001 |    0 | 0.34921290189209851442 | 0.35033791300040147455 | 0.30044918510750001103 | 0.34951101407934069937 | 0.34983937866613388830 | 0.30064960725452541233 | 0.34962824212760083976 | 0.3  
4969441925161866094 | 0.30067733862078049930  
(1 row)  

透视优化

1. 关于索引(BRIN, GIST, BTREE_GIST)

通常我们会限定两个维度,筛选人群,1时间范围,2地理位置范围。

由于轨迹数据通常是时间和堆的线性相关性很好的,所以,在索引方面,可以使用BRIN索引。

brin索引详见

《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》

而对于地理位置,如果要进行快速筛选的话,可以建立GIST索引

如果要建立两者的复合索引,可以使用btree_gist插件,那么时间和地理位置就能放在一个GIST索引中了。

create extension btree_gist;  

2. 递归优化

如果轨迹点很多,但是大多数为重复人群,可使用递归优化IN查询

参考

《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》

《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

《时序数据合并场景加速分析和实现 - 复合索引,窗口分组查询加速,变态递归加速》

3. case when 优化,在使用本例的cube,grouping sets,rollup前,或者其他不支持数据透视语法的数据库中,可以使用case when的方法来聚合,但是每条数据都要经过case when的计算,耗费很大的CPU。

select   
  sum(case when c1=0 then 1 else 0 end)/(count(*))::float8 as c1_0,  
  sum(case when c1=1 then 1 else 0 end)/(count(*))::float8 as c1_1,  
  sum(case when c1=2 then 1 else 0 end)/(count(*))::float8 as c1_2,  
  sum(case when c1=3 then 1 else 0 end)/(count(*))::float8 as c1_3,  
  sum(case when c1=4 then 1 else 0 end)/(count(*))::float8 as c1_4,  
  sum(case when c2=0 then 1 else 0 end)/(count(*))::float8 as c2_0,  
  sum(case when c2=1 then 1 else 0 end)/(count(*))::float8 as c2_1,  
  sum(case when c2=2 then 1 else 0 end)/(count(*))::float8 as c2_2,  
  sum(case when c3=0 then 1 else 0 end)/(count(*))::float8 as c3_0,  
  sum(case when c3=1 then 1 else 0 end)/(count(*))::float8 as c3_1,  
  sum(case when c3=2 then 1 else 0 end)/(count(*))::float8 as c3_2,  
  sum(case when c3=3 then 1 else 0 end)/(count(*))::float8 as c3_3,  
  sum(case when c3=4 then 1 else 0 end)/(count(*))::float8 as c3_4,  
  sum(case when c4=0 then 1 else 0 end)/(count(*))::float8 as c4_0,  
  sum(case when c4=1 then 1 else 0 end)/(count(*))::float8 as c4_1,  
  sum(case when c4=2 then 1 else 0 end)/(count(*))::float8 as c4_2,  
  sum(case when c5=0 then 1 else 0 end)/(count(*))::float8 as c5_0,  
  sum(case when c5=1 then 1 else 0 end)/(count(*))::float8 as c5_1,  
  sum(case when c5=2 then 1 else 0 end)/(count(*))::float8 as c5_2,  
  sum(case when c6=0 then 1 else 0 end)/(count(*))::float8 as c6_0,  
  sum(case when c6=1 then 1 else 0 end)/(count(*))::float8 as c6_1,  
  sum(case when c6=2 then 1 else 0 end)/(count(*))::float8 as c6_2  
from   
people  
where id in (  
  select id from people_loc where crt_time between '2017-03-06'::date and '2017-03-08'::date  
);  
  
          c1_0          |          c1_1          |          c1_2          |          c1_3          |            c1_4            |          c2_0          |          c2_1          |          c2_2          |          c3_0          |          
  c3_1          |          c3_2          |          c3_3          |            c3_4            |          c4_0          |          c4_1          |          c4_2          |          c5_0          |          c5_1          |          c5_2    
        |          c6_0          |          c6_1          |          c6_2            
------------------------+------------------------+------------------------+------------------------+----------------------------+------------------------+------------------------+------------------------+------------------------+--------  
----------------+------------------------+------------------------+----------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+----------------  
--------+------------------------+------------------------+------------------------  
 0.24980225645085151051 | 0.29976094301771467071 | 0.25052894429839958504 | 0.19990785623303423374 | 0.000000000000000000000000 | 0.35012740672019240526 | 0.35012425542857250901 | 0.29974833785123508572 | 0.25017915092859110163 | 0.29969  
035408542899478 | 0.24948901806383382357 | 0.20064147692214608001 | 0.000000000000000000000000 | 0.34921290189209851442 | 0.35033791300040147455 | 0.30044918510750001103 | 0.34951101407934069937 | 0.34983937866613388830 | 0.3006496072545  
2541233 | 0.34962824212760083976 | 0.34969441925161866094 | 0.30067733862078049930  
(1 row)  
  
Time: 8282.168 ms  

perf report

# Events: 8K cycles  
#  
# Overhead   Command       Shared Object                                               Symbol  
# ........  ........  ..................  ...................................................  
#  
    12.15%  postgres  postgres            [.] ExecMakeFunctionResultNoSets  
            |  
            --- ExecMakeFunctionResultNoSets  
               |            
                --100.00%-- (nil)  
  
     7.11%  postgres  postgres            [.] ExecEvalCase  
            |  
            --- ExecEvalCase  
               |            
                --100.00%-- (nil)  
  
     6.85%  postgres  postgres            [.] ExecTargetList.isra.6.lto_priv.1346  
            |  
            --- ExecTargetList.isra.6.lto_priv.1346  
  
     5.43%  postgres  postgres            [.] ExecProject.constprop.414  
            |  
            --- ExecProject.constprop.414  
  
     5.37%  postgres  postgres            [.] ExecEvalScalarVarFast  
            |  
            --- ExecEvalScalarVarFast  
  
     4.35%  postgres  postgres            [.] slot_getattr  
            |  
            --- slot_getattr  
  
     4.13%  postgres  postgres            [.] advance_aggregates  
            |  
            --- advance_aggregates  
  
     3.43%  postgres  postgres            [.] slot_deform_tuple.lto_priv.1138  
            |  
            --- slot_deform_tuple.lto_priv.1138  
  
     3.12%  postgres  postgres            [.] ExecClearTuple  
            |  
            --- ExecClearTuple  
  
     2.82%  postgres  postgres            [.] IndexNext  
            |  
            --- IndexNext  
  
     2.45%  postgres  postgres            [.] ExecEvalConst  
            |  
            --- ExecEvalConst  
               |            
                --100.00%-- (nil)  

小结

1. 语法cube, grouping sets, rollup给数据透视提供了比较好的便利。

2. 行列变换可以使用tablefunc插件。

3. case when过多时,对CPU的开销会比较大。

4. 结合PostGIS可以很方便的基于地理位置和时间维度,分析人群特性。

5. 关于精准营销的文章,请参考

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统》

Flag Counter

digoal’s 大量PostgreSQL文章入口