建设自己的取数平台:聚合分析

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 除明细数据导出外,取数场景中还会涉及到聚合分析。

文中涉及到数仓的内容,均基于阿里云Maxcompute构建的离线数仓为例。


度量与维度


  • 每天的订单量:“日期”就是维度,“订单量”就是度量;
  • 每天每个城市的订单量:“日期”、“城市”就是维度,“订单量”就是度量
  • 每个订单的价格:“订单”就是维度,“价格”就是度量
  • 每个用户的年龄:“用户”就是维度,“年龄”就是度量
  • ……


通过以上例子,会比较直观的区分出:同样都是指标,哪些是“度量”、哪些是“维度


简单地说,

维度就是描述的角度,比如时间维度、地区维度、人维度、订单维度等;

度量就是具体的数值或者比例,比如XX次数、XX人数、XX金额、XX占比等。



提问:一个指标,在一个分析场景下是“度量”,就永远都只会是“度量”么?


当然不是。


回头看刚才的例子:

  • 每个订单的价格,这里“价格”是度量。那如果我要分析“不同价格区间的订单数量”(20元以内的订单数、20~100元的订单数、100元以上的订单数),“价格”或者说“价格区间”就变成了维度;
  • 每个用户的年龄,这里的“年龄”是度量。那如果我要分析“不同年龄段的用户数量”(18岁以下的用户数、18~25岁的用户数、……),“年龄”或者说“年龄段”就变成了维度;




维度


理解了“度量”和“维度”的区别之后,我们先来看维度如何选择。


维度,相当于SQL里的 group by 子句。一套维度组合的值,可以唯一确定一条结果数据。


可穷举


在聚合分析的场景下,维度的数量必须要是可以穷举的

例如中国的城市,600多个;一年中的日期,365天;……


这就对指标的数据类型有了要求。

  • 数值型:不可作为维度
  • 时间型:可以作为维度
  • 布尔型:可以作为维度
  • 枚举型字符串:可以作为维度
  • 非枚举型字符串:不可作为维度


不可作为维度的数据类型,可以通过创建衍生指标的方式,调整成可以作为维度的数据类型。例如“年龄段”、“价格区间”、“是否包含XX关键词”等方式。


粒度、下钻与上卷


部分维度还会涉及到粒度的问题。


以时间型指标为例。一般我们数仓里会以DATETIME类型保存时间指标,最小单位为秒。虽然直接以“秒”为单位,也满足可穷举的要求。但在实际的聚合分析中,我们通常会以“天”、“月”、“年”为粒度去分析,少数情况还会有以“周”、“小时”、“季度”为粒度的分析场景。这就牵涉到了时间的粒度。需要在选择维度指标的同时,选择粒度。


枚举型字符串中,有一种很特殊的指标:“地理指标”。假设数仓里保存的是四级地址(街道乡镇),满足可穷举的要求。但在实际的聚合分析中,我们可能会以“区县”、“城市”、“省份”、“大区”、“国家”等维度去分析。这里就牵涉到了“下钻/上卷”的问题。需要在选择地理相关指标作为维度的同时,选择“下钻/上卷”的层级。




度量


度量,相当于SQL当中的 SELECT 子句。


命名


度量的含义,可能和所使用的指标相差甚远。所以每个度量都需要重新命名,以获得更好的可读性。默认使用指标名称+聚合方法+序号的方式命名。


聚合方法


大家都知道,在带有group by的SQL中,SELECT后边跟着的、没有出现在group by后边的字段,都需要被聚合函数包裹住。所以,在选择每个度量指标的同时,都需要选择该指标的聚合方法。


