Sql of note/Pivoting in sql: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
No edit summary |
No edit summary |
||
Line 30: | Line 30: | ||
) | ) | ||
group by 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> | </pre> |
Revision as of 21:31, 7 November 2018
thdata:
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
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 )