Pianificazione viaggi con dati di volo

Query gerarchiche e ricorsive in SQL Server

Jasmin Ludolf

Content Developer

Tabellone di un aeroporto

Esempio di tabellone in aeroporto

Com'è strutturato un dataset di voli?

Departure	Arrival	FlightNumber	Cost	Time
London	Paris	LH3827	90	2
Vienna	New York	MH2370	379	8
New York	Paris	LH9832	489	9
Vienna	Paris	SU2389	200	3
London	Chicago	OP1230	650	10
New York	Chicago	NL5460	150	2

Come costruire una rotta di volo?

Un'immagine con tutte le possibili rotte di volo nel mondo

Usa la ricorsione per ottenere tutte le rotte possibili
Una rotta è definita da aeroporto di partenza e di destinazione
Limita gli scali per avere rotte realistiche

Costruire una rotta di volo - passo 1

WITH flightRoute (Departure, Arrival, stops) AS(
  -- Anchor query
  SELECT f.Departure,f.Arrival, 0
      FROM flightPlan f
      WHERE Departure = 'Vienna'
  -- Recursive query
  UNION ALL
      SELECT p.Departure, f.Arrival, p.stops + 1
      FROM flightPlan f, flightRoute p
      WHERE p.Arrival = f.Departure AND 
        p.stops < 5 
)

SELECT Departure, Arrival, stops
    FROM flightRoute

+-----------+---------------+--------+
| Departure | Arrival       | stops  |
|-----------|---------------|--------|
| Vienna    | Paris         | 2      |
| Vienna    | San Francisco | 3      |
| Vienna    | Vienna        | 3      |
| Vienna    | Frankfurt     | 3      |
| ...       | ...           | ...    |
+-----------+---------------+--------+

Costruire una rotta di volo - passo 2

WITH flightRoute (Departure, Arrival, stops, route) AS(
  SELECT f.Departure, f.Arrival, 0, 
  CAST(Departure + '->' + Arrival AS VARCHAR(MAX))
      FROM flightPlan f
      WHERE Departure = 'Vienna'

  UNION ALL
  SELECT p.Departure, f.Arrival, p.stops + 1, 
  p.totalCost + f.Cost, 
  CAST(p.route + '->' + f.Arrival AS VARCHAR(MAX))
      FROM flightPlan f, flightRoute p

      WHERE p.Arrival = f.Departure AND p.stops < 5
)

Introduci route nel membro di ancoraggio
Traccia le route nel membro ricorsivo
Limita il numero di scali

Costruire una rotta di volo - risultato

SELECT Departure, Arrival, Route
    FROM flightRoute

+-----------+--------------+-------------------------------------------+
| Departure | Arrival      | route                                     |
|-----------|--------------|-------------------------------------------+
| London    | New York     | London -> Vienna -> Chicago -> New York   |
| Vienna    | Chicago      | Vienna -> London -> Chicago               |        
| Paris     | Los Angeles  | Paris -> Toronto -> Los Angeles           |
| Chicago   | New York     | Chicago -> New York                       |
| Rome      | New York     | Rome -> London -> Chicago -> New York     |    
| ...       | ...          | ...                                       |
+-----------+--------------+-------------------------------------------+

Query su rotte possibili con limiti

WITH flightRoute (Departure, Arrival, stops, totalCost, route) AS(
  SELECT f.Departure, f.Arrival, 0, Cost,
    CAST(Departure + '->' + Arrival AS NVARCHAR(MAX))
      FROM flightPlan f
      WHERE Departure = 'New York'
  UNION ALL
  SELECT  p.Departure, f.Arrival, p.stops+1, 
  p.totalCost + f.Cost, p.route + '->' + f.Arrival
      FROM flightPlan f, flightRoute p
      WHERE p.Arrival = f.Departure AND p.stops < '...' 
)

SELECT '...'    
    FROM flightRoute
    WHERE '...' ;

Trova tutti gli aeroporti di destinazione possibili se:

L’aeroporto di partenza è fisso
- New York
Il numero di stops è limitato a n
L’output è filtrato da una condizione
- limite di costo
- rotta più economica verso una destinazione

Troviamo rotte di volo possibili!

Query gerarchiche e ricorsive in SQL Server