Skip to main content

Basic syntax

with car as c:
    c.select([c.Name, c.Horsepower], sort=c.Year)
    c.limit(10)
  • SQL
  • Result
  • AST
  • AST graph
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.
  • SQL
  • Result
  • AST
  • AST graph
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)})
  • SQL
  • Result
  • AST
  • AST graph
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)
  • SQL
  • Result
  • AST
  • AST graph
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)
  • SQL
  • Result
  • AST
  • AST graph
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"
    )
  • SQL
  • Result
  • AST
  • AST graph
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")
  • SQL
  • Result
  • AST
  • AST graph
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
⌘I