Transposed Tables

VSCode

For comparison, it is often interesting to turn a table on it's side. The # transpose tag on a query does just that.

source: airports is duckdb.table('../data/airports.parquet') extend {
  measure: airport_count is count()
}

Normal Table

run: airports -> {
  group_by: fac_type
  aggregate: 
    airport_count
    californa_count is airport_count { where: state = 'CA' }
    ny_count is airport_count { where: state = 'CA' }
    major_count is airport_count { where: major = 'Y' }
    average_elevation is elevation.avg()
}

QUERY RESULTS

[
  {
    "fac_type": "AIRPORT",
    "airport_count": 13925,
    "californa_count": 569,
    "ny_count": 569,
    "major_count": 270,
    "average_elevation": 1237.0441651705567
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 5135,
    "californa_count": 396,
    "ny_count": 396,
    "major_count": 0,
    "average_elevation": 950.5125608568646
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 473,
    "californa_count": 12,
    "ny_count": 12,
    "major_count": 0,
    "average_elevation": 488.82241014799155
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 125,
    "californa_count": 2,
    "ny_count": 2,
    "major_count": 0,
    "average_elevation": 806.144
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 86,
    "californa_count": 2,
    "ny_count": 2,
    "major_count": 0,
    "average_elevation": 1375.046511627907
  }
]

SELECT 
   base."fac_type" as "fac_type",
   COUNT(1) as "airport_count",
   (COUNT(CASE WHEN base."state"='CA' THEN 1 END)) as "californa_count",
   (COUNT(CASE WHEN base."state"='CA' THEN 1 END)) as "ny_count",
   (COUNT(CASE WHEN base."major"='Y' THEN 1 END)) as "major_count",
   AVG(base."elevation") as "average_elevation"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Transposed Table

Great for comparison

# transpose
run: airports -> {
  group_by: fac_type
  aggregate: 
    airport_count
    californa_count is airport_count { where: state = 'CA' }
    ny_count is airport_count { where: state = 'CA' }
    major_count is airport_count { where: major = 'Y' }
    average_elevation is elevation.avg()
}

QUERY RESULTS

[
  {
    "fac_type": "AIRPORT",
    "airport_count": 13925,
    "californa_count": 569,
    "ny_count": 569,
    "major_count": 270,
    "average_elevation": 1237.0441651705567
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 5135,
    "californa_count": 396,
    "ny_count": 396,
    "major_count": 0,
    "average_elevation": 950.5125608568646
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 473,
    "californa_count": 12,
    "ny_count": 12,
    "major_count": 0,
    "average_elevation": 488.82241014799155
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 125,
    "californa_count": 2,
    "ny_count": 2,
    "major_count": 0,
    "average_elevation": 806.144
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 86,
    "californa_count": 2,
    "ny_count": 2,
    "major_count": 0,
    "average_elevation": 1375.046511627907
  }
]

SELECT 
   base."fac_type" as "fac_type",
   COUNT(1) as "airport_count",
   (COUNT(CASE WHEN base."state"='CA' THEN 1 END)) as "californa_count",
   (COUNT(CASE WHEN base."state"='CA' THEN 1 END)) as "ny_count",
   (COUNT(CASE WHEN base."major"='Y' THEN 1 END)) as "major_count",
   AVG(base."elevation") as "average_elevation"
FROM '../data/airports.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST