大模型与数据分析：探索Text-to-SQL（下）

大模型与数据分析：探索Text-to-SQL（中）：https://developer.aliyun.com/article/1480725

• 复合指标
  

派生指标的另一个类型是复合指标，如果把它单独独立出来也可以，如果把它归类为原子指标也可以，取决于我们如何做数据的开发以及应用。先来看几个复合指标的例子：

1. 平均销售价格：派生指标是通过销售额和销售量计算得出的，它反映了每个产品的平均售价。原子指标是销售额和销售量。
2. 转化率：派生指标是通过访问量和转化量计算得出的，它反映了每个渠道的转化效果。原子指标是访问量和转化量。
3. 客户生命周期价值：派生指标是通过客户平均购买金额、购买频率和客户保留率计算得出的，它反映了每个客户对企业的贡献价值。原子指标是客户购买金额、购买频率和客户保留率。

上面三个例子都是在原子指标间进行计算的原子级复合指标。也可以通过两个派生指标来计算复合指标，例如派生指标是：最近7天浙江 iPhone  的平均销售价格 = 近7天浙江 iPhone 的销售额 / 近7天浙江 iPhone 的销售量。

▐  指标要素

上面介绍了很多的概念，其实核心思想是统一对指标的认知和理解，每一个概念单独去理解可能无法有一个整体的感受。可以看下图，来完成对指标的整体理解：

我们把【原子指标】【时间周期】【业务范围】【维度】【条件限定】统称为指标要素，他们是指标的实体组织。

1. 原子指标：就是度量，它确定了统计目标和聚合方法
   
2. 时间周期：是一种特殊的维度，它确定了统计的时间范围，从什么时间开始，从什么时间结束
   
3. 业务范围：是一种特殊的维度，它确定了统计目标的范围
   
4. 【时间周期】和【业务范围】单独拿出来，是为了更好的表达指标的意义
   
5. 条件限定：是对统计数据进行自由剪裁的过程
   
6. 维度：是用于观察统计目标的视角，可以有”无限个“维度
   

▐  指标要素的SQL表达方式

基于指标要素，我们可以把它和 SQL 关联起来理解。便于了解数据的加工和实现过程,有益于从技术的视角理解指标要素。

• 先了解SQL的大结构
  

SQL 的核心作用就是从数据表中提取数据。操作对象是表，所以可以理解为：去哪张表里，以什么样的条件，取哪些数据，要以什么样的方法进行数据计算SQL 的基本操作逻辑：

SELTECT --选取哪些字段：在这里提供字段的各种计算方式，例如SUM，MIX，MIN，IF, ELSE等，对这一列数据进行操作
FROM --从哪张表取：在这里提供单表、多表关联（JOIN,不同表提取多列合并成一张表)、多表合并 UNION（不同表，但表结构相同，上下对齐成一张表）
WHERE --以什么样的条件：在这里和SELECT一样提供字段的各种计算方式，来限制取值范围
GROUP BY,ORDER BY --组合与排序。

• 原子指标对应select
  

原子指标是度量，它确定了统计目标和聚合方法，在 SQL 中，它作用于 SELECT 范围内。可以这么理解，SELECT 范围内的内容就是【原子指标】。例如：

select count(order_ID)—>计算订单数
select sum(order_amount)->计算订单金额

• 业务范围对应from
  

数据来源于哪张表，一定是确定了业务范围，在数仓中，一般会对表进行分类，分类的规则会基于业务来进行，便于管理。例如：

select count(order_id) 
from dwd.order_list   --在订单明细表中计算订单数

• 条件限定对应where
  

条件限定，一般体现在 where 条件语句中。表达以什么样的条件来看指标。例如：

-- 在订单明细表中计算订单金额大于100的订单数
select count(order_id)
from dwd.order_list
where order_amount > 100

【时间周期】当作限定条件出现在where条件中

-- 在订单明细表中计算2023年5月20日订单金额大于100的订单数
select count(order_id)
from dwd.order_list
where order_amount > 100 AND order_date=‘2023-05-20'

