mysql - 大量sum()的SQL性能

Question

在我的 J2EE Web 应用程序中，我需要生成一个条形图，表示系统中具有特定警报的用户的百分比。 (编辑 - 我忘了提及，该图仅处理与每个用户的第一个情况相关的警报，因此是 min(date) )。

我的数据库架构的简化(但结构相似)版本如下:

users { id, name }
situations { id, user_id, date }
alerts { id, situation_id,  alertA, alertB }

其中，用户到情况为1-n，情况到警报为1-1。

我省略了数据类型，但警报(alertA 和 B)是 bool 值。在我的实际案例中，有很多这样的警报(大约 30 个)。

到目前为止，这就是我的想法:

select sum(alerts.alertA), sum(alerts.alertB)
form alerts, (
    select id, min(date)
    from situations
    group by user_id) as situations
where situations.id = alerts.situation_id;

然后将这些总和除以

select count(users.id) from users;

这似乎远非理想。

您关于如何改进查询的建议/建议将非常感激(或者也许我需要重新考虑我的数据库架构)...

谢谢

安东尼

PS。我还考虑在警报表更新时使用触发器来刷新图表特定表，但我想这是不同查询的主题(如果结果证明有问题)。

Answer 1

首先，再次考虑一下您的架构。您将有很多不同的警报，并且您可能不想为每一个警报添加一列。

考虑将您的 alerts 表更改为类似 { id,ituation_id, type, value } 的内容，其中 type 将为 (A ,B,C,....) 和 value 将是您的 bool 值。

计算百分比的任务将分为:

(1)统计用户总数:

SELECT COUNT(id) AS total FROM users

(2)找到每个用户的“第一个”情况:

SELECT situations.id, situations.user_id
-- selects the minimum date for every user_id
FROM (SELECT user_id, MIN(date) AS min_date
      FROM situations
      GROUP BY user_id) AS first_situation
-- gets the situations.id for user with minimum date
JOIN situations ON
  first_situation.user_id = situations.user_id AND
  first_situation.min_date = situations.date
-- limits number of situations per user to 1 (possible min_date duplicates)
GROUP BY user_id

(3) 统计在子查询中至少一种情况下设置警报的用户:

SELECT
  alerts.type,
  COUNT(situations.user_id)
FROM ( ... situations.user_id, situations.id ... ) AS situations
JOIN alerts ON
  situations.id = alerts.situation_id
WHERE
  alerts.value = 1
GROUP BY
  alerts.type

将这三个步骤放在一起，得到如下内容:

SELECT
  alerts.type,
  COUNT(situations.user_id)/users.total
FROM (SELECT situations.id, situations.user_id
      FROM (SELECT user_id, MIN(date) AS min_date
            FROM situations
            GROUP BY user_id) AS first_situation
      JOIN situations ON
        first_situation.user_id = situations.user_id AND
        first_situation.min_date = situations.date
      GROUP BY user_id
     ) AS situations
JOIN alerts ON
  situations.id = alerts.situation_id
JOIN (SELECT COUNT(id) AS total FROM users) AS users
WHERE
  alerts.value = 1
GROUP BY
  alerts.type

所有查询都是我在未经测试的情况下写出来的。即使它们的工作原理并不完全一样，您仍然应该明白!