大模型与数据分析:探索Text-to-SQL(下)

简介: 大模型与数据分析:探索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 过程,例如表格中的分组,报表中的下钻过程,实际上指标命名带上维度没有意义,可以在应用的过程中,告知用户支持什么维度。


  • 一致性与生态


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


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


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

image.png

总结


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


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


image.png

团队介绍


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

目录
相关文章
|
3天前
|
SQL 数据挖掘 HIVE
【Hive SQL 每日一题】在线课程学生行为数据分析
该数据分析师任务是分析在线学习平台的学生行为,以优化课程内容和学习体验。提供的数据包括`students`表(含学生ID、姓名、年龄和性别)和`course_activity`表(含活动ID、学生ID、课程ID、活动日期和学习时长)。分析涉及:1) 学生参加的课程数量,2) 课程总学习时长,3) 按性别分组的平均学习时长,4) 学生首次参加的课程及日期,5) 学生最近一次学习的时长,以及6) 参与学生最多的课程。所有查询都使用了SQL,部分涉及窗口函数和分组统计。数据集可在给定链接下载。
|
14天前
|
机器学习/深度学习 人工智能 数据可视化
【视频】CNN(卷积神经网络)模型以及R语言实现回归数据分析
【视频】CNN(卷积神经网络)模型以及R语言实现回归数据分析
|
14天前
|
算法 数据挖掘 数据建模
用COPULA模型进行蒙特卡洛(MONTE CARLO)模拟和拟合股票收益数据分析
用COPULA模型进行蒙特卡洛(MONTE CARLO)模拟和拟合股票收益数据分析
|
14天前
|
机器学习/深度学习 数据可视化 数据挖掘
R语言实现CNN(卷积神经网络)模型进行回归数据分析
R语言实现CNN(卷积神经网络)模型进行回归数据分析
|
14天前
|
机器学习/深度学习 算法 数据挖掘
R语言泰坦尼克号随机森林模型案例数据分析
R语言泰坦尼克号随机森林模型案例数据分析
|
14天前
|
存储 数据挖掘
R语言Kaggle泰坦尼克号性别阶级模型数据分析案例
R语言Kaggle泰坦尼克号性别阶级模型数据分析案例
|
14天前
|
SQL 自然语言处理 数据挖掘
大模型与数据分析:探索Text-to-SQL(上)
大模型与数据分析:探索Text-to-SQL(上)
256 0
|
14天前
|
SQL 自然语言处理 数据挖掘
大模型与数据分析:探索Text-to-SQL(中)
大模型与数据分析:探索Text-to-SQL(中)
148 0
|
14天前
|
自然语言处理 数据可视化 数据挖掘
首批!瓴羊Quick BI完成中国信通院大模型驱动的智能数据分析工具专项测试
首批!瓴羊Quick BI完成中国信通院大模型驱动的智能数据分析工具专项测试
50 1
|
14天前
|
数据采集 自然语言处理 数据可视化
数据分析案例-基于snownlp模型的MatePad11产品用户评论情感分析(上)
数据分析案例-基于snownlp模型的MatePad11产品用户评论情感分析
104 0