Суммарное суммирование по нулевым значениям
Я попытался вычислить столбец накопленной суммы, чтобы найти текущих рабочих в каждом месяце, но получаю NULL вместо нынешнего сотрудника, как в предыдущем месяце.
Стол сотрудников:
id date_started date_terminated
1 01-Apr-14 NULL
2 21-Apr-14 NULL
3 11-Apr-14 NULL
4 01-Apr-14 NULL
5 01-Apr-14 NULL
6 05-Apr-14 NULL
7 01-Apr-14 NULL
8 01-Apr-14 NULL
9 01-Apr-14 NULL
10 29-Apr-14 NULL
11 21-Apr-14 NULL
12 01-Apr-14 NULL
13 01-Apr-14 NULL
14 01-Apr-14 NULL
15 05-Aug-14 NULL
16 01-Oct-1 NULL
17 13-Oct-14 NULL
18 22-Oct-14 NULL
19 25-Oct-14 NULL
10 29-Oct-14 NULL
Таблица дат: Содержит date столбец, имеющий данные из 2011-Jan-01 на текущую дату.
Таблица полученных результатов по моему запросу:
+--------------------------------------------------------------+
| date | employee_joined | present_employees |
+--------------------------------------------------------------+
| 2014-01-01 00:00:00-7 | NULL | NULL |
| 2014-02-01 00:00:00-7 | NULL | NULL |
| 2014-03-01 00:00:00-7 | NULL | NULL |
| 2014-04-01 00:00:00-7 | 14 | 14 |
| 2014-05-01 00:00:00-7 | NULL | NULL |
| 2014-06-01 00:00:00-7 | NULL | NULL |
| 2014-07-01 00:00:00-7 | NULL | NULL |
| 2014-08-01 00:00:00-7 | 1 | 15 |
| 2014-09-01 00:00:00-7 | NULL | NULL |
| 2014-10-01 00:00:00-7 | 5 | 20 |
+--------------------------------------------------------------+
Я ищу результирующую таблицу:
+--------------------------------------------------------------+
| date | employee_joined | present_employees |
+--------------------------------------------------------------+
| 2014-01-01 00:00:00-7 | NULL | NULL |
| 2014-02-01 00:00:00-7 | NULL | NULL |
| 2014-03-01 00:00:00-7 | NULL | NULL |
| 2014-04-01 00:00:00-7 | 14 | 14 |
| 2014-05-01 00:00:00-7 | NULL | 14 |
| 2014-06-01 00:00:00-7 | NULL | 14 |
| 2014-07-01 00:00:00-7 | NULL | 14 |
| 2014-08-01 00:00:00-7 | 1 | 15 |
| 2014-09-01 00:00:00-7 | NULL | 15 |
| 2014-10-01 00:00:00-7 | 5 | 20 |
+--------------------------------------------------------------+
Я пытался получить данные из запроса ниже:
/*-----ONLY FOR PRESENT EMPLOYEES USING CUMULATIVE SUM--------*/
WITH fdates AS
(
SELECT DATE_TRUNC('month', d.date) AS date
FROM dates d
WHERE d.date::DATE <= '10-01-2014' AND
d.date::DATE >= '01-01-2014'
group by DATE_TRUNC('month', d.date)
),
employeeJoin AS
(
SELECT COALESCE( COUNT(e.id), 0 ) AS employee_joined,
DATE_TRUNC( 'month', e.date_started) AS date_started
FROM employees e GROUP BY DATE_TRUNC( 'month', e.date_started)
),
employeeJoinRownum AS
(
SELECT employee_joined, date_started, row_number() OVER (order by date_started) rownum
FROM employeeJoin
)
SELECT d.*, employee_joined AS employee_joined,
(SELECT sum(employee_joined) FROM employeeJoinRownum eJ2 WHERE eJ2.rownum <= eJ1.rownum) AS Total_Joined_Employees
FROM fdates d
LEFT OUTER JOIN employeeJoinRownum eJ1 ON( eJ1.date_started = DATE_TRUNC('month', d.date) )
ORDER BY d.date
Решение
Следующий запрос подсчитывает количество сотрудников, которых присоединились, и сотрудников, ушедших на каждую дату, а затем использует оконная функция накапливать результаты.
SELECT
dates.date,
COUNT(DISTINCT ej.id) AS employee_joined,
COUNT(DISTINCT el.id) AS employee_left,
SUM(COUNT(DISTINCT ej.id) - COUNT(DISTINCT el.id)) OVER (ORDER BY dates.date) AS present_employees
FROM
dates LEFT JOIN employees ej
ON
ej.date_started = dates.date LEFT JOIN employees el
ON
el.date_terminated = dates.date
GROUP BY
dates.date;
Если у вас нет предварительно заполненных dates таблица, вы можете использовать generate_series вместо этого установите возвращаемую функцию и оставьте соединение с ней.
SELECT
...
FROM
GENERATE_SERIES('2014-01-01', '2014-01-10', '1 day'::interval) dates LEFT JOIN employees ej
ON
...
Другие решения
Вы можете нормализовать таблицу, создав строку для события соединения и завершения:
select welcome as date
, 1 as size_change
from emps
union all
select bye
, -1
from emps
where bye is not null
Теперь вы можете использовать промежуточную сумму для расчета текущего размера:
; with events as
(
select welcome as date
, 1 as size_change
from emps
union all
select bye
, -1
from emps
where bye is not null
)
select distinct to_char(date, 'YYYY-MM-DD') as date
, sum(size_change) over (order by date) as family_size
from events
order by
date
;