使用PostgreSQL计算来自前几行计算值的计算值。

Question

我需要解决的问题：

为了计算每天用于公共假期或病假的工作小时数，我们使用前三个月的平均工作小时数（起始值为每天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，所以如果有任何区别，我就拥有最新的功能。

最初的回答：

- Ancoron

Answer 1

我无法使用窗口函数解决那个涉及行列计算的问题，即使是借助于 递归CTE ，同时为了处理第三个历史月份的天数(d_total_1)和小时数(h_sum_1)，不得不引入专门的列。由于递归临时表只能连接一次，这种方法非常特殊。

此外，我在输入数据中添加了第四行，并使用附加索引列进行连接，通常可以通过子查询生成该列:

SELECT ROW_NUMBER() OVER (ORDER BY c_month) AS row_num, * FROM monthly_sums

所以，这是我的想法：

WITH RECURSIVE calc AS (
        SELECT 
            monthly_sums.row_num,
            monthly_sums.c_month,
            monthly_sums.d_work,
            monthly_sums.d_off,
            monthly_sums.h_work,
            (monthly_sums.d_off * 8)::numeric(10,2) AS h_off_sum,
            monthly_sums.d_work + monthly_sums.d_off AS d_total,
            0.0 AS d_total_1,
            (monthly_sums.h_work + monthly_sums.d_off * 8)::numeric(10,2) AS h_sum,
            0.0 AS h_sum_1,
            (
                (monthly_sums.h_work + monthly_sums.d_off * 8)
                /
                (monthly_sums.d_work + monthly_sums.d_off)
            )::numeric(10,2) AS h_off
        FROM
            (
                SELECT * FROM (VALUES
                    (1, '2018-12', 16, 5, 150.25),
                    (2, '2019-01', 20, 3, 171.25),
                    (3, '2019-02', 15, 5, 120.5),
                    (4, '2019-03', 19, 2, 131.75)
                ) AS tmp (row_num, c_month, d_work, d_off, h_work)
            ) AS monthly_sums
        WHERE
            monthly_sums.row_num = 1
    UNION ALL
        SELECT
            monthly_sums.row_num,
            monthly_sums.c_month,
            monthly_sums.d_work,
            monthly_sums.d_off,
            monthly_sums.h_work,
            lat_off.h_off_sum::numeric(10,2),
            lat_days.d_total,
            calc.d_total AS d_total_1,
            lat_sum.h_sum::numeric(10,2),
            calc.h_sum AS h_sum_1,
            lat_calc.h_off::numeric(10,2)
        FROM
            (
                SELECT * FROM (VALUES
                    (1, '2018-12', 16, 5, 150.25),
                    (2, '2019-01', 20, 3, 171.25),
                    (3, '2019-02', 15, 5, 120.5),
                    (4, '2019-03', 19, 2, 131.75)
                ) AS tmp (row_num, c_month, d_work, d_off, h_work)
            ) AS monthly_sums
            INNER JOIN calc ON (calc.row_num = monthly_sums.row_num - 1),
            LATERAL (SELECT monthly_sums.d_work + monthly_sums.d_off AS d_total) AS lat_days,
            LATERAL (SELECT monthly_sums.d_off * calc.h_off AS h_off_sum) AS lat_off,
            LATERAL (SELECT monthly_sums.h_work + lat_off.h_off_sum AS h_sum) AS lat_sum,
            LATERAL (SELECT
                (calc.h_sum_1 + calc.h_sum + lat_sum.h_sum)
                /
                (calc.d_total_1 + calc.d_total + lat_days.d_total)
                AS h_off
            ) AS lat_calc
        WHERE
            monthly_sums.row_num > 1
    )
SELECT c_month, d_work, d_off, d_total, h_work, h_off, h_off_sum, h_sum FROM calc
;

...它给出的结果是：

 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.00 | 190.25
 2019-01 |     20 |     3 |      23 | 171.25 |  8.83 |     27.18 | 198.43
 2019-02 |     15 |     5 |      20 |  120.5 |  8.65 |     44.15 | 164.65
 2019-03 |     19 |     2 |      21 | 131.75 |  8.00 |     17.30 | 149.05
(4 rows)

(PostgreSQL的默认类型转换行为是对数值进行四舍五入，因此结果与最初预期略有不同，但实际上是正确的。)

请注意，PostgreSQL通常对数据类型非常挑剔，并且拒绝处理可能导致精度损失的差异（例如numeric vs. integer）的查询，这就是为什么我在两个地方都使用了显式类型的列的原因。

使用LATERAL子查询解决了谜题的最后一块拼图，这使我能够让一个计算引用之前一个计算的结果，并且甚至可以独立于计算层次结构移动最终输出中的列。

如果有人能想出一个更简单的变体，我会很乐意学习。

- Ancoron