postgresql - 如何进行PostgreSQL完全加入:按天计数事件数

我在统计一天内发生并解决的事故数量。存储为“意外”的数据如下所示。每个发生的事故都有唯一的事故问题ID，以及解决该问题的雇主ID。请注意，有些事故并没有在发生的同一天得到解决，还有一些事故是同时发生的。

INSERT INTO Accidents (empid, accident_issue, accident_date, resolved_date) VALUES
('abcd', 'a49b0a4k', '3/12/19 13:25', '3/12/19 13:37'),
('abcd', 'ao3jbvna', '2/1/19 21:16', '2/1/19 21:19'),
('abcd', 'g4b04kcd', '12/12/18 20:37', '12/12/18 21:34'),
('abcd', 'hk9502jb', '12/10/18 21:09', '12/10/18 21:13'),
('abcd', 'cj9rj4vb', '11/30/18 19:44', '11/30/18 19:49'),
('abcd', 'd948mafg', '11/24/18 19:53',  '11/26/18 19:55'),
('abcd', 'mkgiud84', '11/24/18 12:48',  '11/25/18 14:37'),
('abcd', 'it93hvmv', '11/24/18 12:48',  '11/25/18 15:29'),
('efgh', '94jbniv4', '5/17/18 19:56', '5/17/18 20:11'),
('efgh', '5k0bnck5', '4/13/18 15:07', '4/13/18 15:13'),
('efgh', 'mborj3hf', '2/28/18 21:32', '2/28/18 21:51'),
('efgh', 'vkrok4mn', '2/21/18 16:19', '2/21/18 16:35'),
('efgh', '2ivj39cn', '2/20/18 22:01', '2/20/18 22:06'),
('efgh', '0virj3mv', '2/20/18 16:21', '2/20/18 16:23'),
('efgh', 'x20xzn93', '2/9/18 21:16', '2/10/18 21:30'),
('efgh', '49jcn3k5', '2/6/18 19:35', '2/8/18 22:36');

我希望查询结果能显示出每个雇主每天分配和解决的事故数量。
我最初的想法是先分别计算出事故数量和每天解决的事故数量，然后将两个表完全合并。
这是我到目前为止一直在工作的代码。

SELECT
    a.empid,
    a.date,
    a.number_of_accidents,
    b.number_resolved
FROM
    (SELECT A1.empid, A1.accident_issue, to_char(accident_date::date, 'yyyy-mm-dd') as date,
              count(accident_date) as number_of_accidents
     FROM Accidents as A1
       GROUP BY A1.empid, A1.accident_issue
     ) AS a
FULL OUTER JOIN
    (SELECT B1.empid, B1.accident_issue, to_char(resolved_date::date, 'yyyy-mm-dd') as date,
              count(resolved_date) as number_resolved
     FROM Accidents as B1
       GROUP BY B1.empid, B1.accident_issue
     ) AS b
    ON a.date = b.date
    GROUP BY a.empid, a.date

当单独运行时，两个表a和b似乎返回了我想要的结果，但是当放在一起时，由于某种原因，输出会损坏并产生多个重复行。
我希望结果如下

| empid |    date   | number_of_accidents | number_solved |
|-------|-----------|---------------------|---------------|
| abcd  |  11/24/18 |           3         |       0       |
| abcd  |  11/25/18 |           0         |       2       |
| abcd  |  2/1/19   |           1         |       1       |
| abcd  |  3/12/19  |           1         |       1       |
| efgh  |  2/20/18  |           2         |       2       |
| efgh  |  2/21/18  |           1         |       1       |

有什么问题，我是不是走对了方向？
任何帮助都将不胜感激。谢谢您！

最佳答案

在子查询中按雇员和日期聚合，并在公共日和雇员时完全联接它们。

SELECT coalesce(o.empid, r.empid) empid,
       coalesce(o.day, r.day) date,
       o.count number_of_accidents,
       r.count number_resolved
       FROM (SELECT a.empid,
                    date_trunc('day', a.accident_date) day,
                    count(*) count
                    FROM accidents a
                    GROUP BY a.empid,
                             date_trunc('day', a.accident_date)) o
            FULL JOIN (SELECT a.empid,
                              date_trunc('day', a.resolved_date) day,
                              count(*) count
                              FROM accidents a
                              GROUP BY a.empid,
                                       date_trunc('day', a.resolved_date)) r
                      ON r.empid = o.empid
                         AND r.day = o.day;

关于postgresql - 如何进行PostgreSQL完全加入:按天计数事件数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55670190/