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

Getting Started

Showcase

Basic Concepts

Walkthrough

Integrations