sqlexplode: SQL 科技的超能力

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 兩個維度進行排序。