「引言」

"成為SQL大師的秘訣在這里！🏆

把SQL（Structured Query Language，結(jié)構(gòu)化查詢語言）想象成數(shù)據(jù)世界的瑞士軍刀。在這個由數(shù)據(jù)構(gòu)建的宇宙里，沒有什么是一點SQL魔法解決不了的。

隨著數(shù)據(jù)量的增長，像偵探一樣尋找線索的數(shù)據(jù)專家越來越受歡迎。僅僅了解高級SQL概念可不夠哦，你得像魔法師一樣在工作中嫻熟地施展它們。面試時，這可是贏得數(shù)據(jù)科學(xué)職位的法寶！

因此，我在這里列出了5個高級SQL概念，每個概念都配有解釋和查詢示例，助你在2022年成為數(shù)據(jù)界的魔法大師。

我特意將這篇文章保持簡短，讓你能快速閱讀完畢，掌握這些必知的、讓面試官眼前一亮的SQL技巧。🏆

目錄

• 公共表表達(dá)式（Common Table Expressions, CTEs）
• 排序函數(shù)：ROW_NUMBER() vs RANK() vs DENSE_RANK()
• CASE WHEN 語句
• 根據(jù)日期-時間列提取數(shù)據(jù)
• 自連接（SELF JOIN）

📍示例數(shù)據(jù)：使用Faker創(chuàng)建的虛擬銷售數(shù)據(jù)，文末獲取。

. . .

公共表表達(dá)式（Common Table Expressions, CTEs）

在處理現(xiàn)實世界數(shù)據(jù)時，有時你需要查詢另一個查詢的結(jié)果。一種簡單的實現(xiàn)方法是使用子查詢。

然而，隨著復(fù)雜性的增加，計算子查詢變得難以閱讀和調(diào)試。

這時，公共表表達(dá)式（CTEs）就派上用場，讓你的工作變得更加輕松。CTEs 使復(fù)雜查詢的編寫和維護(hù)變得更簡單。✅

例如，考慮使用以下子查詢進(jìn)行數(shù)據(jù)提取：

SELECT sales_manager, product_category, unit_price
FROM dummy_sales_data
WHERE sales_manager IN (SELECT DISTINCT sales_manager
                        FROM dummy_sales_data
                        WHERE shipping_address = 'Germany'
                          AND unit_price > 150)
  AND product_category IN (SELECT DISTINCT product_category
                           FROM dummy_sales_data
                           WHERE product_category = 'Healthcare'
                             AND unit_price > 150)
ORDER BY unit_price DESC;

在這里，我僅使用了兩個易于理解的子查詢。

即使如此，要跟蹤這些查詢?nèi)匀缓芾щy，更不用說當(dāng)你在子查詢中增加更多計算，或者甚至添加更多子查詢時 —— 復(fù)雜性增加，使得代碼的可讀性和維護(hù)難度隨之增加。

現(xiàn)在，讓我們看看使用公共表表達(dá)式將上述子查詢簡化后的版本，如下所示：

WITH SM AS
         (SELECT DISTINCT sales_manager
          FROM dummy_sales_data
          WHERE shipping_address = 'Germany'
            AND unit_price > 150),
     PC AS
         (SELECT DISTINCT product_category
          FROM dummy_sales_data
          WHERE product_category = 'Healthcare'
            AND unit_price > 150)
SELECT sales_manager, product_category, unit_price
FROM dummy_sales_data
WHERE sales_manager IN (SELECT sales_manager FROM SM)
  AND product_category IN (SELECT product_category FROM PC)
ORDER BY unit_price DESC ;

復(fù)雜的子查詢被分解為更簡單的代碼塊。

通過這種方式，復(fù)雜的子查詢被重寫為兩個更容易理解和修改的公共表表達(dá)式（CTE）SM和PC。🎯

以上兩個查詢執(zhí)行時間相同，結(jié)果如下所示：

公共表表達(dá)式（CTE）本質(zhì)上允許您根據(jù)查詢結(jié)果創(chuàng)建一個臨時表。這提高了代碼的可讀性和維護(hù)性。✅

現(xiàn)實世界的數(shù)據(jù)集可能有數(shù)百萬或數(shù)十億行，占用數(shù)千GB的存儲空間。直接使用這些表中的數(shù)據(jù)進(jìn)行計算，尤其是將它們與其他表連接起來，將是非常昂貴的。

對于此類任務(wù)的最佳解決方案是使用CTE。💯

接下來，讓我們看看如何使用窗口函數(shù)為數(shù)據(jù)集中的每一行分配一個整數(shù)排名。

. . .

排序函數(shù)：ROW_NUMBER() vs RANK() vs DENSE_RANK()

在處理真實數(shù)據(jù)集時，另一個常用的概念是記錄排名。公司會在不同場景中用到排名，例如：

