按月透视 MySQL 中的数据 [英] Pivot Data in MySQL by Month

问题描述

我有一个包含这些字段的表格(型号、日期、价格、数量)我需要一个 SQL(到 MySQL)，它给我一个动态表，其中包含型号名称、数量和总销售额(价格 * 数量)，但按月(日期)分隔.例如.

I have a table with this fields (model, date, price, quantity) I need a SQL (to MySQL) that gives me a dynamic table with the model name, the quantity and the gross sales (price*quantity), but separated by month (date). For example.

            April           |         May          |     June
Model |quantity| Gross Sales|  quantity|Gross Sales| quantity | Gross Sales
  1       6         50           4        40           5            45
  2       10         30          3        9            5            15

谢谢！！

更新:

表格:

    CREATE TABLE `sales` (
      `model` varchar DEFAULT NULL,
      `price` int DEFAULT NULL,
      `quantity` int(2) DEFAULT NULL,
      `date` timestamp
    )

推荐答案

这很棘手，也许有更好的方法可以做到，但这是我的两分钱，注意分组列名在 MySQL 中是不可能的，因为只有列名和列名的关系是1:1.

This is quite tricky, maybe there's a better way to do it but here's my two cents, note that the grouped column names is impossible in MySQL as there is only the relationship between columns and column names is 1:1.

下面是一些代码，它生成测试数据，然后建立一个过程，该过程依赖于动态 SQL 来生成返回所需结果所需的查询，该查询也是动态执行的.请注意，代码运行时应该不会出错，但对 DROP 语句进行注释以防止对现有结构造成不必要的损坏.过程 RANDBETWEEN 在最终存储过程中不需要，但用于生成测试数据.

Below is some code, It generates test data then builds up a procedure that relies on dynamic SQL to generate the query necessary to return the results you require which is also executed dynamically. Note that the code should run without error but DROP statements are commented to prevent unwanted damage to your existing structure. The procedure RANDBETWEEN isn't needed in the final stored procedure but is used for generating test data.

#DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
      `model` varchar(64) DEFAULT NULL,
      `price` int DEFAULT NULL,
      `quantity` int(2) DEFAULT NULL,
      `date` datetime
    );

SET @VMinModel := 1;
SET @VMaxModel := 5;

SET @VMinPrice := 10;
SET @VMaxPrice := 100;

SET @VMinQuantity := 1;
SET @VMaxQuantity := 10;

SET @VMinTime := UNIX_TIMESTAMP('2016-01-01');
SET @VMaxTime := UNIX_TIMESTAMP('2016-12-31');

#DROP FUNCTION RANDBETWEEN;

DELIMITER $

CREATE FUNCTION RANDBETWEEN(VMin INTEGER UNSIGNED, VMax INTEGER UNSIGNED) RETURNS INTEGER UNSIGNED
DETERMINISTIC
BEGIN

    RETURN ROUND(RAND() * (VMax - VMin) + VMin);

END$

DELIMITER ;

INSERT INTO `sales` SELECT RANDBETWEEN(@VMinModel, @VMaxModel), RANDBETWEEN(@VMinPrice, @VMaxPrice), RANDBETWEEN(@VMinQuantity, @VMaxQuantity), FROM_UNIXTIME(RANDBETWEEN(@VMinTime, @VMaxTime)) FROM information_schema.COLUMNS LIMIT 1000;

SET @VXDimension := 'model';
SET @VYDimension := 'DATE_FORMAT(date, ''%Y-%m'')';
SET @VMeasures := 'quantity, quantity * price';
SET @VTable := 'sales';

#DROP PROCEDURE IF EXISTS singleDimensionMeltAndCast;

DELIMITER $

CREATE PROCEDURE singleDimensionMeltAndCast (VXDimension CHAR(255), VYDimension CHAR(255), VMeasures CHAR(255), VTable CHAR(255)) 
BEGIN

    SET SESSION group_concat_max_len = 65536;

    DROP TEMPORARY TABLE IF EXISTS YDimensionValues;
    SET @VCreateYDimensionValueTable := CONCAT('CREATE TEMPORARY TABLE YDimensionValues SELECT DISTINCT ', VYDimension, ' AS YDim1 FROM ', VTable, ' ORDER BY ', VYDimension, ' ASC;');

    PREPARE stmt FROM @VCreateYDimensionValueTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DROP TEMPORARY TABLE IF EXISTS Measures;
    CREATE TEMPORARY TABLE Measures (measure CHAR(255));
    SET @VCreateMeasuresTable := CONCAT('INSERT INTO Measures VALUES(''', REPLACE(VMeasures, ', ', '''), ('''), ''')');

    PREPARE stmt FROM @VCreateMeasuresTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @VYDimensionSQLFieldsComponent = (SELECT GROUP_CONCAT('SUM(IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)) `[', YDim1, ' x ',  measure, ']`' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);
    SET @VYDimensionSQLGroupComponent = (SELECT GROUP_CONCAT('IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);

    SET @VFinalQuery := CONCAT('SELECT ', VXDimension, ', ', @VYDimensionSQLFieldsComponent, ' FROM ', VTable, ' GROUP BY ', @VYDimensionSQLGroupComponent, ' ORDER BY ', VXDimension, ';');
    PREPARE stmt FROM @VFinalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$

DELIMITER ;

CALL singleDimensionMeltAndCast(@VXDimension, @VYDimension, @VMeasures, @VTable);

如果您有任何问题，请告诉我.

Let me know if you have any questions.

问候，

詹姆斯

这篇关于按月透视 MySQL 中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！