Malloy Documentation
search
previousSegment Maps
Using MalloySQLnext

Legacy Renderer

VSCode document

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

document
## 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

document
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_​typeairport_​countmajor_​countaverage_​elevationtop_​states
AIRPORT13,9252701,237.044
stateairport_​countaverage_​elevation
TX1,3891,039.952
IL625681.067
CA5691,203.562
HELIPORT5,1350950.513
stateairport_​countaverage_​elevation
TX435455.009
CA396906.831
PA307709.498
SEAPLANE BASE4730488.822
stateairport_​countaverage_​elevation
AK104132.048
MN721,114
FL4351.93
ULTRALIGHT1250806.144
stateairport_​countaverage_​elevation
LA1885.111
IN17735.882
PA13961.846
STOLPORT8601,375.047
stateairport_​countaverage_​elevation
FL1387
TN9912.444
TX8684.125
GLIDERPORT3701,611.405
stateairport_​countaverage_​elevation
TX51,072.6
FL438.75
CO37,061.667
BALLOONPORT1201,047.25
stateairport_​countaverage_​elevation
IL2811.5
NJ2265
MA1465
[
  {
    "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