Showcase
Vinyl Syntax
Showcase
Vinyl Syntax
with car as c:
c.select([c.Name, c.Horsepower], sort=c.Year)
c.limit(10)
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 │
└─────────────────────┴───────────────────────────┴────────────┘
r0 := DatabaseTable: cars
Name string
Miles_per_Gallon float64
Cylinders int64
Displacement float64
Horsepower float64
Weight_in_lbs int64
Acceleration float64
Year timestamp(6)
Origin string
r1 := Project[r0]
Year: r0.Year
Name: r0.Name
Horsepower: r0.Horsepower
r2 := Sort[r1]
asc r1.Year
Limit[r2, n=10]
╭────────────────────────╮
│ 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 │
╰──────────────────────────────╯
from vinyl.operations import coalesce
with birdstrikes as b:
b.filter(b.Effect__Amount_of_damage == None)
b.filter(b.Airport__Name != None)
b.select({"airport_name": coalesce(b.Airport__Name, "Unknown")})
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 │
│ … │
└───────────────────────────────┘
r0 := DatabaseTable: birdstrikes
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
r1 := Filter[r0]
IsNull(r0.Effect__Amount_of_damage)
r2 := Filter[r1]
NotNull(r1.Airport__Name)
Project[r2]
airport_name: Coalesce([r2.Airport__Name, 'Unknown'])
╭─────────────────────────╮
│ 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 │
╰───────────────────────────────────────╯
def perimeter(x, y):
return 2 * (x + y)
with iris as i:
i.define({"petalPerimeter": perimeter(i.petalLength, i.petalWidth)})
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 │
│ … │ … │ … │ … │ … │ … │
└─────────────┴────────────┴─────────────┴────────────┴─────────┴────────────────┘
r0 := DatabaseTable: iris
sepalLength float64
sepalWidth float64
petalLength float64
petalWidth float64
species string
Project[r0]
sepalLength: r0.sepalLength
sepalWidth: r0.sepalWidth
petalLength: r0.petalLength
petalWidth: r0.petalWidth
species: r0.species
petalPerimeter: r0.petalLength + r0.petalWidth * 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 │
╰─────────────────────────╯
with co2_concentration as c:
c.define(
{"CO2_normalized": c.CO2 - c.CO2.mean()},
by=[c.Date.dt.extract("month")],
)
c.select([c.CO2_normalized], sort=c.Date)
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 │
│ … │ … │
└────────────┴────────────────┘
r0 := DatabaseTable: co2_concentration
CO2 float64
Date date
r1 := Project[r0]
ExtractMonth(Date): ExtractMonth(r0.Date)
CO2: r0.CO2
Date: r0.Date
CO2_normalized: r0.CO2 - WindowFunction(func=Mean(r0.CO2), frame=RowsWindowFrame(table=r0, group_by=[ExtractMonth(r0.Date)]))
r2 := Project[r1]
Date: r1.Date
CO2_normalized: r1.CO2_normalized
Sort[r2]
asc r2.Date
╭────────────────────────────╮
│ 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 │
╰──────────────────────────────────╯
with weather as w:
w.define({"high_vs_expected": w.actual["high"] - w.normal["high"]})
w.dropna(w.high_vs_expected)
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 │
└────────┴─────────────────────────────────┴─────────────────────────────────┴───────┴──────────────────┘
r0 := DatabaseTable: weather
day string
normal high: int64
low: int64
actual high: int64
low: int64
id int64
r1 := Project[r0]
day: r0.day
normal: r0.normal
actual: r0.actual
id: r0.id
high_vs_expected: StructField(r0.actual, field='high') - StructField(r0.normal, field='high')
DropNa[r1]
how:
any
subset:
r1.high_vs_expected
╭────────────────────────────────────────────╮
│ 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 │
╰────────────────────────────────────────────╯
import re
with budget as b:
b.unpivot(
[col for col in b.columns if re.match(r"\d{4}", col)],
colnames_to = "year",
colnames_transform = int,
values_to= "budget"
)
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 │
│ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │
└──────────────────────┴────────────────────┴─────────────┴─────────────┴──────────────┴──────────────────────┴───────────────────┴────────┴───────┴────────┘
r0 := DatabaseTable: budget
Source Category Code int64
Source subcategory int64
Agency code int64
Bureau code int64
Account code int64
Treasury Agency code int64
On- or off-budget string
1962 string
1963 string
1964 string
1965 string
1966 string
1967 string
1968 string
1969 string
1970 string
1971 string
1972 string
1973 string
1974 string
1975 string
1976 string
TQ string
1977 string
1978 string
1979 string
1980 string
1981 string
1982 string
1983 string
1984 string
1985 string
1986 string
1987 string
1988 string
1989 string
1990 string
1991 string
1992 string
1993 string
1994 string
1995 string
1996 string
1997 string
1998 string
1999 string
2000 string
2001 string
2002 string
2003 string
2004 string
2005 string
2006 string
2007 string
2008 string
2009 string
2010 string
2011 string
2012 string
2013 string
2014 string
2015 string
2016 string
2017 string
2018 string
2019 string
2020 string
r1 := Project[r0]
Source Category Code: r0['Source Category Code']
Source subcategory: r0['Source subcategory']
Agency code: r0['Agency code']
Bureau code: r0['Bureau code']
Account code: r0['Account code']
Treasury Agency code: r0['Treasury Agency code']
On- or off-budget: r0['On- or off-budget']
TQ: r0.TQ
__pivoted__: Unnest(Array([StructColumn(names=['year', 'budget'], values=[1962, r0['1962']]), StructColumn(names=['year', 'budget'], values=[1963, r0['1963']]), StructColumn(names=['year', 'budget'], values=[1964, r0['1964']]), StructColumn(names=['year', 'budget'], values=[1965, r0['1965']]), StructColumn(names=['year', 'budget'], values=[1966, r0['1966']]), StructColumn(names=['year', 'budget'], values=[1967, r0['1967']]), StructColumn(names=['year', 'budget'], values=[1968, r0['1968']]), StructColumn(names=['year', 'budget'], values=[1969, r0['1969']]), StructColumn(names=['year', 'budget'], values=[1970, r0['1970']]), StructColumn(names=['year', 'budget'], values=[1971, r0['1971']]), StructColumn(names=['year', 'budget'], values=[1972, r0['1972']]), StructColumn(names=['year', 'budget'], values=[1973, r0['1973']]), StructColumn(names=['year', 'budget'], values=[1974, r0['1974']]), StructColumn(names=['year', 'budget'], values=[1975, r0['1975']]), StructColumn(names=['year', 'budget'], values=[1976, r0['1976']]), StructColumn(names=['year', 'budget'], values=[1977, r0['1977']]), StructColumn(names=['year', 'budget'], values=[1978, r0['1978']]), StructColumn(names=['year', 'budget'], values=[1979, r0['1979']]), StructColumn(names=['year', 'budget'], values=[1980, r0['1980']]), StructColumn(names=['year', 'budget'], values=[1981, r0['1981']]), StructColumn(names=['year', 'budget'], values=[1982, r0['1982']]), StructColumn(names=['year', 'budget'], values=[1983, r0['1983']]), StructColumn(names=['year', 'budget'], values=[1984, r0['1984']]), StructColumn(names=['year', 'budget'], values=[1985, r0['1985']]), StructColumn(names=['year', 'budget'], values=[1986, r0['1986']]), StructColumn(names=['year', 'budget'], values=[1987, r0['1987']]), StructColumn(names=['year', 'budget'], values=[1988, r0['1988']]), StructColumn(names=['year', 'budget'], values=[1989, r0['1989']]), StructColumn(names=['year', 'budget'], values=[1990, r0['1990']]), StructColumn(names=['year', 'budget'], values=[1991, r0['1991']]), StructColumn(names=['year', 'budget'], values=[1992, r0['1992']]), StructColumn(names=['year', 'budget'], values=[1993, r0['1993']]), StructColumn(names=['year', 'budget'], values=[1994, r0['1994']]), StructColumn(names=['year', 'budget'], values=[1995, r0['1995']]), StructColumn(names=['year', 'budget'], values=[1996, r0['1996']]), StructColumn(names=['year', 'budget'], values=[1997, r0['1997']]), StructColumn(names=['year', 'budget'], values=[1998, r0['1998']]), StructColumn(names=['year', 'budget'], values=[1999, r0['1999']]), StructColumn(names=['year', 'budget'], values=[2000, r0['2000']]), StructColumn(names=['year', 'budget'], values=[2001, r0['2001']]), StructColumn(names=['year', 'budget'], values=[2002, r0['2002']]), StructColumn(names=['year', 'budget'], values=[2003, r0['2003']]), StructColumn(names=['year', 'budget'], values=[2004, r0['2004']]), StructColumn(names=['year', 'budget'], values=[2005, r0['2005']]), StructColumn(names=['year', 'budget'], values=[2006, r0['2006']]), StructColumn(names=['year', 'budget'], values=[2007, r0['2007']]), StructColumn(names=['year', 'budget'], values=[2008, r0['2008']]), StructColumn(names=['year', 'budget'], values=[2009, r0['2009']]), StructColumn(names=['year', 'budget'], values=[2010, r0['2010']]), StructColumn(names=['year', 'budget'], values=[2011, r0['2011']]), StructColumn(names=['year', 'budget'], values=[2012, r0['2012']]), StructColumn(names=['year', 'budget'], values=[2013, r0['2013']]), StructColumn(names=['year', 'budget'], values=[2014, r0['2014']]), StructColumn(names=['year', 'budget'], values=[2015, r0['2015']]), StructColumn(names=['year', 'budget'], values=[2016, r0['2016']]), StructColumn(names=['year', 'budget'], values=[2017, r0['2017']]), StructColumn(names=['year', 'budget'], values=[2018, r0['2018']]), StructColumn(names=['year', 'budget'], values=[2019, r0['2019']]), StructColumn(names=['year', 'budget'], values=[2020, r0['2020']])]))
Project[r1]
Source Category Code: r1['Source Category Code']
Source subcategory: r1['Source subcategory']
Agency code: r1['Agency code']
Bureau code: r1['Bureau code']
Account code: r1['Account code']
Treasury Agency code: r1['Treasury Agency code']
On- or off-budget: r1['On- or off-budget']
TQ: r1.TQ
year: StructField(r1.__pivoted__, field='year')
budget: StructField(r1.__pivoted__, field='budget')
Too large to show
with barley as b:
b.pivot(colnames_from="year", values_from="yield")
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 │
│ … │ … │ … │ … │
└───────────┴─────────────────┴──────────┴──────────┘
r0 := DatabaseTable: barley
yield float64
variety string
year int64
site string
Aggregate[r0]
groups:
variety: r0.variety
site: r0.site
metrics:
1931: First(r0.yield, where=r0.year == 1931)
1932: First(r0.yield, where=r0.year == 1932)
b.visualize()
╭────────────────────╮
│ 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 │
╰───────────────────────╯
Was this page helpful?