空值的累积总和 [英] cumulative summation over null values
本文介绍了空值的累积总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经尝试计算累积金额列，以找出每个月的现有工作员工，但是按照上个月的情况，我得到的是NULL而不是现在的员工。
表员工：
I have tried to calculate cumulative sum column to find out Present Working Employees in each month, but am getting NULL instead of present employee as per previous month.
Table employees:
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`到当前日期的数据。
从我的查询中获得的结果表：
Table dates: It containing `date` column which having data from `2011-Jan-01` to current date.
Obtained result Table from my query :
+--------------------------------------------------------------+
| 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 |
+--------------------------------------------------------------+
我正在寻找结果表：
I am looking for resultant table:
+--------------------------------------------------------------+
| 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 | 2264 | 2264 |
| 2014-05-01 00:00:00-7 | NULL | 2264 |
| 2014-06-01 00:00:00-7 | NULL | 2264 |
| 2014-07-01 00:00:00-7 | NULL | 2264 |
| 2014-08-01 00:00:00-7 | 1 | 2265 |
| 2014-09-01 00:00:00-7 | NULL | 2265 |
| 2014-10-01 00:00:00-7 | 5 | 2270 |
+--------------------------------------------------------------+
我试图从下面的查询中获取数据：
I have tried to get data from below query:
/*-----ONLY FOR PRESENT EMPLOYEES--------*/
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
推荐答案
按照日期对所有员工进行分组后，您可以在员工表上使用自我加入。
如下所示
选择date_started，COUNT（1）NumberOfEmployee加入
进入＃table2
来自员工
group by date_started
选择e1.date_started，SUM（E2.NumberOfEmployeejoined）
来自＃table2的
e1加入＃table2 e2在E1上。 date_started> = E2.date_started
按e1.date_started分组
Hi,
You could use the self join on the employees table after grouping up all the employess by date .
something like below
select date_started,COUNT(1) NumberOfEmployeejoined
Into #table2
from employees
Group by date_started
select e1.date_started,SUM(E2.NumberOfEmployeejoined)
from #table2 e1 Join #table2 e2 On E1.date_started>=E2.date_started
Group by e1.date_started
这篇关于空值的累积总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
