为什么当我做两个连接时 COUNT 的结果是双倍的? [英] Why are the result of COUNT double when I do two join?

问题描述

我有这张桌子

设备

 id      name         groupId     serviceId
791   Mamie Ortega      205         1832

群组

 id   serviceId
205     1832

记录

 id          date                      deviceId
792   2017-07-13 13:30:19.740360         784
793   2017-07-13 13:30:19.742799         784

警报

 id    status    deviceId
241      new        784
242      new        784 

我正在运行此查询

SELECT device.id, device.name, COUNT(records.id) AS "last24HMessagesCount", COUNT(alarms.id) AS "activeAlarmsCount"
FROM device
  INNER JOIN "group" AS "group" ON "device"."groupId" = "group"."id" AND "group"."id" = '205'
  LEFT OUTER JOIN "record" AS "records" ON "device"."id" = "records"."deviceId" AND "records"."date" > '2017-07-12 11:43:02.838 +00:00'
  LEFT OUTER JOIN "alarm" AS "alarms" ON "device"."id" = "alarms"."deviceId" AND "alarms"."status" = 'new'
WHERE "device"."serviceId" = 1832
GROUP BY device.id;

这给了我这个结果

 id      name       last24HMessagesCount      activeAlarmsCount   
791   Mamie Ortega         4                          4

这个结果是错误的，last24HMessagesCount 和 activeAlarmsCount 应该是 2.

This result is wrong, I'm supposed to have 2 for last24HMessagesCount and activeAlarmsCount.

如果我删除计数之一，例如 last24HMessagesCount 并执行

If I remove one of the count, last24HMessagesCount for example and execute

SELECT device.id, device.name, COUNT(alarms.id) AS "activeAlarmsCount"
FROM device
  INNER JOIN "group" AS "group" ON "device"."groupId" = "group"."id" AND "group"."id" = '205'
  LEFT OUTER JOIN "alarm" AS "alarms" ON "device"."id" = "alarms"."deviceId" AND "alarms"."status" = 'new'
WHERE "device"."serviceId" = 1832
GROUP BY device.id;

结果正确

 id      name       activeAlarmsCount   
791   Mamie Ortega         2

我不明白，为什么计数翻了一番?

I do not understand, why are the counts double?

推荐答案

这很容易回答.你有两个record 和两个alarm.您加入这些并获得四条记录，您可以计算这些记录.

This is very simple to answer. You have two record and two alarm. You join these and get four records, which you count.

您可以通过计算不同的 ID 来解决此问题:

You can workaround this problem by counting distinct IDs:

COUNT(DISTINCT records.id) AS "last24HMessagesCount",
COUNT(DISTINCT alarms.id) AS "activeAlarmsCount"

但我不推荐这个.你为什么要加入record和alarm?它们没有直接关系.你想要加入的是record的数量和alarm的数量.所以在加入之前聚合:

but I would not recommend this. Why do you join record and alarm anyway? They are not directly related. What you want to join is the number of record and the number of alarm. So aggregate before joining:

SELECT 
  device.id, 
  device.name, 
  records.cnt AS "last24HMessagesCount", 
  alarms.cnt AS "activeAlarmsCount"
FROM device
LEFT OUTER JOIN 
(
  SELECT deviceId, count(*) AS cnt
  FROM record
  WHERE "date" > '2017-07-12 11:43:02.838 +00:00'
  GROUP BY deviceId
) AS records ON device.id = records.deviceId
LEFT OUTER JOIN 
(
  SELECT deviceId, count(*) AS cnt
  FROM alarm
  WHERE status = 'new'
  GROUP BY deviceId
) AS alarms ON device.id = alarms.deviceId
WHERE device.serviceId = 1832
  AND device.groupId = 205;

(我已经删除了对组"表的不必要的连接.)

(I've removed the unnecessary join to the "group" table.)

这篇关于为什么当我做两个连接时 COUNT 的结果是双倍的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！