Vinyl Syntax
Basic syntax
Basic syntax
SELECT
"t0"."Year",
"t0"."Name",
"t0"."Horsepower"
FROM "cars" AS "t0"
ORDER BY
"t0"."Year" ASC
LIMIT 10
SELECT
"t0"."Year",
"t0"."Name",
"t0"."Horsepower"
FROM "cars" AS "t0"
ORDER BY
"t0"."Year" ASC
LIMIT 10
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ Year ┃ Name ┃ Horsepower ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ timestamp(6) │ string │ float64 │
├─────────────────────┼───────────────────────────┼────────────┤
│ 1970-01-01 00:00:00 │ chevrolet chevelle malibu │ 130.0 │
│ 1970-01-01 00:00:00 │ buick skylark 320 │ 165.0 │
│ 1970-01-01 00:00:00 │ plymouth satellite │ 150.0 │
│ 1970-01-01 00:00:00 │ amc rebel sst │ 150.0 │
│ 1970-01-01 00:00:00 │ ford torino │ 140.0 │
│ 1970-01-01 00:00:00 │ ford galaxie 500 │ 198.0 │
│ 1970-01-01 00:00:00 │ chevrolet impala │ 220.0 │
│ 1970-01-01 00:00:00 │ plymouth fury iii │ 215.0 │
│ 1970-01-01 00:00:00 │ pontiac catalina │ 225.0 │
│ 1970-01-01 00:00:00 │ amc ambassador dpl │ 190.0 │
└─────────────────────┴───────────────────────────┴────────────┘
╭────────────────────────╮
│ Limit │
│ Year: timestamp(6) │
│ Name: string │
│ Horsepower: float64 │
╰────────────────────────╯
▲
│
│
│
╭────────────────────────╮
│ Sort │
│ Year: timestamp(6) │
╭────▶│ Name: string │
│ │ Horsepower: float64 │
│ ╰────────────────────────╯
│ ▲
│ │
│ │
│ │
╭────────────────────╮ │
│ SortKey │ │
│ :: timestamp(6) │ │
╰────────────────────╯ │
▲ │
│ │
│ │
│ │
╭────────────────────╮ │
│ Year: Field │ │
│ :: timestamp(6) │ │
╰────────────────────╯ │
▲ │
│ │
│ │
╰──────────────────│
╭────────────────────────╮
│ Project │
│ Year: timestamp(6) │
╭──────────────────────────────────────▶│ Name: string │◀─╮
│ │ Horsepower: float64 │ │
│ ╰────────────────────────╯ │
│ ▲ │
│ ╭────────│ │
│ │ │ │
│ │ │ │
╭────────────────────╮ ╭──────────────╮ │ │ ╭───────────────────╮
│ Year: Field │ │ Name: Field │ │ │ │ Horsepower: Field │
│ :: timestamp(6) │ │ :: string │─╯ │ │ :: float64 │
╰────────────────────╯ ╰──────────────╯ │ ╰───────────────────╯
▲ ▲ │ ▲
│ │ │ │
│ │ │ │
│ │ │ │
│ │ ╭──────────────────────────────╮
│ │ │ cars: DatabaseTable │
│ │ │ Name: string │
│ │ │ Miles_per_Gallon: float64 │
│ │ │ Cylinders: int64 │
│ │ │ Displacement: float64 │
╰────────────────────────────────────│ Horsepower: float64 │
│ Weight_in_lbs: int64 │
│ Acceleration: float64 │
│ Year: timestamp(6) │
│ Origin: string │
╰──────────────────────────────╯
Null handling
Null handling
column is None and column is not None will not work as expected
because Python requires is statements to evaluate to True or False.
SELECT
COALESCE("t0"."Airport__Name", 'Unknown') AS "airport_name"
FROM "birdstrikes" AS "t0"
WHERE
"t0"."Effect__Amount_of_damage" IS NULL AND NOT "t0"."Airport__Name" IS NULL
SELECT
COALESCE("t0"."Airport__Name", 'Unknown') AS "airport_name"
FROM "birdstrikes" AS "t0"
WHERE
"t0"."Effect__Amount_of_damage" IS NULL AND NOT "t0"."Airport__Name" IS NULL
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ airport_name ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string │
├───────────────────────────────┤
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ WASHINGTON DULLES INTL ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ BARKSDALE AIR FORCE BASE ARPT │
│ … │
└───────────────────────────────┘
╭─────────────────────────╮
│ Project │
╭──────▶│ airport_name: string │
│ ╰─────────────────────────╯
│ ▲
│ │
│ │
│ │
╭──────────────╮ │
│ Coalesce │ │
│ :: string │◀─╮ │
╰──────────────╯ │ │
▲ │ │
│ │ │
│ │ │
│ │ │
╭──────────────────────╮ │ ╭────────────────────╮
│ Airport__Name: Field │ │ │ 'Unknown': Literal │
│ :: string │────────╯ │ :: string │
╰──────────────────────╯ ╰────────────────────╯
▲ │
│ │
│ │
│ │
│ │
│ ╭───────────────────────────────────────╮
│ │ Filter │
│ │ Airport__Name: string │
│ │ Aircraft__Make_Model: string │
│ │ Flight_Date: string │
│ │ Aircraft__Airline_Operator: string │
│ │ Origin_State: string │
│ │ When__Phase_of_flight: string │
╰───────────────────▲│ Wildlife__Size: string │
││ Wildlife__Species: string │
││ When__Time_of_day: string │
││ Cost__Other: int64 │
││ Cost__Repair: int64 │
││ Cost__Total_$: int64 │
││ Speed_IAS_in_knots: float64 │
││ Effect__Amount_of_damage: string │
│╰───────────────────────────────────────╯
│ ▲
│ │
│ │
╭───────────────╮ │
│ NotNull │ │
│ :: boolean │ │
╰───────────────╯ │
▲ │
│ │
│ │
│ │
╭──────────────────────╮ │
│ Airport__Name: Field │ │
│ :: string │ │
╰──────────────────────╯ │
▲ │
│ │
│ │
│ │
│ │
│╭───────────────────────────────────────╮
││ Filter │
││ Airport__Name: string │
││ Aircraft__Make_Model: string │
││ Flight_Date: string │
││ Aircraft__Airline_Operator: string │
││ Origin_State: string │
││ When__Phase_of_flight: string │
╭────▶│ Wildlife__Size: string │
│ │ Wildlife__Species: string │
│ │ When__Time_of_day: string │
│ │ Cost__Other: int64 │
│ │ Cost__Repair: int64 │
│ │ Cost__Total_$: int64 │
│ │ Speed_IAS_in_knots: float64 │
│ │ Effect__Amount_of_damage: string │
│ ╰───────────────────────────────────────╯
│ ▲
│ │
│ │
╭───────────────╮ │
│ IsNull │ │
│ :: boolean │ │
╰───────────────╯ │
▲ │
│ │
│ │
│ │
╭─────────────────────────────────╮ │
│ Effect__Amount_of_damage: Field │ │
│ :: string │ │
╰─────────────────────────────────╯ │
▲ │
│ │
│ │
│ │
│ │
│ ╭───────────────────────────────────────╮
│ │ birdstrikes: DatabaseTable │
│ │ Airport__Name: string │
│ │ Aircraft__Make_Model: string │
│ │ Flight_Date: string │
│ │ Aircraft__Airline_Operator: string │
│ │ Origin_State: string │
│ │ When__Phase_of_flight: string │
╰─────│ Wildlife__Size: string │
│ Wildlife__Species: string │
│ When__Time_of_day: string │
│ Cost__Other: int64 │
│ Cost__Repair: int64 │
│ Cost__Total_$: int64 │
│ Speed_IAS_in_knots: float64 │
│ Effect__Amount_of_damage: string │
╰───────────────────────────────────────╯
Functions
Functions
SELECT
"t0"."sepalLength",
"t0"."sepalWidth",
"t0"."petalLength",
"t0"."petalWidth",
"t0"."species",
(
"t0"."petalLength" + "t0"."petalWidth"
) * CAST(2 AS TINYINT) AS "petalPerimeter"
FROM "iris" AS "t0"
SELECT
"t0"."sepalLength",
"t0"."sepalWidth",
"t0"."petalLength",
"t0"."petalWidth",
"t0"."species",
(
"t0"."petalLength" + "t0"."petalWidth"
) * CAST(2 AS TINYINT) AS "petalPerimeter"
FROM "iris" AS "t0"
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ sepalLength ┃ sepalWidth ┃ petalLength ┃ petalWidth ┃ species ┃ petalPerimeter ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ float64 │ float64 │ float64 │ float64 │ string │ float64 │
├─────────────┼────────────┼─────────────┼────────────┼─────────┼────────────────┤
│ 5.1 │ 3.5 │ 1.4 │ 0.2 │ setosa │ 3.2 │
│ 4.9 │ 3.0 │ 1.4 │ 0.2 │ setosa │ 3.2 │
│ 4.7 │ 3.2 │ 1.3 │ 0.2 │ setosa │ 3.0 │
│ 4.6 │ 3.1 │ 1.5 │ 0.2 │ setosa │ 3.4 │
│ 5.0 │ 3.6 │ 1.4 │ 0.2 │ setosa │ 3.2 │
│ 5.4 │ 3.9 │ 1.7 │ 0.4 │ setosa │ 4.2 │
│ 4.6 │ 3.4 │ 1.4 │ 0.3 │ setosa │ 3.4 │
│ 5.0 │ 3.4 │ 1.5 │ 0.2 │ setosa │ 3.4 │
│ 4.4 │ 2.9 │ 1.4 │ 0.2 │ setosa │ 3.2 │
│ 4.9 │ 3.1 │ 1.5 │ 0.1 │ setosa │ 3.2 │
│ … │ … │ … │ … │ … │ … │
└─────────────┴────────────┴─────────────┴────────────┴─────────┴────────────────┘
╭────────────────────────────╮
│ Project │
│ sepalLength: float64 │
│ sepalWidth: float64 │
╭──────────────────────────────────────────────────────────────────────────────────────────────────────────────▶│ petalLength: float64 │◀─────────────────╮
│ │ petalWidth: float64 │ │
│ │ species: string │ │
│ ╭─────────▶│ petalPerimeter: float64 │ │
│ │ ╰────────────────────────────╯◀──╮ │
│ │ ▲ ▲ │ │
│ ╭───────────────────────────────────────────────────────────────────────────────────────│ │ │
│ │ │ │ │ │ │
│ │ ╭───────────╯ │ │ │ │
╭────────────────────╮ ╭───────────────────╮ ╭────────────────╮ │ ╭───────────────╮│ │ │ │
│ sepalLength: Field │ │ sepalWidth: Field │ │ species: Field │ │ │ Multiply ││ │ │ │
│ :: float64 │ │ :: float64 │ │ :: string │────╯ │ :: float64 │◀─────────│─────────────────│──────────────│──────────────────╮
╰────────────────────╯ ╰───────────────────╯ ╰────────────────╯ ╰───────────────╯ │ │ │ │
▲ ▲ ▲ ▲ │ │ │ │
│ │ │ │ │ ╰──────────────│─────────────────────────────────────╮
│ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ │ │ ╭────────────╮ │ │ ╭───────────────╮ │
│ │ ╰────────────╮ │ 2: Literal │ │ │ │ Add │ │
│ │ │ │ :: int8 │ │ │ │ :: float64 │◀╮ │
│ │ │ ╰────────────╯ │ │ ╰───────────────╯ │ │
│ │ │ │ │ ▲ │ │
│ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ ╰───────────────────────────────────────────╮ ╰────────────────────────╮ │ │ │ │ │
│ │ │ │ ╭────────────────────╮ │ ╭───────────────────╮
│ │ │ │ │ petalLength: Field │ │ │ petalWidth: Field │
│ │ │ │ │ :: float64 │────────╯ │ :: float64 │
│ │ │ │ ╰────────────────────╯ ╰───────────────────╯
│ │ │ │ ▲ ▲
│ ╰─────────────────────╮ │ │ │ │
│ │ │ │ │ │
│ │ │ │────────────────────────────────│─────────────────────────────────────╯
│ │ ╭─────────────────────────╮ │
│ ╰────────│ iris: DatabaseTable │ │
│ │ sepalLength: float64 │ │
│ │ sepalWidth: float64 │ │
╰─────────────────────────────────────────────────────────────────────────────────────────────────────────────────│ petalLength: float64 │───────────────────╯
│ petalWidth: float64 │
│ species: string │
╰─────────────────────────╯
Window Automation
Window Automation
SELECT
"t1"."Date",
"t1"."CO2_normalized"
FROM (
SELECT
EXTRACT(month FROM "t0"."Date") AS "ExtractMonth(Date)",
"t0"."CO2",
"t0"."Date",
"t0"."CO2" - AVG("t0"."CO2") OVER (PARTITION BY EXTRACT(month FROM "t0"."Date") ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "CO2_normalized"
FROM "co2_concentration" AS "t0"
) AS "t1"
ORDER BY
"t1"."Date" ASC
SELECT
"t1"."Date",
"t1"."CO2_normalized"
FROM (
SELECT
EXTRACT(month FROM "t0"."Date") AS "ExtractMonth(Date)",
"t0"."CO2",
"t0"."Date",
"t0"."CO2" - AVG("t0"."CO2") OVER (PARTITION BY EXTRACT(month FROM "t0"."Date") ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "CO2_normalized"
FROM "co2_concentration" AS "t0"
) AS "t1"
ORDER BY
"t1"."Date" ASC
┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ Date ┃ CO2_normalized ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ date │ float64 │
├────────────┼────────────────┤
│ 1958-03-01 │ -38.557288 │
│ 1958-04-01 │ -38.167797 │
│ 1958-05-01 │ -38.147167 │
│ 1958-07-01 │ -37.738333 │
│ 1958-08-01 │ -36.673667 │
│ 1958-09-01 │ -36.724833 │
│ 1958-11-01 │ -37.977833 │
│ 1958-12-01 │ -37.934667 │
│ 1959-01-01 │ -37.171695 │
│ 1959-02-01 │ -37.620862 │
│ … │ … │
└────────────┴────────────────┘
╭────────────────────────────╮
│ Sort │
│ Date: date │
│ CO2_normalized: float64 │
╰────────────────────────────╯
▲ ▲
│ │
│ │
╭────────────╮ │
│ SortKey │ │
│ :: date │ │
╰────────────╯ │
▲ │
│ │
│ │
│ │
╭─────────────╮ │
│ Date: Field │ │
│ :: date │ │
╰─────────────╯ │
▲ │
│ │
╰──────╮ │
│ │
╰───────│
╭────────────────────────────╮
│ Project │
╭────────────────────▶│ Date: date │
│ │ CO2_normalized: float64 │
│ ╰────────────────────────────╯
│ ▲▲
│ ││
│ ││
╭─────────────╮ ╭───────────────────────╮
│ Date: Field │ │ CO2_normalized: Field │
│ :: date │ │ :: float64 │
╰─────────────╯ ╰───────────────────────╯
▲ ▲│
│ ││
│ ││
╰───────────────────────────────────╮│
││
╭──────────────────────────────╮
│ Project │
│ ExtractMonth(Date): int32 │
╭────────────▶│ CO2: float64 │◀─╮
│ │ Date: date │ │
│ │ CO2_normalized: float64 │ │
│ ╰──────────────────────────────╯ │
│ ▲ ▲ │
│ ╭──────────────────────│ │ │
│ │ │ │ │
╭───────────────╮ │ │ │ │ │
│ Subtract │ │ │ │ │ │
╭───────▶│ :: float64 │───────│──────╯ │ │ │
│ ╰───────────────╯ │ │ │ │
│ ▲ │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
│ │ │ │ │ │
╭────────────────╮ │ │ │ │ │
│ WindowFunction │ │ │ │ │ │
│ :: float64 │ │ │ │ │ │
╰────────────────╯ │ │ │ │ │
▲───╮ │ │ │ │ │
│ │ │ │ │ │ │
│ ╰────────────╰───────────╮ │ │ │ │
│ │ │ │ │ │ │
╭───────────────╮ │ ╭─────────────────╮ │ │ │ │ │
│ Mean │ │ │ RowsWindowFrame │ │ │ │ │ │
│ :: float64 │───────╯ │ :: ∅ │◀─│───│───────╮ ╰╮│ │
╰───────────────╯ ╰─────────────────╯ │ │ │ ││ │
▲ ▲ │ │ │ ││ │
│ │ │ │ │ ││ │
│ │ │ │ ╰───────────╮ ││ │
│ │ ╰───│ │ ││ │
│ │ ╭───────────────╮ │ ╭──────────────╮ │
│ │ │ CO2: Field │ │ │ ExtractMonth │ │
╰────────────────────────────────────────│ :: float64 │ ╰───│ :: int32 │ │
│ ╰───────────────╯ ╰──────────────╯ │
│ ▲ │▲ │
│ │ ││ │
╰───────────────│──────╮ ││ │
│ │ ││ │
│ │ ││ ╭─────────────╮
│ │ ││ │ Date: Field │
│ │ │╰────────│ :: date │
│ │ │ ╰─────────────╯
│ ╰───────────╮ │ ▲
│ │ │ │
│ │ │ ╭────────╯
│ │ │ │
│ ╰───────────│───────╯
│ ╭──────────────────────────────────╮
│ │ co2_concentration: DatabaseTable │
╰────────────│ CO2: float64 │
│ Date: date │
╰──────────────────────────────────╯
Structs
Structs
SELECT
*
FROM (
SELECT
"t0"."day",
"t0"."normal",
"t0"."actual",
"t0"."id",
"t0"."actual"."high" - "t0"."normal"."high" AS "high_vs_expected"
FROM "weather" AS "t0"
) AS "t1"
WHERE
NOT "t1"."high_vs_expected" IS NULL
SELECT
*
FROM (
SELECT
"t0"."day",
"t0"."normal",
"t0"."actual",
"t0"."id",
"t0"."actual"."high" - "t0"."normal"."high" AS "high_vs_expected"
FROM "weather" AS "t0"
) AS "t1"
WHERE
NOT "t1"."high_vs_expected" IS NULL
┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━┓
┃ day ┃ normal ┃ actual ┃ id ┃ high_vs_expected ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━┩
│ string │ struct<high: int64, low: int64> │ struct<high: int64, low: int64> │ int64 │ int64 │
├────────┼─────────────────────────────────┼─────────────────────────────────┼───────┼──────────────────┤
│ M │ {'high': 50, 'low': 38} │ {'high': 48, 'low': 36} │ 0 │ -2 │
│ T │ {'high': 50, 'low': 38} │ {'high': 50, 'low': 40} │ 1 │ 0 │
│ W │ {'high': 50, 'low': 38} │ {'high': 55, 'low': 36} │ 2 │ 5 │
│ T │ {'high': 50, 'low': 38} │ {'high': 51, 'low': 33} │ 3 │ 1 │
│ F │ {'high': 50, 'low': 38} │ {'high': 50, 'low': 30} │ 4 │ 0 │
└────────┴─────────────────────────────────┴─────────────────────────────────┴───────┴──────────────────┘
╭────────────────────────────────────────────╮
│ DropNa │
│ day: string │
│ normal: struct<high: int64, low: int64> │
│ actual: struct<high: int64, low: int64> │
│ id: int64 │
│ high_vs_expected: int64 │
╰────────────────────────────────────────────╯
▲ ▲
│ │
│ │
│ │
╭─────────────────────────╮ │
│ high_vs_expected: Field │ │
│ :: int64 │ │
╰─────────────────────────╯ │
▲ │
│ │
│ │
╰──────────────────────│
╭────────────────────────────────────────────╮
│ Project │
│ day: string │
│ normal: struct<high: int64, low: int64> │
╭─────────────────────────────────────────────────────────────────────────────────────────────────────▶│ actual: struct<high: int64, low: int64> │◀─────────────────────────────────────╮
│ │ id: int64 │ │
│ │ high_vs_expected: int64 │ │
│ ╰────────────────────────────────────────────╯ │
│ ▲ ▲ ▲ │
│ ╭─────────────────────────────────────────────────────────────────│─────────────│ │
│ │ │ │ │ │
│ │ │ │ │ │
╭──────────────╮ ╭─────────────╮ ╭─────────────╮ │ │ │ │
│ day: Field │ │ id: Field │ │ Subtract │ │ │ │ │
│ :: string │ │ :: int64 │ │ :: int64 │────────╯ │ │ │
╰──────────────╯ ╰─────────────╯ ╰─────────────╯ │ │ │
▲ ▲ ▲──────────────────│─────────────│────╮ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
│ │ ╭─────────────╮ │ │ │ ╭─────────────╮ │
│ │ │ StructField │ │ │ │ │ StructField │ │
│ │ │ :: int64 │ │ │ ╰─────────────────────────────│ :: int64 │ │
│ │ ╰─────────────╯ │ │ ╰─────────────╯ │
│ │ ▲ │ │ ▲ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
│ ╰───────────────────────────────────────╮ │ │ │ │ │
│ │ ╭───────────────────────────────────────╮ ╭───────────────────────────────────────╮
│ │ │ actual: Field │ │ normal: Field │
│ │ │ :: struct<high: int64, low: int64> │ │ :: struct<high: int64, low: int64> │
│ │ ╰───────────────────────────────────────╯ ╰───────────────────────────────────────╯
│ │ ▲ │ ▲
│ ╰───────────────────╮ │ │ │
│ │ │ │ │
│ │ ╰─────────────│ │
│ ╭────────────────────────────────────────────╮ │
│ │ weather: DatabaseTable │ │
│ │ day: string │ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────│ normal: struct<high: int64, low: int64> │──────────────────────────────────────╯
│ actual: struct<high: int64, low: int64> │
│ id: int64 │
╰────────────────────────────────────────────╯
Pivots
Pivots
SELECT
"t1"."Source Category Code",
"t1"."Source subcategory",
"t1"."Agency code",
"t1"."Bureau code",
"t1"."Account code",
"t1"."Treasury Agency code",
"t1"."On- or off-budget",
"t1"."TQ",
"t1"."__pivoted__"."year" AS "year",
"t1"."__pivoted__"."budget" AS "budget"
FROM (
SELECT
"t0"."Source Category Code",
"t0"."Source subcategory",
"t0"."Agency code",
"t0"."Bureau code",
"t0"."Account code",
"t0"."Treasury Agency code",
"t0"."On- or off-budget",
"t0"."TQ",
UNNEST(
[{'year': CAST(1962 AS SMALLINT), 'budget': "t0"."1962"}, {'year': CAST(1963 AS SMALLINT), 'budget': "t0"."1963"}, {'year': CAST(1964 AS SMALLINT), 'budget': "t0"."1964"}, {'year': CAST(1965 AS SMALLINT), 'budget': "t0"."1965"}, {'year': CAST(1966 AS SMALLINT), 'budget': "t0"."1966"}, {'year': CAST(1967 AS SMALLINT), 'budget': "t0"."1967"}, {'year': CAST(1968 AS SMALLINT), 'budget': "t0"."1968"}, {'year': CAST(1969 AS SMALLINT), 'budget': "t0"."1969"}, {'year': CAST(1970 AS SMALLINT), 'budget': "t0"."1970"}, {'year': CAST(1971 AS SMALLINT), 'budget': "t0"."1971"}, {'year': CAST(1972 AS SMALLINT), 'budget': "t0"."1972"}, {'year': CAST(1973 AS SMALLINT), 'budget': "t0"."1973"}, {'year': CAST(1974 AS SMALLINT), 'budget': "t0"."1974"}, {'year': CAST(1975 AS SMALLINT), 'budget': "t0"."1975"}, {'year': CAST(1976 AS SMALLINT), 'budget': "t0"."1976"}, {'year': CAST(1977 AS SMALLINT), 'budget': "t0"."1977"}, {'year': CAST(1978 AS SMALLINT), 'budget': "t0"."1978"}, {'year': CAST(1979 AS SMALLINT), 'budget': "t0"."1979"}, {'year': CAST(1980 AS SMALLINT), 'budget': "t0"."1980"}, {'year': CAST(1981 AS SMALLINT), 'budget': "t0"."1981"}, {'year': CAST(1982 AS SMALLINT), 'budget': "t0"."1982"}, {'year': CAST(1983 AS SMALLINT), 'budget': "t0"."1983"}, {'year': CAST(1984 AS SMALLINT), 'budget': "t0"."1984"}, {'year': CAST(1985 AS SMALLINT), 'budget': "t0"."1985"}, {'year': CAST(1986 AS SMALLINT), 'budget': "t0"."1986"}, {'year': CAST(1987 AS SMALLINT), 'budget': "t0"."1987"}, {'year': CAST(1988 AS SMALLINT), 'budget': "t0"."1988"}, {'year': CAST(1989 AS SMALLINT), 'budget': "t0"."1989"}, {'year': CAST(1990 AS SMALLINT), 'budget': "t0"."1990"}, {'year': CAST(1991 AS SMALLINT), 'budget': "t0"."1991"}, {'year': CAST(1992 AS SMALLINT), 'budget': "t0"."1992"}, {'year': CAST(1993 AS SMALLINT), 'budget': "t0"."1993"}, {'year': CAST(1994 AS SMALLINT), 'budget': "t0"."1994"}, {'year': CAST(1995 AS SMALLINT), 'budget': "t0"."1995"}, {'year': CAST(1996 AS SMALLINT), 'budget': "t0"."1996"}, {'year': CAST(1997 AS SMALLINT), 'budget': "t0"."1997"}, {'year': CAST(1998 AS SMALLINT), 'budget': "t0"."1998"}, {'year': CAST(1999 AS SMALLINT), 'budget': "t0"."1999"}, {'year': CAST(2000 AS SMALLINT), 'budget': "t0"."2000"}, {'year': CAST(2001 AS SMALLINT), 'budget': "t0"."2001"}, {'year': CAST(2002 AS SMALLINT), 'budget': "t0"."2002"}, {'year': CAST(2003 AS SMALLINT), 'budget': "t0"."2003"}, {'year': CAST(2004 AS SMALLINT), 'budget': "t0"."2004"}, {'year': CAST(2005 AS SMALLINT), 'budget': "t0"."2005"}, {'year': CAST(2006 AS SMALLINT), 'budget': "t0"."2006"}, {'year': CAST(2007 AS SMALLINT), 'budget': "t0"."2007"}, {'year': CAST(2008 AS SMALLINT), 'budget': "t0"."2008"}, {'year': CAST(2009 AS SMALLINT), 'budget': "t0"."2009"}, {'year': CAST(2010 AS SMALLINT), 'budget': "t0"."2010"}, {'year': CAST(2011 AS SMALLINT), 'budget': "t0"."2011"}, {'year': CAST(2012 AS SMALLINT), 'budget': "t0"."2012"}, {'year': CAST(2013 AS SMALLINT), 'budget': "t0"."2013"}, {'year': CAST(2014 AS SMALLINT), 'budget': "t0"."2014"}, {'year': CAST(2015 AS SMALLINT), 'budget': "t0"."2015"}, {'year': CAST(2016 AS SMALLINT), 'budget': "t0"."2016"}, {'year': CAST(2017 AS SMALLINT), 'budget': "t0"."2017"}, {'year': CAST(2018 AS SMALLINT), 'budget': "t0"."2018"}, {'year': CAST(2019 AS SMALLINT), 'budget': "t0"."2019"}, {'year': CAST(2020 AS SMALLINT), 'budget': "t0"."2020"}]
) AS "__pivoted__"
FROM "budget" AS "t0"
) AS "t1"
SELECT
"t1"."Source Category Code",
"t1"."Source subcategory",
"t1"."Agency code",
"t1"."Bureau code",
"t1"."Account code",
"t1"."Treasury Agency code",
"t1"."On- or off-budget",
"t1"."TQ",
"t1"."__pivoted__"."year" AS "year",
"t1"."__pivoted__"."budget" AS "budget"
FROM (
SELECT
"t0"."Source Category Code",
"t0"."Source subcategory",
"t0"."Agency code",
"t0"."Bureau code",
"t0"."Account code",
"t0"."Treasury Agency code",
"t0"."On- or off-budget",
"t0"."TQ",
UNNEST(
[{'year': CAST(1962 AS SMALLINT), 'budget': "t0"."1962"}, {'year': CAST(1963 AS SMALLINT), 'budget': "t0"."1963"}, {'year': CAST(1964 AS SMALLINT), 'budget': "t0"."1964"}, {'year': CAST(1965 AS SMALLINT), 'budget': "t0"."1965"}, {'year': CAST(1966 AS SMALLINT), 'budget': "t0"."1966"}, {'year': CAST(1967 AS SMALLINT), 'budget': "t0"."1967"}, {'year': CAST(1968 AS SMALLINT), 'budget': "t0"."1968"}, {'year': CAST(1969 AS SMALLINT), 'budget': "t0"."1969"}, {'year': CAST(1970 AS SMALLINT), 'budget': "t0"."1970"}, {'year': CAST(1971 AS SMALLINT), 'budget': "t0"."1971"}, {'year': CAST(1972 AS SMALLINT), 'budget': "t0"."1972"}, {'year': CAST(1973 AS SMALLINT), 'budget': "t0"."1973"}, {'year': CAST(1974 AS SMALLINT), 'budget': "t0"."1974"}, {'year': CAST(1975 AS SMALLINT), 'budget': "t0"."1975"}, {'year': CAST(1976 AS SMALLINT), 'budget': "t0"."1976"}, {'year': CAST(1977 AS SMALLINT), 'budget': "t0"."1977"}, {'year': CAST(1978 AS SMALLINT), 'budget': "t0"."1978"}, {'year': CAST(1979 AS SMALLINT), 'budget': "t0"."1979"}, {'year': CAST(1980 AS SMALLINT), 'budget': "t0"."1980"}, {'year': CAST(1981 AS SMALLINT), 'budget': "t0"."1981"}, {'year': CAST(1982 AS SMALLINT), 'budget': "t0"."1982"}, {'year': CAST(1983 AS SMALLINT), 'budget': "t0"."1983"}, {'year': CAST(1984 AS SMALLINT), 'budget': "t0"."1984"}, {'year': CAST(1985 AS SMALLINT), 'budget': "t0"."1985"}, {'year': CAST(1986 AS SMALLINT), 'budget': "t0"."1986"}, {'year': CAST(1987 AS SMALLINT), 'budget': "t0"."1987"}, {'year': CAST(1988 AS SMALLINT), 'budget': "t0"."1988"}, {'year': CAST(1989 AS SMALLINT), 'budget': "t0"."1989"}, {'year': CAST(1990 AS SMALLINT), 'budget': "t0"."1990"}, {'year': CAST(1991 AS SMALLINT), 'budget': "t0"."1991"}, {'year': CAST(1992 AS SMALLINT), 'budget': "t0"."1992"}, {'year': CAST(1993 AS SMALLINT), 'budget': "t0"."1993"}, {'year': CAST(1994 AS SMALLINT), 'budget': "t0"."1994"}, {'year': CAST(1995 AS SMALLINT), 'budget': "t0"."1995"}, {'year': CAST(1996 AS SMALLINT), 'budget': "t0"."1996"}, {'year': CAST(1997 AS SMALLINT), 'budget': "t0"."1997"}, {'year': CAST(1998 AS SMALLINT), 'budget': "t0"."1998"}, {'year': CAST(1999 AS SMALLINT), 'budget': "t0"."1999"}, {'year': CAST(2000 AS SMALLINT), 'budget': "t0"."2000"}, {'year': CAST(2001 AS SMALLINT), 'budget': "t0"."2001"}, {'year': CAST(2002 AS SMALLINT), 'budget': "t0"."2002"}, {'year': CAST(2003 AS SMALLINT), 'budget': "t0"."2003"}, {'year': CAST(2004 AS SMALLINT), 'budget': "t0"."2004"}, {'year': CAST(2005 AS SMALLINT), 'budget': "t0"."2005"}, {'year': CAST(2006 AS SMALLINT), 'budget': "t0"."2006"}, {'year': CAST(2007 AS SMALLINT), 'budget': "t0"."2007"}, {'year': CAST(2008 AS SMALLINT), 'budget': "t0"."2008"}, {'year': CAST(2009 AS SMALLINT), 'budget': "t0"."2009"}, {'year': CAST(2010 AS SMALLINT), 'budget': "t0"."2010"}, {'year': CAST(2011 AS SMALLINT), 'budget': "t0"."2011"}, {'year': CAST(2012 AS SMALLINT), 'budget': "t0"."2012"}, {'year': CAST(2013 AS SMALLINT), 'budget': "t0"."2013"}, {'year': CAST(2014 AS SMALLINT), 'budget': "t0"."2014"}, {'year': CAST(2015 AS SMALLINT), 'budget': "t0"."2015"}, {'year': CAST(2016 AS SMALLINT), 'budget': "t0"."2016"}, {'year': CAST(2017 AS SMALLINT), 'budget': "t0"."2017"}, {'year': CAST(2018 AS SMALLINT), 'budget': "t0"."2018"}, {'year': CAST(2019 AS SMALLINT), 'budget': "t0"."2019"}, {'year': CAST(2020 AS SMALLINT), 'budget': "t0"."2020"}]
) AS "__pivoted__"
FROM "budget" AS "t0"
) AS "t1"
┏━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━┓
┃ Source Category Code ┃ Source subcategory ┃ Agency code ┃ Bureau code ┃ Account code ┃ Treasury Agency code ┃ On- or off-budget ┃ TQ ┃ year ┃ budget ┃
┡━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━┩
│ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ string │ string │ int16 │ string │
├──────────────────────┼────────────────────┼─────────────┼─────────────┼──────────────┼──────────────────────┼───────────────────┼────────┼───────┼────────┤
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1962 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1963 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1964 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1965 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1966 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1967 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1968 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1969 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1970 │ 0 │
│ 931 │ 0 │ 9 │ 0 │ 800415 │ 20 │ On-budget │ 0 │ 1971 │ 0 │
│ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │
└──────────────────────┴────────────────────┴─────────────┴─────────────┴──────────────┴──────────────────────┴───────────────────┴────────┴───────┴────────┘
Too large to show
SELECT
"t0"."variety",
"t0"."site",
FIRST("t0"."yield") FILTER(WHERE
"t0"."year" = CAST(1931 AS SMALLINT)) AS "1931",
FIRST("t0"."yield") FILTER(WHERE
"t0"."year" = CAST(1932 AS SMALLINT)) AS "1932"
FROM "barley" AS "t0"
GROUP BY
1,
2
SELECT
"t0"."variety",
"t0"."site",
FIRST("t0"."yield") FILTER(WHERE
"t0"."year" = CAST(1931 AS SMALLINT)) AS "1931",
FIRST("t0"."yield") FILTER(WHERE
"t0"."year" = CAST(1932 AS SMALLINT)) AS "1932"
FROM "barley" AS "t0"
GROUP BY
1,
2
┏━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┓
┃ variety ┃ site ┃ 1931 ┃ 1932 ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━┩
│ string │ string │ float64 │ float64 │
├───────────┼─────────────────┼──────────┼──────────┤
│ Glabron │ Grand Rapids │ 29.13333 │ 14.43333 │
│ Svansota │ Grand Rapids │ 29.66667 │ 16.63333 │
│ Velvet │ Duluth │ 26.30000 │ 22.46667 │
│ No. 457 │ Grand Rapids │ 32.16667 │ 19.46667 │
│ No. 462 │ Grand Rapids │ 24.93334 │ 19.90000 │
│ No. 475 │ Waseca │ 46.76667 │ 41.26667 │
│ No. 475 │ Crookston │ 44.10000 │ 32.13333 │
│ Manchuria │ University Farm │ 27.00000 │ 26.90000 │
│ Glabron │ Morris │ 28.76667 │ 35.13333 │
│ Svansota │ Morris │ 25.76667 │ 35.03333 │
│ … │ … │ … │ … │
└───────────┴─────────────────┴──────────┴──────────┘
╭────────────────────╮
│ Aggregate │
│ variety: string │
╭────────────────────────────────────────────────────────────────────────────────▶│ site: string │◀────────────────────╮
│ │ 1931: float64 │ │
│ │ 1932: float64 │ │
│ ╰────────────────────╯ │
│ ▲ │
│ ╭──────────────────────────────│ │
│ │ │ │
╭────────────────╮ ╭──────────────╮ │ ╭───────────────╮ ╭───────────────╮
│ variety: Field │ │ site: Field │ │ │ First │ │ First │
│ :: string │ │ :: string │───────────────────────╯ ╭─────▶│ :: float64 │◀──────╮ │ :: float64 │◀──────╮
╰────────────────╯ ╰──────────────╯ │ ╰───────────────╯ │ ╰───────────────╯ │
▲ ▲ │ │ │ ▲ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
│ │ │ │ │ │ │
│ │ ╭───────────────╮ │ ╭───────────────╮ │ ╭───────────────╮
│ │ │ Equals │ │ │ yield: Field │ │ │ Equals │
│ │ ╭─────▶│ :: boolean │ │ │ :: float64 │───────╯ │ :: boolean │◀────╮
│ │ │ ╰───────────────╯ │ ╰───────────────╯ ╰───────────────╯ │
│ │ │ ▲ │ ▲ ▲ │
│ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │
│ ╰─────────────────────────────────────────────╮│ │ │ │ │
│ ╭───────────────╮ ││ ╭─────────────╮ │ │ ╭───────────────╮
│ │ 1931: Literal │ ││ │ year: Field │ │ │ │ 1932: Literal │
│ │ :: int16 │ │╰───────│ :: int64 │────────│───────────────────────────────╯ │ :: int16 │
│ ╰───────────────╯ │ ╰─────────────╯ │ ╰───────────────╯
│ │ ▲ │
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
│ ╰───╭───────────────────────╮ │
│ │ barley: DatabaseTable │ │
│ │ yield: float64 │ │
╰────────────────────────────────────────────────────────────────────────────────│ variety: string │───╯
│ year: int64 │
│ site: string │
╰───────────────────────╯