狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

「引言」

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

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

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

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

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

目錄

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

📍示例數據：使用Faker創建的虛擬銷售數據，文末獲取。

. . .

公共表表達式（Common Table Expressions, CTEs）

在處理現實世界數據時，有時你需要查詢另一個查詢的結果。一種簡單的實現方法是使用子查詢。

然而，隨著復雜性的增加，計算子查詢變得難以閱讀和調試。

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

例如，考慮使用以下子查詢進行數據提取：

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;

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

即使如此，要跟蹤這些查詢仍然很困難，更不用說當你在子查詢中增加更多計算，或者甚至添加更多子查詢時 —— 復雜性增加，使得代碼的可讀性和維護難度隨之增加。

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

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 ;

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

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

以上兩個查詢執行時間相同，結果如下所示：

公共表表達式（CTE）本質上允許您根據查詢結果創建一個臨時表。這提高了代碼的可讀性和維護性。✅

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

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

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

. . .

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

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

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

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

它們之間的區別在于當某些記錄出現并列時就變得明顯。

當結果表中存在重復行時，為每條記錄分配整數的行為和方式會有所不同。✅

接下來，我們將通過一個虛構的銷售數據集示例，按運費降序列出所有產品類別和送貨地址。

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');

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

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

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

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

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

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

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

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

. . .

CASE WHEN 語句

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

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

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

例如，讓我們再次使用虛構的銷售數據，根據數量將銷售訂單分類為高、中、低量級。

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;

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

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

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

CASE 語句的另一個經常使用但較少為人知的用途是 — 數據透視。

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

有時你處理的數據是長格式的（行數 > 列數），而你需要將其轉換為寬格式（列數 > 行數）。

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

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

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，我們為每個運送地址創建了單獨的列，以獲得以下期望的輸出：

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

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

. . .

根據日期-時間列提取數據

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

當數據集中沒有單獨的月份列時，你需要從數據中的日期時間變量中提取所需的日期部分。

不同的SQL環境有不同的函數來提取日期的部分。通常，在MySQL中，你應該了解以下函數：

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)

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

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

如果你用的是SQLite DB Browser，你需要使用strftime()函數來提取日期部分，如下所示。你需要在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()函數，則用以下代碼：

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函數時應該使用的關鍵字：

最后但不可或缺的是：

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

. . .

自連接（SELF JOIN）

與SQL中的其他連接一樣，唯一的區別就是——在自連接中你是將表和自身進行連接。

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

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

例如，創建一個像下面的虛擬員工數據集（Dummy_Employees）：

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

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;

正如預期，它返回了處理的訂單數量超過他們經理的員工——Abdul和Maria。

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

. . .

結論（Conclusion）

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

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