mysql:总计、唯一、返回用户计数

Question

我有下表:

CREATE TABLE zoom
    (`id` int, `fb_id` int, `date` datetime);

INSERT INTO zoom
    (`id`, `fb_id`, `date`)
VALUES
    (1, 1123, '2009-01-01 00:00:00'),
    (2, 1145, '2009-01-01 00:00:00'),
    (3, 1123, '2009-01-02 00:00:00'),
    (4, 1198, '2009-01-02 00:00:00'),
    (5, 1176, '2009-01-02 00:00:00'),
    (6, 1176, '2009-01-03 00:00:00'),
    (7, 1123, '2009-01-03 00:00:00'),
    (8, 1177, '2009-01-03 00:00:00'),
    (9, 1200, '2009-01-03 00:00:00');

这是我到目前为止所拥有的 mysql 代码:

SELECT count(Znew.fb_id) as totalUsers,
count(Zold.fb_id) as returningUsers,
count(Znew.fb_id) - count(Zold.fb_id) as uniqueUsers,
DATE_FORMAT(Znew.date, '%d %b %y') as zoom
FROM zoom Znew
LEFT JOIN zoom Zold
ON Zold.date < Znew.date
AND Zold.fb_id = Znew.fb_id
GROUP BY Znew.date;

运行上述代码后，我得到以下结果:

totalUsers  returningUsers  uniqueUsers zoom
    2             0              2      01 Jan 09
    3             1              2      02 Jan 09
    5             3              2      03 Jan 09

在上面的结果中，只有前两行是正确的，所以基本上我的mysql代码只适用于2个组，第三行显然是错误的。

这是我运行代码后期望的正确结果:

totalUsers  returningUsers  uniqueUsers zoom
    2             0              2      01 Jan 09
    3             1              2      02 Jan 09
    4             2              2      03 Jan 09

有什么想法吗？sqlfiddle:http://sqlfiddle.com/#!9/c5e2a/1

Answer 1

您只是到处缺少一些DISTINCT关键字。

SELECT count(distinct Znew.fb_id) as totalUsers,
count(distinct Zold.fb_id) as returningUsers,
count(distinct Znew.fb_id) - count(distinct Zold.fb_id) as uniqueUsers,
DATE_FORMAT(Znew.date, '%d %b %y') as zoom
FROM zoom Znew
LEFT JOIN zoom Zold
ON Zold.date < Znew.date
AND Zold.fb_id = Znew.fb_id
GROUP BY Znew.date;