SQL 是管理和分析關(guān)系數(shù)據(jù)庫的基本工具。掌握基本的SQL命令能夠完成簡單的數(shù)據(jù)查詢和操作，但是如果想從數(shù)據(jù)中提取更有價(jià)值的信息，數(shù)據(jù)分析工作者和開發(fā)人員應(yīng)該深入學(xué)習(xí)和掌握高級的SQL技巧。

1 窗口函數(shù)

窗口函數(shù)是指在SQL查詢中對一組相關(guān)行進(jìn)行聚合或運(yùn)算操作的函數(shù)。窗口函數(shù)可以在不改變基本表的情況下，為查詢結(jié)果添加額外的計(jì)算列。舉個(gè)例子，使用SUM()函數(shù)與OVER()子句計(jì)算銷售額的運(yùn)行總和。

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS running_total
FROM sales_data;

2 公共表表達(dá)式（CTEs）

CTE（Common Table Expressions，公共表表達(dá)式）是一種在SQL查詢中創(chuàng)建臨時(shí)結(jié)果集的方法，可以被多次引用，提高查詢的可讀性和可維護(hù)性。以下是如何使用CTE計(jì)算每個(gè)產(chǎn)品類別的總收入的示例。

WITH category_revenue AS (
    SELECT category, SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY category
)
SELECT * FROM category_revenue;

3 遞歸查詢

遞歸查詢能夠幫助分析師遍歷層次化數(shù)據(jù)結(jié)構(gòu)，如組織圖或物料清單。假設(shè)這里有一個(gè)表示員工關(guān)系的表，想查找某個(gè)經(jīng)理的所有下屬：

WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 'manager_id_of_interest'
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

4 透視表

透視表將行轉(zhuǎn)換為列，以表格形式匯總數(shù)據(jù)。比如，有一個(gè)包含銷售數(shù)據(jù)的表格，想通過數(shù)據(jù)透視來顯示每個(gè)產(chǎn)品在不同月份的總銷售額：

SELECT *
FROM (
    SELECT product, month, sales
    FROM sales_data
) AS source_table
PIVOT (
    SUM(sales)
    FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May')
) AS pivot_table;

5 分析函數(shù)

分析函數(shù)根據(jù)一組記錄計(jì)算匯總值。例如，可以使用 ROW_NUMBER() 函數(shù)為數(shù)據(jù)集中的每條記錄分配唯一的行號。

SELECT customer_id, order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders;

6 解透視

解透視是透視操作的反向操作，解透視是將一張表中的列轉(zhuǎn)換為行，而透視是將行轉(zhuǎn)換為列。比如，這里有一個(gè)按月匯總銷售數(shù)據(jù)的表格，想取消透視以分析隨時(shí)間變化的趨勢。

SELECT product, month, sales
FROM (
    SELECT 'Jan' AS month, product, sales_jan AS sales FROM sales_data
    UNION ALL
    SELECT 'Feb' AS month, product, sales_feb AS sales FROM sales_data
    UNION ALL
    SELECT 'Mar' AS month, product, sales_mar AS sales FROM sales_data
) AS unpivoted_sales;

7 條件聚合

條件聚合是指根據(jù)指定條件應(yīng)用條件聚合函數(shù)。例如，如果想計(jì)算老客戶訂單的平均銷售額：

SELECT customer_id, 
       AVG(CASE WHEN order_count > 1 THEN order_total ELSE NULL END) AS avg_sales_repeat_customers
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(order_total) AS order_total
    FROM orders
    GROUP BY customer_id
) AS customer_orders;

8 日期函數(shù)

SQL中的日期函數(shù)支持操縱和提取與日期相關(guān)的信息。例如，可以使用DATE_TRUNC()函數(shù)按月對銷售數(shù)據(jù)進(jìn)行分組。

SELECT DATE_TRUNC('month', order_date) AS month, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', order_date);

9 合并語句

合并語句（也稱為 UPSERT 或 ON DUPLICATE KEY UPDATE）可讓分析師根據(jù)與源表的連接結(jié)果在目標(biāo)表中插入、更新或刪除記錄。比如，要同步兩個(gè)包含客戶數(shù)據(jù)的表。

MERGE INTO target_table AS t
USING source_table AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email) VALUES (s.customer_id, s.name, s.email);

10 情況語句

情況語句支持在SQL查詢中應(yīng)用條件邏輯。例如，使用情況語句根據(jù)客戶的總購買金額對其進(jìn)行分類。

SELECT customer_id,
       CASE
           WHEN total_purchase_amount >= 1000 THEN 'Platinum'
           WHEN total_purchase_amount >= 500 THEN 'Gold'
           ELSE 'Silver'
       END AS customer_category
FROM (
    SELECT customer_id, SUM(order_total) AS total_purchase_amount
    FROM orders
    GROUP BY customer_id
) AS customer_purchases;