PostgreSQL\GPDB 多维数据透视典型案例分享

标签

PostgreSQL , 数据透视 , 实时 , 物化 , 预计算 , 多维分析 , 流计算 , 增量合并 , 调度 , HLL

---

背景

典型的电商类数据透视业务，透视的语料可能会包含一些用户的标签数据：例如包含品牌的ID，销售区域的ID，品牌对应用户的ID，以及若干用户标签字段，时间字段等。

标签可能会按不同的维度进行归类，例如tag1 性别，tag2 年龄段, tag3 兴趣爱好, ...。

业务方较多的需求可能是对自有品牌的用户进行透视，统计不同的销售区域（渠道）、时间段、标签维度下的用户数（一个非常典型的数据透视需求）。

例子

数据结构举例

每天所在区域、销售渠道的活跃用户ID

t1 (  
  uid,       -- 用户ID  
  groupid,   -- 销售渠道、区域ID  
  day        -- 日期  
)  

每个品牌的自有用户，维护增量

t2 (  
  uid,    -- 用户ID  
  pinpai  -- 品牌  
)  

用户标签，维护增量

t3 (  
  uid,    -- 用户ID  
  tag1,   -- 标签1，如兴趣  
  tag2,   -- 标签2，如性别  
  tag3,   -- 标签3，如年龄段  
  ... ,  
)  

透视举例

对某品牌、某销售区域，某标签、某日进行透视。

例如

select   
  '兴趣' as tag,   
  t3.tag1 as tag_value,   
  count(1) as cnt   
from   
  t1,   
  t2,   
  t3   
where   
  t1.uid = t3.uid   
  and t1.uid = t2.uid   
  and t2.pinpai = ?   
  and t1.groupid = ?   
  AND t1.day = '2017-06-25'   
group by t3.tag1   

这类查询的运算量较大，而且分析师可能对不同的维度进行比对分析，因此建议采用预计算的方法进行优化。

预计算优化

预计算需要得到的结果如下：

t_result (  
  day,      -- 日期  
  pinpai,   -- 品牌ID  
  groupid,  -- 渠道、地区、门店ID  
  tag1,     -- 标签类型1  
  tag2,     -- 标签类型2  
  tag3,     -- 标签类型3  
  ...       -- 标签类型n  
  cnt,      -- 用户数  
  uids,     -- 用户ID数组，这个为可选字段，如果不需要知道ID明细，则不需要保存  
  hll_uids  -- 用户HLL估值  
)  

对于GPDB，可以按day范围一级分区，按pinpai, groupid哈希分析，随机分布，同时建立tag?字段索引。 从而实现快速的检索。

得到这份结果后，分析师的查询简化如下（前三个条件通过分区过滤数据，最后根据tag?的索引快速得到结果）：

select   
  day, pinpai, groupid, 'tag?' as tag, cnt, uids, hll_uids   
from t_result  
where  
  day =   
  and pinpai =   
  and groupid =   
  and tag? = ?    

预计算后，甚至能以非常少量的运算量，实现更加复杂的维度分析，例如分析某两天的差异用户，分析多个TAG叠加的用户等

预计算的方法

产生统计结果的SQL如下

select   
  t1.day,  
  t2.pinpai,  
  t1.groupid,  
  t3.tag1,   
  t3.tag2,  
  t3.tag3,  
  ...  
  count(1) as cnt ,  
  array_agg(uid) as uids,  
  hll_add_agg(hll_hash_integer(uid)) as hll_uids  
from   
  t1,   
  t2,   
  t3   
where   
  t1.uid = t3.uid   
  and t1.uid = t2.uid   
group by   
  t1.day,  
  t2.pinpai,  
  t1.groupid,  
  grouping sets (  
    (t3.tag1),   
    (t3.tag2),  
    (t3.tag3),  
    (...),  
    (t3.tagn)  
  )  

解释：

1、将uid聚合为数组

array_agg(uid)  

2、将UID转换为hll hash val，并聚合为HLL类型

hll_add_agg(hll_hash_integer(uid))  

3、为了按每个标签维度进行统计，可以使用多维分析语法grouping sets，不必写多条SQL来实现，数据也只会扫一遍，将按每个标签维度进行统计

  grouping sets (  
    (t3.tag1),   
    (t3.tag2),  
    (t3.tag3),  
    (...),  
    (t3.tagn)  
  )  

多维分析的语法详见

《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》

《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》

预计算结果透视查询

如果进行复杂透视，可以将分析结果的不同记录进行数组的逻辑运算，得到最终UID集合结果。

一、数组逻辑运算

1、在数组1但不在数组2的值

create or replace function arr_miner(anyarray, anyarray) returns anyarray as $$  
  select array(select * from (select unnest($1) except select unnest($2)) t group by 1);  
