Sessionized Data - Map/Reduce

VSCode

Flight event data contains dep_time, carrier, origin, destination and tail_num (the plane that made the flight). The query below takes the flight event data and maps it into sessions of flight_date, carrier, and tail_num. For each session, a nested list of flight_legs by the aircraft on that day. The flight legs are numbered.

run: duckdb.table('../data/flights.parquet') extend {
  where: carrier = 'WN' and dep_time ? @2002-03-03
  measure: flight_count is count()
} -> {
  calculate: session_id is row_number()
  group_by:
    flight_date is dep_time.day
    carrier
    tail_num
    aggregate: 
      flight_count 
      max_delay is max(dep_delay)
      total_distance is distance.sum()
  nest: flight_legs is  {
    order_by: 3
    calculate: flght_leg is row_number() 
    group_by:
      tail_num
      dep_minute is dep_time.minute
      origin
      destination
      dep_delay
      arr_delay
  } 
}

QUERY RESULTS

session_id

flight_date

carrier

tail_num

flight_count

max_delay

total_distance

flight_legs

2002-03-03

N391SW

4,179

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N391SW	2002-03-03 07:50	BWI	PVD	0	0
2	N391SW	2002-03-03 09:44	PVD	TPA	14	19
3	N391SW	2002-03-03 13:50	TPA	PVD	35	48
4	N391SW	2002-03-03 17:08	PVD	BNA	38	61
5	N391SW	2002-03-03 19:40	BNA	HOU	50	60

2002-03-03

N791SW

4,814

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N791SW	2002-03-03 08:00	TPA	LAS	0	12
2	N791SW	2002-03-03 10:37	LAS	MDW	17	22
3	N791SW	2002-03-03 16:45	MDW	STL	25	25
4	N791SW	2002-03-03 18:15	STL	MCO	20	10
5	N791SW	2002-03-03 22:05	MCO	FLL	20	20

2002-03-03

N735SA

3,790

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N735SA	2002-03-03 07:15	OAK	LAS	0	-10
2	N735SA	2002-03-03 09:00	LAS	SMF	0	5
3	N735SA	2002-03-03 11:00	SMF	SAN	15	10
4	N735SA	2002-03-03 12:45	SAN	LAS	20	17
5	N735SA	2002-03-03 14:14	LAS	SLC	19	19
6	N735SA	2002-03-03 16:55	SLC	PHX	25	10
7	N735SA	2002-03-03 18:53	PHX	SLC	18	10
8	N735SA	2002-03-03 20:45	SLC	RNO	15	10
9	N735SA	2002-03-03 21:27	RNO	PDX	12	5

2002-03-03

N723SW

5,162

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N723SW	2002-03-03 08:40	PHX	PVD	0	-16
2	N723SW	2002-03-03 15:55	PVD	PHX	0	-30
3	N723SW	2002-03-03 20:20	PHX	SAN	5	-12
4	N723SW	2002-03-03 21:00	SAN	PHX	0	0

2002-03-03

N641SW

3,105

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N641SW	2002-03-03 09:15	OAK	ONT	0	-1
2	N641SW	2002-03-03 10:50	ONT	OAK	0	0
3	N641SW	2002-03-03 12:35	OAK	PDX	10	5
4	N641SW	2002-03-03 14:25	PDX	OAK	0	-3
5	N641SW	2002-03-03 16:37	OAK	LAX	7	0
6	N641SW	2002-03-03 18:15	LAX	ELP	0	7
7	N641SW	2002-03-03 21:30	ELP	MAF	0	0

2002-03-03

N628SW

2,786

flght_leg	tail_num	dep_minute	origin	destination	dep_delay	arr_delay
1	N628SW	2002-03-03 08:00	OAK	LAX	0	-7
2	N628SW	2002-03-03 10:00	LAX	OAK	0	5
3	N628SW	2002-03-03 11:45	OAK	GEG	10	0
4	N628SW	2002-03-03 13:55	GEG	SEA	0	-5
5	N628SW	2002-03-03 15:25	SEA	RNO	0	-12
6	N628SW	2002-03-03 17:30	RNO	LAS	0	-10
7	N628SW	2002-03-03 19:23	LAS	PHX	13	20

