A new renderer is currently being developed for Malloy. To use this experimental renderer, use the model tag ## renderer_next
. More docs to come.
## renderer_next 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 } }
[ { "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": "CO", "airport_count": 3, "average_elevation": 7061.666666666667 } ] }, { "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