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
    t0 NUMERIC;
    delta_t NUMERIC;
    deviation_value NUMERIC;
    deviation_ten NUMERIC;
    deviation_unit NUMERIC;
    row_data RECORD;
BEGIN
    t0 := temperature + (select value from numeric_constants where key = 'dtv' limit 1);
	raise notice 't0 %', t0;

    delta_t := t0 - (select value from numeric_constants where key = 'mp' limit 1);
    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;


select
u.name as "ФИО",
mr.name as "Должность",
COUNT(b.id) as "Количество измерений",
SUM(case validate_measurement(p.temperature, p.pressure, p.wind_direction) when 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
order by "Количество ошибочных данных" desc;