假设我的表看起来像这样:
Sessions
start_dts (datetime)
end_dts (datetime)
数据如下所示:
start_dts end_dts
12/25/2011 01:55:00 12/25/2011 03:30:00
我需要查询结果如下所示:
Date Hour MinutesOnline
12/25/2011 0 0
12/25/2011 1 5
12/25/2011 2 60
12/25/2011 3 30
... (every hour of the date range being queried)
这甚至可以通过单个查询实现吗?
这是一个不错的开始。 这将适用于任何日期/时间范围。但是,它有一个主要先决条件:您需要创建一个带有dt_hr datetime
字段的intervals
表,其中包含您正在扫描的所有间隔。
Ex: '2011-12-25 00:00:00',
'2011-12-25 01:00:00',
'2011-12-25 02:00:00',
'2011-12-25 03:00:00',
. . .
'2011-12-25 23:00:00'
-
SELECT DATE_FORMAT(intervals.dt_hr,'%m/%d/%Y') AS Date,
EXTRACT(HOUR FROM intervals.dt_hr) AS Hour,
CASE
WHEN intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))
AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
THEN 60
WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))
AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
THEN 60 - EXTRACT(MINUTE FROM s2.start_dts)
WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
AND intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))
THEN EXTRACT(MINUTE FROM s2.end_dts)
WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))
AND intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
THEN EXTRACT(MINUTE FROM s2.end_dts) - EXTRACT(MINUTE FROM s2.start_dts)
ELSE 0
END AS MinutesOnLine
FROM intervals
LEFT JOIN sessions s2
ON intervals.dt_hr >= TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts))
AND intervals.dt_hr <= TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
若要生成intervals
表,可以创建一个存储过程,该过程创建具有date_hour序列的临时表。 有关执行此操作的方法,请参阅获取两个日期之间的日期列表。
与其说是回答,不如说是大评论。
MySQL做得不够好,但是一个 CTE,用于提出一组介于 start_dts 和 end_dts 之间的日期时间所以你得到
startTime endTime
12/25/2011 01:00:00 12/25/2011 02:00:00
12/25/2011 02:00:00 12/25/2011 03:00:00
12/25/2011 03:00:00 12/25/2011 04:00:00
加入回CT上的会话。结束时间
然后是小时(CTS.开始时间) - 小时(sessions.start_dts)和时间差的模量(以分钟为单位)在 CTE.endtime 和 start_dts
或。。。