我有一个包含2个重要列的表,一个包含日期和时间,另一个包含秒数(因此是整数)。
我希望获得一周中的每一天以及一天中的每一小时所拥有的秒数。
我设法在一周的每一天中获得24行信息,其中一列为dow,另一列为小时,最后一秒为秒。我想拥有的只是它的7行,其中一列是道琼斯指数,另一列是一个数组或大约24秒的东西。
关于如何摆脱它的任何想法?
我结束的查询是:
SELECT dow, hour, sum(duration/60) as minutes
FROM( SELECT duration,
extract( 'dow' from datetime ) as dow,
extract( 'hour' from datetime ) as hour
FROM "statistics"
WHERE datetime between '2013-10-01' and '2013-11-01'
and duration >= 300
) as foo
GROUP BY dow, hour
ORDER BY dow, hour
这给了我这样的结果:
dow | hour | minuts
-----+------+--------
0 | 0 | 742
0 | 1 | 572
0 | 2 | 634
0 | 3 | 208
0 | 4 | 333
0 | 5 | 302
0 | 6 | 183
0 | 7 | 108
0 | 8 | 135
0 | 9 | 201
0 | 10 | 369
0 | 11 | 429
0 | 12 | 340
0 | 13 | 439
0 | 14 | 572
0 | 15 | 420
0 | 16 | 636
0 | 17 | 958
0 | 18 | 878
0 | 19 | 1176
0 | 20 | 995
0 | 21 | 740
0 | 22 | 783
0 | 23 | 857
1 | 0 | 474
1 | 1 | 576
1 | 2 | 431
1 | 3 | 157
1 | 4 | 140
1 | 5 | 55
1 | 6 | 17
1 | 8 | 118
1 | 9 | 356
1 | 10 | 994
1 | 11 | 930
1 | 12 | 1177
1 | 13 | 1228
1 | 14 | 896
1 | 15 | 1072
1 | 16 | 1254
1 | 17 | 1764
1 | 18 | 1303
1 | 19 | 1176
1 | 20 | 1314
1 | 21 | 1003
1 | 22 | 1161
1 | 23 | 811
...
尝试扔array_agg()
,例如:
WITH
monthly_stats as (
SELECT duration,
extract( 'dow' from datetime ) as dow,
extract( 'hour' from datetime ) as hour
FROM "statistics"
WHERE datetime >= '2013-10-01'
AND datetime < '2013-10-01' + interval '1 month'
AND duration >= 300
),
daily_stats as (
SELECT dow, hour, sum(duration/60) as minutes
FROM monthly_stats
GROUP BY dow, hour
)
SELECT dow, array_agg(minutes ORDER BY hour)
FROM daily_stats
GROUP BY dow
ORDER BY dow
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句