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
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
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"
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
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
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"
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
Was this page helpful?