主页 / dba / 问题 / 291005
Accepted
TheDemonLord
Asked: 2021-05-05 14:53:56 +0800 CST

查找一组用户的 Max() 更新时间

  • 772

数据库是 MariaDB 10.3.25

我有 2 列相关：

用户 ID 和 LastUpdate

UserID 由 user@domain LastUpdate 是一个日期字段。

这是我的问题 - 我有当前的查询：

select a.UserID, 
substring_index(a.UserID, '@', -1), 
max(a.lastupdate) 
from MyTable a 
group by a.UserID
having max(a.lastupdate) < '2020-03-31'

这显示了一年多内未更新的所有用户和域。但是，我要考虑以下情况：

UserID LastUpdate
[email protected] 2020-08-16
[email protected] 2019-05-16
[email protected] 2021-05-05

使用当前查询，将捕获 [email protected]，因此域 A.com 将被捕获为未使用，但用户 [email protected] 仍处于活动状态 - 我想做一个 Max(a.lastupdate ) 但将其分组， substring_index(a.UserID, '@', -1) 但也会吐出该域的所有用户。我敢肯定，答案让我眼前一亮……

mysql mariadb

3 个回答

  1. 看看是不是你想要的

    -- this main query retrieve all users under the subquery domain
SELECT a.UserID
    , substring_index(a.UserID, '@', -1)
    , MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (

    -- this subquery search all domain inactive in last 12 months
    SELECT substring_index(a.UserID, '@', -1) AS domain
    FROM MyTable a
    GROUP BY substring_index(a.UserID, '@', -1)
    HAVING MAX(a.lastupdate) < '2020-03-31'

)
ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate
    • 1
  2. Best Answer

    最好的方法可能是以下。该解决方案利用了GENERATED COLUMNMariaDB >= 10.3（也在 MySQL >= 5.7 中）的功能——对于这样的查询非常方便——它还使查询更具可读性。

    下面的所有代码都可以在小提琴上找到：

    CREATE TABLE login
(
  user_id VARCHAR (255) NOT NULL PRIMARY KEY,
  last_login TIMESTAMP NOT NULL,
  domain_name VARCHAR (255) 
    GENERATED ALWAYS AS ((SUBSTRING_INDEX(user_id, '@', -1))) VIRTUAL,  -- can be STORED

  INDEX (last_login),  -- indexing up to you if you have lots of data...
  INDEX (domain_name)
);

    然后是一些示例记录：

    INSERT INTO login (user_id, last_login) 
VALUES 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'), 
('[email protected]', '2021-03-06'),
('[email protected]', '2020-03-20'), 
('[email protected]', '2020-03-15'), 
('[email protected]', '2020-03-14'), 
('[email protected]', '2020-02-12'), 
('[email protected]', '2020-01-31');

    然后运行查询：

    SELECT * FROM 
(
  SELECT domain_name, MAX(last_login) AS last_login_by_domain
  FROM login
  GROUP BY domain_name
) AS tab
WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
ORDER BY last_login_by_domain DESC;

-- DESC because you might want to deal with recent ones more urgently?
-- renew subscription... whatever

    结果：

    domain_name   last_login_by_domain       
     xyz.ie    2020-03-20 00:00:00

    这适用于来自 dbfiddle.uk（一个很好的资源）的 MariaDB >= 10.3 的所有版本以及 MySQL >= 5.7 的版本。

    它也适用于 MySQL 的 ONLY_FULL_GROUP_BY 设置！这非常重要 - 如果未设置该变量，则查询可能（并且将- 也将在此处和此处查看）返回错误结果（请参阅我对此处对该问题的其他答案的评论）。

    举个例子，看看小提琴的底部，我已经指出了在这个（相对简单的）案例中另一个答案出现的问题 - 在一个复杂的陈述中，这个问题可能导致各种难以发现错误 - 警告购买者！

    编辑：查看对问题的评论后（小提琴）：

    SELECT user_id 
FROM login
WHERE domain_name IN
(
  SELECT domain_name FROM
  (
    SELECT 
      domain_name, MAX(last_login) AS last_login_by_domain
    FROM login
    GROUP BY domain_name
  ) AS tab
  WHERE last_login_by_domain < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
);

    结果：

    user_id
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
    • 1

  3. （主要问题由子查询处理，如其他答案中所见。这是一些附带问题。）

    INDEX(UserID, lastupdated)

    将有助于性能。

    为什么不这样做

    HAVING MAX(a.lastupdate) < NOW() - INTERVAL 1 YEAR

    这样您就不必不断更改查询。

    • 0

相关问题