1. 按銷售單位數(shù)排名最暢銷品牌
2. 按訂單數(shù)或產(chǎn)生的收入排名最佳產(chǎn)品類別
3. 獲取每個類型中觀看次數(shù)最多的電影名稱

ROW_NUMBER、RANK()和DENSE_RANK()基本上用于為結(jié)果集中指定分區(qū)的每條記錄分配連續(xù)的整數(shù)。

它們之間的區(qū)別在于當(dāng)某些記錄出現(xiàn)并列時就變得明顯。

當(dāng)結(jié)果表中存在重復(fù)行時，為每條記錄分配整數(shù)的行為和方式會有所不同。✅

接下來，我們將通過一個虛構(gòu)的銷售數(shù)據(jù)集示例，按運費降序列出所有產(chǎn)品類別和送貨地址。

SELECT product_category,
       shipping_address,
       shipping_cost,
       ROW_NUMBER() OVER
           (PARTITION BY product_category,
               shipping_address
           ORDER BY shipping_cost DESC) AS rowNumber,
       RANK() OVER
           (PARTITION BY product_category,
               shipping_address
           ORDER BY shipping_cost DESC)    rankValues,
       DENSE_RANK() OVER
           (PARTITION BY product_category,
               shipping_address
           ORDER BY shipping_cost DESC)    denseRankValues
FROM dummy_sales_data
WHERE product_category IS NOT NULL
  AND shipping_address NOT IN ('Germany', 'India')
  AND status IN ('Delivered');

如你所見，這三個函數(shù)的語法都相同，但其輸出卻有所不同，如下所示：

RANK() 函數(shù)根據(jù) ORDER BY 子句的條件檢索排名行。可以看到，前五行之間存在并列，即前五行在 Shipping_Cost 列（在 ORDER BY 子句中提到的列）中的值相同。

RANK 為這五行分配了相同的整數(shù)。然而，它將重復(fù)行的數(shù)量加到重復(fù)的排名上，以獲得下一行的排名。這就是為什么第六行（標(biāo)記為紅色）的 RANK 分配了排名 6（5個重復(fù)行 + 1個重復(fù)排名）。

DENSE_RANK 與 RANK 類似，但即使行之間存在并列，它也不會跳過任何數(shù)字。這可以在上圖的綠色框中看到。

與上面兩個不同的是，ROW_NUMBER 簡單地為分區(qū)中的每條記錄按順序分配數(shù)字，從1開始。如果它在同一分區(qū)中檢測到兩個相同的值，它會為這兩個值分配不同的排名數(shù)字。

對于產(chǎn)品類別 — 運送地址的下一個分區(qū) → Entertainment — Italy，三個函數(shù)的排名都會重新從1開始，如下所示：

如果在 ORDER BY 子句中使用的列中沒有重復(fù)值，那么這三個函數(shù)將返回相同的輸出。💯

接下來，下一個概念將更多地介紹如何使用條件語句和數(shù)據(jù)透視。

. . .

CASE WHEN 語句

CASE語句允許你在SQL中實現(xiàn)if-else邏輯，因此你可以使用它來執(zhí)行條件查詢。

CASE語句本質(zhì)上測試WHEN子句中提到的條件，并返回THEN子句中提到的值。當(dāng)沒有條件滿足時，它將返回ELSE子句中提到的值。✅

在處理真實數(shù)據(jù)項目時，CASE語句經(jīng)常用于根據(jù)其他列中的值對數(shù)據(jù)進(jìn)行分類。它也可以與聚合函數(shù)一起使用。

例如，讓我們再次使用虛構(gòu)的銷售數(shù)據(jù)，根據(jù)數(shù)量將銷售訂單分類為高、中、低量級。

SELECT order_id,
       order_date,
       sales_manager,
       quantity,
       CASE
           WHEN quantity > 51 THEN 'High'
           WHEN quantity < 51 THEN 'Low'
           ELSE 'Medium' END AS orderVolume
FROM dummy_sales_data;

簡單地說，它創(chuàng)建了一個新列 OrderVolume，并根據(jù) Quantity 列中的值添加了‘High’（高）、‘Low’（低）、‘Medium’（中）等值。

📌 你可以包含多個 WHEN..THEN 子句，并且可以省略 ELSE 子句，因為它是可選的。

📌 如果你沒有提到 ELSE 子句并且沒有條件滿足，查詢將會為那個特定記錄返回 NULL。

CASE 語句的另一個經(jīng)常使用但較少為人知的用途是 — 數(shù)據(jù)透視。

數(shù)據(jù)透視是一種重新排列結(jié)果集中的列和行的過程，以便你可以從不同的角度查看數(shù)據(jù)。

有時你處理的數(shù)據(jù)是長格式的（行數(shù) > 列數(shù)），而你需要將其轉(zhuǎn)換為寬格式（列數(shù) > 行數(shù)）。

