# SQL 查询

# 一、SQL 查询的简介

对于难以使用分析模型实现的高级分析,您可以使用 SQL 查询来进行实现。TA 系统使用 Presto 查询引擎,可以使用标准 SQL 对系统中的所有数据进行快速查询。

除此之外,我们提供了动态参数的功能,帮助使用者在不用修改 SQL 语句的情况下,通过调整参数值的方法,对语句中的部分参数内容进行变更以适应新的查询条件,实现一次编写、多次使用。

SQL 查询结果可以被保存成报表,支持放入看板中进行数据查看,同时支持在看板中借助探索模块,对 SQL 报表的动态参数进行实时修改,以此实现类似其他模型中调整条件的能力。

# 二、SQL 查询的位置和使用角色

可以从“行为分析”模块下的“SQL 查询”进入

公司超管 管理员 分析师 普通成员
使用“SQL 查询”

需要注意,系统自带的分析师角色不具备“使用 SQL 查询”的权限,如需使用,可在新创建的分析师及以上权限的角色中添加 SQL 使用权限,并提供给使用者

# 三、SQL 查询的页面概览

SQL 查询的页面,主要由上半部分的语句编写框以及下半部分的标签页所组成,标签页又分四个 TAB 页面,分别为“表结构”、“查询历史”、“查询结果”与“语句书签”标签页

# 3.1 语句编写框

语句编写框的核心是语句的输入框,在输入框中编写 SQL 查询语句,编写时需要注意以下几点:

  • 字段名请使用双引号 " " 括起,也可以缺省,但如果查询字段名带有特殊符号(如$#),则必须使用双引号
  • 字符串请必须使用单引号 ' '括起
  • 只允许使用SELECT语句(即只支持查询)
  • 查询事件表时,请尽量使用分区键"$part_date""$part_event"进行数据时间以及查询事件的筛选,以提高查询的效率
  • 列表类型的属性,在底层存储时以字符串形式存储,使用\t分割元素,如需将其转换为列表类型,建议使用复制属性名功能(列表属性的复制内容中包含转化为列表的表达式),或者使用函数split("属性名",chr(0009))进行拆解

光标位于输入框时,可以使用以下快捷键:

  • Ctrl + Enter:执行语句
  • Ctrl + Shift + F:格式化语句
  • Ctrl + Z:撤销
  • Ctrl + Y:恢复

语句编写框的工具栏位于输入框下方,您可以在工具栏左侧点击“添加动态参数”按钮,在语句末尾插入一个动态参数,工具栏右侧的 ICON,从左到右分别为:

  1. 帮助:查看 SQL 查询的快捷键以及相关帮助文档
  2. 格式化:将查询语句进行格式化
  3. 复制语句:将输入框内的查询语句复制到剪切板
  4. 添加书签:将查询语句保存为书签,方便后续进行查询或进行修改
  5. 计算:执行输入框内的查询语句

# 3.2 标签页

# 3.2.1 表结构

“表结构”标签页主要呈现的是数据表的结构

# 3.2.1.1 数据表概览

“表结构”标签页最左侧的是数据表概览,可以查看所有有访问权限的项目的表,点击概览中的相关表,标签页右侧将会展示该表的字段信息

在数据表概览中,可查看并进行查询的数据表类型有:

  • 事件表
  • 用户表
  • 维度表
  • 分群标签表
  • 临时表(可查看 4.4 节)
  • 自定义表(使用二次开发工具生成)
  • 用户每日镜像表(需要联系 TA 工作人员开启)
  • 与 Presto 关联的外部数据源(借助 Presto Connectors 实现,可联系 TA 工作人员了解关联方式)

点击概览左上角的返回按钮,可以查看其他同样具备“使用 SQL 查询”权限的项目的数据表,并进行查询

点击表后的“表解析”按钮,将会生成一条包含该表所有字段的查询语句,在输入框末尾换行插入,通过该条语句,可以快速获得数据表的明细数据,帮助理解表结构

点击“复制表名”按钮,将复制该表的表名至剪切板

# 3.2.1.2 字段列表

字段列表呈现的是所选表的所有字段的信息,包括属性名、属性类型以及属性中文名

  • 点击字段后的“复制”按钮,可以复制该字段的字段名,字段名将用双引号 " " 括起
  • 点击字段前的复选框,可以选中多个字段,批量复制多个字段,每个字段都用双引号 " " 括起,字段间用逗号,分割
  • 列表类型的属性,在复制时将会包含转化将该字段(存储时类型为字符串)为列表的表达式
# 3.2.1.3 事件列表

事件列表只会在选中事件表时才会展示,展示的是所选项目中所有显示状态的实际事件(不包括虚拟事件),建议在查看事件数据时,使用该列表获取事件的对应属性信息

  • 点击“事件解析”按钮,将会生成一条包含该事件所有字段的查询语句,在输入框末尾换行插入,通过该条语句,可以快速获得该事件的明细数据
  • 点击“事件事件名”按钮,将复制该事件的事件名至剪切板

点击表中事件所在行或行尾的箭头,可以查看该事件的所有属性

  • 点击顶部的“事件解析”,可以获得包含该事件所有字段的查询语句
  • 点击字段后的“复制”按钮,可以复制该字段的字段名,字段名将用双引号 " " 括起
  • 点击字段前的复选框,可以选中多个字段,批量复制多个字段,每个字段都用双引号 " " 括起,字段间用逗号,分割
  • 列表类型的属性,在复制时将会包含转化将该字段(存储时类型为字符串)为列表的表达式
# 3.2.1.4 标签分群列表

标签分群列表只会在选中标签分群表时才会展示,展示的是所选项目中所有标签与分群,建议通过标签分群列表获取所需标签分群的信息

  • 点击“标签分群解析”按钮,将会生成一条标签或分群的查询语句,在输入框末尾换行插入,可以在此基础上进行修改,完成标签分群的关联查询
  • 点击“复制标签分群号”按钮,可以复制其标签分群号

# 3.2.2 查询历史

查询历史标签页主要呈现的是您进行过的查询语句,其中呈现的是语句的完成时间、计算耗费时间、查询语句等信息

  • 点击“设置”,该条查询的语句将替换语句输入框中的内容
  • 点击“查询”,将会跳转至查询结果,查看该次查询的结果数据
  • 点击“下载”,将下载此次查询结果的.csv格式文件

在查询历史标签页下,只能查看到自己在所有项目中的 SQL 查询历史,无法查看他人的历史。同时只会记录每个用户近 30 天的近 100 条查询历史,超过范围的将定期清空

# 3.2.3 查询结果

查询结果标签页将会展示上一次查询的结果,或者在查询历史页选择查看的历史查询结果。在本页面,您可以下载结果数据,或将结果保存成临时表供后续使用;此外,我们开放了 SQL 保存成报表的能力,保存成报表的 SQL 查询可以被放在看板中

  • 点击“下载数据”,将下载此次查询结果的.csv格式文件
  • 点击“保存临时表”,会将本次查询的结果存储为临时表,该临时表属于当前所在项目,即页面左上角显示的项目(请注意,临时表不支持中文列名)
  • 点击“保存报表”,会将该条查询语句保存为报表,该报表属于当前所在项目,即页面左上角显示的项目,具体介绍请参考 4.3 节

WARNING

需要注意的是,查询结果页面最多只展示前 1000 条记录,超过 1000 条的记录可以通过下载功能下到本地后再进行查询,下载功能最大支持的 100 万条数据的下载

# 3.2.4 语句书签

语句书签标签页展示所有已经存储的语句书签,已存书签的内容无法被修改,如需更新书签,建议创建新书签并删除历史书签:

  • 点击“设置”,书签中的内容将替换语句输入框中的内容
  • 点击“重命名”,可以对该条书签的名称进行改名
  • 点击“删除”,该条书签将会被永久删除

# 四、SQL 查询的使用场景

# 4.1 查询数据

在语句输入框内编写 SQL 语句,点击工具栏右侧的“计算”按钮,或者快捷键Ctrl + Enter(需光标在输入框内),即可进行数据的查询。

此时进行的查询是实际的查询,不会读取缓存,查询的是当前的实时数据。

在点击查询后,会出现查询进度条,如果希望取消本次查询,可以点击“取消计算”。被取消的计算,可以在“查询历史”中重新执行。

# 4.2 动态参数

动态参数提供了调整语句中部分内容的能力,使用者可以将每次查询时需要修改的部分设置为动态参数,在查询时只用调整参数,无需修改语句,即可实现动态调整。

在上图中,查询时间以及查询数据的渠道都设置为动态参数,在每次计算时,只需要在参数列表中修改参数的值,在执行时参数对应部分的内容也会做出同样的修改,相当于查询条件收到这些参数的控制

动态参数的表达式规则为${参数类型:参数名},可以在语句输入框中直接输入表达式,或者在工具栏中点击“添加动态参数”进行添加。

语句中的同名参数视为一个参数,使用过程中,可以创建多个参数变量,如图:

上图的参数“查询时间”在语句中被多次使用,则该参数的修改将会作用到所有被使用处。在多表联查时,同一参数多次使用可以很好地统一各表的筛选条件。

动态参数可以被保存在报表中,报表将会记录动态参数的信息,包括参数备注以及别名,在看板的探索模块中,查看者将会看到这些参数,并可以进行动态的调整。

如果语句中使用了动态参数,则保存报表时会记录该次查询时的参数值作为参数默认值,从看板查看时,动态参数将会以取该默认值。如果动态参数的类型是事件时间,且查询时选择了动态时间,则看板上每次查看时的时间也会动态变化。

# 4.3 保存报表

保存报表需要在“查询结果”页进行,相当于要求保存的报表需要是可执行的语句。

相比其他模型进行保存报表,SQL 查询在保存时需要进行更多设置,如下图:

主要的区别在于,增加了查看权限以及看板缓存的设置

查看权限

  • 查看权限可以控制该报表在看板中有哪些用户有权限查看,用户必须满足所有查看权限,才能在看板中查看到该报表

  • 事件权限:要求被分享者需要带有所选事件的权限(即成员的事件权限),比如查询用户的付费金额时,可以设置要求“付费”事件的权限

  • 跨项目权限:仅当查询语句中出现其他项目(相对当前所在项目)才会出现,默认是勾选,即用户查看到该报表,必须是报表中所有涉及到的项目的成员(普通成员亦可),反之则不进行校验

看板缓存

  • 看板缓存只会在保存报表的该次查询超过 120 秒时才会出现,通过延长缓存的方法,减少慢 SQL 的查询频率,降低其对集群的压力

  • 如果设置了看板缓存(大于 0 即为设置),则该报表将无法在看板中进行手动刷新(看板的定时刷新仍然有效),并且数据的缓存时长将会延长到设置的时间,在此期间只能查看之前查询的缓存

  • 建议对实时要求不高的复杂查询设置缓存时长,比如对于非实时数据的 SQL 报表,设置成 24 小时缓存

与其他模型的报表类似,可以在“已存报表”界面中查看已创建的 SQL 查询报表,同时 SQL 查询报表支持放入看板中,分享给其他用户。

如果保存报表时的查询超过 300 秒,则必须设置看板缓存,同时限制进入该报表的探索模块,建议优化查询语句,收窄查询范围来加快查询以避免收到限制。

# 4.4 临时表

WARNING

在 3.0 之前创建的临时表,由于只对个人生效,将无法在报表中使用。我们强烈建议转换成新版本的项目临时表,点击个人临时表后的“转化”按钮即可完成转化

可以在查询结果页中,将结果数据存储创建为临时表,存储的临时表将和当前所处项目关联,即页面左上角标识的项目,创建的临时表可以被拥有该项目 SQL 查询权限的其他用户使用

如果需要删除临时表,可以在数据表概览中选中临时表,点击之后的“删除临时表”按钮完成删除

# 五、Presto SQL 特殊用法与进阶函数

本章节将介绍 Presto SQL 中比较特殊的用法,以及一些较为进阶的函数的用法,如果希望了解 Presto SQL 的更多内容,可以访问Presto 的官网文档 (opens new window)

# 5.1 try 函数 与 try_cast 函数

try(expression)

try函数会其中表达式的异常情况,并将异常值返回为 NULL,如果不使用try函数,则语句出现异常时将直接报错导致查询失败。

您还可以搭配coalesce函数使用特定值替换 NULL 值,比如下列,将字段a转换为整数,转换失败则可转换为 0

coalesce(try(cast("a" as integer)), 0)

以上类型转换可以使用 try_cast 函数来实现,try_cast的作用与cast函数一致,都是对值进行类型转换,区别在于try_cast在类型转换错误时会返回 NULL,避免造成查询失败。

coalesce(try_cast("a" as integer), 0)

# 5.2 时间/日期函数

使用current_datecurrent_timecurrent_timestamplocaltimelocaltimestamp时不用加圆括号,Presto 也不支持加圆括号的写法,使用时请注意。

# 5.2.1 字符串与时间的转换

可以直接在字符串格式的时间表达式前加关键字timestamp,比如timestamp '2020-01-01 00:00:00',即可直接获得对应的时间

date_parsedate_format分别是字符串转时间以及时间转字符串,用法都是传入需要转化的字段以及对应的 format,如下分别是字符串$part_date转时间以及时间#event_time转字符串:

date_parse("$part_date", '%Y-%m-%d')
date_format("#event_time", '%Y-%m-%d %T')

以上函数的 format 格式使用的是 MySQL 的格式,如需使用 JAVA 格式,可使用函数format_datetimeparse_datetime

# 5.2.2 时间计算函数

函数date_add可对时间进行偏移,unit为单位,value为偏移量,如果value为负数,则为往前偏移

date_add(unit, value, timestamp)

函数date_diff,用来计算两个时间的差值,算法是timestamp2 - timestamp1,返回的是单位为unit的整数

date_diff(unit, timestamp1, timestamp2)

两个函数的unit的取值范围,可以参考以下表格

单位 描述
millisecond 毫秒
second
minute 分钟
hour 小时
day
week
month
quarter 季度
year

# 5.3 开窗函数

Presto 支持开窗函数,窗口函数中有不少非常实用的函数,如 first_value 与 last_value 很适合计算一段时间内第一次或最后一次做某事的值。

比如计算每名用户首次产生购买商品行为时购买的物品:

SELECT user_id,first_purchase_product FROM
(SELECT user_id,first_value(product_name) over(partition by user_id order by time) AS first_purchase_product FROM log.purchase)
GROUP BY user_id,first_purchase_product

first_valuelast_value需要搭配over子句使用,over子句中的partition by类似于group by,即按照所给字段分组,order by则会决定进行排序的字段。

# 5.4 JSON 解析

在一些特殊场景下,我们建议通过字符串的方式记录复杂的数据结构,上传到后台。那么在之后的使用过程中,可以使用 JSON 解析函数转化或提取能在 SQL 中使用的数据

# 5.4.1 字符串转 JSON

json_parse可以将符合 JSON 格式的字符串转成 SQL 中的 JSON 类型数据,两函数等价:

json_parse(JSON '{"abc":[1, 2, 3]}')

# 5.4.2 JSON 转其他类型

转化为 JSON 的数据,可以通过CAST转化为其他 SQL 类型的数据,比如将刚刚转成 JSON 的字符串再次转成 MAP:

CAST(json_parse('{"abc":[1, 2, 3]}') AS MAP(varchar,array(integer)))

如果希望将字符串重新转成 JSON,可以使用json_format

json_format(json_parse('{"abc":[1, 2, 3]}'))

# 5.4.3 直接提取 JSON 数据

在很多情况下,只需要提取 JSON 中的部分数据即可,此时可以使用json_extract_scalar进行提取,其通过 JSONPath 表达式进行提取,返回字符串:

json_extract_scalar(json, json_path)

并且json_extract_scalar可以直接对 JSON 的字符串进行提取,无需手动转化为 JSON 类型,比如以下,提取abc的第一个元素:

json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')

# 六、最佳实践

# 6.1 补充其他模型

由于模型限制的原因,一些分析场景在实现上无法完全满足要求,比如展示要求,或者排序要求,此时可以使用 SQL 查询来进行完全的自定义。

比如近期付费排名,由于模型中不支持自定义排序,因此可以使用 SQL 查询实现排序:

SELECT
  "#account_id"
, sum("recharge_value") "Total_Payment"
FROM
  ta.v_event_2
WHERE ("$part_event" = 'recharge' AND ${PartDate:date1} )
GROUP BY "#account_id"
ORDER BY sum("recharge_value") ASC

# 6.2 跨项目数据汇总

由于其他模型都受到项目的严格控制,因此跨项目的数据查询或者数据汇总只能通过 SQL 查询实现。

一个比较常见的场景是,不同的应用、业务、地区的数据被存放在不同的项目中,当希望将这些数据汇总起来查看的,使用 SQL 查询可以非常方便的实现这类需求。

以下是一个将项目 ID 1 与项目 ID 2 的数据联合查询的案例,在汇总项目中将以下语句存储为报表,放入看板进行分析,即可实现跨项目的数据汇总:

SELECT a."$part_date","Product_A_DAU","Product_B_DAU" FROM
(SELECT "$part_date",count(*) AS "Product_A_DAU" FROM ta.v_event_1 WHERE ${PartDate:date1} GROUP BY "$part_date") a
JOIN
(SELECT "$part_date",count(*) AS "Product_B_DAU" FROM ta.v_event_2 WHERE ${PartDate:date1} GROUP BY "$part_date") b
ON a."$part_date" = b."$part_date" ORDER BY a."$part_date" ASC

在案例中,动态参数${PartDate:date1}在两处被使用,相当于同时控制了两者表的查询时间,使其计算周期保持统一,这是在跨项目查询时常用的方法。

# 6.3 复杂数据结构的利用

在一些情况下,一些复杂的数据结构,比如 Map、JSON 等,需要在后续的分析中被使用,此时我们会建议通过字符串的方式记录这些复杂数据结构,再通过 SQL 查询进行利用。

下面的案例是对复杂结构属性 hero_json 的解析,提取其中的 hero_id 以及 hero_level,从而实现后续的分析:

SELECT
  json_extract_scalar(hero_json, '$.hero_id') hero_id
, json_extract_scalar(hero_json, '$.hero_level') hero_level
FROM
  ta.v_event_2
WHERE ("$part_event" = 'fight_success' AND ${PartDate:date} )