sqlexplode: SQL 科技的超能力

发布时间：2023-05-18

SQL （Structured Query Language）是一种用于操作关系型数据库系统的语言，也是大部分 IT 从业人员日常工作必备的技能之一。而 sqlexplode 则是 SQL 中的一种神奇的函数，可以让我们在查询结果中快速地将一个包含多个值的字段拆分成多条记录，极大地方便了数据分析的处理。在本文中，我们将从多个方面来详细介绍 sqlexplode 函数的使用方法和示例。

一、splitting 人口数据 using sqlexplode

为了更好的理解 sqlexplode 的能力，我们先来看一个实际的数据案例。假设现在我们有一张存储人口数据的表（名为 population），其中有一个名为 city 的字段，用来表示每个城市有哪些国家的人口：

CREATE TABLE `population` (
  `City` varchar(50) NOT NULL,
  `Country` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `population` (`City`, `Country`) VALUES
('Beijing', 'China;Japan;South Korea'),
('Shanghai', 'China;United States;Australia;Japan;South Korea;Germany;Canada;Denmark;France;Spain;UK');

可以看到，在上述表格中，北京的人口来自于三个不同的国家，而上海的人口更是来自于十一个不同的国家。如果我们想进一步统计每个城市的总人口数，那么我们就需要将每个城市的人口数据拆分成多行，其中每行只包含一个国家的人口数据。这时候，我们就可以使用 sqlexplode 函数了：

SELECT
    City,
    Country,
    COUNT(*) as population
FROM
    population
    CROSS JOIN LATERAL
    (SELECT *
     FROM   sqlexplode(split_string(Country, ";"))) AS t
GROUP BY
    City,
    Country
ORDER BY
    population DESC;

上述 SQL 语句中的 split_string 函数用于将每行数据中的多个国家名字拆分成多个部分，并使用 ; 作为分隔符。然后，我们将每个城市的名称和拆分后的国家名字列进行了 CROSS JOIN 操作，并使用 sqlexplode 函数将结果拆分成多行。最后，我们再按照城市和国家两个纬度进行 GROUP BY 操作，统计每个城市每个国家的实际人口数量。

二、exploding 订单数据 using sqlexplode

另一个非常常见的数据拆分需求是将包含多个订单号的订单信息数据拆分为多行，其中每行只包含一个订单号。假设我们有一个包含订单信息的表格（名为 orders），其中有一个名为 order_numbers 的字段，用于记录每个订单包含的订单号：

CREATE TABLE `orders` (
  `id` int NOT NULL,
  `order_numbers` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `orders` (`id`, `order_numbers`) VALUES
(1, '202009201;202009202;202009203;202009204'),
(2, '202009205'),
(3, '202009206;202009207;202009208');

为了将订单信息数据拆分为单独的行，我们可以使用 sqlexplode 函数。下面是一个使用 sqlexplode 函数的 SQL 语句示例：

SELECT 
    orders.id,
    t.val AS order_number
FROM 
    orders
    CROSS JOIN LATERAL 
    (SELECT *
     FROM   sqlexplode(split_string(order_numbers, ';'))) AS t
ORDER BY 
    orders.id ASC,
    t.idx ASC;

上述 SQL 语句中的 split_string 函数用于将每行数据中的多个订单号拆分成多个部分，并使用 ; 作为分隔符。然后，我们将每个订单的 id 和拆分后的订单号列进行了 CROSS JOIN 操作，并使用 sqlexplode 函数将结果拆分成多行。最后，我们再按照订单的 id 和订单号的顺序进行排序。

三、exploding 变量数据 using sqlexplode

除了可以用于拆分数据库中的表格数据，sqlexplode 函数还可以用于拆分变量中的数据。假设我们有一个变量包含多个城市名称，每个城市名称之间用逗号隔开。那么我们就可以使用 sqlexplode 函数将变量数据拆分为多个城市名称：

SET @city_list="Beijing,Shanghai,Tokyo,New York,Hong Kong,Sydney,Paris";
SELECT id,city
FROM population
CROSS JOIN LATERAL 
(SELECT *
FROM sqlexplode(split_string(@city_list,','))) AS t
WHERE t.val=Country
GROUP BY id,city;

上述 SQL 语句中的 @city_list 变量用于存储多个城市名称，每个城市名称用逗号隔开。然后，我们通过 sqlexplode 函数将变量中的数据拆分成多行，并通过 WHERE 子句筛选出每个城市的名称与 population 表品相符的数据。最后，我们再按照 id 和城市名称的顺序进行分组。

四、explode 大数据 using sqlexplode

最后，我们来介绍一下如何使用 sqlexplode 函数处理大数据。在处理大数据时，往往会遇到数据集大小超过内存容量限制的问题。对于这种情况，我们可以使用 Spark 或者 Hadoop 等分布式计算平台进行数据处理。下面是一个 Spark SQL 中使用 sqlexplode 函数的示例：

SELECT
    x,
    y,
    CASE
        WHEN z1 IS NOT NULL THEN z1
        WHEN z2 IS NOT NULL THEN z2
        ELSE z3 END AS z
FROM
    (SELECT 
        x,
        y,
        LATERAL VIEW explode(split(z, ' ')) z_exploded  
    FROM 
        big_table) t
    LATERAL VIEW OUTER explode(array(z_exploded[0],z_exploded[1],z_exploded[2])) z_exploded_array
    LATERAL VIEW OUTER explode(split(z_exploded_array,',')) z_exploded_array_comma AS z1
    LATERAL VIEW OUTER explode(split(z_exploded_array,':')) z_exploded_array_colon AS z2
    LATERAL VIEW OUTER explode(split(z_exploded_array,';')) z_exploded_array_semi AS z3;

上述 Spark SQL 语句中的 big_table 是一个大型的数据表。我们首先使用 LATERAL VIEW explode() 函数将 big_table 中的 z 字段拆分成多行，并分别拆分成不同的数组类型。然后，我们再在数组类型中使用 LATERAL VIEW explode() 函数，将拆分后的数据进一步拆分成单独的数据行。最后，我们再通过 CASE 语句将每行数据进行合并，并按照 x, y 两个维度进行排序。

推荐文章