Malloy Documentation
search
previousTransform Data
Dimensional Indexesnext

Sessionized Data - Map/Reduce

VSCode document

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.

document
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": 4,
    "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
      }
    ]
  },
  {
    "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": 2,
    "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": 5,
    "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": 3,
    "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": 1,
    "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
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    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 ROW_NUMBER() OVER(PARTITION BY group_set  ORDER BY  DATE_TRUNC('day', base."dep_time") desc NULLS LAST ) END as "session_id__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",
    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",
    CASE WHEN group_set=1 THEN 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 ) END as "flght_leg__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,2,3,4,9,10,11,12,13,14
)
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