我需要解决的问题:
为了计算每天用于公共假期或病假的工作小时数,我们使用前三个月的平均工作小时数(起始值为每天8小时)。
棘手的部分是需要考虑上个月的计算值,也就是说,如果上个月有一个被分配为8.5小时的公共假期,这些计算出的小时数将影响上个月每天的平均工作小时数,然后用于分配本月的假期工作时间。
到目前为止,我只想到了下面的方法,但还没有考虑逐行计算:
最初的回答是给出以下结果:
为了计算每天用于公共假期或病假的工作小时数,我们使用前三个月的平均工作小时数(起始值为每天8小时)。
棘手的部分是需要考虑上个月的计算值,也就是说,如果上个月有一个被分配为8.5小时的公共假期,这些计算出的小时数将影响上个月每天的平均工作小时数,然后用于分配本月的假期工作时间。
到目前为止,我只想到了下面的方法,但还没有考虑逐行计算:
WITH
const (h_target, h_extra) AS (VALUES (8.0, 20)),
monthly_sums (c_month, d_work, d_off, h_work) AS (VALUES
('2018-12', 16, 5, 150.25),
('2019-01', 20, 3, 171.25),
('2019-02', 15, 5, 120.5)
),
calc AS (
SELECT
ms.*,
(ms.d_work + ms.d_off) AS d_total,
(ms.h_work + ms.d_off * const.h_target) AS h_total,
(avg((ms.h_work + ms.d_off * const.h_target) / (ms.d_work + ms.d_off))
OVER (ORDER BY ms.c_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW))::numeric(10,2)
AS h_off
FROM monthly_sums AS ms
CROSS JOIN const
)
SELECT
calc.c_month,
calc.d_work,
calc.d_off,
calc.d_total,
calc.h_work,
calc.h_off,
(d_off * lag(h_off, 1, const.h_target) OVER (ORDER BY c_month)) AS h_off_sum,
(h_work + d_off * lag(h_off, 1, const.h_target) OVER (ORDER BY c_month)) AS h_sum
FROM calc CROSS JOIN const;
最初的回答是给出以下结果:
c_month | d_work | d_off | d_total | h_work | h_off | h_off_sum | h_sum
---------+--------+-------+---------+--------+-------+-----------+--------
2018-12 | 16 | 5 | 21 | 150.25 | 9.06 | 40.0 | 190.25
2019-01 | 20 | 3 | 23 | 171.25 | 8.77 | 27.18 | 198.43
2019-02 | 15 | 5 | 20 | 120.5 | 8.52 | 43.85 | 164.35
(3 rows)
对于依赖于先前行值(lag
)的第一行和第二行列,此计算是正确的,但是每天平均小时数的计算显然是错误的,因为我无法想出如何将当前行值(h_sum
)反馈到新的h_off
计算中。
所需结果应如下:
最初的回答
c_month | d_work | d_off | d_total | h_work | h_off | h_off_sum | h_sum
---------+--------+-------+---------+--------+-------+-----------+--------
2018-12 | 16 | 5 | 21 | 150.25 | 9.06 | 40.0 | 190.25
2019-01 | 20 | 3 | 23 | 171.25 | 8.84 | 27.18 | 198.43
2019-02 | 15 | 5 | 20 | 120.5 | 8.64 | 44.2 | 164.7
(3 rows)
...意思是h_off
用于下个月的h_off_sum
和可用月份(最多三个)的h_sum
和h_sum
,然后依次计算出当前月份的h_off
(基本上是在最多三个月内的avg(h_sum / d_total)
)。
因此,实际计算如下:
c_month | calculation | h_off
---------+----------------------------------------------------+-------
| | 8.00 << initial
.---------------------- uses ---------------------^
2018-12 | ((190.25 / 21)) / 1 | 9.06
.------------ uses ---------------^
2019-01 | ((190.25 / 21) + (198.43 / 23)) / 2 | 8.84
.--- uses --------^
2019-02 | ((190.25 / 21) + (198.43 / 23) + (164.7 / 20)) / 3 | 8.64
附言:我正在使用PostgreSQL 11,所以如果有任何区别,我就拥有最新的功能。
最初的回答: