时间戳

首页 > 解决方案 > MySQL - 让用户在日期之间的位置

mysql - MySQL - 让用户在日期之间的位置

问题描述

我有一个日志表，其中包含某个位置的用户进出日志。

我需要创建一个返回以下数据的查询：

在两个日期（FromDate 和 ToDate）之间访问该站点的所有用户

对于此查询，用户在两个日期之间访问了该站点，如果：

这些是我的一些尝试：

SELECT UserId, 
       MAX(EventDate) as lastAction,
       MAX(CASE WHEN InOrOut = 1 THEN EventDate END) as lastEntrance,
       MAX(CASE WHEN InOrOut = 0 THEN EventDate END) as lastExit
    FROM site_entrances
    WHERE siteId = 35
    GROUP BY UserId
    HAVING (lastExit IS NULL AND lastEntrance <= '2020-02-17 00:00:00') OR (lastEntrance IS NULL AND  lastExit >= '2020-01-17 00:00:00') OR (lastEntrance <= '2020-02-17 00:00:00' AND lastExit >= '2020-01-17 00:00:00');

这个问题是：如果有最新入口在ToDate之后，但存在在此查询中有效的旧入口，则将其忽略...

SELECT t.* 
FROM site_entrances as t, (SELECT site_entrances.UserId, MAX(EventDate) as 'latest'
                                     FROM site_entrances 
                                     WHERE siteid = 35
                                     AND site_entrances.EventDate <= '2020-02-17 09:26:10'
                                     GROUP BY UserId) as maxDate
WHERE t.UserId= maxDate.UserId
AND t.EventDate = maxDate.latest
AND (t.InOrOut = 1 OR (t.InOrOut = 0 AND t.EventDate >= '2020-01-17 00:00:00'))

这是我使用的一个，它对我有用。问题是它很慢......

我认为第一个需要一些正确方向的指导，但这对我来说是完美的，但是。正确方向的一点将不胜感激。

编辑

创建表：

create table site_entrances (
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    EventDate DateTime NOT NULL,
    InOrOut TINYINT NOT NULL,
    SiteId INT NOT NULL,
    UserId VARCHAR(13) NOT NULL,
    INDEX (SiteId, EventDate, UserId) 
);

插入：

insert into rtls.site_entrances (EventDate, InOrOut, SiteId, UserId) 
values
# entrance before ToDate and exit after FromDate -> valid
('2020-01-02 00:01:00', 1, 35, 'a'),
('2020-04-01 00:01:00', 0, 35, 'a'),

# entrance and exit in range -> valid
('2020-01-18 00:01:00', 1, 35, 'b'),
('2020-01-19 00:01:00', 0, 35, 'b'),
('2020-03-18 00:01:00', 1, 35, 'b'), # not relevant to the query

# entrances without exits but they are befor FromDate -> valid
('2020-01-01 00:01:00', 1, 35, 'c1'),
('2020-01-20 00:01:00', 1, 35, 'c2'),

# entrance before ToDate and exit after FromDate -> valid
('2020-01-01 00:01:00', 1, 35, 'd'),
('2020-02-01 00:01:00', 0, 35, 'd'),

# exits without entrances but they are after FromDate -> valid
('2020-03-01 00:01:00', 0, 35, 'e1'),
('2020-02-01 00:01:00', 0, 35, 'e2'),

# entrance before ToDate and exit after FromDate -> valid
('2020-01-20 00:01:00', 1, 35, 'f'),
('2020-04-20 00:01:00', 0, 35, 'f'),

# entrance before ToDate but exit not after FromDate -> not valid
('2020-01-01 00:01:00', 1, 35, 'x'),
('2020-01-03 00:01:00', 0, 35, 'x'),

# exit after FromDate but entrance  not before ToDate -> not valid
('2020-04-01 00:01:00', 1, 35, 'y'),
('2020-04-02 00:01:00', 0, 35, 'y');

我正在做的搜索是在日期之间：'2020-01-17 00:00:00' AND '2020-02-17 00:00:00'

预期结果：

UserId | Entrance              | Exit
d      | '2020-01-01 00:01:00' | '2020-02-01 00:01:00'
a      | '2020-01-02 00:01:00' | '2020-04-01 00:01:00'
b      | '2020-01-18 00:01:00' | '2020-01-19 00:01:00'
c1     | '2020-01-01 00:01:00' | NULL
c2     | '2020-01-20 00:01:00' | NULL
f      | '2020-01-20 00:01:00' | '2020-04-20 00:01:00'
e2     | NULL                  | '2020-02-01 00:01:00'
e1     | NULL                  | '2020-03-01 00:01:00'

标签： mysql

解决方案

三个单独的查询：

  • 两个事件都存在
SELECT t1.UserId, t1.EventDate Entrance, t2.EventDate `Exit`
FROM site_entrances t1
JOIN site_entrances t2
WHERE t1.UserId = t2.UserId
  AND t1.InOrOut = 1
  AND t2.InOrOut = 0
  AND t1.EventDate < t2.EventDate
  AND ( @startdate < t2.EventDate AND t1.EventDate < @enddate )
  AND NOT EXISTS ( SELECT NULL
                   FROM site_entrances t3
                   WHERE t1.UserId = t3.UserId
                     AND t1.EventDate < t3.EventDate
                     AND t3.EventDate < t2.EventDate )
  • 只进门
SELECT t1.UserId, t1.EventDate Entrance, NULL `Exit`
FROM site_entrances t1
WHERE EventDate < @enddate
  AND InOrOut = 1
  AND NOT EXISTS ( SELECT NULL
                   FROM site_entrances t2
                   WHERE t1.UserId = t2.UserId
                     AND t1.EventDate < t2.EventDate )
  • 只是出口
SELECT t1.UserId, NULL Entrance, t1.EventDate `Exit`
FROM site_entrances t1
WHERE @startdate < EventDate 
  AND InOrOut = 0
  AND NOT EXISTS ( SELECT NULL
                   FROM site_entrances t2
                   WHERE t1.UserId = t2.UserId
                     AND t1.EventDate > t2.EventDate )

检查它们。如果一切正确，则将它们联合起来。

小提琴

推荐阅读