Wie kann ich die Ausgabe einer Pivot-Tabelle in MySQL zurückgeben?

Question

Wenn ich eine MySQL-Tabelle habe, die etwa so aussieht:

company\_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

Ist es möglich, eine MySQL-Abfrage auszuführen, um eine Ausgabe wie diese zu erhalten:

company\_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

Die Idee ist, dass pagecount kann variieren, so dass die Anzahl der Ausgabespalten dies widerspiegeln sollte, eine Spalte für jede action / pagecount Paar und dann die Anzahl der Treffer pro company_name . Ich bin mir nicht sicher, ob dies eine Pivot-Tabelle genannt wird, aber jemand hat das vorgeschlagen?

Answer 1

Dies bedeutet im Grunde est eine Pivot-Tabelle.

Eine schöne Anleitung, wie man dies erreicht, finden Sie hier: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

Ich empfehle Ihnen, diesen Beitrag zu lesen und diese Lösung an Ihre Bedürfnisse anzupassen.

Update

Nachdem der obige Link derzeit nicht mehr verfügbar ist, fühle ich mich verpflichtet, einige zusätzliche Informationen für alle, die hier nach Antworten zu mysql pivot suchen, bereitzustellen. Es hatte wirklich eine riesige Menge an Informationen, und ich werde nicht alles von dort in hier (noch mehr, da ich einfach nicht wollen, um ihre riesigen Wissen zu kopieren), aber ich werde einige Ratschläge, wie man mit Pivot-Tabellen die Sql-Weg im Allgemeinen mit dem Beispiel von peku, der die Frage in den ersten Platz gefragt geben.

Vielleicht kommt der Link bald wieder, ich werde die Augen offen halten.

Der Weg der Tabellenkalkulation...

Viele Leute verwenden für diesen Zweck einfach ein Tool wie MSExcel, OpenOffice oder andere Tabellenkalkulationsprogramme. Das ist eine gute Lösung. Kopieren Sie die Daten einfach dorthin und verwenden Sie die Werkzeuge, die die grafische Benutzeroberfläche bietet, um dieses Problem zu lösen.

Aber... das war nicht die Frage, und es könnte sogar zu einigen Nachteilen führen, z.B. wie man die Daten in die Tabellenkalkulation bekommt, problematische Skalierung und so weiter.

Der SQL-Weg...

Seine Tabelle sieht in etwa wie folgt aus:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Schauen Sie nun in seine/ihre gewünschte Tabelle:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

Die Zeilen ( EMAIL , PRINT x pages ) ähneln den Bedingungen. Die Hauptgruppierung ist nach company_name .

Um die Bedingungen zu schaffen, schreit dies geradezu nach der Verwendung des CASE -Erklärung. Um nach etwas zu gruppieren, also, verwenden Sie ... GROUP BY .

Die Basis-SQL, die dieses Pivot liefert, kann etwa so aussehen:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

Dies sollte sehr schnell das gewünschte Ergebnis bringen. Der große Nachteil dieses Ansatzes: Je mehr Zeilen Sie in Ihrer Pivot-Tabelle haben wollen, desto mehr Bedingungen müssen Sie in Ihrer SQL-Anweisung definieren.

Auch damit kann man umgehen, deshalb neigt man dazu, vorbereitete Anweisungen, Routinen, Zähler und dergleichen zu verwenden.

Einige zusätzliche Links zu diesem Thema:

• http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• https://codingsight.com/pivot-tables-in-mysql/

Answer 2

Meine Lösung ist in T-SQL ohne Pivots:

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

Answer 3

Für MySQL können Sie die Bedingungen direkt in SUM() Funktion und es wird ausgewertet als Boolescher Wert 0 o 1 und so können Sie Ihre Zählung auf der Grundlage Ihrer Kriterien durchführen, ohne dass Sie IF/CASE Aussagen

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

Answer 4

Für dynamisches Schwenken verwenden Sie GROUP_CONCAT con CONCAT . Die GRUPPE_CONCAT verkettet Zeichenketten aus einer Gruppe zu einer einzigen Zeichenkette mit verschiedenen Optionen.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')

    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

HIER DEMO

Answer 5

A Standard-SQL Version mit boolesche Logik :

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

_db<>Gefiedel aquí_
_{Alte <a href="http://sqlfiddle.com/#!9/1fdc5b/110" rel="nofollow noreferrer">sqlfiddle</a>}

Wie?

TRUE OR NULL ergibt TRUE .
FALSE OR NULL ergibt NULL .
NULL OR NULL ergibt NULL .
Und COUNT zählt nur Nicht-Null-Werte. Voilà.