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_count	californa_count	ny_count	major_count	average_elevation
AIRPORT	13,925	569	569	270	1,237.044
HELIPORT	5,135	396	396	0	950.513
SEAPLANE BASE	473	12	12	0	488.822
ULTRALIGHT	125	2	2	0	806.144
STOLPORT	86	2	2	0	1,375.047

[
  {
    "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 
   airports."fac_type" as "fac_type",
   COUNT( 1) as "airport_count",
   (COUNT( CASE WHEN airports."state"='CA' THEN 1 END)) as "californa_count",
   (COUNT( CASE WHEN airports."state"='CA' THEN 1 END)) as "ny_count",
   (COUNT( CASE WHEN airports."major"='Y' THEN 1 END)) as "major_count",
   AVG(airports."elevation") as "average_elevation"
FROM '../data/airports.parquet' as airports
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	HELIPORT	SEAPLANE BASE	ULTRALIGHT	STOLPORT
airport_count	13,925	5,135	473	125	86
californa_count	569	396	12	2	2
ny_count	569	396	12	2	2
major_count	270	0	0	0	0
average_elevation	1,237.044	950.513	488.822	806.144	1,375.047

[
  {
    "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 
   airports."fac_type" as "fac_type",
   COUNT( 1) as "airport_count",
   (COUNT( CASE WHEN airports."state"='CA' THEN 1 END)) as "californa_count",
   (COUNT( CASE WHEN airports."state"='CA' THEN 1 END)) as "ny_count",
   (COUNT( CASE WHEN airports."major"='Y' THEN 1 END)) as "major_count",
   AVG(airports."elevation") as "average_elevation"
FROM '../data/airports.parquet' as airports
GROUP BY 1
ORDER BY 2 desc NULLS LAST