Блог

Я пытаюсь подсчитать количество несчастных случаев, произошедших и разрешенных за данный день. Данные, сохраненные как «Несчастные случаи», выглядят примерно так, как показано ниже. Каждой произошедшей аварии присваивается уникальный идентификатор accident_issue и идентификатор работодателя, который ее решил. Обратите внимание, что некоторые аварии не устраняются в тот же день, когда это произошло, и что некоторые аварии произошли одновременно.

 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;