software:mysq:pivots
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
software:mysq:pivots [2020/11/12 07:37] – rodolico | software:mysq:pivots [2022/11/09 23:07] (current) – removed rodolico | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Create a pivot result in mariadb/ | ||
- | This relies heavily on [[https:// | ||
- | |||
- | A pivot query (aka crosstab) is a type of query where the top, where you normally have column names, is the result of the query. Some database engines have a special //PIVOT// function built in for this, but MySQL/ | ||
- | |||
- | Take this data structure (stolen from Taryn' | ||
- | |||
- | <code sql create_test.sql> | ||
- | CREATE TABLE clients | ||
- | (`clID` int, `clName` varchar(5)) | ||
- | ; | ||
- | |||
- | INSERT INTO clients | ||
- | (`clID`, `clName`) | ||
- | VALUES | ||
- | (1, ' | ||
- | (2, ' | ||
- | (3, ' | ||
- | ; | ||
- | |||
- | CREATE TABLE scores | ||
- | (`ID` int, `clID` int, `PlayDate` datetime, `Score` numeric(10, | ||
- | ; | ||
- | |||
- | INSERT INTO scores | ||
- | (`ID`, `clID`, `PlayDate`, `Score`) | ||
- | VALUES | ||
- | (1, 2, ' | ||
- | (2, 2, ' | ||
- | (3, 3, ' | ||
- | (4, 3, ' | ||
- | (5, 3, ' | ||
- | (6, 1, ' | ||
- | (7, 1, ' | ||
- | ; | ||
- | |||
- | </ | ||
- | |||
- | A simple query to find everyone' | ||
- | |||
- | <code sql> | ||
- | select | ||
- | | ||
- | | ||
- | | ||
- | from | ||
- | | ||
- | join scores using (clID) | ||
- | group by | ||
- | | ||
- | | ||
- | order by | ||
- | | ||
- | | ||
- | </ | ||
- | |||
- | which would return | ||
- | |||
- | < | ||
- | +--------+---------------------+------------+ | ||
- | | clName | PlayDate | ||
- | +--------+---------------------+------------+ | ||
- | | Chris | 2012-01-12 00:00:00 | 0.01220 | | ||
- | | Chris | 2012-01-13 00:00:00 | 0.00530 | | ||
- | | Gale | 2012-01-23 00:00:00 | | ||
- | | Donna | 2012-01-24 00:00:00 | 0.00020 | | ||
- | | Gale | 2012-01-24 00:00:00 | 0.10110 | | ||
- | | Donna | 2012-01-26 00:00:00 | | ||
- | | Donna | 2012-01-27 00:00:00 | 0.00010 | | ||
- | +--------+---------------------+------------+ | ||
- | </ | ||
- | |||
- | But, in many cases, a pivot makes it more readable to humans. A pivot of the above data could be | ||
- | < | ||
- | +---------------------+--------+---------+---------+ | ||
- | | playdate | ||
- | +---------------------+--------+---------+---------+ | ||
- | | 2012-01-12 00:00:00 | 0.0122 | 0 | 0 | | ||
- | | 2012-01-13 00:00:00 | 0.0053 | 0 | 0 | | ||
- | | 2012-01-23 00:00:00 | 0 | -0.0125 | 0 | | ||
- | | 2012-01-24 00:00:00 | 0 | 0.1011 | 0.0002 | | ||
- | | 2012-01-26 00:00:00 | 0 | 0 | -0.0056 | | ||
- | | 2012-01-27 00:00:00 | 0 | 0 | 0.0001 | | ||
- | +---------------------+--------+---------+---------+ | ||
- | </ | ||
- | |||
- | If you already know all the values for clName, you can create a static query, creating a column for each of them. | ||
- | |||
- | <code sql> | ||
- | select s.playdate, | ||
- | sum(case when clname = ' | ||
- | sum(case when clname = ' | ||
- | sum(case when clname = ' | ||
- | from clients c | ||
- | inner join scores s | ||
- | on c.clid = s.clid | ||
- | group by s.playdate; | ||
- | </ | ||
- | |||
- | What this does is creates three columns for the actual score; one for each clName, then only adds the ones where clName matches. The sql CASE is excellent. | ||
- | |||
- | However, if you add a new user, you must change your query. You can do that via a script, by dynamically building the above query, or you can use a prepared statement to do build a dynamic SQL statement all within the database engine. | ||
- | |||
- | What we would like to do is create the three lines separating out the scores for Chris, Gale and Donna dynamically, | ||
- | <code sql> | ||
- | SELECT | ||
- | GROUP_CONCAT(DISTINCT | ||
- | CONCAT( | ||
- | ' | ||
- | clName, | ||
- | ''' | ||
- | clName, ' | ||
- | ) | ||
- | ) | ||
- | FROM clients; | ||
- | </ | ||
- | |||
- | At this point, you have built this part of the query | ||
- | < | ||
- | sum(case when clname = ' | ||
- | sum(case when clname = ' | ||
- | sum(case when clname = ' | ||
- | </ | ||
- | |||
- | We use the INTO clause to place that into a variable named @SQL | ||
- | <code sql> | ||
- | SET @sql = NULL; | ||
- | SELECT | ||
- | GROUP_CONCAT(DISTINCT | ||
- | CONCAT( | ||
- | ' | ||
- | clName, | ||
- | ''' | ||
- | clName, ' | ||
- | ) | ||
- | ) INTO @sql | ||
- | FROM clients; | ||
- | </ | ||
- | |||
- | The variable @SQL now contains that information, | ||
- | |||
- | <code sql pivot.sql> | ||
- | /* https:// | ||
- | |||
- | SET @sql = NULL; | ||
- | SELECT | ||
- | GROUP_CONCAT(DISTINCT | ||
- | CONCAT( | ||
- | ' | ||
- | clName, | ||
- | ''' | ||
- | clName, ' | ||
- | ) | ||
- | ) INTO @sql | ||
- | FROM clients; | ||
- | |||
- | SET @sql | ||
- | = CONCAT(' | ||
- | from clients c | ||
- | inner join scores s | ||
- | on c.clid = s.clid | ||
- | group by s.playdate' | ||
- | |||
- | PREPARE stmt FROM @sql; | ||
- | EXECUTE stmt; | ||
- | DEALLOCATE PREPARE stmt; | ||
- | </ | ||
- | |||
- | run the above and you'll see the same result. Now, let's add Mary to the mix and add a couple of her scores. | ||
- | |||
- | <code sql> | ||
- | insert into clients values (4, ' | ||
- | insert into scores (`ID`, `clID`, `PlayDate`, `Score`) | ||
- | | ||
- | (8, | ||
- | (9, | ||
- | (10, | ||
- | </ | ||
- | |||
- | Running the static example, we still only calculate Chris, Gale and Donna. We would have to add a new clause to get Mary. But, if we run the dynamic query, Mary is automatically added to the report. | ||
- | |||
- | < | ||
- | +---------------------+--------+---------+---------+--------+ | ||
- | | playdate | ||
- | +---------------------+--------+---------+---------+--------+ | ||
- | | 2012-01-12 00:00:00 | 0.0122 | 0 | 0 | 0 | | ||
- | | 2012-01-13 00:00:00 | 0.0053 | 0 | 0 | 0.0567 | | ||
- | | 2012-01-23 00:00:00 | 0 | -0.0125 | 0 | 0 | | ||
- | | 2012-01-24 00:00:00 | 0 | 0.1011 | 0.0002 | 0.1715 | | ||
- | | 2012-01-26 00:00:00 | 0 | 0 | -0.0056 | 0 | | ||
- | | 2012-01-27 00:00:00 | 0 | 0 | 0.0001 | 0 | | ||
- | +---------------------+--------+---------+---------+--------+ | ||
- | </ | ||
- | For additional things you can do with pivot' | ||
- | |||
- | ===== Links ===== | ||
- | * [[https:// | ||
- | * [[https:// |