SQL 从入门到放弃：ROW_NUMBER() OVER 和 ROLLUP

最近在写 SQL，菜如老狗的我在大哥的指导下学会了用一些方便的 SQL 函数代替以前繁杂的写法，对于常见的数据场景还是很有用的。

提起 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 的写法

-- 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 的写法

-- 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 ：

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 ：

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：

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 对应的最新一条记录
• 如何减少多余运算
• 如何对数据进行上卷分析

这三个问题，是否有一点思路了？如果没有，那我就给你说声对不起。（卑微）

最后，本人充满热情和耐心教女朋友写 SQL，假一赔十包教包会，请问女朋友在哪里领？