【维度值】当作条件出现在where条件中

-- 在订单明细表中计算2023年5月20日订单金额大于100且在杭州发生的订单数
select count(order_id)
from dwd.order_list
where order_amount > 100 AND order_date='2023-05-20' AND city_name='北京'

• 维度对应group by
  

维度会参与 select 过程和 group by 过程。group by 的目的是分组，分组就是为了以不同的视角去看数据。

-- 在订单明细表中计算2023年5月20日订单金额大于100的订单数, 按城市分组
select count(order_id)
      ,city_name
from dwd.order_list
where order_amount>100 AND order_date='2023-05-20' 
group by city_name

• 一张图看指标要素与SQL结构的对应关系
  

知晓指标要素与 SQL 语句的对应关系，能够对指标的实现过程有更深层次的理解。这里最重要的意义在于用户对指标的定义能够映射到技术方案上。能够基于这层关系，对数据进行合理的建模、开发与使用。

▐  指标要素管理

上面把指标抽象成指标要素便于我们统一对指标的理解，其实更重要的目的是便于使用与管理。管理上的意义在于能够做到指标开发使用从无边界到有边界的过程，逐步收敛覆盖，另一层面能够做好统一的标准，最后由此做基础，向上放射到不同的系统、环境中去，形成整体的生态。

• 覆盖与收敛
  

根据派生指标的概念，通过【原子指标】+【维度】+【时间周期】+【条件限定】组成了一个派生指标，当每一个指标元素出现大于1的情况时，就会出现多个派生指标，计算方法是它们的乘积。
例如上面的情况，3个【原子指标】* 4个【维度值】* 3个【时间周期】* 2个【条件限定】= 72个派生指标。
指标在使用的过程中，不论是口头交谈还是系统展示，都会以上图右边的形式来体现，【视频业务日销售额】谁都可以读懂。没有哪个用户去把指标拆解成这些要素来沟通，除非出现数据问题。所以我们在报表、汇报、业务沟通的过程中，都是如【视频业务日销售额】的指标形式体现出来的。
这样对于管理有一个非常大的好处，可以基于指标要素的组合进行最大可能的使用覆盖。
根据业务的实际诉求，完成分析体系的建设：确定分析框架，确定分析类别，确定分析场景等等，例如用户行为分析、业绩分析、经营分析、安全性分析、竞对分析、财务分析..等多个场景。基于这些分析框架，可以逐步的抽象出指标要素，确定有多少个【原子指标】+【维度】，然后就可以大致的得出，能够覆盖”多少个“指标了。
这样做的好处在于，业务用到的绝大多数指标，都是可以覆盖在指标要素组成的这些结果之内的，指标管理者、开发者只需要关注指标要素的增减即可，不用根据具体的需求 CASE BY CASE 的去完成任务，大大减少了管理和开发成本，从而实现了”收敛“ 。

• 及时性提升
  

如果已经确定好指标要素【原子指标】+【维度】+【时间周期】+【条件限定】，这些指标就可以提前进行计算：

把指标要素组合的指标，提前进行预算，因为是结果集，即便是组合再多，也能控制在百万、千万级别，或者是分块、分组来存储，这样就有数据量级小的特性，我们可以把结果存入到响应速度更快的内存数据库中，完成”空间换时间“，解决大多数人无法等待超长时间的计算过程。即便数据科技技术发展到今天，如 SPARK、clickhouse 等大规模秒级响应的查询技术已经很成熟了，这种空间换时间的方式依然非常受用。从成本的角度来讲，非常划算。

• 命名的统一性
  

如果使用指标要素的管理理念来生产、管理指标，在用户使用指标的时候，可以做到指标名称的统一性。

回顾来看，所有应用的指标都可以认为是派生指标，派生指标的指标元素中，有哪些可以参与命名，哪些不用参与命名：

