62 подписчика
--Тестовый сценарий-4 (JOIN)
WITH random_departures AS (
SELECT airport_code
FROM bookings.airports_data
ORDER BY random()
LIMIT (random() * 9 + 1)::int
),
random_validity_date AS (
SELECT
date_trunc('day',
lower(validity) +
(random() * EXTRACT(EPOCH FROM (upper(validity) - lower(validity)))) * interval '1 second'
) as random_date
FROM bookings.routes
WHERE validity IS NOT NULL
AND upper(validity) > lower(validity) + interval '7 days'
ORDER BY random()
LIMIT 1
)
SELECT r.route_no,
r.departure_airport,
a_dep.city->>'en' as departure_city,
r.arrival_airport,
a_arr.city->>'en' as arrival_city,
r.duration,
r.days_of_week
FROM bookings.routes r
JOIN bookings.airports_data a_dep ON r.departure_airport = a_dep.airport_code
JOIN bookings.airports_data a_arr ON r.arrival_airport = a_arr.airport_code
CROSS JOIN random_validity_date rvd
WHERE r.departure_airport IN (SELECT airport_code FROM random_departures)
AND r.validity @> rvd.random_date
AND array_length(r.days_of_week, 1) > 0
ORDER BY r.departure_airport, r.route_no
LIMIT 300;
Около минуты
17 декабря