MySQL Pivot实现数据透视|极客笔记

MySQL Pivot实现数据透视

1. 引言

数据透视是一种常见的数据分析技术，它可以将原始数据按照不同的维度进行汇总并展示，帮助我们更好地理解数据。在MySQL中，我们可以通过使用PIVOT操作来实现数据透视。

本文将详细介绍MySQL中如何实现数据透视，包括数据源准备、表格转置和数据透视的实现方法。

2. 数据源准备

首先，我们需要准备一个数据源来进行后续的数据透视操作。假设我们有一个sales表，包含了销售数据信息，结构如下所示：

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    region VARCHAR(50),
    date DATE,
    amount DECIMAL(10, 2)
);

我们在表中插入一些数据作为示例：

INSERT INTO sales (id, product_name, region, date, amount)
VALUES
    (1, 'A', 'Region1', '2021-01-01', 100),
    (2, 'A', 'Region2', '2021-01-01', 200),
    (3, 'B', 'Region1', '2021-01-01', 150),
    (4, 'B', 'Region2', '2021-01-01', 250),
    (5, 'A', 'Region1', '2021-01-02', 300),
    (6, 'A', 'Region2', '2021-01-02', 400),
    (7, 'B', 'Region1', '2021-01-02', 350),
    (8, 'B', 'Region2', '2021-01-02', 450);

现在，我们已经准备好了数据源，接下来将介绍如何使用MySQL实现数据透视。

3. 表格转置

在进行数据透视之前，我们需要先进行表格的转置操作，将原始数据表格转置成我们想要的形式。

假设我们的目标是按照产品名称作为列，按照地区分组，查询每个地区对应的销售金额，我们可以将表格转置成以下形式：

Region	Product A	Product B
Region1	Amount	Amount
Region2	Amount	Amount

我们可以通过以下SQL语句实现表格的转置：

SELECT
    region,
    SUM(CASE WHEN product_name = 'A' THEN amount END) AS `Product A`,
    SUM(CASE WHEN product_name = 'B' THEN amount END) AS `Product B`
FROM
    sales
GROUP BY
    region;

以上SQL语句中，我们将原始表格按照产品名称进行分组，并使用SUM函数计算销售金额。通过CASE WHEN语句，我们可以将不同产品名称对应的销售金额置于不同的列中。

4. 数据透视

有了转置后的表格，我们就可以进行数据透视操作了。在MySQL中，可以通过使用PIVOT语句来实现数据透视。

假设我们的目标是按照日期作为列，按照产品名称分组，查询每个产品每天的销售金额，我们可以使用以下SQL语句进行数据透视：

SELECT
    product_name,
    SUM(CASE WHEN date = '2021-01-01' THEN amount END) AS `2021-01-01`,
    SUM(CASE WHEN date = '2021-01-02' THEN amount END) AS `2021-01-02`
FROM
    (SELECT
        region,
        product_name,
        date,
        amount
    FROM
        sales
    ) AS t
PIVOT
    (
    SUM(amount)
    FOR date IN ('2021-01-01', '2021-01-02')
    ) AS p
GROUP BY
    product_name;

以上SQL语句中，我们首先通过子查询获取到转置后的表格数据，并将其命名为t。然后，在t表格上使用PIVOT语句，将日期作为列，并使用SUM函数计算对应的销售金额。通过FOR date IN ('2021-01-01', '2021-01-02')语句，我们可以指定要展示的日期。最后，我们按照产品名称分组，查询每个产品每天的销售金额。