# SQL 查询
# 一、SQL 查询的简介
对于难以使用分析模型实现的高级分析，您可以使用 SQL 查询来进行实现。TE 系统使用 Trino 查询引擎，可以使用标准 SQL 对系统中的所有数据进行快速查询。
除此之外，我们提供了动态参数的功能，帮助使用者在不用修改 SQL 语句的情况下，通过调整参数值的方法，对语句中的部分参数内容进行变更以适应新的查询条件，实现一次编写、多次使用。
SQL 查询结果可以被保存成报表，支持放入看板中进行数据查看，同时支持在看板中借助探索模块，对 SQL 报表的动态参数进行实时修改，以此实现类似其他模型中调整条件的能力。
# 二、SQL 查询的位置和使用角色
可以从“行为分析”模块下的“SQL 查询”进入
| 公司超管 | 管理员 | 分析师 | 普通成员 | |
|---|---|---|---|---|
| SQL查询 | ● | ● | △ | △ |
权限说明：
● 角色必有
▲ 角色默认有，可以没有
△ 角色默认没有，可以有
○ 角色必没有
需要注意，系统自带的分析师角色不具备“使用 SQL 查询”的权限，如需使用，可在新创建的分析师及以上权限的角色中添加 SQL 使用权限，并提供给使用者
# 三、SQL 查询的页面概览
SQL 查询的页面，主要由上半部分的语句编写框以及下半部分的标签页所组成，标签页又分四个 TAB 页面，分别为“表结构”、“查询历史”、“查询结果”与“语句书签”标签页
# 3.1 语句编写框
语句编写框的核心是语句的输入框，在输入框中编写 SQL 查询语句，编写时需要注意以下几点：
- 字段名请使用双引号
" "括起，也可以缺省，但如果查询字段名带有特殊符号（如$、#），则必须使用双引号 - 字符串请必须使用单引号
' '括起 - 可以使用
SELECT语句以及WITH子句 - 查询事件表时，请尽量使用分区键
"$part_date"和"$part_event"进行数据时间以及查询事件的筛选，以提高查询的效率 - 列表类型的属性，在底层存储时以字符串形式存储，使用
\t分割元素，如需将其转换为列表类型，建议使用复制属性名功能（列表属性的复制内容中包含转化为列表的表达式），或者使用函数split("属性名",chr(0009))进行拆解
光标位于输入框时，可以使用以下快捷键：
Ctrl + Enter：执行语句Ctrl + Shift + F：格式化语句Ctrl + Z：撤销Ctrl + Y：恢复
语句编写框的工具栏位于输入框下方，您可以在工具栏左侧点击“添加动态参数”按钮，在语句末尾插入一个动态参数，工具栏右侧的 ICON，从左到右分别为：
- 帮助：查看 SQL 查询的快捷键以及相关帮助文档
- 格式化：将查询语句进行格式化
- 复制语句：将输入框内的查询语句复制到剪切板
- 添加书签：将查询语句保存为书签，方便后续进行查询或进行修改
- 计算：执行输入框内的查询语句
# 3.2 标签页
# 3.2.1 表结构
“表结构”标签页主要呈现的是数据表的结构
# 3.2.1.1 数据表概览
“表结构”标签页最左侧的是数据表概览，可以查看所有有访问权限的项目的表，点击概览中的相关表，标签页右侧将会展示该表的字段信息
在数据表概览中，可查看并进行查询的数据表类型有：
- 事件表
- 用户表
- 维度表
- 分群标签表
- 临时表（可查看 4.4 节）
- 自定义表（使用二次开发工具生成）
- 用户每日镜像表（需要联系 TE 工作人员开启）
- 与 Trino 关联的外部数据源（借助 Trino Connectors 实现，可联系 TE 工作人员了解关联方式）
点击概览左上角的返回按钮，可以查看其他同样具备“使用 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 查询可在保存时进行更多设置，如下图：
主要的区别在于，增加了查看权限以及看板缓存的设置
查看权限
查看权限可以控制该报表在看板中有哪些用户有权限查看，用户必须满足所有查看权限，才能在看板中查看到该报表：
- 事件权限：SQL报表默认受到成员在项目中的数据权限的控制，如某成员不具备事件A的可见权限，该成员即无法查看基于事件A创建的SQL报表。在SQL报表保存时，支持设置额外的权限控制，即要求被共享者需要带有所选事件的权限（即成员的事件权限），比如查询用户的付费金额时，可以额外设置要求“付费”事件的权限
- 跨项目权限：仅当查询语句中出现其他项目（相对当前所在项目）才会出现，默认是开启，即项目内成员查看到该报表，必须是报表中所有涉及到的所有项目的成员才可查看
看板缓存
- 看板缓存即SQL报表存放于看板时，展示的缓存将会保持的时长，可以通过设置看板缓存减少慢 SQL 与非实时报表的查询频率，节省集群计算资源
- 如果设置了看板缓存（大于 0 即为设置），但查询时长小于 300 秒，报表仍然可以进行手动刷新，刷新后新数据的缓存时限将重新计算
- 看板定时刷新对所有SQL报表都生效，无论报表的查询时长以及报表是否设置缓存，都将在看板定时刷新时同步刷新；对于非实时数据的 SQL 报表，可以将报表缓存设置成 24 小时，并打开看板定时刷新来实现每日只更新一次数据
- 如果保存报表时的查询超过 300 秒，则必须设置看板缓存，同时无法进行手动刷新（看板定时刷新仍然生效），且无法进入该报表的探索模块，建议优化查询语句，收窄查询范围来加快查询以避免收到限制。
与其他模型的报表类似，可以在“已存报表”界面中查看已创建的 SQL 查询报表，同时 SQL 查询报表支持放入看板中，分享给其他用户。
# 4.4 临时表
WARNING
在 3.0 之前创建的临时表，由于只对个人生效，将无法在报表中使用。我们强烈建议转换成新版本的项目临时表，点击个人临时表后的“转化”按钮即可完成转化
可以在查询结果页中，将结果数据存储创建为临时表，存储的临时表将和当前所处项目关联，即页面左上角标识的项目，创建的临时表可以被拥有该项目 SQL 查询权限的其他用户使用
如果需要删除临时表，可以在数据表概览中选中临时表，点击之后的“删除临时表”按钮完成删除
# 五、Trino SQL 特殊用法与进阶函数
本章节将介绍 Trino SQL 中比较特殊的用法，以及一些较为进阶的函数的用法，如果希望了解 Trino SQL 的更多内容，可以访问Trino 的官网文档 (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_date、current_time、current_timestamp、localtime与localtimestamp时不用加圆括号，Trino 也不支持加圆括号的写法，使用时请注意。
# 5.2.1 字符串与时间的转换
可以直接在字符串格式的时间表达式前加关键字timestamp，比如timestamp '2020-01-01 00:00:00'，即可直接获得对应的时间
date_parse与date_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_datetime与parse_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 开窗函数
Trino 支持开窗函数，窗口函数中有不少非常实用的函数，如 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_value与last_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} )