What Is Malloy
PostgreSQL
There are a couple of different ways to go about this in Malloy.
Method 1: Pivoting a Visualization
We can compare performance of different years using the same x and y-axes. Line charts take the x-axis, y-axis and dimensional (color) axis as parameters.
In this case, the x-axis is month_of_year, the y-axis is flight_count and the dimensional (color) axis is the year.
source: flights is duckdb.table('../data/flights.parquet') extend { measure: flight_count is count() }
run: flights -> { group_by: month_of_year is month(dep_time) aggregate: flight_count group_by: flight_year is dep_time.year }
[ { "month_of_year": 8, "flight_count": 6415, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 6228, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 6227, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 6202, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 6118, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 6100, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 6084, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 6083, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 6064, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 6043, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 5997, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 8, "flight_count": 5993, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 5775, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 5770, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 5732, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 5707, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 5689, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 5622, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 5609, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 5533, "flight_year": "2005-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 5463, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 5385, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 5223, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 5134, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 5124, "flight_year": "2004-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 5117, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 5114, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 5017, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 5005, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 8, "flight_count": 4918, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 4892, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 4873, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 4729, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 4726, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 4720, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 8, "flight_count": 4607, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 4451, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 4440, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 4431, "flight_year": "2003-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 4407, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 4375, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 4348, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 4346, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 4340, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 4328, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 8, "flight_count": 4326, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 4261, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 4252, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 8, "flight_count": 4182, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 4102, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 4086, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 4071, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 5, "flight_count": 4047, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 4046, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 7, "flight_count": 4043, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 4020, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 1, "flight_count": 4008, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 3999, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 3914, "flight_year": "2002-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 3876, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 3, "flight_count": 3869, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 3858, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 3850, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 3819, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 10, "flight_count": 3809, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 4, "flight_count": 3778, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 6, "flight_count": 3774, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 12, "flight_count": 3744, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 11, "flight_count": 3718, "flight_year": "2000-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 3672, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 9, "flight_count": 3616, "flight_year": "2001-01-01T00:00:00.000Z" }, { "month_of_year": 2, "flight_count": 3583, "flight_year": "2002-01-01T00:00:00.000Z" } ]
SELECT EXTRACT(month FROM base."dep_time") as "month_of_year", COUNT(1) as "flight_count", DATE_TRUNC('year', base."dep_time") as "flight_year" FROM '../data/flights.parquet' as base GROUP BY 1,3 ORDER BY 2 desc NULLS LAST
By adding year as the third column, we can display different years on the same chart. Note the # line_chart tag above the query. This is a hint to the renderer to display the data as a line chart. Changing the definition of flight_year to year(dep_time)::string makes the line chart interpret the year as "categorical," giving distinct colors for each year rather than a gradient.
# line_chart run: flights -> { group_by: month_of_year is month(dep_time) aggregate: flight_count group_by: flight_year is year(dep_time)::string }
[ { "month_of_year": 8, "flight_count": 6415, "flight_year": "2005" }, { "month_of_year": 10, "flight_count": 6228, "flight_year": "2004" }, { "month_of_year": 7, "flight_count": 6227, "flight_year": "2005" }, { "month_of_year": 12, "flight_count": 6202, "flight_year": "2004" }, { "month_of_year": 6, "flight_count": 6118, "flight_year": "2005" }, { "month_of_year": 9, "flight_count": 6100, "flight_year": "2005" }, { "month_of_year": 11, "flight_count": 6084, "flight_year": "2004" }, { "month_of_year": 10, "flight_count": 6083, "flight_year": "2005" }, { "month_of_year": 5, "flight_count": 6064, "flight_year": "2005" }, { "month_of_year": 4, "flight_count": 6043, "flight_year": "2005" }, { "month_of_year": 1, "flight_count": 5997, "flight_year": "2005" }, { "month_of_year": 8, "flight_count": 5993, "flight_year": "2004" }, { "month_of_year": 7, "flight_count": 5775, "flight_year": "2004" }, { "month_of_year": 12, "flight_count": 5770, "flight_year": "2005" }, { "month_of_year": 3, "flight_count": 5732, "flight_year": "2005" }, { "month_of_year": 2, "flight_count": 5707, "flight_year": "2005" }, { "month_of_year": 9, "flight_count": 5689, "flight_year": "2004" }, { "month_of_year": 6, "flight_count": 5622, "flight_year": "2004" }, { "month_of_year": 5, "flight_count": 5609, "flight_year": "2004" }, { "month_of_year": 11, "flight_count": 5533, "flight_year": "2005" }, { "month_of_year": 3, "flight_count": 5463, "flight_year": "2004" }, { "month_of_year": 4, "flight_count": 5385, "flight_year": "2004" }, { "month_of_year": 2, "flight_count": 5223, "flight_year": "2004" }, { "month_of_year": 12, "flight_count": 5134, "flight_year": "2003" }, { "month_of_year": 1, "flight_count": 5124, "flight_year": "2004" }, { "month_of_year": 7, "flight_count": 5117, "flight_year": "2003" }, { "month_of_year": 5, "flight_count": 5114, "flight_year": "2003" }, { "month_of_year": 3, "flight_count": 5017, "flight_year": "2003" }, { "month_of_year": 6, "flight_count": 5005, "flight_year": "2003" }, { "month_of_year": 8, "flight_count": 4918, "flight_year": "2003" }, { "month_of_year": 1, "flight_count": 4892, "flight_year": "2003" }, { "month_of_year": 4, "flight_count": 4873, "flight_year": "2003" }, { "month_of_year": 11, "flight_count": 4729, "flight_year": "2003" }, { "month_of_year": 10, "flight_count": 4726, "flight_year": "2003" }, { "month_of_year": 2, "flight_count": 4720, "flight_year": "2003" }, { "month_of_year": 8, "flight_count": 4607, "flight_year": "2001" }, { "month_of_year": 6, "flight_count": 4451, "flight_year": "2001" }, { "month_of_year": 7, "flight_count": 4440, "flight_year": "2001" }, { "month_of_year": 9, "flight_count": 4431, "flight_year": "2003" }, { "month_of_year": 5, "flight_count": 4407, "flight_year": "2001" }, { "month_of_year": 6, "flight_count": 4375, "flight_year": "2002" }, { "month_of_year": 5, "flight_count": 4348, "flight_year": "2002" }, { "month_of_year": 7, "flight_count": 4346, "flight_year": "2002" }, { "month_of_year": 10, "flight_count": 4340, "flight_year": "2002" }, { "month_of_year": 4, "flight_count": 4328, "flight_year": "2001" }, { "month_of_year": 8, "flight_count": 4326, "flight_year": "2002" }, { "month_of_year": 9, "flight_count": 4261, "flight_year": "2002" }, { "month_of_year": 3, "flight_count": 4252, "flight_year": "2000" }, { "month_of_year": 8, "flight_count": 4182, "flight_year": "2000" }, { "month_of_year": 3, "flight_count": 4102, "flight_year": "2002" }, { "month_of_year": 12, "flight_count": 4086, "flight_year": "2001" }, { "month_of_year": 1, "flight_count": 4071, "flight_year": "2001" }, { "month_of_year": 5, "flight_count": 4047, "flight_year": "2000" }, { "month_of_year": 4, "flight_count": 4046, "flight_year": "2002" }, { "month_of_year": 7, "flight_count": 4043, "flight_year": "2000" }, { "month_of_year": 1, "flight_count": 4020, "flight_year": "2000" }, { "month_of_year": 1, "flight_count": 4008, "flight_year": "2002" }, { "month_of_year": 12, "flight_count": 3999, "flight_year": "2002" }, { "month_of_year": 11, "flight_count": 3914, "flight_year": "2002" }, { "month_of_year": 2, "flight_count": 3876, "flight_year": "2000" }, { "month_of_year": 3, "flight_count": 3869, "flight_year": "2001" }, { "month_of_year": 10, "flight_count": 3858, "flight_year": "2000" }, { "month_of_year": 9, "flight_count": 3850, "flight_year": "2000" }, { "month_of_year": 11, "flight_count": 3819, "flight_year": "2001" }, { "month_of_year": 10, "flight_count": 3809, "flight_year": "2001" }, { "month_of_year": 4, "flight_count": 3778, "flight_year": "2000" }, { "month_of_year": 6, "flight_count": 3774, "flight_year": "2000" }, { "month_of_year": 12, "flight_count": 3744, "flight_year": "2000" }, { "month_of_year": 11, "flight_count": 3718, "flight_year": "2000" }, { "month_of_year": 2, "flight_count": 3672, "flight_year": "2001" }, { "month_of_year": 9, "flight_count": 3616, "flight_year": "2001" }, { "month_of_year": 2, "flight_count": 3583, "flight_year": "2002" } ]
SELECT EXTRACT(month FROM base."dep_time") as "month_of_year", COUNT(1) as "flight_count", CAST(EXTRACT(year FROM base."dep_time") AS varchar) as "flight_year" FROM '../data/flights.parquet' as base GROUP BY 1,3 ORDER BY 2 desc NULLS LAST
Method 2: Filtered Aggregates
Filters make it easy to reuse aggregate calculations for trends analysis.
run: flights -> { group_by: carrier aggregate: flights_in_2002 is flight_count { where: dep_time = @2002 } flights_in_2003 is flight_count { where: dep_time = @2003 } # percent percent_change is (flight_count { where: dep_time = @2003 } - flight_count { where: dep_time = @2002 }) / nullif(flight_count { where: dep_time = @2003 }, 0) }
[ { "carrier": "WN", "flights_in_2002": 14708, "flights_in_2003": 14300, "percent_change": -0.028531468531468533 }, { "carrier": "US", "flights_in_2002": 6501, "flights_in_2003": 4321, "percent_change": -0.5045128442490164 }, { "carrier": "AA", "flights_in_2002": 5963, "flights_in_2003": 5566, "percent_change": -0.07132590729428674 }, { "carrier": "UA", "flights_in_2002": 5590, "flights_in_2003": 6068, "percent_change": 0.07877389584706658 }, { "carrier": "NW", "flights_in_2002": 5081, "flights_in_2003": 5187, "percent_change": 0.020435704646230964 }, { "carrier": "DL", "flights_in_2002": 4400, "flights_in_2003": 5536, "percent_change": 0.20520231213872833 }, { "carrier": "MQ", "flights_in_2002": 3469, "flights_in_2003": 1341, "percent_change": -1.586875466070097 }, { "carrier": "HP", "flights_in_2002": 1586, "flights_in_2003": 1609, "percent_change": 0.014294592914853946 }, { "carrier": "AS", "flights_in_2002": 1263, "flights_in_2003": 1484, "percent_change": 0.14892183288409702 }, { "carrier": "CO", "flights_in_2002": 1087, "flights_in_2003": 1162, "percent_change": 0.06454388984509467 }, { "carrier": "EV", "flights_in_2002": 0, "flights_in_2003": 5989, "percent_change": 1 }, { "carrier": "RU", "flights_in_2002": 0, "flights_in_2003": 4011, "percent_change": 1 }, { "carrier": "B6", "flights_in_2002": 0, "flights_in_2003": 1530, "percent_change": 1 }, { "carrier": "OH", "flights_in_2002": 0, "flights_in_2003": 0, "percent_change": null }, { "carrier": "TZ", "flights_in_2002": 0, "flights_in_2003": 572, "percent_change": 1 } ]
SELECT base."carrier" as "carrier", (COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2002-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)) as "flights_in_2002", (COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)) as "flights_in_2003", ((((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)))-((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2002-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-01-01 00:00:00') THEN 1 END)))))*1.0/(NULLIF((COUNT(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN 1 END)),0)) as "percent_change" FROM '../data/flights.parquet' as base GROUP BY 1 ORDER BY 2 desc NULLS LAST
Method 3: Calculate with Lag
The calculate: clause is Malloy's window function equivalent, and allows us to compute year over year calculations using the lag function:
run: flights -> { group_by: dep_year is dep_time.year aggregate: flight_count calculate: last_year is lag(flight_count, 1) # percent growth is (lag(flight_count, 1) - flight_count) / lag(flight_count, 1) order_by: dep_year }
[ { "dep_year": "2000-01-01T00:00:00.000Z", "flight_count": 47142, "last_year": null, "growth": null }, { "dep_year": "2001-01-01T00:00:00.000Z", "flight_count": 49175, "last_year": 47142, "growth": -0.043125026515633616 }, { "dep_year": "2002-01-01T00:00:00.000Z", "flight_count": 49648, "last_year": 49175, "growth": -0.00961870869344179 }, { "dep_year": "2003-01-01T00:00:00.000Z", "flight_count": 58676, "last_year": 49648, "growth": -0.18184015468901063 }, { "dep_year": "2004-01-01T00:00:00.000Z", "flight_count": 68397, "last_year": 58676, "growth": -0.16567250664666985 }, { "dep_year": "2005-01-01T00:00:00.000Z", "flight_count": 71789, "last_year": 68397, "growth": -0.04959281839846777 } ]
SELECT DATE_TRUNC('year', base."dep_time") as "dep_year", COUNT(1) as "flight_count", LAG((COUNT(1)),1) OVER( ORDER BY DATE_TRUNC('year', base."dep_time") ASC NULLS LAST ) as "last_year", (((LAG((COUNT(1)),1) OVER( ORDER BY DATE_TRUNC('year', base."dep_time") ASC NULLS LAST ))-(COUNT(1))))*1.0/(LAG((COUNT(1)),1) OVER( ORDER BY DATE_TRUNC('year', base."dep_time") ASC NULLS LAST )) as "growth" FROM '../data/flights.parquet' as base GROUP BY 1 ORDER BY 1 ASC NULLS LAST
Bonus: Relative timeframes and expression reuse
You might like to write queries that automatically adjust based on the current timeframe. The query below uses date arithmetic to filter the data to time frames relative to now. These measures probably aren't generally useful in the model so we use the extend: operation to add these measure so they are only locally accessable within the query.
source: inventory_items is duckdb.table('../data/inventory_items.parquet') source: order_items is duckdb.table('../data/order_items.parquet') extend { join_one: inventory_items on inventory_item_id = inventory_items.id measure: order_item_count is count() view: category_growth is { extend: { // add measures for use just in this query measure: last_year is order_item_count { where: created_at ? now.year - 1 year } prior_year is order_item_count { where: created_at ? now.year - 2 years } } limit: 10 group_by: inventory_items.product_category aggregate: last_year prior_year # percent percent_change is (last_year - prior_year) / nullif(last_year, 0) } } run: order_items -> category_growth
[ { "product_category": "Accessories", "last_year": 0, "prior_year": 12713, "percent_change": null }, { "product_category": "Plus", "last_year": 0, "prior_year": 1170, "percent_change": null }, { "product_category": "Fashion Hoodies & Sweatshirts", "last_year": 0, "prior_year": 4617, "percent_change": null }, { "product_category": "Suits & Sport Coats", "last_year": 0, "prior_year": 1711, "percent_change": null }, { "product_category": "Underwear", "last_year": 0, "prior_year": 1740, "percent_change": null } ]
SELECT inventory_items_0."product_category" as "product_category", (COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END)) as "last_year", (COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year) + INTERVAL (1) year)) THEN 1 END)) as "prior_year", ((((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END)))-((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (2) year) + INTERVAL (1) year)) THEN 1 END)))))*1.0/(NULLIF(((COUNT(CASE WHEN (base."created_at">=(DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year)) and (base."created_at"<((DATE_TRUNC('year', LOCALTIMESTAMP) - INTERVAL (1) year) + INTERVAL (1) year)) THEN 1 END))),0)) as "percent_change" FROM '../data/order_items.parquet' as base LEFT JOIN '../data/inventory_items.parquet' AS inventory_items_0 ON base."inventory_item_id"=inventory_items_0."id" GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10