每月全天的每小时的总和利用率 [英] sum totals utilization for each hour in day for all day in month
问题描述
我有使用PrestoDB的以下SQL查询:
I have the following SQL query using PrestoDB:
SELECT
date_trunc('month',TimeFrom) AS Month,
date_trunc('hour',TimeFrom) AS HourFrom,
date_trunc('hour',TimeTo) AS HourTo,
CASE
WHEN resource = 'c81d9eb67e93' THEN 'S'
WHEN resource = 'cce1eee4c73f' THEN 'L'
WHEN resource = 'fbc1d63e0f15' THEN 'G'
WHEN resource = '5ef75e433db2' THEN 'Q'
WHEN resource = '4b6ba65cbe14' THEN 'T'
WHEN resource = 'df0a8b60467d' THEN 'R'
WHEN resource = '013472b63651' THEN 'C'
WHEN resource = '7464b77d93be' THEN 'F'
WHEN resource = '6f1186bdbc12' THEN 'M'
ELSE 'other resource'
END AS Resource,
COUNT (*) AS Utilization
FROM (SELECT
resource,
date_add('hour', i - 1, mytable.TimeFrom) AS TimeFrom,
date_add('hour', i, mytable.TimeFrom) AS TimeTo
FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) AS numbers
INNER JOIN (SELECT
resource,
date_trunc(
'hour',
FROM_UNIXTIME(timefrom)
) AS TimeFrom,
date_trunc(
'hour',
(FROM_UNIXTIME(timeto) + interval '45' minute)
) AS TimeTo
FROM
reservation
WHERE
type = 'create'
ORDER BY
date_trunc(
'day',
FROM_UNIXTIME(timefrom)
),
resource) AS mytable ON numbers.i <= date_diff('hour', TimeFrom, TimeTo)
ORDER BY
TimeFrom,
resource)
GROUP BY
date_trunc('month',TimeFrom),
date_trunc('hour',TimeFrom),
date_trunc('hour',TimeTo),
resource
它返回如下所示的表:
Month | HourFrom | HourTo | Resource | Utilization
["2015-08-01 00:00:00.000","2015-08-05 14:00:00.000","2015-08-05 15:00:00.000","S",4]
["2015-08-01 00:00:00.000","2015-08-05 17:00:00.000","2015-08-05 18:00:00.000","Q",1]
["2015-08-01 00:00:00.000","2015-08-05 17:00:00.000","2015-08-05 18:00:00.000","Q",1]
["2015-08-01 00:00:00.000","2015-08-05 18:00:00.000","2015-08-05 19:00:00.000","S",5]
["2015-08-01 00:00:00.000","2015-08-05 20:00:00.000","2015-08-05 21:00:00.000","Q",1]
["2015-08-01 00:00:00.000","2015-08-05 21:00:00.000","2015-08-05 22:00:00.000","Q",1]
["2015-08-01 00:00:00.000","2015-08-05 22:00:00.000","2015-08-05 23:00:00.000","Q",1]
["2015-08-01 00:00:00.000","2015-08-06 00:00:00.000","2015-08-06 01:00:00.000","L",2]
["2015-08-01 00:00:00.000","2015-08-06 00:00:00.000","2015-08-06 01:00:00.000","Q",2]
["2015-08-01 00:00:00.000","2015-08-06 02:00:00.000","2015-08-06 03:00:00.000","S",3]
正如你可以看到HourFrom和HourTo列按天分割。有没有办法聚合他们,以便我有一个月中的所有日子每小时的总数/总数?
As you can see the HourFrom and HourTo columns get split up by days. Is there a way to aggregate them so that I have the sum/total count of each hour on all days in a month?
所以说我有一月份的数据。 2月,我们只有上午1点到4点开放,只有2个资源,那么我想得到的决赛桌看起来像这样(即Jan1AM - Jan2AM给出了在该时间段内的所有天的利用率[总] 1月在一行):
So say I have data for January & February and we are only open from 1AM-4AM and there are only 2 resources then the final table that I would like to get would look something like this(i.e Jan1AM - Jan2AM gives me utilization number[total] for all days during that time frame in January in one row):
Month | HourFrom | HourTo | Resource | Utilization
Jan Jan 1AM Jan 2AM S number
Jan Jan 1AM Jan 2AM X number
Jan Jan 2AM Jan 3AM S number
Jan Jan 2AM Jan 3AM X number
Jan Jan 3AM Jan 4AM S number
Jan Jan 3AM Jan 4AM X number
Feb Feb 1AM Feb 2AM S number
Feb Feb 1AM Feb 2AM X number
Feb Feb 2AM Feb 3AM S number
Feb Feb 2AM Feb 3AM X number
Feb Feb 3AM Feb 4AM S number
Feb Feb 3AM Feb 4AM X number
如何更改原始查询以执行这样的操作?
How would I change my original query to do something like this?
推荐答案
我将开始一个更简单的查询,使用24小时制的小时,不打扰填补缺少的时间。这将是:
I would start with a simpler query that uses a 24-hour clock for the hours and doesn't bother to fill in missing hours. This would be:
SELECT date_trunc('month', TimeFrom) AS Month,
hour(TimeFrom) as HourFrom,
(CASE WHEN resource = 'c81d9eb67e93' THEN 'S'
WHEN resource = 'cce1eee4c73f' THEN 'L'
WHEN resource = 'fbc1d63e0f15' THEN 'G'
WHEN resource = '5ef75e433db2' THEN 'Q'
WHEN resource = '4b6ba65cbe14' THEN 'T'
WHEN resource = 'df0a8b60467d' THEN 'R'
WHEN resource = '013472b63651' THEN 'C'
WHEN resource = '7464b77d93be' THEN 'F'
WHEN resource = '6f1186bdbc12' THEN 'M'
ELSE 'other resource'
END) AS Resource,
COUNT(*) AS Utilization
FROM reservation r
WHERE type = 'create'
GROUP BY date_trunc('month', TimeFrom), hour(TimeFrom), resource;
也许这个版本足以完成你想要完成的工作。
Perhaps this version is sufficient for what you are tyring to accomplish.
这篇关于每月全天的每小时的总和利用率的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!