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

2023-11-22 09:46:35 浏览数 (1)

最近在写 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 的写法

代码语言：javascript复制
-- 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 的写法

代码语言：javascript复制
-- 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 表数据就为：

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 ：

代码语言：javascript复制
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 的数据为例，我们的理想数据是：

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 ：

代码语言：javascript复制
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：

代码语言：javascript复制
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，假一赔十包教包会，请问女朋友在哪里领？

sql rollup row 入门 数据

0 人点赞