在這種情況下，CASE語句非常有用。💯

例如，讓我們找出每個銷售經(jīng)理在新加坡、英國、肯尼亞和印度處理的訂單量：

SELECT sales_manager,
       COUNT(CASE
                 WHEN shipping_address = 'Singapore' THEN order_id
           END) AS Singapore_orders,
       COUNT(CASE
                 WHEN shipping_address = 'UK' THEN order_id
           END) AS UK_orders,
       COUNT(CASE
                 WHEN shipping_address = 'Kenya' THEN order_id
           END) AS Kenya_orders,
       COUNT(CASE
                 WHEN shipping_address = 'India' THEN order_id
           END) AS India_orders
FROM dummy_sales_data
GROUP BY sales_manager;

使用 CASE..WHEN..THEN，我們?yōu)槊總€運送地址創(chuàng)建了單獨的列，以獲得以下期望的輸出：

根據(jù)你的使用情況，你也可以與 CASE 語句一起使用不同的聚合函數(shù)，如 SUM（總和）、AVG（平均值）、MAX（最大值）、MIN（最小值）。

接下來，在處理真實世界數(shù)據(jù)時，經(jīng)常包含日期時間值。因此，了解如何提取日期時間值的不同部分，如月份、周數(shù)、年份，是很重要的。

. . .

根據(jù)日期-時間列提取數(shù)據(jù)

在許多面試中，面試官可能會要求你按月聚合數(shù)據(jù)或計算特定月份的某個指標(biāo)。

當(dāng)數(shù)據(jù)集中沒有單獨的月份列時，你需要從數(shù)據(jù)中的日期時間變量中提取所需的日期部分。

不同的SQL環(huán)境有不同的函數(shù)來提取日期的部分。通常，在MySQL中，你應(yīng)該了解以下函數(shù)：

EXTRACT(part_of_date FROM date_time_column_name)
YEAR(date_time_column_name)
MONTH(date_time_column_name)
MONTHNAME(date_time_column_name)
DATE_FORMAT(date_time_column_name)

比如，使用前面虛擬銷售數(shù)據(jù)集，我們可以計算每個月的總訂單量：

SELECT MONTH(order_date) AS month,
       SUM(quantity) AS total_quantity
FROM dummy_sales_data
GROUP BY MONTH(order_date);

如果你用的是SQLite DB Browser，你需要使用strftime()函數(shù)來提取日期部分，如下所示。你需要在strftime()中使用%m來提取月份。

SELECT strftime('%m', order_date) as month,
       SUM(quantity) as total_quantity
from dummy_sales_data
GROUP BY strftime('%m', order_date)

如果使用EXTRACT()函數(shù)，則用以下代碼：

SELECT EXTRACT(MONTH FROM order_date) AS month,
       SUM(quantity) AS total_quantity
FROM dummy_sales_data
GROUP BY EXTRACT(MONTH FROM order_date);

下圖展示了最常提取的日期部分，以及你在使用EXTRACT函數(shù)時應(yīng)該使用的關(guān)鍵字：

最后但不可或缺的是：

你經(jīng)常會在現(xiàn)實世界中看到，數(shù)據(jù)是存儲在一個大表中，而不是多個小表中。這時，自連接（SELF JOIN）就派上用場了，它在處理這些數(shù)據(jù)集時解決了一些有趣的問題。

. . .

自連接（SELF JOIN）

與SQL中的其他連接一樣，唯一的區(qū)別就是——在自連接中你是將表和自身進(jìn)行連接。

記住，沒有SELF JOIN關(guān)鍵字，所以當(dāng)連接中的兩個表是同一個表時，你只需使用JOIN。由于兩個表名相同，在使用自連接時使用表別名是必要的。✅

編寫一個SQL查詢，找出那些賺得比他們經(jīng)理多的員工 — 這是關(guān)于自連接在面試中最常被問到的問題之一。

例如，創(chuàng)建一個像下面的虛擬員工數(shù)據(jù)集（Dummy_Employees）：

嘗試使用下面這個查詢找出哪些員工處理的訂單數(shù)量超過他們的經(jīng)理：

SELECT t1.EmployeeName, t1.TotalOrders
FROM Dummy_Employees AS t1
JOIN Dummy_Employees AS t2
ON t1.ManagerID = t2.EmployeeID
WHERE t1.TotalOrders > t2.TotalOrders;

正如預(yù)期，它返回了處理的訂單數(shù)量超過他們經(jīng)理的員工——Abdul和Maria。

幾乎80%的面試中都遇到了這個問題。因此，這是自連接（SELF JOIN）的經(jīng)典案例。

. . .

結(jié)論（Conclusion）

以上就是我想給給大家分享的5個高級SQL概念及其實際應(yīng)用。

希望你能快速讀完這篇文章，并且發(fā)現(xiàn)它對提升你的SQL技能有所幫助。