mySQL，多个 COUNT INNER JOINs 与 GROUP BY DATE()

Question

我有一个安装日志表，我想从这个表中选择

1. 状态为 1 的每天安装数
2. 状态为 0 的每天安装数
3. 每天的安装数量，状态为 IN(0, 1)

created_utc 字段是我保存安装 unix 时间戳的地方。

这是我的表结构

CREATE TABLE `installs` (
  `created_utc` int(11) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1' COMMENT '1 - installed, 0 - deleted'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是我的查询，它不能正常工作(我选择 u.id 作为拐杖来执行内部连接......我完全迷路了)

SELECT
    u.id,
    count(pa.created_utc) AS installs_this_day, UNIX_TIMESTAMP(FROM_UNIXTIME(pa.created_utc, '%Y-%m-%d'))*1000 AS time_utc,
    count(pa2.created_utc) AS installs_active_this_day
FROM 
    users u
INNER JOIN installs AS pa ON pa.status = 0
INNER JOIN installs AS pa2 ON pa2.status = 1
WHERE u.id = 1
GROUP BY DATE(from_unixtime(pa.created_utc)), DATE(from_unixtime(pa2.created_utc))

这会导致重复和不正确的计数。

1   45  1430280000000   45
1   36  1430280000000   36
1   117 1430280000000   117
1   72  1430280000000   72
1   36  1430280000000   36
1   531 1430280000000   531
1   144 1430280000000   144
1   36  1430280000000   36
1   18  1430280000000   18
1   27  1430280000000   27
1   9   1430280000000   9

安装表数据示例

INSERT INTO `installs` (`created_utc`, `status`)
VALUES
    (1430320706, 0),
    (1430319316, 0),
    (1430311682, 0),
    (1430311506, 0),
    (1430311498, 0),
    (1430311498, 0),
    (1430311386, 0),
    (1430311370, 0),
    (1430311356, 0),
    (1430311272, 1),
    (1430278530, 1),
    (1430229788, 1);

Answer 1

我认为您只需要条件聚合，而不是两个或三个连接。像这样:

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(pa.created_utc, '%Y-%m-%d'))*1000 AS time_utc,
       sum(pa.status in (0, 1)) as total_installs,
       sum(pa.status = 0) as installs_status0,
       sum(pa.status = 1) as installs_status1
FROM installs pa
GROUP BY UNIX_TIMESTAMP(FROM_UNIXTIME(pa.created_utc, '%Y-%m-%d'))*1000;