在MySQL中如何進(jìn)行多維數(shù)據(jù)透視分析

在 MySQL 中，可以使用 SQL 語句和內(nèi)置函數(shù)對多維數(shù)據(jù)進(jìn)行透視分析

首先，創(chuàng)建一個(gè)示例表格，包含銷售數(shù)據(jù)：

CREATE TABLE sales_data (
    product_id INT,
    region VARCHAR(255),
    month VARCHAR(255),
    revenue DECIMAL(10, 2)
);

接下來，向表格中插入一些示例數(shù)據(jù)：

INSERT INTO sales_data (product_id, region, month, revenue)
VALUES (1, 'North', 'January', 1000.00),
       (1, 'North', 'February', 1200.00),
       (1, 'South', 'January', 800.00),
       (1, 'South', 'February', 900.00),
       (2, 'North', 'January', 1500.00),
       (2, 'North', 'February', 1600.00),
       (2, 'South', 'January', 1300.00),
       (2, 'South', 'February', 1400.00);

現(xiàn)在，我們將使用 CASE 語句和聚合函數(shù)（如 SUM()）對數(shù)據(jù)進(jìn)行多維透視。以下是一個(gè)查詢示例，展示了如何根據(jù)產(chǎn)品 ID、地區(qū)和月份對銷售收入進(jìn)行透視：

SELECT product_id, region,
       SUM(CASE WHEN month = 'January' THEN revenue ELSE 0 END) AS January_Revenue,
       SUM(CASE WHEN month = 'February' THEN revenue ELSE 0 END) AS February_Revenue
FROM sales_data
GROUP BY product_id, region;

這將返回以下結(jié)果：

+------------+----------+---------------+----------------+
| product_id | region   | January_Revenue | February_Revenue |
+------------+----------+---------------+----------------+
|         1  | North    | 1000.00        | 1200.00         |
|         1  | South    | 800.00         | 900.00          |
|         2  | North    | 1500.00        | 1600.00         |
|         2  | South    | 1300.00        | 1400.00         |
+------------+----------+---------------+----------------+

通過調(diào)整 CASE 語句和分組條件，你可以根據(jù)需要對數(shù)據(jù)進(jìn)行不同的多維透視分析。