多维立方体
概述:
实际生产中,各种指标的报表统计,往往都会设计到多维分析,比如: 统计日活数量,日会话数量,日会话次数,日回头访问数,日新增,日用户平均访问时长,访问深度等,都可以从以下纬度来分析:
- 时间段
- 省市区等地域纬度
- 设备类型
- 操作系统
App
版本App
下载安装渠道
而数据分析师,可能会提出各种各样的"纬度组合"下的指标统计需求
- 省: 日活总数
- 省、市:日活总数
- 手机型号,日活总数
- 省,手机型号,日活总数
如果上述纬度分析需求,都逐个开发计算sql
(逐个去group by 聚合) 工作繁琐!
那么,如何解决这个问题呢
关键要点:
- 创建一个统一目标维度分析的聚合结果集,这个表应该包含所有的纬度字段
- 利用hive高阶聚合函数,在一个
sql
中,即可计算出所有可能的纬度组合
多维立方体样例
省 | 市 | 区 | 手机型号 | 操作系统 | App版本 | 下载渠道 | 小时段 | 日活总数 |
---|---|---|---|---|---|---|---|---|
江西 | \n | \n | \n | \n | \n | \n | \n | 1000 |
江苏 | \n | \n | \n | \n | \n | \n | \n | 1500 |
河南 | \n | \n | \n | \n | \n | \n | \n | 1800 |
…… | ||||||||
江西 | 九江 | \n | \n | \n | \n | \n | \n | 800 |
江西 | 赣州 | \n | \n | \n | \n | \n | \n | 600 |
江西 | 南昌 | \n | \n | \n | \n | \n | \n | 450 |
江西 | …… | \n | \n | \n | \n | \n | \n | 550 |
操作样例
假如我要从获取各个省份日活数
select
province,
dau_cnt
from cube
-- coalesce 只要有一个不为 null 就不会返回null
where privince is not null and coalesce(city,district,devicetype,osname,...) is not null
纬度基数:
假设上述表中函数很大
假设按(省,市,区,手机型号,app
版本,下载渠道,小时段) 维度组合计算日活数,结果行数有:
省维度基数 * 市维度基数 * 区维度技术 * …
基数: 某个维度字段的去重值个数 select count(distinct 字段) from table group 字段
方法一
基础写法:
Insert into table cube
SELECT
province,
city,
district,
null as device_type,
null as os_name,
null as app_version
count(distinct guid) as dau_cnt
from t src
group by province,city,districe;
这种写法,要完成上面的数据立方体的完整计算,需要2
的6
次方个sql
方法二
with cube函数
Insert into table cube
select
province,
city,
district,
device_type,
os_name,
app_version,
release_channel,
hour_segement,
count(distinct guid) as dau_cnt
from t src
group by
province,
city,
district,
device_type,
os_name,
app_version,
release_channel,
hour_segement
WITH CUBE;
加上了最后一行的with dube
函数sql
量一条解决,做到"别人加班我下班", 功能参考上个任务
方法三
grouping set函数
由用户自己决定需要哪些维度组合
INSERT INTO TABLE cube
SELECT
province,
city,
district,
device_type,
os_name,
app_version,
release_channel,
hour_segement,
count(distinct guid) as dau_cnt
FROM t_src
GROUP BY
province,
city,
district,
device_type,
os_name,
app_version,
release_channel,
house_segment
GROUPING SETS(
(),
(province),
(province,city),
(province,city,district),
(device_type));