Тестовый запрос: использование паттерна ARRAY

WITH validities AS (

SELECT

validity,

lower(validity) AS validity_begin,

upper(validity) AS validity_end

FROM (SELECT DISTINCT validity FROM bookings.routes) AS validities

),

dates AS (

SELECT

validity,

tz,

dt::date,

extract(isodow FROM dt) AS dow,

validity_begin,

validity_end

FROM validities v

CROSS JOIN (SELECT DISTINCT timezone FROM bookings.airports) tz(tz)

CROSS JOIN generate_series(

timezone(tz, v.validity_begin)::date::timestamp,

timezone(tz, v.validity_end)::date::timestamp,

'1 day'::interval

) dt

),

schedules AS (

SELECT

r.route_no,

a.timezone AS tz,

r.validity,

r.scheduled_time,

unnest(r.days_of_week) AS dow

FROM bookings.routes r

JOIN bookings.airports a ON a.airport_code = r.departure_airport

),

dates_to_be AS (

SELECT

s.route_no,

s.tz,

s.validity,

s.dow,

timezone(s.tz, d.dt + s.scheduled_time) AS scheduled_dep

FROM schedules s

JOIN dates d ON d.validity = s.validity AND d.tz = s.tz AND d.dow = s.dow

WHERE timezone(s.tz, d.dt + s.scheduled_time) >= d.validity_begin

AND timezone(s.tz, d.dt + s.scheduled_time) < d.validity_end

),

flight_schedule_departures AS (

-- Используем массив вместо MAX

SELECT array_agg(scheduled_departure ORDER BY scheduled_departure DESC) AS dep_array

FROM bookings.flights

),

absent AS (

SELECT

dtb.route_no AS route_no,

dtb.tz,

dtb.validity,

dtb.scheduled_dep AS sched_dep_to_be

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN bookings.airports a ON a.airport_code = r.departure_airport

RIGHT JOIN dates_to_be dtb ON dtb.route_no = f.route_no

AND dtb.validity = r.validity

AND dtb.scheduled_dep = f.scheduled_departure

CROSS JOIN flight_schedule_departures fsd

WHERE f.route_no IS NULL

-- Берем первый элемент отсортированного массива (максимальный)

AND dtb.scheduled_dep < (fsd.dep_array[1])

),

excess AS (

SELECT

f.route_no,

a.timezone,

r.validity,

f.scheduled_departure AS sched_dep_not_to_be

FROM bookings.flights f

JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure

JOIN bookings.airports a ON a.airport_code = r.departure_airport

LEFT JOIN dates_to_be dtb ON dtb.route_no = f.route_no

AND dtb.validity = r.validity

AND dtb.scheduled_dep = f.scheduled_departure

WHERE dtb.route_no IS NULL

)

SELECT

(SELECT count(*) FROM absent) AS absent_flights,

(SELECT count(*) FROM excess) AS excess_flights,

CASE

WHEN (SELECT count(*) FROM absent) > 0 AND (SELECT count(*) FROM excess) > 0

THEN 'ERROR: absent and excess flights'

WHEN (SELECT count(*) FROM absent) > 0 THEN 'ERROR: absent flights'

WHEN (SELECT count(*) FROM excess) > 0 THEN 'ERROR: excess flights'

ELSE 'Ok'

END AS verdict;