mysql - 将超过一个小时的持续时间逐小时插入到另一个表中

Question

请耐心等待，我的英语并不完美

我已经设置了一个 mysql 表来将日志聚合到其中:

create table logs(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
player_id MEDIUMINT UNSIGNED NOT NULL,
`in` DATETIME NOT NULL,
`out` DATETIME NOT NULL,
channel_id INT UNSIGNED NOT NULL,
frame_id INT UNSIGNED NOT NULL,
media_id INT UNSIGNED NOT NULL
PRIMARY KEY (id)

我在 in 和 out 之间使用 timediff 来查明文件播放了多长时间以及开始播放的时间。它变得有点大，所以我决定将需要的信息汇总到新表中:

create table reports(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
year SMALLINT UNSIGNED NOT NULL,
month TINYINT UNSIGNED NOT NULL,
day TINYINT UNSIGNED NOT NULL,
hour TINYINT UNSIGNED NOT NULL,
player_id MEDIUMINT UNSIGNED NOT NULL,
channel_id INT UNSIGNED NOT NULL,
frame_id INT UNSIGNED NOT NULL,
media_id INT UNSIGNED NOT NULL,
count MEDIUMINT UNSIGNED NOT NULL,
duration MEDIUMINT UNSIGNED NOT NULL

我使用以下查询来填写报告表:

insert into reports (year, month, day, hour, player_id, media_id, channel_id, frame_id, count, duration)
select year(logs.in), month(logs.in) as Month, day(logs.in) as Day, hour(logs.in) as Hour, logs.player_id, logs.media_id, logs.channel_id, logs.frame_id, count(logs.media_id) as Count, SUM(TIME_TO_SEC(TIMEDIFF(logs.out, logs.in))) as Duration 
FROM logs
where (logs.player_id=1 OR logs.player_id=2)
GROUP BY year(logs.in), month(logs.in), day(logs.in), hour(logs.in), player_id, media_id, channel_id, frame_id;

如果 timediff 小于一个小时，它工作正常。但是一种媒体可以播放的时间远不止几分钟:

+-------+------+------+-----------+------------+----------+----------+----------------------+--------------+
| Month | Day  | Hour | player_id | channel_id | frame_id | media_id | count(logs.media_id) | Duration     |
+-------+------+------+-----------+------------+----------+----------+----------------------+--------------+
|     6 |   19 |   14 |         5 |          4 |       18 |       54 |                    1 | 275h 48m 24s |
|     6 |   20 |    8 |         4 |          3 |       18 |       54 |                    1 | 78h 45m 28s  |

因此，如果我检查该文件是否在 6 月 20 日在 player_id 5 上播放，我的查询将显示它不是，尽管它是。我想要实现的是像这样填写我的报告表:

+-------+------+------+-----------+------------+----------+----------+----------------------+--------------+
| Month | Day  | Hour | player_id | channel_id | frame_id | media_id | count(logs.media_id) | Duration     |
+-------+------+------+-----------+------------+----------+----------+----------------------+--------------+
|     6 |   19 |   14 |         5 |          4 |       18 |       54 |                    1 |      48m 24s |
|     6 |   19 |   15 |         5 |          4 |       18 |       54 |                    1 |  1h  00m 00s |
|     6 |   19 |   16 |         5 |          4 |       18 |       54 |                    1 |  1h  00m 00s |

有办法吗？提前致谢。

Answer 1

好的，我找到了解决方案。首先，我必须创建一个包含时间范围的表。为了方便起见，我使用存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_intervals`(startdate 
timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
  (
  interval_start timestamp,
  interval_end timestamp
  );
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd
 (MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;
  insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
  set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END 

然后我根据这个临时表处理我的主表

SELECT DATE(time_intervals.interval_start) AS Date, EXTRACT(HOUR FROM time_intervals.interval_start) AS Hour, player_id, channel_id, frame_id, media_id, count(media_id),
      sum(CASE 
          WHEN time_intervals.interval_start > TIMESTAMPADD(HOUR,HOUR(logs.in), DATE(logs.in)) 
    AND time_intervals.interval_start < TIMESTAMPADD(HOUR,HOUR(logs.out), DATE(logs.out))
    THEN 3600
WHEN time_intervals.interval_start = TIMESTAMPADD(HOUR,HOUR(logs.in), DATE(logs.in)) 
AND time_intervals.interval_start < TIMESTAMPADD(HOUR,HOUR(logs.out), DATE(logs.out))
THEN 3600 - EXTRACT(MINUTE FROM logs.in)*60 - EXTRACT(SECOND FROM logs.in)
 WHEN time_intervals.interval_start = TIMESTAMPADD(HOUR,HOUR(logs.out), DATE(logs.out))
AND time_intervals.interval_start > TIMESTAMPADD(HOUR,HOUR(logs.in), DATE(logs.in)) 
THEN EXTRACT(SECOND FROM logs.out) + EXTRACT(MINUTE FROM logs.out)*60 
WHEN time_intervals.interval_start = TIMESTAMPADD(HOUR,HOUR(logs.in), DATE(logs.in)) 
AND time_intervals.interval_start = TIMESTAMPADD(HOUR,HOUR(logs.out), DATE(logs.out))
THEN EXTRACT(MINUTE FROM logs.out)*60 + EXTRACT(SECOND FROM logs.out) - EXTRACT(MINUTE FROM logs.in)*60 - EXTRACT(SECOND FROM logs.in)
 ELSE 0
 END) AS duration 
FROM time_intervals
LEFT JOIN logs 
Force index(media_id, player_id, channel_id, frame_id)
  ON time_intervals.interval_start >= TIMESTAMPADD(HOUR,HOUR(logs.in), DATE(logs.in)) 
    AND time_intervals.interval_start <= TIMESTAMPADD(HOUR,HOUR(logs.out), DATE(logs.out))
    GROUP BY media_id, player_id, date, hour, channel_id, frame_id;

需要一段时间来处理，尤其是在处理数十亿行的情况下，就像我的情况一样，但是可以。