左连接上的MySQL重复计数

Question

数据库结构

CREATE TABLE installs(
    id INT, PRIMARY KEY(id), 
    created DATETIME)

CREATE TABLE uninstalls(
    id INT, PRIMARY KEY(id),
    created DATETIME,
    install_id INT)

查询("Me vs. The MySQL")

SELECT DATE(installs.created),
  COUNT(installs.id),
  COUNT(uninstall.id)
FROM installs
LEFT JOIN uninstalls ON uninstalls.install_id = installs.id
GROUP BY DATE(installs.created)

“预期”输出

DATE(installs.created) | COUNT(installs.id) | COUNT(uninstalls.id) 
  2012-11-20           | *installs on date* | *uninstalls on date*

所以-我每天查看一行，以及当天发生的安装/卸载次数。

问题所在

“安装”的数据每天都是正确的。但令人遗憾的是，每天的“卸载”数据都是不正确的。

Answer 1

分别对安装和卸载进行计数，为其他计数分别添加一列(由零组成)。然后用UNION将这两个组合在一起，再次按日期分组，并取每个日期的最大值(以消除添加的零)：

SELECT created as date, max(installs) as installs, max(uninstalls) as uninstalls
FROM
  (SELECT created, count(*) AS installs, 0 AS uninstalls
   FROM installs
   GROUP BY created
  UNION ALL
   SELECT created, 0 AS installs, count(*) AS uninstalls
   FROM uninstalls
   GROUP BY created) c
GROUP BY created
ORDER BY created

Answer 2

发生此问题是因为您正在链接安装和卸载。

如果您希望查看给定日期的每个事件的计数，则需要按日期连接，而不是按安装ID连接。如果数据集很大，则速度将非常慢(当您根据字段上的函数进行分组或连接时，优化器不能使用该字段上的索引)。

SELECT DATE(installs.created),
  COUNT(installs.id),
  COUNT(uninstall.id)
FROM installs 
LEFT JOIN uninstalls ON DATE(uninstalls.created) = DATE(installs.created)
GROUP BY DATE(installs.created)

Answer 3

创建一个包含所有安装和卸载日期的CTE，然后连接到单独的安装表和卸载表。

; WITH Dates AS
(
    SELECT DATE(created) AS created
    FROM
    (
        SELECT created
        FROM installs

            UNION ALL

        SELECT created
        FROM uninstalls
    ) a
    GROUP BY DATE(created)
)
, InstallCTE AS
(
    SELECT DATE(created) AS created
        , COUNT(*) AS c
    FROM installs
    GROUP BY DATE(created)
)
, UninstallCTE AS
(
    SELECT DATE(created) AS created
        , COUNT(*) AS c
    FROM uninstalls
    GROUP BY DATE(created)
)
SELECT d.created AS [date]
    , i.c AS install_count
    , u.c AS uninstall_count
FROM Dates d
LEFT JOIN InstallCTE i
    ON d.created = i.created
LEFT JOIN UninstallCTE u
    ON d.created = u.created