MySQL在正常表格中连续N天计数

admin

MySQL在正常表格中连续N天计数

sql

我想计算特定用户在给定日期之前和之后连续N天开会的情况。

例如：计算ID为1的用户在2013年1月16日的连续开会天数。

我在这里和这里都找到了一些很好的答案，但是表的格式不是上面的示例那样正常，因此我无法弄清楚如何为我的场合实现它。

示例表结构如下：

CREATE TABLE IF NOT EXISTS `meetings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `meetings_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `meeting_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `meeting_id` (`meeting_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Constraints for table `meetings_users`
--
ALTER TABLE `meetings_users`
  ADD CONSTRAINT `meetings_users_ibfk_2` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `meetings_users_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

样品插入

INSERT INTO  `users` ( `id` ) VALUES (1)

INSERT INTO `meetings` ( `id`, `time` ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00')


INSERT INTO `meetings_users` ( `id`, `meeting_id`, `user_id` ) VALUES 
(1, 1, 1), 
(2, 2, 1), 
(3, 3, 1)

所需的输出：

*+---------+-----------------+
| user_id | consecutive_days |
+---------+------------------+
|       1 | 3                |
+---------+------------------+

阅读 172

2021-07-01

共1个答案

admin

这样的事情怎么样。我希望可以在没有子查询的情况下将其重写，但是我必须要动脑子…（为了适应变化的要求，对数据集和查询进行了修改）

DROP TABLE IF EXISTS meetings;
CREATE TABLE IF NOT EXISTS meetings 
( meeting_id int(10) unsigned NOT NULL AUTO_INCREMENT
, meeting_time datetime NOT NULL
, PRIMARY KEY (meeting_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS meetings_users;
CREATE TABLE IF NOT EXISTS meetings_users 
( user_id int(10) unsigned NOT NULL
, meeting_id int(10) unsigned NOT NULL
, PRIMARY KEY (meeting_id,user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users 
( user_id int(10) unsigned NOT NULL AUTO_INCREMENT
, PRIMARY KEY (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO  users ( user_id ) VALUES (1),(2),(3),(4);

INSERT INTO meetings ( meeting_id, meeting_time ) VALUES 
(1, '2013-01-14 10:00:00'), 
(2, '2013-01-15 10:00:00'), 
(3, '2013-01-16 10:00:00'),
(4, '2013-01-17 10:00:00'),
(5, '2013-01-18 10:00:00'),
(6, '2013-01-19 10:00:00'),
(7, '2013-01-20 10:00:00'),
(8, '2013-01-14 12:00:00');


INSERT INTO meetings_users (meeting_id, user_id ) VALUES 
(1, 1), 
(2, 1),
(2, 3),
(3, 1),
(3, 3),
(4, 2),
(4, 3), 
(5, 2), 
(6, 1),
(1, 8);

SET @dt = '2013-01-15';

SELECT user_id
     , start
     , DATEDIFF(@dt,start)+1 cons
  FROM
     (
       SELECT a.user_id
            , a.meeting_date Start
            , MIN(c.meeting_date) End
        , DATEDIFF(MIN(c.meeting_date),a.meeting_date)  + 1 diff
         FROM (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) a
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) b
       ON b.user_id = a.user_id
          AND a.meeting_date = b.meeting_date + INTERVAL 1 DAY
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) c
       ON c.user_id = a.user_id
          AND a.meeting_date <= c.meeting_date
         LEFT
         JOIN (SELECT DISTINCT mu.user_id,DATE(m.meeting_time) meeting_date FROM meetings_users mu JOIN meetings m ON m.meeting_id = mu.meeting_id) d
           ON d.user_id = a.user_id
          AND c.meeting_date = d.meeting_date - INTERVAL 1 DAY
        WHERE b.meeting_date IS NULL
      AND c.meeting_date IS NOT NULL
          AND d.meeting_date IS NULL
        GROUP
       BY a.user_id
        , a.meeting_date
     ) x
 WHERE @dt BETWEEN start AND end;
 +---------+------------+------+
 | user_id | start      | cons |
 +---------+------------+------+
 |       1 | 2013-01-14 |    2 |
 |       3 | 2013-01-15 |    1 |
 +---------+------------+------+

2021-07-01