指标的命名规范性，直接影响使用者对指标的理解，并能够影响到整个指标使用的效果，如果命名不规范，会导致大家认知出现偏差，经常会出现不同名称同一指标，甚至还有同一指标不同名称的情况，增加大家的沟通对齐成本。

指标命名的基本原则：简短易懂，便于传播，不易出现理解偏差。

1. 时间周期：必须参与命名，累计、昨日、月度、周；时间周期最直接的圈定了统计的范围，需要明确的展示在指标名称上，简单直接，避免不同人的理解歧义，减少错误发生的几率。
   
2. 原子指标：必须参与命名，指标的核心。
   
3. 业务范围：可参与命名，如果在系统、使用场景流程做的比较的情况下，可不用参与命名。例如，进入到”视频业务“的专属分析系统中，系统对业务有明确的划分板块，例如进入”电影“板块，指标名称就无需带上【电影】这个业务范围了，比如昨日电影播放量就可以直接写成播放量即可。
   
4. 条件限定：不参与命名。条件限定有量个非常重要的特性，就是很容易变长，二是它出现在指标建立之后的灵活应用上，是临时性效果。例如【昨日播放量】这个条件是：大于 18 岁，中国地区，IOS 端，会员，近 30 天未登录的，如果参与命名的话就是：【会员 IOS 端中国区大于 18 岁其近 30 天未登录的昨日播放量】这样读起来就非常别扭。而且组合条件还需要考虑语言的通顺性，例如这样组合【大于 18 岁中国区 IOS 端近 30 天未登录会员昨日播放量】读起来就会拗口。另外，很多条件限定都是临时性提出的，例如年龄大于 18 岁，但是有可能随时调整到 16 岁，如果按照人的年龄分布来讲，我们可以从 1 岁到 100 岁这 100 个数字都当做限定条件，这样指标就会无限增多膨胀，增大开发、管理、使用成本。
   
5. 维度：不参与指标命名，维度与条件限定相同，它具有无限扩展的情况，并且无法从语言上让指标变得易于理解。例如【昨日播放量】支持维度：销售渠道、城市、端、业务类型，加入维度后的命名是【昨日播放量销售渠道城市端业务类型】这样指标就变的不可读。实际情况是维度在分析过程中参与 GROUP BY 过程，例如表格中的分组，报表中的下钻过程，实际上指标命名带上维度没有意义，可以在应用的过程中，告知用户支持什么维度。
   

• 一致性与生态
  

运用指标要素的指标管理模型，本质上是抽象+收敛的过程，确定少量的指标要素，覆盖大多数的使用指标，减少开发、运维、管理和认知成本。一致性问题同样可以在这个模型中被解决。

业务基于这个模型思路，去构建指标模型，并用系统加以管理，当做整个生态的底层基础。

建立在这个模型之上，可以对接更上层的应用系统，例如报表工具，业务分析系统，用户管理系统，经营分析系统等设计到指标应用的场景中，从而让整个业务、数据分析系统生态中都利用起这个模型的思想。

总结

上面分享方案是理想的，真正能不能应用起来，是另一回事。现实是，一个小小的指标，可能经历多个团队，多年，多次治理，都达不到好的效果；对用户来讲，指标体系建设以及使用需要一定的学习、理解成本。

数据指标是一个需要认准解决方案（流程、标准、组织）长期持续做下去的事情，如果出现中断或者反复，沉淀的经验不能继承，则很难达到指标准确、及时好用的状态。学习成本以及运营同样是一个非常重要的因素。再简单的指标，也需要读懂口径、也需要明确指标在哪里看到的最准，数据出现了问题要找谁，需要一个完善指标体系建设。

团队介绍

淘天业务技术用户运营平台技术团队是一支懂用户，技术驱动的年轻队伍，以用户为中心，通过技术创新提升用户全生命周期体验，持续为用户创造价值。
团队立足体系化打造业界领先的用户增长基础设施，以媒体外投平台、ABTest平台、用户运营平台为代表的基础设施赋能阿里集团用户增长，日均处理数据量千亿规模、调用QPS千万级。