फ्लाइट डेटा के लिए ट्रैवल प्लानिंग

SQL Server में Hierarchical और Recursive Queries

Jasmin Ludolf

Content Developer

एयरपोर्ट का स्कोरबोर्ड

किसी एयरपोर्ट का उदाहरण स्कोरबोर्ड

फ्लाइट डेटासेट की संरचना कैसी होती है?

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

फ्लाइट रूट कैसे बनाएँ?

दुनिया भर में सभी संभावित फ्लाइट रूट्स का चित्र

सभी संभावित फ्लाइट रूट्स पाने के लिए recursion का उपयोग करें
रूट को departure और destination एयरपोर्ट से परिभाषित करें
यथार्थवादी रूट्स के लिए layovers की संख्या सीमित करें

फ्लाइट रूट बनाना - चरण 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      |
| ...       | ...           | ...    |
+-----------+---------------+--------+

फ्लाइट रूट बनाना - चरण 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
)

Anchor member में route जोड़ें
Recursive member में routes ट्रैक करें
stops की संख्या सीमित करें

फ्लाइट रूट बनाना - परिणाम

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     |    
| ...       | ...          | ...                                       |
+-----------+--------------+-------------------------------------------+

सीमाओं के साथ संभावित फ्लाइट्स की क्वेरी

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 '...' ;

सभी संभावित destination एयरपोर्ट ढूँढें जहाँ:

Departure एयरपोर्ट तय है
- New York
stops की संख्या n तक सीमित है
आउटपुट किसी शर्त से सीमित है
- cost limit
- किसी destination तक सबसे सस्ता रूट

चलिए संभावित फ्लाइट रूट्स ढूँढते हैं!

SQL Server में Hierarchical और Recursive Queries