Блог

Проблема, которую мне нужно решить:

Для того, чтобы вычислить количество часов в день, которые используются для (государственных) праздников или дней болезни, используются средние рабочие часы за предыдущие 3 месяца (с начальным значением 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
  

P.S.: Я использую PostgreSQL 11, поэтому у меня под рукой самые последние функции, если это имеет какое-либо значение.

Я вообще не смог решить эту проблему вычисления между столбцами и между строками с использованием оконных функций, не прибегая к специальному использованию рекурсивного CTE, а также к введению специальных столбцов для дней ( d_total_1 ) и часов ( h_sum_1 ) 3-го исторического месяца (поскольку вы не можете присоединиться к рекурсивной временной таблице более одного раза).

Кроме того, я добавил 4-ю строку к входным данным и использовал дополнительный столбец индекса, на который я могу ссылаться при объединении, который обычно составляется с помощью подзапроса, подобного этому:

 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 против integer ), вот почему я использовал явные типы для столбцов в обоих местах.

Одна из последних частей головоломки была решена с помощью БОКОВЫХ подзапросов, что позволяет мне ссылаться на результат предыдущего вычисления и даже перемещаться по столбцам в конечном выводе независимо от иерархии вычислений.

Если кто-нибудь может предложить более простой вариант, я был бы рад узнать об этом.