$$ language sql strict;  

2、数组1和数组2的交集

create or replace function arr_overlap(anyarray, anyarray) returns anyarray as $$  
  select array(select * from (select unnest($1) intersect select unnest($2)) t group by 1);  
$$ language sql strict;  

3、数组1和数组2的并集

create or replace function arr_merge(anyarray, anyarray) returns anyarray as $$    
  select array(select unnest(array_cat($1,$2)) group by 1);  
$$ language sql strict;  

例如在促销活动前（2017-06-24）的用户集合为UID1[]，促销活动后（2017-06-25）的用户集合为UID2[]，想知道促销活动得到了哪些新增用户。

arr_miner(uid2[], uid1[]) 即可得到。

二、我们使用了HLL类型，HLL本身支持数据的逻辑计算

1、计算唯一值个数

hll_cardinality(users)  

2、计算两个HLL的并集，得到一个HLL

hll_union()  

例如在促销活动前（2017-06-24）的用户集合HLL为uid1_hll，促销活动后（2017-06-25）的用户集合HLL为uid2_hll，想知道促销活动得到了多少新增用户。

hll_cardinality(uid2_hll) - hll_cardinality(uid1_hll)  

预计算调度

业务以前通过即时JOIN得到透视结果，而现在我们使用事先统计的方法得到透视结果，事先统计本身是需要调度的。

调度方法取决于数据的来源，以及数据合并的方法，流式增量或批量增量。

一、数据按天统计，历史统计数据无更新，只有增量。

定时将统计结果写入、合并至t_result结果表。

insert into t_result   
select   
  t1.day,  
  t2.pinpai,  
  t1.groupid,  
  t3.tag1,   
  t3.tag2,  
  t3.tag3,  
  ...  
  count(1) as cnt ,  
  array_agg(uid) as uids ,  
  hll_add_agg(hll_hash_integer(uid)) as hll_uids  
from   
  t1,   
  t2,   
  t3   
where   
  t1.uid = t3.uid   
  and t1.uid = t2.uid   
group by   
  t1.day,  
  t2.pinpai,  
  t1.groupid,  
  grouping sets (  
    (t3.tag1),   
    (t3.tag2),  
    (t3.tag3),  
    (...),  
    (t3.tagn)  
  )  

二、合并统计维度数据

每天的统计结果只有按天统计的结果，如果要查询按月，或者按年的统计，需要对天的数据查询并汇聚。

当然，业务也能选择异步汇聚，最终用户查询汇聚后的结果。

t_result_month (  
  month,    -- yyyy-mm  
  pinpai,   -- 品牌ID  
  groupid,  -- 渠道、地区、门店ID  
  tag1,     -- 标签类型1  
  tag2,     -- 标签类型2  
  tag3,     -- 标签类型3  
  ...       -- 标签类型n  
  cnt,      -- 用户数  
  uids,    -- 用户ID数组，这个为可选字段，如果不需要知道ID明细，则不需要保存  
  hll_uids   -- 用户HLL估值  
)  

array聚合需要自定义一个聚合函数

postgres=# create aggregate arragg (anyarray) ( sfunc=arr_merge, stype=anyarray);  
CREATE AGGREGATE  
postgres=# select arragg(c1) from (values (array[1,2,3]),(array[2,5,6])) t (c1);  
   arragg      
-------------  
 {6,3,2,1,5}  
(1 row)  

按月汇聚SQL如下

select   
  to_char(day, 'yyyy-mm'),  
  pinpai,  
  groupid,  
  tag1,  
  tag2,  
  tag3,  
  ...  
  array_length(arragg(uid),1) as cnt,  
  arragg(uid) as uids,  
  hll_union_agg() as hll_uids  
from t_result  
group by   
  to_char(day, 'yyyy-mm'),  
  pinpai,  
  groupid,  
  tag1,  
  tag2,  
  tag3,  
  ...  

按年汇聚以此类推。

三、流式调度

如果业务方有实时统计的需求，那么可以使用流式计算的方法，实时进行以上聚合统计。方法详见

《流计算风云再起 - PostgreSQL携PipelineDB力挺IoT》

《基于PostgreSQL的流式PipelineDB, 1000万/s实时统计不是梦》

《"物联网"流式处理应用 - 用PostgreSQL实时处理(万亿每天)》

如果数据量非常庞大，可以根据分区键，对数据进行分流，不同的数据落到不同的流计算节点，最后汇总流计算的结果到HybridDB(base on GPDB)中。

《ApsaraDB的左右互搏(PgSQL+HybridDB+OSS) - 解决OLTP+OLAP混合需求》

参考

https://github.com/aggregateknowledge/postgresql-hll