提起 SQL，（从前的）我脑子只有 SELECT、COUNT()、SUM()、JOIN、GROUP BY 等云云。对于较为复杂的数据场景，总是绞尽脑汁的用 GROUP BY 和 JOIN 来实现，却不知有类似功能的 SQL 函数。

下面举个栗子，说说我学到的一些 SQL 函数和简化 SQL 的方法，以 Hive SQL 作为模版。代表因为 SQL 函数和语法大多类似，原理通用，在使用其他 SQL 时参考即可。

一个栗子

（假装）有张订单流水表 t_order_detail，它的结构是这样的：

每次对数据库中的订单进行插入、更新操作，系统都会进行上报，新增一条流水到 t_order_detail 中。例如，order_id 为 1001的订单被创建，后续状态被扭转为已完结，在流水表中就会存在下述记录：

然后，（假装有个）产品爸爸想要了解订单和用户情况，需要以下数据：

• 当日总订单数

• 当日总购买用户数

• 当日总的和根据商户分组的订单状态为已完结的订单数和用户数

• 当日总的和根据商户分组的订单状态为已取消的订单数和用户数

我们把产品爸爸的需求翻译一下，就变成了求下列的当日数据：

• 根据 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：

根据上述需求，可以拆分为以下步骤：

• 由于我们只关注每个订单的最新一条记录（即版本号最大的记录），因此先可以先对表做一次清洗，获得新表数据 t_clean_order_detail。

• 根据新表获得数据。

如何获得每个 order_id 对应的最新一条记录

清洗过后的理想数据为：

获取思路是这样的：根据 order_id 分组，在每个分组中取出 data_version 最大的一条。

说到分组，我那贫瘠的 SQL 词库里就只想到了 GROUP BY。

使用  GROUP BY 的写法

sql代码解读复制代码-- t_latest_record：获取当天内，根据 order_id 分组，每个分组内的 order_id 以及最大的版本号的数据。 -- t_total_record：获取当天内所有订单流水数据。 -- ${today_begin_time}：变量，代表当天00:00的时间戳。 -- ${today_end_time}：变量，代表当天23:59的时间戳。 SELECT         *     FROM         (             SELECT                     ${today} AS today,                     order_id,                     MAX(data_version) AS data_version                 FROM                     t_order_detail                 WHERE                     create_time >= ${today_begin_time}                     AND create_time <= ${today_end_time}                 GROUP BY                     order_id         ) t_latest_record             LEFT JOIN (                   SELECT                  *                 FROM                     t_order_detail                 WHERE                     create_time >= ${today_begin_time}                     AND create_time <= ${today_end_time}             ) t_total_record                 ON t_latest_record.order_id = t_total_record.order_id                 AND t_latest_record.data_version = t_total_record.data_version;

整体思路是：根据 order_id 分组，获得每个订单的 order_id 以及最大的 data_version，再用左连接取到 order_id 和 data_version 相等的记录，即我们要的最新一条记录。

这样看也不是很复杂，但我们运算了2次，让2个临时表做了一次左连接。看起来比较繁琐。有没有函数直接能代替上述过程？

使用 ROW_NUMBER() OVER 的写法

sql代码解读复制代码-- t_sorted_order_detail：根据 order_id 分组后，再根据 row_num 排序得到的订单流水数据。 SELECT         *     FROM         (             SELECT                     *,                     ROW_NUMBER() OVER (                         PARTITION BY                             order_id                         ORDER BY                             data_version DESC                     ) AS row_num                 FROM                     t_order_detail                 WHERE                     create_time >= ${today_begin_time}                     AND create_time <= ${today_end_time}         )t_sorted_order_detail     WHERE          row_num = 1;

SQL 看起来是不是清爽多了？

说明

• ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause ) 它的作用是，根据某个字段分组，然后根据字段排序，并拿到排序第一条记录。 PARTITION BY 承担了 GROUP BY 的角色，即根据某些字段分组；ORDER BY 即排序，即根据某些字段对每个分组的数据进行排序。然后 ROW_NUMBER() OVER 这个函数就会为每条记录返回在分组内排好的序号。

因此，根据上述 SQL，我们得到 t_sorted_order_detail 表数据就为：

然后，我们取每个分组里面 row_num 为1的数据，就得到了版本号最大的数据了。

根据清洗后的表获得数据

现在我们得到了清洗后的表：t_clean_order_detail。它的数据如下（row_num 在此处无用，因此去掉）：

接下来我们进行第二步：根据新表获得统计数据。

如何减少多余运算

首先看两个总数如何获得：

• 根据 order_id 去重的记录总数，total_order_day。

• 根据 user_id 去重的记录总数，total_user_day。

以前的我：两个数据，两次运算，每个临时表对应一个数据，然后两个临时表 JOIN 获得两个数据。

实际上，这两个数据的来源相同，计算逻辑相似。当遇到这种情况，就可以合并运算。这样不仅可以提高效率，还能让 SQL 更简洁。

例如，获取这两个总数的 SQL ：

sql代码解读复制代码SELECT         COUNT(1) AS total_order_day,         COUNT(DISTINCT (user_id)) AS total_user_day     FROM         t_clean_order_detail     WHERE         create_time >= ${today_begin_time}         AND create_time <= ${today_end_time};

说明

• 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 的数据为例，我们的理想数据是：

以前的我看到会想：分组，GROUP BY 搞定；还要一个总的，另外计算一个总的结果，然后把结果进行 UNION，完美。

但是，当我了解到了 SQL 自带的多维分析语句和函数，我才知道年轻的我多么幼稚：根本不用两次计算，还要自己整合结果。只要一套组合拳，一次运算就足够解决。

首先看下，不考虑根据 merchant_id 分组的情况，要获取的 total_cancel_user_day、total_cancel_user_day 的 SQL ：

sql代码解读复制代码SELECT         COUNT(IF (             state = 2,             1,             null         )) AS total_cancel_order_day,         COUNT(DISTINCT (             IF (                 state = 2,                 user_id,                 null             )         )) AS total_cancel_user_day     FROM         t_clean_order_detail     WHERE         update_time >= ${today_begin_time}         AND update_time <= ${today_begin_time};

跟前面的 SQL 其实很像。

接下来考虑根据 merchant_id 分组和获得总计时的 SQL。利用 SQL 提供的多维分析函数，我们使用 GROUP BY 根据 merchant_id 分组，使用 WITH ROLLUP 得到每个分组的小计和总计，使用 GROUPING 区分每个分组小计和总计的行，使用 DECODE 为记数维度（ merchant_id 和总）命名。

得到如下 SQL：

sql代码解读复制代码SELECT         COUNT(IF (             state = 2,             1,             null         )) AS total_cancel_order_day,         COUNT(DISTINCT (             IF (                 state = 2,                 user_id,                 null             )         )) AS total_cancel_user_day,         DECODE(GROUPING(merchant_id),             1, "总",             merchant_id         ) AS merchant_id     FROM         t_clean_order_detail     WHERE         update_time >= ${today_begin_time}         AND update_time <= ${today_begin_time}     GROUP BY         merchant_id WITH ROLLUP;

说明

• 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 对应的最新一条记录

• 如何减少多余运算

• 如何对数据进行上卷分析

作者：lianwwup
链接：https://juejin.cn/post/6967590860774965279
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。