最近在写 SQL，菜如老狗的我在大哥的指导下学会了用一些方便的 SQL 函数代替以前繁杂的写法，对于常见的数据场景还是很有用的。
提起 SQL，（从前的）我脑子只有 SELECT、COUNT()、SUM()、JOIN、GROUP BY 等云云。对于较为复杂的数据场景，总是绞尽脑汁的用 GROUP BY 和 JOIN 来实现，却不知有类似功能的 SQL 函数。
下面举个栗子，说说我学到的一些 SQL 函数和简化 SQL 的方法，以 Hive SQL 作为模版。代表因为 SQL 函数和语法大多类似，原理通用，在使用其他 SQL 时参考即可。
一个栗子
（假装）有张订单流水表 t_order_detail，它的结构是这样的：
| 字段名 | 字段描述 |
|---|---|
| order_id | 订单 id，订单的唯一标识 |
| user_id | 用户 id，标识订单所属的用户 |
| merchant_id | 商户 id，标识订单所属的商户 id |
| state | 订单状态，具有已确认（0）、已完结（1）、已取消（2）等状态 |
| create_time | 记录的创建时间 |
| data_version | 版本号，对同一笔订单进行插入、更新操作时，版本号也相应更新 |
每次对数据库中的订单进行插入、更新操作，系统都会进行上报，新增一条流水到 t_order_detail 中。例如，order_id 为 1001的订单被创建，后续状态被扭转为已完结，在流水表中就会存在下述记录：
| order_id | user_id | merchant_id | state | create_time | data_version |
|---|---|---|---|---|---|
| 10001 | user_1 | merchant_1 | 0 | 1622249031 | 1 |
| 10001 | user_1 | merchant_1 | 1 | 1622249082 | 2 |
然后，（假装有个）产品爸爸想要了解订单和用户情况，需要以下数据：
- 当日总订单数
- 当日总购买用户数
- 当日总的和根据商户分组的订单状态为已完结的订单数和用户数
- 当日总的和根据商户分组的订单状态为已取消的订单数和用户数
我们把产品爸爸的需求翻译一下，就变成了求下列的当日数据：
- 根据 order_id 去重的记录总数，total_order_day。
- 根据 user_id 去重的记录总数，total_user_day。
- 最新一条记录 state=1、根据 merchant_id 分组以及总的：
- 根据 order_id 去重的记录总数，total_finish_order_day。
- 根据 user_id 去重的记录总数，total_finish_user_day。
- 最新一条记录 state=2、根据 merchant_id 分组以及总的：
- 根据 order_id 去重的记录总数，total_cancel_order_day。
- 根据 user_id 去重的记录总数，total_cancel_user_day。
假设我们表的记录现在是这样的，一共有三笔订单，user_1 在 merchant_1、merchant_2 下有2笔订单，订单终态分别为1和2；user_2 在 merchant_1 下有1笔订单，订单终态为2：
| order_id | user_id | merchant_id | state | create_time | data_version |
|---|---|---|---|---|---|
| 10001 | user_1 | merchant_1 | 1 | 1622249082 | 2 |
| 10001 | user_1 | merchant_1 | 0 | 1622249031 | 1 |
| 10002 | user_1 | merchant_2 | 0 | 1622249011 | 1 |
| 10002 | user_1 | merchant_2 | 2 | 1622249022 | 2 |
| 10003 | user_2 | merchant_1 | 0 | 1622249031 | 1 |
| 10003 | user_2 | merchant_1 | 2 | 1622249082 | 2 |
根据上述需求，可以拆分为以下步骤：
- 由于我们只关注每个订单的最新一条记录（即版本号最大的记录），因此先可以先对表做一次清洗，获得新表数据 t_clean_order_detail。
- 根据新表获得数据。
如何获得每个 order_id 对应的最新一条记录
清洗过后的理想数据为：
| order_id | user_id | merchant_id | state | create_time | data_version |
|---|---|---|---|---|---|
| 10001 | user_1 | merchant_1 | 1 | 1622249082 | 2 |
| 10002 | user_1 | merchant_2 | 2 | 1622249022 | 2 |
| 10003 | user_2 | merchant_1 | 2 | 1622249082 | 2 |
获取思路是这样的：根据 order_id 分组，在每个分组中取出 data_version 最大的一条。
说到分组，我那贫瘠的 SQL 词库里就只想到了 GROUP BY。
使用 GROUP BY 的写法
1 | sql复制代码-- t_latest_record：获取当天内，根据 order_id 分组，每个分组内的 order_id 以及最大的版本号的数据。 |
整体思路是：根据 order_id 分组，获得每个订单的 order_id 以及最大的 data_version，再用左连接取到 order_id 和 data_version 相等的记录，即我们要的最新一条记录。
这样看也不是很复杂，但我们运算了2次，让2个临时表做了一次左连接。看起来比较繁琐。有没有函数直接能代替上述过程？
使用 ROW_NUMBER() OVER 的写法
1 | sql复制代码-- t_sorted_order_detail：根据 order_id 分组后，再根据 row_num 排序得到的订单流水数据。 |
SQL 看起来是不是清爽多了？
说明
- ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )
它的作用是，根据某个字段分组，然后根据字段排序，并拿到排序第一条记录。
PARTITION BY 承担了 GROUP BY 的角色，即根据某些字段分组；ORDER BY 即排序，即根据某些字段对每个分组的数据进行排序。然后 ROW_NUMBER() OVER 这个函数就会为每条记录返回在分组内排好的序号。
因此，根据上述 SQL，我们得到 t_sorted_order_detail 表数据就为：
| order_id | user_id | merchant_id | state | create_time | data_version | row_num |
|---|---|---|---|---|---|---|
| 10001 | user_1 | merchant_1 | 1 | 1622249082 | 2 | 1 |
| 10001 | user_1 | merchant_1 | 0 | 1622249031 | 1 | 2 |
| 10002 | user_1 | merchant_2 | 2 | 1622249022 | 2 | 1 |
| 10002 | user_1 | merchant_2 | 0 | 1622249011 | 1 | 2 |
| 10003 | user_2 | merchant_1 | 2 | 1622249082 | 2 | 1 |
| 10003 | user_2 | merchant_1 | 0 | 1622249031 | 1 | 2 |
然后，我们取每个分组里面 row_num 为1的数据，就得到了版本号最大的数据了。
根据清洗后的表获得数据
现在我们得到了清洗后的表：t_clean_order_detail。它的数据如下（row_num 在此处无用，因此去掉）：
| order_id | user_id | merchant_id | state | create_time | data_version |
|---|---|---|---|---|---|
| 10001 | user_1 | merchant_1 | 1 | 1622249082 | 2 |
| 10002 | user_1 | merchant_2 | 2 | 1622249022 | 2 |
| 10003 | user_2 | merchant_1 | 2 | 1622249082 | 2 |
接下来我们进行第二步：根据新表获得统计数据。
如何减少多余运算
首先看两个总数如何获得：
- 根据 order_id 去重的记录总数，total_order_day。
- 根据 user_id 去重的记录总数，total_user_day。
以前的我：两个数据，两次运算，每个临时表对应一个数据，然后两个临时表 JOIN 获得两个数据。
实际上，这两个数据的来源相同，计算逻辑相似。当遇到这种情况，就可以合并运算。这样不仅可以提高效率，还能让 SQL 更简洁。
例如，获取这两个总数的 SQL ：
1 | sql复制代码SELECT |
说明
- COUNT()
作用为计算满足条件的行数，COUNT(1)返回总行数。 - DISTINCT
根据字段去重。
如何对数据进行上卷分析
上卷，用人话来说，就是汇总数据得到总值。在后面的4个数据中，不仅要根据 merchant_id 分组得到小计，还要得到总值。我们来看下如何获取。
因为它们也是逻辑类似，来源相同的数据。因此我们只取其中2个来讲解：
- 当日最新一条记录 state=2、根据 merchant_id 分组以及总的：
- 根据 order_id 去重的记录总数，total_cancel_order_day。
- 根据 user_id 去重的记录总数，total_cancel_user_day。
光看文字比较抽象，拿 t_clean_order_detail 的数据为例，我们的理想数据是：
| total_cancel_order_day | total_cancel_user_day | merchant_id |
|---|---|---|
| 1 | 1 | mechant_1 |
| 1 | 1 | merchant_2 |
| 2 | 2 | 总 |
以前的我看到会想：分组，GROUP BY 搞定；还要一个总的，另外计算一个总的结果，然后把结果进行 UNION，完美。
但是，当我了解到了 SQL 自带的多维分析语句和函数，我才知道年轻的我多么幼稚：根本不用两次计算，还要自己整合结果。只要一套组合拳，一次运算就足够解决。
首先看下，不考虑根据 merchant_id 分组的情况，要获取的 total_cancel_user_day、total_cancel_user_day 的 SQL ：
1 | sql复制代码SELECT |
跟前面的 SQL 其实很像。
接下来考虑根据 merchant_id 分组和获得总计时的 SQL。利用 SQL 提供的多维分析函数，我们使用 GROUP BY 根据 merchant_id 分组，使用 WITH ROLLUP 得到每个分组的小计和总计，使用 GROUPING 区分每个分组小计和总计的行，使用 DECODE 为记数维度（ merchant_id 和总）命名。
得到如下 SQL：
1 | sql复制代码SELECT |
说明
- IF(boolean testCondition, T valueTrue, T valueFalseOrNull)
条件返回。testCondition为 true 或者非 NULL 时，返回 valueTrue；否则返回 valueFalseOrNull。 - DECODE(expression , search , result [, search , result]… [, default])
DECODE 函数与一系列嵌套的 IF-THEN-ELSE 语句相似。expression 与 search 等依次进行比较。如果 expression 和第 i 个 search 项匹配，就返回第 i 个对应的 result 。如果 expression 与任何的 search 值都不匹配，则返回 default 。 - ROLLUP
ROLLUP 对 groupbyClause 进行扩展，可以令 SELECT 语句根据分组的维度计算多层小计，并计算总计。 - GROUPING
使用 ROLLUP 中的一个列作为参数，GROUPING 函数在遇到 ROLL UP 生成的 NULL 值时，返回1。即如果这一列是个小计或总计时，GROUPING 返回1，否则返回0。它只能用在 ROLLUP 或者 CUBE 的查询里。
总结
回顾一下前面的三个知识点
- 如何获得每个 order_id 对应的最新一条记录
- 如何减少多余运算
- 如何对数据进行上卷分析
这三个问题，是否有一点思路了？如果没有，那我就给你说声对不起。（卑微）
最后，本人充满热情和耐心教女朋友写 SQL，假一赔十包教包会，请问女朋友在哪里领？
本文转载自: 掘金