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

数据透视-商场(如沃尔玛)选址应用本文章来自于阿里云云栖社区摘要：标签PostgreSQL,数据透视,cube,groupingsets,rollup背景人群透视是商业与数据结合的案例之一，比如大型商场的选址，可与分析的数据包括车流、人流量等等。标签PostgreSQL,数据透视,cube,groupingsets,rollup背景人群透视是商业与数据结合的案例之一，比如大型商场的选址，可与分析的数据包括车流、人流量等等。结合数据可以更深入的分析人群的组成结构，消费能力等等，给大型商场的选址带来更多的参考价值。那么如何使用数据库透视人群数据呢？场景构建1.人群属性表记载了每个人的各个属性段落，比如收入、车龄、固定资产等等。如下createtablepeople(idserial8primarykey,--用户IDc1int2,--年龄分段,假设分5个档,使用0,1,2,3,4表示c2int2,--个人收入分段,假设分3个档,使用0,1,2表示c3int2,--车龄分段,假设分5个档,使用0,1,2,3,4表示c4int2,--家庭收入分段,假设分3个档,使用0,1,2表示c5int2,--固定资产分段,假设分3个档,使用0,1,2表示c6int2--存款分段,假设分3个档,使用0,1,2表示);2.人群动态轨迹记录的是人群的活动位置或轨迹使用PostgreSQLPostGIS插件，可以很方便的记录轨迹数据，并且支持GIST索引，可以快速的根据某个区域或范围搜索对应的人群。createtablepeople_loc(idint8,--用户ID--locgeometry,--位置crt_timetimestamp--时间);生成测试数据1.生成1000万人群的测试数据,其中车龄为4,年龄段为4的不插入，制造一些空洞。insertintopeople(c1,c2,c3,c4,c5,c6)selectmod((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)fromgenerate_series(1,10000000);postgres=#select*frompeoplelimit10;id|c1|c2|c3|c4|c5|c6----+----+----+----+----+----+----1|2|1|3|0|1|22|0|0|1|0|1|03|2|1|0|2|0|24|1|0|0|0|1|25|3|2|2|1|2|16|1|2|0|0|1|17|2|1|0|1|0|08|1|1|0|1|0|29|3|0|3|1|2|110|3|2|2|0|2|1(10rows)2.生成1000万人群轨迹数据insertintopeople_loc(id,crt_time)selectrandom()*10000000,now()+format('%L',(500000-random()*1000000))::intervalfromgenerate_series(1,10000000);postgres=#select*frompeople_loclimit10;id|crt_time---------+----------------------------7278581|2017-03-0516:35:13.8284353456421|2017-03-0709:08:26.853477976602|2017-03-0418:47:49.1761761996929|2017-03-1108:46:31.9555736590325|2017-03-1114:48:55.2312637252414|2017-03-0408:17:28.7317338763332|2017-03-0115:37:11.573639426083|2017-03-1117:51:46.4747574399781|2017-03-0508:07:45.9625999049432|2017-03-0914:10:42.211882(10rows)数据透视1.选择人群以某个点为中心、或者根据某个闭环区域，圈一部分人群，（采用PostGIS）这里不举例GIS(跟兴趣的童鞋可以使用PostGIS测试一下，性能杠杠的)，我直接以时间为度量直接圈人。selectidfrompeople_locwherecrt_timebetween'2017-03-06'::dateand'2017-03-08'::date;有人可能要问，如果这个时间段，同一个人出现了多条轨迹，怎么处理呢？这里使用了IN，PostgreSQL的优化器很强大，JOIN时数据库会自动聚合，不必在这里GROUPBY，原理可参考如下文章。《聊一下PostgreSQL优化器-in里面有重复值时PostgreSQL如何处理?》（原文链接：=5176.100239.blogcont71875.16.5QXS0X&file=20161223_01.md）2.数据透视PostgreSQL的SQL兼容性非常强大，对于数据透视，可以使用groupingsets,cube,rollup等语法。《GROUPINGSETS,CUBEandROLLUP》（原文链接：=5176.100239.blogcont71875.17.p1ibfO&file=20150526_02.md）selectc1,c2,c3,c4,c5,c6,count(*)cntfrompeoplewhereidin(selectidfrompeople_locwherecrt_timebetween'2017-03-06'::dateand'2017-03-08'::date)GROUPBYGROUPINGSETS(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||4770260||||||3963491||||||4756162||||||3975023||||||317184||0||||396947||1||||475504||2||||395852||3||||318348(21rows)更多透视用法参考cube,rollup,groupingsets用法。目前PostgreSQL,HybridDB,Greenplum都支持以上语法。3.结果转换使用WITH语法，将以上结果进行转换withtmpas(selectc1,c2,c3,c4,c5,c6,count(*)cntfrompeoplewhereidin(selectidfrompeople_locwherecrt_timebetween'2017-03-06'::dateand'2017-03-08'::date)GROUPBYGROUPINGSETS(c1,c2,c3,c4,c5,c6,()))selectcasewhenc1isnotnullthen'c1_'||c1whenc2isnotnullthen'c2_'||c2whenc3isnotnullthen'c3_'||c3whenc4isnotnullthen'c4_'||c4whenc5isnotnullthen'c5_'||c5whenc6isnotnullthen'c6_'||c6else'cnt'endAScol,t1.cntasprivate,t2.cntasall,t1.cnt::numeric/t2.cntasratiofromtmpt1,(selectcntfromtmpwheretmp.c1isnullandtmp.c2isnullandtmp.c3isnullandtmp.c4isnullandtmp.c5isnullandtmp.c6isnull)t2;col|private|all|ratio------+---------+---------+------------------------c2_0|555530|1586651|0.35012740672019240526c2_1|555525|1586651|0.35012425542857250901c2_2|475596|1586651|0.29974833785123508572cnt|1586651|1586651|1.00000000000000000000c4_0|554079|1586651|0.34921290189209851442c4_1|555864|1586651|0.35033791300040147455c4_2|476708|1586651|0.30044918510750001103c6_0|554738|1586651|0.34962824212760083976c6_1|554843|1586651|0.34969441925161866094c6_2|477070|1586651|0.30067733862078049930c5_0|554552|1586651|0.34951101407934069937c5_1|555073|1586651|0.34983937866613388830c5_2|477026|1586651|0.30064960725452541233c1_0|396349|1586651|0.24980225645085151051c1_1|475616|1586651|0.29976094301771467071c1_2|397502|1586651|0.25052894429839958504c1_3|317184|1586651|0.19990785623303423374c3_0|396947|1586651|0.25017915092859110163c3_1|475504|1586651|0.29969035408542899478c3_2|395852|1586651|0.24948901806383382357c3_3|318348|1586651|0.20064147692214608001(21rows)Time:8466.507msperfreport#Events:8Kcycles##OverheadCommandSharedObjectSymbol#.....................................................................................#6.29%postgrespostgres[.]comparetup_heap|---comparetup_heap||--41.84%--(nil)||--33.36%--0x1||--8.44%--0x23e8e||--8.43%--0x2|--7.93%--0x35.16%postgrespostgres[.]slot_deform_tuple.lto_priv.1138|---slot_deform_tu