Legacy Renderer

VSCode

A new renderer is currently being developed for Malloy; however, some functionality is not yet fully implemented. To revert back to the legacy renderer, use the model tag ## renderer_legacy.

You will need to revert back to the legacy renderer if you are using:

Export to HTML in VS Code
Drilling
Pivoting
Transpose

## renderer_legacy
source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: name is concat(code, ' - ', full_name)
  measure: airport_count is count()
}

An example table

run: airports -> {
  group_by: fac_type
  aggregate: 
    airport_count
    major_count is airport_count { where: major = 'Y' }
    average_elevation is elevation.avg()
  nest: top_states is {
    group_by: state
    aggregate:
      airport_count
      average_elevation is elevation.avg()
    limit: 3
  }
}

QUERY RESULTS

fac_type

airport_count

major_count

average_elevation

top_states

AIRPORT

13,925

270

1,237.044

state	airport_count	average_elevation
TX	1,389	1,039.952
IL	625	681.067
CA	569	1,203.562

HELIPORT

5,135

950.513

state	airport_count	average_elevation
TX	435	455.009
CA	396	906.831
PA	307	709.498

SEAPLANE BASE

473

488.822

state	airport_count	average_elevation
AK	104	132.048
MN	72	1,114
FL	43	51.93

ULTRALIGHT

125

806.144

state	airport_count	average_elevation
LA	18	85.111
IN	17	735.882
PA	13	961.846

STOLPORT

1,375.047

state	airport_count	average_elevation
FL	13	87
TN	9	912.444
TX	8	684.125

GLIDERPORT

1,611.405

state	airport_count	average_elevation
TX	5	1,072.6
FL	4	38.75
CA	3	1,108.333

BALLOONPORT

1,047.25

state	airport_count	average_elevation
IL	2	811.5
NJ	2	265
MA	1	465

[
  {
    "fac_type": "AIRPORT",
    "airport_count": 13925,
    "major_count": 270,
    "average_elevation": 1237.0441651705567,
    "top_states": [
      {
        "state": "TX",
        "airport_count": 1389,
        "average_elevation": 1039.9524838012958
      },
      {
        "state": "IL",
        "airport_count": 625,
        "average_elevation": 681.0672
      },
      {
        "state": "CA",
        "airport_count": 569,
        "average_elevation": 1203.5623901581723
      }
    ]
  },
  {
    "fac_type": "HELIPORT",
    "airport_count": 5135,
    "major_count": 0,
    "average_elevation": 950.5125608568646,
    "top_states": [
      {
        "state": "TX",
        "airport_count": 435,
        "average_elevation": 455.0091954022989
      },
      {
        "state": "CA",
        "airport_count": 396,
        "average_elevation": 906.8308080808081
      },
      {
        "state": "PA",
        "airport_count": 307,
        "average_elevation": 709.4983713355049
      }
    ]
  },
  {
    "fac_type": "SEAPLANE BASE",
    "airport_count": 473,
    "major_count": 0,
    "average_elevation": 488.82241014799155,
    "top_states": [
      {
        "state": "AK",
        "airport_count": 104,
        "average_elevation": 132.04807692307693
      },
      {
        "state": "MN",
        "airport_count": 72,
        "average_elevation": 1114
      },
      {
        "state": "FL",
        "airport_count": 43,
        "average_elevation": 51.93023255813954
      }
    ]
  },
  {
    "fac_type": "ULTRALIGHT",
    "airport_count": 125,
    "major_count": 0,
    "average_elevation": 806.144,
    "top_states": [
      {
        "state": "LA",
        "airport_count": 18,
        "average_elevation": 85.11111111111111
      },
      {
        "state": "IN",
        "airport_count": 17,
        "average_elevation": 735.8823529411765
      },
      {
        "state": "PA",
        "airport_count": 13,
        "average_elevation": 961.8461538461538
      }
    ]
  },
  {
    "fac_type": "STOLPORT",
    "airport_count": 86,
    "major_count": 0,
    "average_elevation": 1375.046511627907,
    "top_states": [
      {
        "state": "FL",
        "airport_count": 13,
        "average_elevation": 87
      },
      {
        "state": "TN",
        "airport_count": 9,
        "average_elevation": 912.4444444444445
      },
      {
        "state": "TX",
        "airport_count": 8,
        "average_elevation": 684.125
      }
    ]
  },
  {
    "fac_type": "GLIDERPORT",
    "airport_count": 37,
    "major_count": 0,
    "average_elevation": 1611.4054054054054,
    "top_states": [
      {
        "state": "TX",
        "airport_count": 5,
        "average_elevation": 1072.6
      },
      {
        "state": "FL",
        "airport_count": 4,
        "average_elevation": 38.75
      },
      {
        "state": "CA",
        "airport_count": 3,
        "average_elevation": 1108.3333333333333
      }
    ]
  },
  {
    "fac_type": "BALLOONPORT",
    "airport_count": 12,
    "major_count": 0,
    "average_elevation": 1047.25,
    "top_states": [
      {
        "state": "IL",
        "airport_count": 2,
        "average_elevation": 811.5
      },
      {
        "state": "NJ",
        "airport_count": 2,
        "average_elevation": 265
      },
      {
        "state": "MA",
        "airport_count": 1,
        "average_elevation": 465
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    base."fac_type" as "fac_type__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "airport_count__0",
    (CASE WHEN group_set=0 THEN
      COUNT(CASE WHEN base."major"='Y' THEN 1 END)
      END) as "major_count__0",
    CASE WHEN group_set=0 THEN
      AVG(base."elevation")
      END as "average_elevation__0",
    CASE WHEN group_set=1 THEN
      base."state"
      END as "state__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "airport_count__1",
    CASE WHEN group_set=1 THEN
      AVG(base."elevation")
      END as "average_elevation__1"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,6
)
SELECT
  "fac_type__0" as "fac_type",
  MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count",
  MAX(CASE WHEN group_set=0 THEN "major_count__0" END) as "major_count",
  MAX(CASE WHEN group_set=0 THEN "average_elevation__0" END) as "average_elevation",
  COALESCE(LIST({
    "state": "state__1", 
    "airport_count": "airport_count__1", 
    "average_elevation": "average_elevation__1"}  ORDER BY  "airport_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:3],[]) as "top_states"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST