74 подписчика
Тестовый запрос -ARRAY
WITH random_period AS (
SELECT
book_date AS start_date,
book_date + INTERVAL '30 days' AS end_date
FROM bookings
ORDER BY RANDOM()
LIMIT 1
),
booking_summary AS (
SELECT
b.book_ref,
b.book_date,
COUNT(t.ticket_no) as ticket_count,
ARRAY_AGG(t.ticket_no) as ticket_numbers
FROM bookings b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
WHERE b.book_date BETWEEN (SELECT start_date FROM random_period)
AND (SELECT end_date FROM random_period)
GROUP BY b.book_ref, b.book_date
)
SELECT
bs.book_ref,
bs.book_date,
bs.ticket_count,
tn.ticket_no, -- Используем развернутое значение
t.passenger_name,
s.flight_id,
f.status
FROM booking_summary bs
CROSS JOIN UNNEST(bs.ticket_numbers) AS tn(ticket_no) -- Разворачиваем массив здесь
LEFT JOIN tickets t ON t.ticket_no = tn.ticket_no -- Теперь соединяем по одиночному значению
LEFT JOIN segments s ON s.ticket_no = t.ticket_no
LEFT JOIN flights f ON f.flight_id = s.flight_id
ORDER BY bs.book_date, tn.ticket_no; -- Сортируем по развернутому значению
Около минуты
30 ноября 2025