[
  {
    "session_id": 1,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N391SW",
    "flight_count": 5,
    "max_delay": 50,
    "total_distance": 4179,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N391SW",
        "dep_minute": "2002-03-03T07:50:00.000Z",
        "origin": "BWI",
        "destination": "PVD",
        "dep_delay": 0,
        "arr_delay": 0
      },
      {
        "flght_leg": 2,
        "tail_num": "N391SW",
        "dep_minute": "2002-03-03T09:44:00.000Z",
        "origin": "PVD",
        "destination": "TPA",
        "dep_delay": 14,
        "arr_delay": 19
      },
      {
        "flght_leg": 3,
        "tail_num": "N391SW",
        "dep_minute": "2002-03-03T13:50:00.000Z",
        "origin": "TPA",
        "destination": "PVD",
        "dep_delay": 35,
        "arr_delay": 48
      },
      {
        "flght_leg": 4,
        "tail_num": "N391SW",
        "dep_minute": "2002-03-03T17:08:00.000Z",
        "origin": "PVD",
        "destination": "BNA",
        "dep_delay": 38,
        "arr_delay": 61
      },
      {
        "flght_leg": 5,
        "tail_num": "N391SW",
        "dep_minute": "2002-03-03T19:40:00.000Z",
        "origin": "BNA",
        "destination": "HOU",
        "dep_delay": 50,
        "arr_delay": 60
      }
    ]
  },
  {
    "session_id": 2,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N791SW",
    "flight_count": 5,
    "max_delay": 25,
    "total_distance": 4814,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N791SW",
        "dep_minute": "2002-03-03T08:00:00.000Z",
        "origin": "TPA",
        "destination": "LAS",
        "dep_delay": 0,
        "arr_delay": 12
      },
      {
        "flght_leg": 2,
        "tail_num": "N791SW",
        "dep_minute": "2002-03-03T10:37:00.000Z",
        "origin": "LAS",
        "destination": "MDW",
        "dep_delay": 17,
        "arr_delay": 22
      },
      {
        "flght_leg": 3,
        "tail_num": "N791SW",
        "dep_minute": "2002-03-03T16:45:00.000Z",
        "origin": "MDW",
        "destination": "STL",
        "dep_delay": 25,
        "arr_delay": 25
      },
      {
        "flght_leg": 4,
        "tail_num": "N791SW",
        "dep_minute": "2002-03-03T18:15:00.000Z",
        "origin": "STL",
        "destination": "MCO",
        "dep_delay": 20,
        "arr_delay": 10
      },
      {
        "flght_leg": 5,
        "tail_num": "N791SW",
        "dep_minute": "2002-03-03T22:05:00.000Z",
        "origin": "MCO",
        "destination": "FLL",
        "dep_delay": 20,
        "arr_delay": 20
      }
    ]
  },
  {
    "session_id": 4,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N735SA",
    "flight_count": 9,
    "max_delay": 25,
    "total_distance": 3790,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T07:15:00.000Z",
        "origin": "OAK",
        "destination": "LAS",
        "dep_delay": 0,
        "arr_delay": -10
      },
      {
        "flght_leg": 2,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T09:00:00.000Z",
        "origin": "LAS",
        "destination": "SMF",
        "dep_delay": 0,
        "arr_delay": 5
      },
      {
        "flght_leg": 3,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T11:00:00.000Z",
        "origin": "SMF",
        "destination": "SAN",
        "dep_delay": 15,
        "arr_delay": 10
      },
      {
        "flght_leg": 4,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T12:45:00.000Z",
        "origin": "SAN",
        "destination": "LAS",
        "dep_delay": 20,
        "arr_delay": 17
      },
      {
        "flght_leg": 5,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T14:14:00.000Z",
        "origin": "LAS",
        "destination": "SLC",
        "dep_delay": 19,
        "arr_delay": 19
      },
      {
        "flght_leg": 6,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T16:55:00.000Z",
        "origin": "SLC",
        "destination": "PHX",
        "dep_delay": 25,
        "arr_delay": 10
      },
      {
        "flght_leg": 7,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T18:53:00.000Z",
        "origin": "PHX",
        "destination": "SLC",
        "dep_delay": 18,
        "arr_delay": 10
      },
      {
        "flght_leg": 8,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T20:45:00.000Z",
        "origin": "SLC",
        "destination": "RNO",
        "dep_delay": 15,
        "arr_delay": 10
      },
      {
        "flght_leg": 9,
        "tail_num": "N735SA",
        "dep_minute": "2002-03-03T21:27:00.000Z",
        "origin": "RNO",
        "destination": "PDX",
        "dep_delay": 12,
        "arr_delay": 5
      }
    ]
  },
  {
    "session_id": 5,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N723SW",
    "flight_count": 4,
    "max_delay": 5,
    "total_distance": 5162,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N723SW",
        "dep_minute": "2002-03-03T08:40:00.000Z",
        "origin": "PHX",
        "destination": "PVD",
        "dep_delay": 0,
        "arr_delay": -16
      },
      {
        "flght_leg": 2,
        "tail_num": "N723SW",
        "dep_minute": "2002-03-03T15:55:00.000Z",
        "origin": "PVD",
        "destination": "PHX",
        "dep_delay": 0,
        "arr_delay": -30
      },
      {
        "flght_leg": 3,
        "tail_num": "N723SW",
        "dep_minute": "2002-03-03T20:20:00.000Z",
        "origin": "PHX",
        "destination": "SAN",
        "dep_delay": 5,
        "arr_delay": -12
      },
      {
        "flght_leg": 4,
        "tail_num": "N723SW",
        "dep_minute": "2002-03-03T21:00:00.000Z",
        "origin": "SAN",
        "destination": "PHX",
        "dep_delay": 0,
        "arr_delay": 0
      }
    ]
  },
  {
    "session_id": 6,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N641SW",
    "flight_count": 7,
    "max_delay": 10,
    "total_distance": 3105,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T09:15:00.000Z",
        "origin": "OAK",
        "destination": "ONT",
        "dep_delay": 0,
        "arr_delay": -1
      },
      {
        "flght_leg": 2,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T10:50:00.000Z",
        "origin": "ONT",
        "destination": "OAK",
        "dep_delay": 0,
        "arr_delay": 0
      },
      {
        "flght_leg": 3,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T12:35:00.000Z",
        "origin": "OAK",
        "destination": "PDX",
        "dep_delay": 10,
        "arr_delay": 5
      },
      {
        "flght_leg": 4,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T14:25:00.000Z",
        "origin": "PDX",
        "destination": "OAK",
        "dep_delay": 0,
        "arr_delay": -3
      },
      {
        "flght_leg": 5,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T16:37:00.000Z",
        "origin": "OAK",
        "destination": "LAX",
        "dep_delay": 7,
        "arr_delay": 0
      },
      {
        "flght_leg": 6,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T18:15:00.000Z",
        "origin": "LAX",
        "destination": "ELP",
        "dep_delay": 0,
        "arr_delay": 7
      },
      {
        "flght_leg": 7,
        "tail_num": "N641SW",
        "dep_minute": "2002-03-03T21:30:00.000Z",
        "origin": "ELP",
        "destination": "MAF",
        "dep_delay": 0,
        "arr_delay": 0
      }
    ]
  },
  {
    "session_id": 3,
    "flight_date": "2002-03-03T00:00:00.000Z",
    "carrier": "WN",
    "tail_num": "N628SW",
    "flight_count": 7,
    "max_delay": 13,
    "total_distance": 2786,
    "flight_legs": [
      {
        "flght_leg": 1,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T08:00:00.000Z",
        "origin": "OAK",
        "destination": "LAX",
        "dep_delay": 0,
        "arr_delay": -7
      },
      {
        "flght_leg": 2,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T10:00:00.000Z",
        "origin": "LAX",
        "destination": "OAK",
        "dep_delay": 0,
        "arr_delay": 5
      },
      {
        "flght_leg": 3,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T11:45:00.000Z",
        "origin": "OAK",
        "destination": "GEG",
        "dep_delay": 10,
        "arr_delay": 0
      },
      {
        "flght_leg": 4,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T13:55:00.000Z",
        "origin": "GEG",
        "destination": "SEA",
        "dep_delay": 0,
        "arr_delay": -5
      },
      {
        "flght_leg": 5,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T15:25:00.000Z",
        "origin": "SEA",
        "destination": "RNO",
        "dep_delay": 0,
        "arr_delay": -12
      },
      {
        "flght_leg": 6,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T17:30:00.000Z",
        "origin": "RNO",
        "destination": "LAS",
        "dep_delay": 0,
        "arr_delay": -10
      },
      {
        "flght_leg": 7,
        "tail_num": "N628SW",
        "dep_minute": "2002-03-03T19:23:00.000Z",
        "origin": "LAS",
        "destination": "PHX",
        "dep_delay": 13,
        "arr_delay": 20
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    ROW_NUMBER() OVER(PARTITION BY group_set  ORDER BY  DATE_TRUNC('day', base."dep_time") desc NULLS LAST ) as "session_id__0",
    DATE_TRUNC('day', base."dep_time") as "flight_date__0",
    base."carrier" as "carrier__0",
    base."tail_num" as "tail_num__0",
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as "flight_count__0",
    CASE WHEN group_set=0 THEN
      max(base."dep_delay")
      END as "max_delay__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."distance"),0)
      END as "total_distance__0",
    ROW_NUMBER() OVER(PARTITION BY group_set, DATE_TRUNC('day', base."dep_time"), base."carrier", base."tail_num"  ORDER BY  CASE WHEN group_set=1 THEN
      DATE_TRUNC('minute', base."dep_time")
      END ASC NULLS LAST ) as "flght_leg__1",
    CASE WHEN group_set=1 THEN
      base."tail_num"
      END as "tail_num__1",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('minute', base."dep_time")
      END as "dep_minute__1",
    CASE WHEN group_set=1 THEN
      base."origin"
      END as "origin__1",
    CASE WHEN group_set=1 THEN
      base."destination"
      END as "destination__1",
    CASE WHEN group_set=1 THEN
      base."dep_delay"
      END as "dep_delay__1",
    CASE WHEN group_set=1 THEN
      base."arr_delay"
      END as "arr_delay__1"
  FROM '../data/flights.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  WHERE (base."carrier"='WN')and((base."dep_time">=TIMESTAMP '2002-03-03 00:00:00')and(base."dep_time"<TIMESTAMP '2002-03-04 00:00:00'))
  GROUP BY 1,3,4,5,10,11,12,13,14,15
)
SELECT
  MAX(CASE WHEN group_set=0 THEN "session_id__0" END) as "session_id",
  "flight_date__0" as "flight_date",
  "carrier__0" as "carrier",
  "tail_num__0" as "tail_num",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  MAX(CASE WHEN group_set=0 THEN "max_delay__0" END) as "max_delay",
  MAX(CASE WHEN group_set=0 THEN "total_distance__0" END) as "total_distance",
  COALESCE(LIST({
    "flght_leg": "flght_leg__1", 
    "tail_num": "tail_num__1", 
    "dep_minute": "dep_minute__1", 
    "origin": "origin__1", 
    "destination": "destination__1", 
    "dep_delay": "dep_delay__1", 
    "arr_delay": "arr_delay__1"}  ORDER BY  "dep_minute__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "flight_legs"
FROM __stage0
GROUP BY 2,3,4
ORDER BY 2 desc NULLS LAST