SQLQuery1(2).sql - master - temirkanov2005/Mikhil

SQLQuery1(2).sql
174 строки4 KB
temirkanov2005
upload files
17 ноя 2024, 23:12
17 ноя 2024, 23:12ab4f83a
WITH RECURSIVE RoutePaths AS (
    SELECT 
        r.route_id,
        r.train_id,
        s.station_id,
        s.station_name,
        r.stop_order,
        1 AS station_count,
        t.departure_time AS departure_time,
        t.arrival_time AS arrival_time
    FROM 
        Routes r
    JOIN 
        Trains t ON r.train_id = t.train_id
    JOIN 
        Stations s ON r.station_id = s.station_id
    WHERE 
        t.departure_time BETWEEN '2024-11-05' AND '2024-11-20'
        AND (t.days_of_week LIKE '%Mon%' OR t.days_of_week LIKE '%Tue%' OR 
             t.days_of_week LIKE '%Wed%' OR t.days_of_week LIKE '%Thu%' OR 
             t.days_of_week LIKE '%Fri%' OR t.days_of_week LIKE '%Sat%' OR 
             t.days_of_week LIKE '%Sun%')
        
    UNION ALL

    SELECT 
        rp.route_id,
        r.train_id,
        s.station_id,
        s.station_name,
        r.stop_order,
        rp.station_count + 1,
        rp.arrival_time AS departure_time,
        t.arrival_time AS arrival_time
    FROM 
        RoutePaths rp
    JOIN 
        Routes r ON rp.train_id = r.train_id AND rp.stop_order < r.stop_order
    JOIN 
        Trains t ON r.train_id = t.train_id
    JOIN 
        Stations s ON r.station_id = s.station_id
    WHERE 
        rp.arrival_time + INTERVAL 40 MINUTE <= t.departure_time 
        AND rp.arrival_time + INTERVAL 8 HOUR >= t.departure_time
)

SELECT 
    route_id,
    COUNT(DISTINCT station_id) AS total_stations
FROM 
    RoutePaths
GROUP BY 
    route_id
ORDER BY 
    total_stations DESC
LIMIT 1;







WITH RECURSIVE route AS (
    SELECT
        t.train_id,
        s.station_id AS start_station,
        s.station_name AS start_station_name,
        t.departure_time,
        1 AS station_count,
        ARRAY[s.station_id] AS visited_stations
    FROM
        trains t
    JOIN
        schedules sc ON t.train_id = sc.train_id
    JOIN
        stations s ON sc.station_id = s.station_id
    WHERE
        t.departure_time BETWEEN '2024-11-05' AND '2024-11-20'
    
    UNION ALL
    
    SELECT
        r.train_id,
        s.station_id AS next_station,
        s.station_name AS next_station_name,
        t.departure_time,
        r.station_count + 1,
        r.visited_stations || s.station_id
    FROM
        route r
    JOIN
        schedules sc ON r.train_id = sc.train_id
    JOIN
        stations s ON sc.station_id = s.station_id
    JOIN
        trains t ON r.train_id = t.train_id
    WHERE
        sc.departure_time >= r.departure_time + INTERVAL '40 minutes'
        AND sc.departure_time <= r.departure_time + INTERVAL '8 hours'
        AND NOT s.station_id = ANY(r.visited_stations)
)

SELECT
    start_station_name,
    COUNT(*) AS total_stations,
    MAX(station_count) AS max_stations
FROM
    route
GROUP BY
    start_station_name
ORDER BY
    max_stations DESC
LIMIT 1;








SET enable_nestloop = OFF; -- �������� �������� ��� ��������� ������������������

WITH RECURSIVE route AS (
    SELECT
        t.train_id,
        s.station_id AS start_station,
        s.station_name AS start_station_name,
        t.departure_time,
        1 AS station_count,
        ARRAY[s.station_id] AS visited_stations
    FROM
        trains t
    JOIN
        schedules sc ON t.train_id = sc.train_id
    JOIN
        stations s ON sc.station_id = s.station_id
    WHERE
        t.departure_time BETWEEN '2024-11-05' AND '2024-11-20'
    
    UNION ALL
    
    SELECT
        r.train_id,
        s.station_id AS next_station,
        s.station_name AS next_station_name,
        sc.departure_time,
        r.station_count + 1,
        r.visited_stations || s.station_id
    FROM
        route r
    JOIN
        schedules sc ON r.train_id = sc.train_id
    JOIN
        stations s ON sc.station_id = s.station_id
    WHERE
        sc.departure_time >= r.departure_time + INTERVAL '40 minutes'
        AND sc.departure_time <= r.departure_time + INTERVAL '8 hours'
        AND NOT s.station_id = ANY(r.visited_stations)
)

SELECT
    start_station_name,
    COUNT(*) AS total_stations,
    MAX(station_count) AS max_stations
FROM
    route
GROUP BY
    start_station_name
ORDER BY
    max_stations DESC
LIMIT 1;