PostgresStudy2025/homework/4.sql

-- Таблица для расчета среднего
CREATE TABLE average_deviations_temperature (
    height INT PRIMARY KEY, -- Высота
    negative_values NUMERIC[], -- Массив для отрицательных значений
    positive_values NUMERIC[]  -- Массив для положительных значений
);


INSERT INTO average_deviations_temperature (height, negative_values, positive_values)
VALUES 
(
    200,
    ARRAY[-1, -2, -3, -4, -5, -6, -7, -8, -9, -10, -20, -30, -40, -50],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    400,
    ARRAY[-1, -2, -3, -4, -5, -6, -6, -7, -8, -9, -19, -29, -38, -48],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    800,
    ARRAY[-1, -2, -3, -4, -5, -6, -6, -7, -7, -8, -18, -28, -37, -46],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    1200,
    ARRAY[-1, -2, -3, -4, -4, -5, -5, -6, -7, -8, -17, -26, -35, -44],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    1600,
    ARRAY[-1, -2, -3, -3, -4, -4, -5, -6, -7, -7, -17, -25, -34, -42],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    2000,
    ARRAY[-1, -2, -3, -3, -4, -4, -5, -6, -6, -7, -16, -24, -32, -40],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    2400,
    ARRAY[-1, -2, -2, -3, -4, -4, -5, -5, -6, -7, -15, -23, -31, -38],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    3000,
    ARRAY[-1, -2, -2, -3, -4, -4, -4, -5, -5, -6, -15, -22, -30, -37],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
),
(
    4000,
    ARRAY[-1, -2, -2, -3, -4, -4, -4, -4, -5, -6, -14, -20, -27, -34],
    ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 30, NULL, NULL]
) on conflict do nothing;

insert into numeric_constants("key", "value") values ('dtv', 0.3), ('mp', 15.9) ON CONFLICT DO NOTHING;

CREATE OR REPLACE FUNCTION calculate_average_deviation(temperature NUMERIC)
RETURNS TABLE(height INT, deviation NUMERIC) AS $$
DECLARE
    dtv NUMERIC;
    mp NUMERIC;
    t0 NUMERIC;
    delta_t NUMERIC;
    deviation_value NUMERIC;
    deviation_ten NUMERIC;
    deviation_unit NUMERIC;
    row_data RECORD;
BEGIN
    select value into dtv from numeric_constants where key = 'dtv' limit 1;
    select value into mp from numeric_constants where key = 'mp' limit 1;
    
    IF dtv IS NULL THEN
        dtv := 0.3;
        RAISE NOTICE 'Константа dtv не найдена, использовано значение 0.3';
    END IF;
    IF mp IS NULL THEN
        mp := 15.9;
        RAISE NOTICE 'Константа mp не найдена, использовано значение 15.9';
    END IF;
    
    t0 := temperature + dtv;
	raise notice 't0 %', t0;

    delta_t := t0 - mp;
    delta_t := ROUND(delta_t);

    FOR row_data IN SELECT * FROM average_deviations_temperature LOOP
        deviation_ten := (delta_t / 10) * 10;
        deviation_unit := delta_t % 10;
		deviation_ten := deviation_ten - deviation_unit;

        IF delta_t < 0 THEN
            deviation_value := row_data.negative_values[abs(deviation_ten)] + 
                               row_data.negative_values[abs(deviation_unit)];
        ELSE
            deviation_value := row_data.positive_values[abs(deviation_ten)] + 
                               row_data.positive_values[abs(deviation_unit)];
        END IF;

        height := row_data.height;
        deviation := deviation_value;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

WITH user_measurements AS (
    SELECT
        u.name AS "ФИО",
        mr.name AS "Должность",
        COUNT(b.id) AS "Количество измерений",
        SUM(CASE WHEN validate_measurement(p.temperature, p.pressure, p.wind_direction) IS NULL THEN 1 ELSE 0 END) AS "Количество ошибочных данных"
    FROM users u 
    JOIN ranks mr ON u.rank_id = mr.id
    JOIN measurement_batch b ON u.id = b.user_id
    JOIN measurement_params p ON p.measurement_batch_id = b.id
    GROUP BY u.name, mr.name
)
SELECT 
    "ФИО",
    "Должность",
    "Количество измерений",
    "Количество ошибочных данных"
FROM user_measurements
ORDER BY "Количество ошибочных данных" DESC;