Sql of note/Pivoting in sql: Difference between revisions

From Federal Burro of Information
Jump to navigationJump to search
(Created page with " thdata: <pre> sqlite> select * from thedata limit 10; collecttime|sensor1|sensor2 1525694702|685|255 1525695002|694|262 1525695302|706|267 1525695602|706|269 1525695902|700|...")
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
On this case I'm using SQLite, but any ANSI sql will work.


thdata:
== The Schema ==
 
<pre>
sqlite> .schema
CREATE TABLE thedata
            (collecttime int, sensor1 int, sensor2 int);
sqlite>
</pre>
 
== The Data ==


<pre>
<pre>
Line 16: Line 26:
1525697402|698|261
1525697402|698|261
</pre>
</pre>
== Preliminary ==
How many record for each hour ?
<pre>
SELECT count(*), hour
from (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
)
group by hour;
</pre>
Intermediate step:
<pre>
select day,
CASE WHEN hour == '21' THEN theavg ELSE 0 END AS twentyone
FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    where
      date ( datetime(collecttime, 'unixepoch', 'localtime') ) == '2018-10-25'
    group by day, hour
    )
</pre>
== The Query ==
<pre>
SELECT day ,
SUM(CASE WHEN hour == '01' THEN theavg ELSE 0 END) AS one,
SUM(CASE WHEN hour == '02' THEN theavg ELSE 0 END) AS two,
SUM(CASE WHEN hour == '03' THEN theavg ELSE 0 END) AS three,
SUM(CASE WHEN hour == '04' THEN theavg ELSE 0 END) AS four,
SUM(CASE WHEN hour == '05' THEN theavg ELSE 0 END) AS five,
SUM(CASE WHEN hour == '06' THEN theavg ELSE 0 END) AS six,
SUM(CASE WHEN hour == '07' THEN theavg ELSE 0 END) AS seven,
SUM(CASE WHEN hour == '08' THEN theavg ELSE 0 END) AS eight,
SUM(CASE WHEN hour == '09' THEN theavg ELSE 0 END) AS nine,
SUM(CASE WHEN hour == '10' THEN theavg ELSE 0 END) AS ten,
SUM(CASE WHEN hour == '11' THEN theavg ELSE 0 END) AS eleven,
SUM(CASE WHEN hour == '12' THEN theavg ELSE 0 END) AS twelve,
SUM(CASE WHEN hour == '13' THEN theavg ELSE 0 END) AS thirteen,
SUM(CASE WHEN hour == '14' THEN theavg ELSE 0 END) AS fourteen,
SUM(CASE WHEN hour == '15' THEN theavg ELSE 0 END) AS fifteen,
SUM(CASE WHEN hour == '16' THEN theavg ELSE 0 END) AS sixteen,
SUM(CASE WHEN hour == '17' THEN theavg ELSE 0 END) AS seventeen,
SUM(CASE WHEN hour == '18' THEN theavg ELSE 0 END) AS eighteen,
SUM(CASE WHEN hour == '19' THEN theavg ELSE 0 END) AS nineteen,
SUM(CASE WHEN hour == '20' THEN theavg ELSE 0 END) AS twenty,
SUM(CASE WHEN hour == '21' THEN theavg ELSE 0 END) AS twentyone,
SUM(CASE WHEN hour == '22' THEN theavg ELSE 0 END) AS twentytwo,
SUM(CASE WHEN hour == '23' THEN theavg ELSE 0 END) AS twentythree,
SUM(CASE WHEN hour == '24' THEN theavg ELSE 0 END) AS twentyfour
FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
)
GROUP BY 1
ORDER BY 1;
</pre>
and the results:
<pre>
2018-10-06|0.0|0.0|0.0|0.0|0.0|0.0|52.0833333333333|323.583333333333|671.083333333333|742.666666666667|850.833333333333|839.25|942.416666666667|855.666666666667|734.25|581.75|625.916666666667|446.166666666667|5.16666666666667|0.0|0.0|0.0|20.0|0
2018-10-07|25.3333333333333|25.5833333333333|25.25|25.4166666666667|25.5833333333333|25.5|157.583333333333|556.416666666667|566.666666666667|597.5|853.333333333333|877.583333333333|940.0|882.5|729.666666666667|592.0|384.833333333333|119.25|0.666666666666667|38.3333333333333|29.0833333333333|29.4166666666667|29.4166666666667|0
2018-10-08|0.0|0.0|0.0|0.0|0.0|0.0|80.25|304.416666666667|529.333333333333|760.083333333333|847.833333333333|926.916666666667|914.0|895.25|840.583333333333|718.333333333333|496.333333333333|249.416666666667|134.166666666667|91.1666666666667|34.4166666666667|36.75|35.5833333333333|0
2018-10-09|0.0|0.0|0.0|0.0|0.0|0.0833333333333333|193.25|606.416666666667|814.666666666667|909.583333333333|1068.25|1412.08333333333|1457.91666666667|1464.33333333333|1450.33333333333|1363.91666666667|847.25|347.083333333333|4.41666666666667|23.4166666666667|25.75|15.1666666666667|25.1666666666667|0
2018-10-10|0.0|0.0|0.0|0.0|0.0|0.0833333333333333|190.083333333333|488.416666666667|615.083333333333|693.666666666667|934.166666666667|1410.66666666667|1466.75|1476.41666666667|1455.5|1355.0|922.083333333333|336.333333333333|2.91666666666667|57.4166666666667|0.0|0.0|0.0|0
2018-10-11|0.0|0.0|0.0|0.0|0.0|0.0|214.916666666667|619.833333333333|889.75|1021.5|1115.25|1254.58333333333|1183.41666666667|1181.5|1095.5|933.25|580.833333333333|252.75|116.833333333333|19.0833333333333|0.0|0.0|0.0|0
2018-10-12|22.9166666666667|0.0|0.0|0.0|0.0|0.0|207.166666666667|500.416666666667|694.916666666667|712.75|1025.16666666667|1174.75|1274.41666666667|1276.25|1262.16666666667|988.166666666667|743.666666666667|190.833333333333|2.25|2.0|43.25|27.9166666666667|27.5833333333333|0
2018-10-13|4.0|0.0|0.0|0.0|0.0|0.0|98.5|522.25|789.833333333333|986.833333333333|979.416666666667|852.75|928.75|1075.0|998.583333333333|1218.25|842.833333333333|256.416666666667|62.25|82.3333333333333|9.0|0.0|26.1666666666667|0
2018-10-14|24.8333333333333|0.0|0.0|0.0|0.0|0.0|106.5|449.833333333333|814.0|963.0|1123.58333333333|1039.66666666667|1358.16666666667|1466.08333333333|1440.66666666667|1300.75|766.416666666667|250.5|63.3333333333333|81.5|29.0833333333333|28.9166666666667|29.0833333333333|0
2018-10-15|0.0|0.0|0.0|0.0|0.0|0.0|95.5|194.416666666667|408.5|454.75|751.75|801.333333333333|889.916666666667|887.5|1022.91666666667|842.916666666667|734.0|248.25|124.75|66.4166666666667|27.9166666666667|28.1666666666667|27.8333333333333|0
</pre>
== Roudning version ==
<pre>
SELECT day ,
ROUND( SUM(CASE WHEN hour == '01' THEN theavg ELSE 0 END) AS one,
ROUND( SUM(CASE WHEN hour == '02' THEN theavg ELSE 0 END) AS two,
ROUND( SUM(CASE WHEN hour == '03' THEN theavg ELSE 0 END) AS three,
ROUND( SUM(CASE WHEN hour == '04' THEN theavg ELSE 0 END) AS four,
ROUND( SUM(CASE WHEN hour == '05' THEN theavg ELSE 0 END) AS five,
ROUND( SUM(CASE WHEN hour == '06' THEN theavg ELSE 0 END) AS six,
ROUND( SUM(CASE WHEN hour == '07' THEN theavg ELSE 0 END) AS seven,
ROUND( SUM(CASE WHEN hour == '08' THEN theavg ELSE 0 END) AS eight,
ROUND( SUM(CASE WHEN hour == '09' THEN theavg ELSE 0 END) AS nine,
ROUND( SUM(CASE WHEN hour == '10' THEN theavg ELSE 0 END) AS ten,
ROUND( SUM(CASE WHEN hour == '11' THEN theavg ELSE 0 END) AS eleven,
ROUND( SUM(CASE WHEN hour == '12' THEN theavg ELSE 0 END) AS twelve,
ROUND( SUM(CASE WHEN hour == '13' THEN theavg ELSE 0 END) AS thirteen,
ROUND( SUM(CASE WHEN hour == '14' THEN theavg ELSE 0 END) AS fourteen,
ROUND( SUM(CASE WHEN hour == '15' THEN theavg ELSE 0 END),2) AS fifteen,
ROUND( SUM(CASE WHEN hour == '16' THEN theavg ELSE 0 END),2) AS sixteen,
ROUND( SUM(CASE WHEN hour == '17' THEN theavg ELSE 0 END),2) AS seventeen,
ROUND( SUM(CASE WHEN hour == '18' THEN theavg ELSE 0 END),2) AS eighteen,
ROUND( SUM(CASE WHEN hour == '19' THEN theavg ELSE 0 END),2) AS nineteen,
ROUND( SUM(CASE WHEN hour == '20' THEN theavg ELSE 0 END),2) AS twenty,
ROUND( SUM(CASE WHEN hour == '21' THEN theavg ELSE 0 END),2) AS twentyone,
ROUND( SUM(CASE WHEN hour == '22' THEN theavg ELSE 0 END),2) AS twentytwo,
ROUND( SUM(CASE WHEN hour == '23' THEN theavg ELSE 0 END),2) AS twentythree,
ROUND( SUM(CASE WHEN hour == '24' THEN theavg ELSE 0 END),2) AS twentyfour
FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
)
GROUP BY 1
ORDER BY 1;
</pre>
Todo:
* validate
* rounding
* graphing

Latest revision as of 03:43, 13 November 2018

On this case I'm using SQLite, but any ANSI sql will work.

The Schema

sqlite> .schema
CREATE TABLE thedata
             (collecttime int, sensor1 int, sensor2 int);
sqlite>

The Data

sqlite> select * from thedata limit 10;
collecttime|sensor1|sensor2
1525694702|685|255
1525695002|694|262
1525695302|706|267
1525695602|706|269
1525695902|700|267
1525696202|700|266
1525696502|697|264
1525696802|695|261
1525697102|694|259
1525697402|698|261

Preliminary

How many record for each hour ? 

SELECT count(*), hour
from (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
)
group by hour;


Intermediate step: 

select day,
 CASE WHEN hour == '21' THEN theavg ELSE 0 END AS twentyone
 FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    where 
      date ( datetime(collecttime, 'unixepoch', 'localtime') ) == '2018-10-25'
    group by day, hour
    )

The Query

SELECT day ,
 SUM(CASE WHEN hour == '01' THEN theavg ELSE 0 END) AS one,
 SUM(CASE WHEN hour == '02' THEN theavg ELSE 0 END) AS two,
 SUM(CASE WHEN hour == '03' THEN theavg ELSE 0 END) AS three,
 SUM(CASE WHEN hour == '04' THEN theavg ELSE 0 END) AS four,
 SUM(CASE WHEN hour == '05' THEN theavg ELSE 0 END) AS five,
 SUM(CASE WHEN hour == '06' THEN theavg ELSE 0 END) AS six,
 SUM(CASE WHEN hour == '07' THEN theavg ELSE 0 END) AS seven,
 SUM(CASE WHEN hour == '08' THEN theavg ELSE 0 END) AS eight,
 SUM(CASE WHEN hour == '09' THEN theavg ELSE 0 END) AS nine,
 SUM(CASE WHEN hour == '10' THEN theavg ELSE 0 END) AS ten,
 SUM(CASE WHEN hour == '11' THEN theavg ELSE 0 END) AS eleven,
 SUM(CASE WHEN hour == '12' THEN theavg ELSE 0 END) AS twelve,
 SUM(CASE WHEN hour == '13' THEN theavg ELSE 0 END) AS thirteen,
 SUM(CASE WHEN hour == '14' THEN theavg ELSE 0 END) AS fourteen,
 SUM(CASE WHEN hour == '15' THEN theavg ELSE 0 END) AS fifteen,
 SUM(CASE WHEN hour == '16' THEN theavg ELSE 0 END) AS sixteen,
 SUM(CASE WHEN hour == '17' THEN theavg ELSE 0 END) AS seventeen,
 SUM(CASE WHEN hour == '18' THEN theavg ELSE 0 END) AS eighteen,
 SUM(CASE WHEN hour == '19' THEN theavg ELSE 0 END) AS nineteen,
 SUM(CASE WHEN hour == '20' THEN theavg ELSE 0 END) AS twenty,
 SUM(CASE WHEN hour == '21' THEN theavg ELSE 0 END) AS twentyone,
 SUM(CASE WHEN hour == '22' THEN theavg ELSE 0 END) AS twentytwo,
 SUM(CASE WHEN hour == '23' THEN theavg ELSE 0 END) AS twentythree,
 SUM(CASE WHEN hour == '24' THEN theavg ELSE 0 END) AS twentyfour
 FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
 )
 GROUP BY 1
 ORDER BY 1;

and the results: 

2018-10-06|0.0|0.0|0.0|0.0|0.0|0.0|52.0833333333333|323.583333333333|671.083333333333|742.666666666667|850.833333333333|839.25|942.416666666667|855.666666666667|734.25|581.75|625.916666666667|446.166666666667|5.16666666666667|0.0|0.0|0.0|20.0|0
2018-10-07|25.3333333333333|25.5833333333333|25.25|25.4166666666667|25.5833333333333|25.5|157.583333333333|556.416666666667|566.666666666667|597.5|853.333333333333|877.583333333333|940.0|882.5|729.666666666667|592.0|384.833333333333|119.25|0.666666666666667|38.3333333333333|29.0833333333333|29.4166666666667|29.4166666666667|0
2018-10-08|0.0|0.0|0.0|0.0|0.0|0.0|80.25|304.416666666667|529.333333333333|760.083333333333|847.833333333333|926.916666666667|914.0|895.25|840.583333333333|718.333333333333|496.333333333333|249.416666666667|134.166666666667|91.1666666666667|34.4166666666667|36.75|35.5833333333333|0
2018-10-09|0.0|0.0|0.0|0.0|0.0|0.0833333333333333|193.25|606.416666666667|814.666666666667|909.583333333333|1068.25|1412.08333333333|1457.91666666667|1464.33333333333|1450.33333333333|1363.91666666667|847.25|347.083333333333|4.41666666666667|23.4166666666667|25.75|15.1666666666667|25.1666666666667|0
2018-10-10|0.0|0.0|0.0|0.0|0.0|0.0833333333333333|190.083333333333|488.416666666667|615.083333333333|693.666666666667|934.166666666667|1410.66666666667|1466.75|1476.41666666667|1455.5|1355.0|922.083333333333|336.333333333333|2.91666666666667|57.4166666666667|0.0|0.0|0.0|0
2018-10-11|0.0|0.0|0.0|0.0|0.0|0.0|214.916666666667|619.833333333333|889.75|1021.5|1115.25|1254.58333333333|1183.41666666667|1181.5|1095.5|933.25|580.833333333333|252.75|116.833333333333|19.0833333333333|0.0|0.0|0.0|0
2018-10-12|22.9166666666667|0.0|0.0|0.0|0.0|0.0|207.166666666667|500.416666666667|694.916666666667|712.75|1025.16666666667|1174.75|1274.41666666667|1276.25|1262.16666666667|988.166666666667|743.666666666667|190.833333333333|2.25|2.0|43.25|27.9166666666667|27.5833333333333|0
2018-10-13|4.0|0.0|0.0|0.0|0.0|0.0|98.5|522.25|789.833333333333|986.833333333333|979.416666666667|852.75|928.75|1075.0|998.583333333333|1218.25|842.833333333333|256.416666666667|62.25|82.3333333333333|9.0|0.0|26.1666666666667|0
2018-10-14|24.8333333333333|0.0|0.0|0.0|0.0|0.0|106.5|449.833333333333|814.0|963.0|1123.58333333333|1039.66666666667|1358.16666666667|1466.08333333333|1440.66666666667|1300.75|766.416666666667|250.5|63.3333333333333|81.5|29.0833333333333|28.9166666666667|29.0833333333333|0
2018-10-15|0.0|0.0|0.0|0.0|0.0|0.0|95.5|194.416666666667|408.5|454.75|751.75|801.333333333333|889.916666666667|887.5|1022.91666666667|842.916666666667|734.0|248.25|124.75|66.4166666666667|27.9166666666667|28.1666666666667|27.8333333333333|0

Roudning version

SELECT day ,
 ROUND( SUM(CASE WHEN hour == '01' THEN theavg ELSE 0 END) AS one,
 ROUND( SUM(CASE WHEN hour == '02' THEN theavg ELSE 0 END) AS two,
 ROUND( SUM(CASE WHEN hour == '03' THEN theavg ELSE 0 END) AS three,
 ROUND( SUM(CASE WHEN hour == '04' THEN theavg ELSE 0 END) AS four,
 ROUND( SUM(CASE WHEN hour == '05' THEN theavg ELSE 0 END) AS five,
 ROUND( SUM(CASE WHEN hour == '06' THEN theavg ELSE 0 END) AS six,
 ROUND( SUM(CASE WHEN hour == '07' THEN theavg ELSE 0 END) AS seven,
 ROUND( SUM(CASE WHEN hour == '08' THEN theavg ELSE 0 END) AS eight,
 ROUND( SUM(CASE WHEN hour == '09' THEN theavg ELSE 0 END) AS nine,
 ROUND( SUM(CASE WHEN hour == '10' THEN theavg ELSE 0 END) AS ten,
 ROUND( SUM(CASE WHEN hour == '11' THEN theavg ELSE 0 END) AS eleven,
 ROUND( SUM(CASE WHEN hour == '12' THEN theavg ELSE 0 END) AS twelve,
 ROUND( SUM(CASE WHEN hour == '13' THEN theavg ELSE 0 END) AS thirteen,
 ROUND( SUM(CASE WHEN hour == '14' THEN theavg ELSE 0 END) AS fourteen,
 ROUND( SUM(CASE WHEN hour == '15' THEN theavg ELSE 0 END),2) AS fifteen,
 ROUND( SUM(CASE WHEN hour == '16' THEN theavg ELSE 0 END),2) AS sixteen,
 ROUND( SUM(CASE WHEN hour == '17' THEN theavg ELSE 0 END),2) AS seventeen,
 ROUND( SUM(CASE WHEN hour == '18' THEN theavg ELSE 0 END),2) AS eighteen,
 ROUND( SUM(CASE WHEN hour == '19' THEN theavg ELSE 0 END),2) AS nineteen,
 ROUND( SUM(CASE WHEN hour == '20' THEN theavg ELSE 0 END),2) AS twenty,
 ROUND( SUM(CASE WHEN hour == '21' THEN theavg ELSE 0 END),2) AS twentyone,
 ROUND( SUM(CASE WHEN hour == '22' THEN theavg ELSE 0 END),2) AS twentytwo,
 ROUND( SUM(CASE WHEN hour == '23' THEN theavg ELSE 0 END),2) AS twentythree,
 ROUND( SUM(CASE WHEN hour == '24' THEN theavg ELSE 0 END),2) AS twentyfour
 FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
 )
 GROUP BY 1
 ORDER BY 1;


Todo:

  • validate
  • rounding
  • graphing
Retrieved from "https://wiki.quadratic.net/index.php?title=Sql_of_note/Pivoting_in_sql&oldid=4039"