常见的聚合方法:

  • 计数(COUNT)。不需要参数,背后执行的就是COUNT(1);
  • 去重计算(COUNT+DISTINCT),可以选择任何数据类型的指标;
  • 求和(SUM),只能选择数值型指标;
  • 平均值(AVG,只能选择数值型指标;
  • 中位数(MEDIAN,只能选择数值型指标;
  • 百分位数(PERCENTILE_APPROX),只能选择数值型指标;
  • 最大值(MAX),可以选择任何数据类型的指标;
  • 最小值(MIN),可以选择任何数据类型的指标;
  • 随机值(ANY_VALUE),可以选择任何数据类型的指标;
  • 去重有序的字符串拼接(WM_CONCAT+DISTINCT+ORDER BY),可以选择任何数据类型的指标;


数据类型


度量的数据类型,绝大部分情况,是可以通过聚合方法和指标推算出来的。


例如“计数(COUNT)”得到的一定是数值型;再比如“最大值(MAX)”并且输入的指标是枚举型字符串,得到的一定是枚举型字符串。


展示格式


展示格式默认与度量对应的指标一致,也支持手动调整。


若度量的数据类型与指标不一致时,必须手动选择。


默认值


即,当度量最终的结果为NULL时,显示的值。主要用于数据导出或数据展示时的美观性。


默认值的数据类型,必须与度量一致。


常见的默认值有:NULL、0、空字符串、减号、-1、-999、1等等。也支持自定义。



条件


在已配置好的维度指标和度量指标中、或者在该主题域下的其他指标中,挑选部分作为整体的筛选条件。


对应到SQL中,

  • 维度指标的条件,相当于WHERE子句;
  • 该主题域下的其他指标的条件,相当于WHERE子句;
  • 度量指标,相当于HAVING子句;



排序及限制行数


在已配置好的所有维度和度量中,可以选择任意指标、以任意顺序做排序,并只展示部分数据行。默认不做排序、展示前10000行。




写在最后


经过上述步骤,我们相当于通过可视化界面,完成了一条带有group by子句的SQL的编辑。形如:

SELECT idx1 AS dim1, idx2 AS dim2,count(1)AS metric1
, sum(idx3)AS metric2
FROM table_from_dw
WHERE idx4 >0GROUPBY idx1, idx2
HAVINGcount(1)>10;


最终的数据会被导出成一张数据表格。



相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
atlas Apache
前瞻|Amundsen的数据血缘功能
目前,Amundsen并不支持表级别和列级别的数据血缘功能,也没有办法展示数据的来龙去脉。 作为Amundsen一项非常核心的功能,Lineage功能早已经提上日程,并进入设计与研发阶段。本位将展示此功能的一些基本设计。
587 0
前瞻|Amundsen的数据血缘功能
|
5月前
|
监控 数据可视化 数据挖掘
ERP系统中的数据分析与决策支持解析
【7月更文挑战第25天】 ERP系统中的数据分析与决策支持解析
360 0
|
SQL 弹性计算 分布式计算
基于星轨-数据中台工具的数据探查
使用DataWorks对MaxCompute进行数据探查,通过星轨-数据中台工具进行对MaxCompute的数据探查
|
数据采集 数据建模 BI
数据中台实战(05)-如何统一管理纷繁杂乱的数据指标?
数据中台实战(05)-如何统一管理纷繁杂乱的数据指标?
474 1
|
SQL 分布式计算 运维
开源大数据分析实验(2)——简单用户画像分析之加工数据
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
|
存储 SQL 数据采集
《全链路数据治理-智能数据建模 》——大白话数据建模(3)
《全链路数据治理-智能数据建模 》——大白话数据建模(3)
206 0
|
数据建模
《全链路数据治理-智能数据建模 》——大白话数据建模(5)
《全链路数据治理-智能数据建模 》——大白话数据建模(5)
148 0
|
存储 运维 DataWorks
《全链路数据治理-智能数据建模 》——大白话数据建模(6)
《全链路数据治理-智能数据建模 》——大白话数据建模(6)
142 0
|
DataWorks 数据建模 数据处理
《全链路数据治理-智能数据建模 》——大白话数据建模(1)
《全链路数据治理-智能数据建模 》——大白话数据建模(1)
314 0
|
数据建模
《全链路数据治理-智能数据建模 》——大白话数据建模(4)
《全链路数据治理-智能数据建模 》——大白话数据建模(4)
177 0