API Reference

Base error class for all errors raised by FunSQL.

render(node; tables = Dict{Symbol, SQLTable}(),
             dialect = :default,
             cache = nothing)::SQLString

Create a SQLCatalog object and serialize the query node.

render(catalog::Union{SQLConnection, SQLCatalog}, node::SQLNode)::SQLString

Serialize the query node as a SQL statement.

Parameter catalog of SQLCatalog type encapsulates available database tables and the target SQL dialect. A SQLConnection object is also accepted.

Parameter node is a composite SQLNode object.

The function returns a SQLString value. The result is also cached (with the identity of node serving as the key) in the catalog cache.

Examples

julia> catalog = SQLCatalog(
           :person => SQLTable(:person, columns = [:person_id, :year_of_birth]),
           dialect = :postgresql);

julia> q = From(:person) |>
           Where(Get.year_of_birth .>= 1950);

julia> print(render(catalog, q))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" >= 1950)

render(dialect::Union{SQLConnection, SQLCatalog, SQLDialect},
       clause::SQLClause)::SQLString

Serialize the syntax tree of a SQL query.

reflect(conn;
        schema = nothing,
        dialect = nothing,
        cache = 256)::SQLCatalog

Retrieve the information about available database tables.

The function returns a SQLCatalog object. The catalog will be populated with the tables from the given database schema, or, if parameter schema is not set, from the default database schema (e.g., schema public for PostgreSQL).

Parameter dialect specifies the target SQLDialect. If not set, dialect will be inferred from the type of the connection object.

Shorthand for SQLConnection.

SQLConnection(conn; catalog)

Wrap a raw database connection object together with a SQLCatalog object containing information about database tables.

SQLStatement(conn, raw; vars = Symbol[])

Wrap a prepared SQL statement.

DBInterface.connect(DB{RawConnType},
                    args...;
                    schema = nothing,
                    dialect = nothing,
                    cache = 256,
                    kws...)

Connect to the database server, call reflect to retrieve the information about available tables and return a SQLConnection object.

Extra parameters args and kws are passed to the call:

DBInterface.connect(RawConnType, args...; kws...)

DBInterface.execute(conn::SQLConnection, sql::SQLNode, params)
DBInterface.execute(conn::SQLConnection, sql::SQLClause, params)

Serialize and execute the query node.

DBInterface.execute(conn::SQLConnection, sql::SQLNode; params...)
DBInterface.execute(conn::SQLConnection, sql::SQLClause; params...)

Serialize and execute the query node.

DBInterface.execute(stmt::SQLStatement, params)

Execute the prepared SQL statement.

DBInterface.prepare(conn::SQLConnection, str::SQLString)::SQLStatement

Generate a prepared SQL statement.

DBInterface.prepare(conn::SQLConnection, sql::SQLNode)::SQLStatement
DBInterface.prepare(conn::SQLConnection, sql::SQLClause)::SQLStatement

Serialize the query node and return a prepared SQL statement.

SQLCatalog(; tables = Dict{Symbol, SQLTable}(),
             dialect = :default,
             cache = 256,
             metadata = nothing)
SQLCatalog(tables...;
           dialect = :default, cache = 256, metadata = nothing)

SQLCatalog encapsulates available database tables, the target SQL dialect, a cache of serialized queries, and an optional metadata.

Parameter tables is either a dictionary or a vector of SQLTable objects, where the vector will be converted to a dictionary with table names as keys. A table in the catalog can be included to a query using the From node.

Parameter dialect is a SQLDialect object describing the target SQL dialect.

Parameter cache specifies the size of the LRU cache containing results of the render function. Set cache to nothing to disable the cache, or set cache to an arbitrary Dict-like object to provide a custom cache implementation.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> catalog = SQLCatalog(person, location, dialect = :postgresql)
SQLCatalog(SQLTable(:location, SQLColumn(:location_id), SQLColumn(:state)),
           SQLTable(:person,
                    SQLColumn(:person_id),
                    SQLColumn(:year_of_birth),
                    SQLColumn(:location_id)),
           dialect = SQLDialect(:postgresql))

SQLColumn(; name, metadata = nothing)
SQLColumn(name; metadata = nothing)

SQLColumn represents a column with the given name and optional metadata.

SQLTable(; qualifiers = [], name, columns, metadata = nothing)
SQLTable(name; qualifiers = [], columns, metadata = nothing)
SQLTable(name, columns...; qualifiers = [], metadata = nothing)

The structure of a SQL table or a table-like entity (TEMP TABLE, VIEW, etc) for use as a reference in assembling SQL queries.

The SQLTable constructor expects the table name, an optional vector containing the table schema and other qualifiers, an ordered dictionary columns that maps names to columns, and an optional metadata.

Examples

julia> person = SQLTable(qualifiers = ["public"],
                         name = "person",
                         columns = ["person_id", "year_of_birth"],
                         metadata = (; is_view = false))
SQLTable(qualifiers = [:public],
         :person,
         SQLColumn(:person_id),
         SQLColumn(:year_of_birth),
         metadata = [:is_view => false])

SQLDialect(; name = :default, kws...)
SQLDialect(template::SQLDialect; kws...)
SQLDialect(name::Symbol, kws...)
SQLDialect(ConnType::Type)

Properties and capabilities of a particular SQL dialect.

Use SQLDialect(name::Symbol) to create one of the known dialects. The following names are recognized:

• :duckdb
• :mysql
• :postgresql
• :redshift
• :spark
• :sqlite
• :sqlserver

Keyword parameters override individual properties of a dialect. For details, check the source code.

Use SQLDialect(ConnType::Type) to detect the dialect based on the type of the database connection object. The following types are recognized:

• DuckDB.DB
• MySQL.Connection
• LibPQ.Connection
• SQLite.DB

Examples

julia> postgresql_dialect = SQLDialect(:postgresql)
SQLDialect(:postgresql)

julia> postgresql_odbc_dialect = SQLDialect(:postgresql,
                                            variable_prefix = '?',
                                            variable_style = :positional)
SQLDialect(:postgresql, variable_prefix = '?', variable_style = :POSITIONAL)

SQLString(raw; columns = nothing, vars = Symbol[])

Serialized SQL query.

Parameter columns is a vector describing the output columns.

Parameter vars is a vector of query parameters (created with Var) in the order they are expected by the DBInterface.execute() function.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person);

julia> render(q)
SQLString("""
          SELECT
            "person_1"."person_id",
            "person_1"."year_of_birth"
          FROM "person" AS "person_1\"""",
          columns = [SQLColumn(:person_id), SQLColumn(:year_of_birth)])

julia> q = From(person) |> Where(Fun.and(Get.year_of_birth .>= Var.YEAR,
                                         Get.year_of_birth .< Var.YEAR .+ 10));

julia> render(q, dialect = :mysql)
SQLString("""
          SELECT
            `person_1`.`person_id`,
            `person_1`.`year_of_birth`
          FROM `person` AS `person_1`
          WHERE
            (`person_1`.`year_of_birth` >= ?) AND
            (`person_1`.`year_of_birth` < (? + 10))""",
          columns = [SQLColumn(:person_id), SQLColumn(:year_of_birth)],
          vars = [:YEAR, :YEAR])

julia> render(q, dialect = :postgresql)
SQLString("""
          SELECT
            "person_1"."person_id",
            "person_1"."year_of_birth"
          FROM "person" AS "person_1"
          WHERE
            ("person_1"."year_of_birth" >= $1) AND
            ("person_1"."year_of_birth" < ($1 + 10))""",
          columns = [SQLColumn(:person_id), SQLColumn(:year_of_birth)],
          vars = [:YEAR])

pack(sql::SQLString, vars::Union{Dict, NamedTuple})::Vector{Any}

Convert a dictionary or a named tuple of query parameters to the positional form expected by DBInterface.execute().

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person) |> Where(Fun.and(Get.year_of_birth .>= Var.YEAR,
                                         Get.year_of_birth .< Var.YEAR .+ 10));

julia> sql = render(q, dialect = :mysql);

julia> pack(sql, (; YEAR = 1950))
2-element Vector{Any}:
 1950
 1950

julia> sql = render(q, dialect = :postgresql);

julia> pack(sql, (; YEAR = 1950))
1-element Vector{Any}:
 1950

A tabular or a scalar operation that can be expressed as a SQL query.

A duplicate label where unique labels are expected.

A scalar operation where a tabular operation is expected.

Unexpected number of arguments.

Grouping sets are specified incorrectly.

A node that cannot be rebased.

An undefined or an invalid reference.

An opaque wrapper over an arbitrary SQL node.

A node that produces tabular output.

Invalid application of the @funsql macro.

Convenient notation for assembling FunSQL queries.

Agg(; over = nothing, name, args = [], filter = nothing)
Agg(name; over = nothing, args = [], filter = nothing)
Agg(name, args...; over = nothing, filter = nothing)
Agg.name(args...; over = nothing, filter = nothing)

An application of an aggregate function.

An Agg node must be applied to the output of a Group or a Partition node. In a Group context, it is translated to a regular aggregate function, and in a Partition context, it is translated to a window function.

Examples

Number of patients per year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Select(Get.year_of_birth, Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Number of distinct states among all available locations.

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:location) |>
           Group() |>
           Select(Agg.count_distinct(Get.state));

julia> print(render(q, tables = [location]))
SELECT count(DISTINCT "location_1"."state") AS "count_distinct"
FROM "location" AS "location_1"

For each patient, show the date of their latest visit to a healthcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:person) |>
           LeftJoin(:visit_group => From(:visit_occurrence) |>
                                    Group(Get.person_id),
                    on = (Get.person_id .== Get.visit_group.person_id)) |>
           Select(Get.person_id,
                  :max_visit_start_date =>
                      Get.visit_group |> Agg.max(Get.visit_start_date));

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT
  "person_1"."person_id",
  "visit_group_1"."max" AS "max_visit_start_date"
FROM "person" AS "person_1"
LEFT JOIN (
  SELECT
    max("visit_occurrence_1"."visit_start_date") AS "max",
    "visit_occurrence_1"."person_id"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  GROUP BY "visit_occurrence_1"."person_id"
) AS "visit_group_1" ON ("person_1"."person_id" = "visit_group_1"."person_id")

For each visit, show the number of days passed since the previous visit.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id,
                     order_by = [Get.visit_start_date]) |>
           Select(Get.person_id,
                  Get.visit_start_date,
                  :gap => Get.visit_start_date .- Agg.lag(Get.visit_start_date));

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  "visit_occurrence_1"."person_id",
  "visit_occurrence_1"."visit_start_date",
  ("visit_occurrence_1"."visit_start_date" - (lag("visit_occurrence_1"."visit_start_date") OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date"))) AS "gap"
FROM "visit_occurrence" AS "visit_occurrence_1"

Append(; over = nothing, args)
Append(args...; over = nothing)

Append concatenates input datasets.

Only the columns that are present in every input dataset will be included to the output of Append.

An Append node is translated to a UNION ALL query:

SELECT ...
FROM $over
UNION ALL
SELECT ...
FROM $(args[1])
UNION ALL
...

Examples

Show the dates of all measuments and observations.

julia> measurement = SQLTable(:measurement, columns = [:measurement_id, :person_id, :measurement_date]);

julia> observation = SQLTable(:observation, columns = [:observation_id, :person_id, :observation_date]);

julia> q = From(:measurement) |>
           Define(:date => Get.measurement_date) |>
           Append(From(:observation) |>
                  Define(:date => Get.observation_date));

julia> print(render(q, tables = [measurement, observation]))
SELECT
  "measurement_1"."person_id",
  "measurement_1"."measurement_date" AS "date"
FROM "measurement" AS "measurement_1"
UNION ALL
SELECT
  "observation_1"."person_id",
  "observation_1"."observation_date" AS "date"
FROM "observation" AS "observation_1"

As(; over = nothing, name)
As(name; over = nothing)
name => over

In a scalar context, As specifies the name of the output column. When applied to tabular data, As wraps the data in a nested record.

The arrow operator (=>) is a shorthand notation for As.

Examples

Show all patient IDs.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |> Select(:id => Get.person_id);

julia> print(render(q, tables = [person]))
SELECT "person_1"."person_id" AS "id"
FROM "person" AS "person_1"

Show all patients together with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(From(:location) |> As(:location),
                on = Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

Bind(; over = nothing; args)
Bind(args...; over = nothing)

The Bind node evaluates a query with parameters. Specifically, Bind provides the values for Var parameters contained in the over node.

In a scalar context, the Bind node is translated to a correlated subquery. When Bind is applied to the joinee branch of a Join node, it is translated to a JOIN LATERAL query.

Examples

Show patients with at least one visit to a heathcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence = SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id]);

julia> q = From(:person) |>
           Where(Fun.exists(From(:visit_occurrence) |>
                            Where(Get.person_id .== Var.PERSON_ID) |>
                            Bind(:PERSON_ID => Get.person_id)));

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT "person_1"."person_id"
FROM "person" AS "person_1"
WHERE (EXISTS (
  SELECT NULL AS "_"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  WHERE ("visit_occurrence_1"."person_id" = "person_1"."person_id")
))

Show all patients together with the date of their latest visit to a heathcare provider.

julia> person = SQLTable(:person, columns = [:person_id]);

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:person) |>
           LeftJoin(From(:visit_occurrence) |>
                    Where(Get.person_id .== Var.PERSON_ID) |>
                    Order(Get.visit_start_date |> Desc()) |>
                    Limit(1) |>
                    Bind(:PERSON_ID => Get.person_id) |>
                    As(:visit),
                    on = true) |>
            Select(Get.person_id, Get.visit.visit_start_date);

julia> print(render(q, tables = [person, visit_occurrence]))
SELECT
  "person_1"."person_id",
  "visit_1"."visit_start_date"
FROM "person" AS "person_1"
LEFT JOIN LATERAL (
  SELECT "visit_occurrence_1"."visit_start_date"
  FROM "visit_occurrence" AS "visit_occurrence_1"
  WHERE ("visit_occurrence_1"."person_id" = "person_1"."person_id")
  ORDER BY "visit_occurrence_1"."visit_start_date" DESC
  FETCH FIRST 1 ROW ONLY
) AS "visit_1" ON TRUE

Define(; over; args = [], before = nothing, after = nothing)
Define(args...; over, before = nothing, after = nothing)

The Define node adds or replaces output columns.

By default, new columns are added at the end of the column list while replaced columns retain their position. Set after = true (after = <column>) to add both new and replaced columns at the end (after a specified column). Alternatively, set before = true (before = <column>) to add both new and replaced columns at the front (before the specified column).

Examples

Show patients who are at least 16 years old.

julia> person = SQLTable(:person, columns = [:person_id, :birth_datetime]);

julia> q = From(:person) |>
           Define(:age => Fun.now() .- Get.birth_datetime, before = :birth_datetime) |>
           Where(Get.age .>= "16 years");

julia> print(render(q, tables = [person]))
SELECT
  "person_2"."person_id",
  "person_2"."age",
  "person_2"."birth_datetime"
FROM (
  SELECT
    "person_1"."person_id",
    (now() - "person_1"."birth_datetime") AS "age",
    "person_1"."birth_datetime"
  FROM "person" AS "person_1"
) AS "person_2"
WHERE ("person_2"."age" >= '16 years')

Conceal the year of birth of patients born before 1930.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Define(:year_of_birth => Fun.case(Get.year_of_birth .>= 1930,
                                             Get.year_of_birth,
                                             missing));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  (CASE WHEN ("person_1"."year_of_birth" >= 1930) THEN "person_1"."year_of_birth" ELSE NULL END) AS "year_of_birth"
FROM "person" AS "person_1"

From(; source)
From(tbl::SQLTable)
From(name::Symbol)
From(^)
From(df)
From(f::SQLNode; columns::Vector{Symbol})
From(::Nothing)

From outputs the content of a database table.

The parameter source could be one of:

• a SQLTable object;
• a Symbol value;
• a ^ object;
• a DataFrame or any Tables.jl-compatible dataset;
• A SQLNode representing a table-valued function. In this case, From also requires a keyword parameter columns with a list of output columns produced by the function.
• nothing.

When source is a symbol, it can refer to either a table in SQLCatalog or an intermediate dataset defined with the With node.

The From node is translated to a SQL query with a FROM clause:

SELECT ...
FROM $source

From(^) must be a component of Iterate. In the context of Iterate, it refers to the output of the previous iteration.

From(::DataFrame) is translated to a VALUES clause.

From(nothing) emits a dataset with one row and no columns and can usually be omitted.

Examples

List all patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(person);

julia> print(render(q))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"

List all patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"

Show all patients diagnosed with essential hypertension.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence,
                    columns = [:condition_occurrence_id, :person_id, :condition_concept_id]);

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           With(:essential_hypertension =>
                    From(:condition_occurrence) |>
                    Where(Get.condition_concept_id .== 320128));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

Show the current date.

julia> q = From(nothing) |>
           Select(Fun.current_date());

julia> print(render(q))
SELECT CURRENT_DATE AS "current_date"

julia> q = Select(Fun.current_date());

julia> print(render(q))
SELECT CURRENT_DATE AS "current_date"

Query a DataFrame.

julia> df = DataFrame(name = ["SQL", "Julia", "FunSQL"],
                      year = [1974, 2012, 2021]);

julia> q = From(df) |>
           Group() |>
           Select(Agg.min(Get.year), Agg.max(Get.year));

julia> print(render(q))
SELECT
  min("values_1"."year") AS "min",
  max("values_1"."year") AS "max"
FROM (
  VALUES
    (1974),
    (2012),
    (2021)
) AS "values_1" ("year")

Parse comma-separated numbers.

julia> q = From(Fun.regexp_matches("2,3,5,7,11", "(\\d+)", "g"),
                columns = [:captures]) |>
           Select(Fun."CAST(?[1] AS INTEGER)"(Get.captures));

julia> print(render(q, dialect = :postgresql))
SELECT CAST("regexp_matches_1"."captures"[1] AS INTEGER) AS "_"
FROM regexp_matches('2,3,5,7,11', '(\d+)', 'g') AS "regexp_matches_1" ("captures")

Fun(; name, args = [])
Fun(name; args = [])
Fun(name, args...)
Fun.name(args...)

Application of a SQL function or a SQL operator.

A Fun node is also generated by broadcasting on SQLNode objects. Names of Julia operators (==, !=, &&, ||, !) are replaced with their SQL equivalents (=, <>, and, or, not).

If name contains only symbols, or if name starts or ends with a space, the Fun node is translated to a SQL operator.

If name contains one or more ? characters, it serves as a template of a SQL expression where ? symbols are replaced with the given arguments. Use ?? to represent a literal ? mark. Wrap the template in parentheses if this is necessary to make the SQL expression unambiguous.

Certain names have a customized translation in order to generate common SQL functions and operators with irregular syntax:

Examples

Replace missing values with N/A.

julia> location = SQLTable(:location, columns = [:location_id, :city]);

julia> q = From(:location) |>
           Select(Fun.coalesce(Get.city, "N/A"));

julia> print(render(q, tables = [location]))
SELECT coalesce("location_1"."city", 'N/A') AS "coalesce"
FROM "location" AS "location_1"

Find patients not born in 1980.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Get.year_of_birth .!= 1980);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" <> 1980)

For each patient, show their age in 2000.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Select(Fun."-"(2000, Get.year_of_birth));

julia> print(render(q, tables = [person]))
SELECT (2000 - "person_1"."year_of_birth") AS "_"
FROM "person" AS "person_1"

Find invalid zip codes.

julia> location = SQLTable(:location, columns = [:location_id, :zip]);

julia> q = From(:location) |>
           Select(Fun." NOT SIMILAR TO '[0-9]{5}'"(Get.zip));

julia> print(render(q, tables = [location]))
SELECT ("location_1"."zip" NOT SIMILAR TO '[0-9]{5}') AS "_"
FROM "location" AS "location_1"

Extract the first 3 digits of the zip code.

julia> location = SQLTable(:location, columns = [:location_id, :zip]);

julia> q = From(:location) |>
           Select(Fun."SUBSTRING(? FROM ? FOR ?)"(Get.zip, 1, 3));

julia> print(render(q, tables = [location]))
SELECT SUBSTRING("location_1"."zip" FROM 1 FOR 3) AS "_"
FROM "location" AS "location_1"

Get(; over, name)
Get(name; over)
Get.name        Get."name"      Get[name]       Get["name"]
over.name       over."name"     over[name]      over["name"]
name

A reference to a column of the input dataset.

When a column reference is ambiguous (e.g., with Join), use As to disambiguate the columns, and a chained Get node (Get.a.b.….z) to refer to a column wrapped with … |> As(:b) |> As(:a).

Examples

List patient IDs.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Select(Get(:person_id));

julia> print(render(q, tables = [person]))
SELECT "person_1"."person_id"
FROM "person" AS "person_1"

Show patients with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(From(:location) |> As(:location),
                on = Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

Group(; over, by = [], sets = sets, name = nothing)
Group(by...; over, sets = sets, name = nothing)

The Group node summarizes the input dataset.

Specifically, Group outputs all unique values of the given grouping key. This key partitions the input rows into disjoint groups that are summarized by aggregate functions Agg applied to the output of Group. The parameter sets specifies the grouping sets, either with grouping mode indicators :cube or :rollup, or explicitly as Vector{Vector{Symbol}}. An optional parameter name specifies the field to hold the group.

The Group node is translated to a SQL query with a GROUP BY clause:

SELECT ...
FROM $over
GROUP BY $by...

Examples

Total number of patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group() |>
           Select(Agg.count());

julia> print(render(q, tables = [person]))
SELECT count(*) AS "count"
FROM "person" AS "person_1"

Number of patients per year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Select(Get.year_of_birth, Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

The same example using an explicit group name.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth, name = :person) |>
           Select(Get.year_of_birth, Get.person |> Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Number of patients per year of birth and the total number of patients.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth, sets = :cube) |>
           Select(Get.year_of_birth, Agg.count());

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  count(*) AS "count"
FROM "person" AS "person_1"
GROUP BY CUBE("person_1"."year_of_birth")

Distinct states across all available locations.

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:location) |>
           Group(Get.state);

julia> print(render(q, tables = [location]))
SELECT DISTINCT "location_1"."state"
FROM "location" AS "location_1"

Highlight(; over = nothing; color)
Highlight(color; over = nothing)

Highlight over with the given color.

The highlighted node is printed with the selected color when the query containing it is displayed.

Available colors can be found in Base.text_colors.

Examples

julia> q = From(:person) |>
           Select(Get.person_id |> Highlight(:bold))
let q1 = From(:person),
    q2 = q1 |> Select(Get.person_id)
    q2
end

Iterate(; over = nothing, iterator)
Iterate(iterator; over = nothing)

Iterate generates the concatenated output of an iterated query.

The over query is evaluated first. Then the iterator query is repeatedly applied: to the output of over, then to the output of its previous run, and so on, until the iterator produces no data. All these outputs are concatenated to generate the output of Iterate.

The iterator query may explicitly refer to the output of the previous run using From(^) notation.

The Iterate node is translated to a recursive common table expression:

WITH RECURSIVE iterator AS (
  SELECT ...
  FROM $over
  UNION ALL
  SELECT ...
  FROM $iterator
)
SELECT ...
FROM iterator

Examples

Calculate the factorial.

julia> q = Define(:n => 1, :f => 1) |>
           Iterate(From(^) |>
                   Where(Get.n .< 10) |>
                   Define(:n => Get.n .+ 1, :f => Get.f .* (Get.n .+ 1)));

julia> print(render(q))
WITH RECURSIVE "__1" ("n", "f") AS (
  SELECT
    1 AS "n",
    1 AS "f"
  UNION ALL
  SELECT
    ("__2"."n" + 1) AS "n",
    ("__2"."f" * ("__2"."n" + 1)) AS "f"
  FROM "__1" AS "__2"
  WHERE ("__2"."n" < 10)
)
SELECT
  "__3"."n",
  "__3"."f"
FROM "__1" AS "__3"

*Calculate the factorial, with implicit From(^).

julia> q = Define(:n => 1, :f => 1) |>
           Iterate(Where(Get.n .< 10) |>
                   Define(:n => Get.n .+ 1, :f => Get.f .* (Get.n .+ 1)));

julia> print(render(q))
WITH RECURSIVE "__1" ("n", "f") AS (
  SELECT
    1 AS "n",
    1 AS "f"
  UNION ALL
  SELECT
    ("__2"."n" + 1) AS "n",
    ("__2"."f" * ("__2"."n" + 1)) AS "f"
  FROM "__1" AS "__2"
  WHERE ("__2"."n" < 10)
)
SELECT
  "__3"."n",
  "__3"."f"
FROM "__1" AS "__3"

An alias for Join(...; ..., on = true).

Join(; over = nothing, joinee, on, left = false, right = false, optional = false)
Join(joinee; over = nothing, on, left = false, right = false, optional = false)
Join(joinee, on; over = nothing, left = false, right = false, optional = false)

Join correlates two input datasets.

The Join node is translated to a query with a JOIN clause:

SELECT ...
FROM $over
JOIN $joinee ON $on

You can specify the join type:

• INNER JOIN (the default);
• LEFT JOIN (left = true or LeftJoin);
• RIGHT JOIN (right = true);
• FULL JOIN (both left = true and right = true);
• CROSS JOIN (on = true).

When optional is set, the JOIN clause is omitted if the query does not depend on any columns from the joinee branch.

To make a lateral join, apply Bind to the joinee branch.

Use As to disambiguate output columns.

Examples

Show patients with their state of residence.

julia> person = SQLTable(:person, columns = [:person_id, :location_id]);

julia> location = SQLTable(:location, columns = [:location_id, :state]);

julia> q = From(:person) |>
           Join(:location => From(:location),
                Get.location_id .== Get.location.location_id) |>
           Select(Get.person_id, Get.location.state);

julia> print(render(q, tables = [person, location]))
SELECT
  "person_1"."person_id",
  "location_1"."state"
FROM "person" AS "person_1"
JOIN "location" AS "location_1" ON ("person_1"."location_id" = "location_1"."location_id")

An alias for Join(...; ..., left = true).

Limit(; over = nothing, offset = nothing, limit = nothing)
Limit(limit; over = nothing, offset = nothing)
Limit(offset, limit; over = nothing)
Limit(start:stop; over = nothing)

The Limit node skips the first offset rows and then emits the next limit rows.

To make the output deterministic, Limit must be applied directly after an Order node.

The Limit node is translated to a query with a LIMIT or a FETCH clause:

SELECT ...
FROM $over
OFFSET $offset ROWS
FETCH NEXT $limit ROWS ONLY

Examples

Show the oldest patient.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth) |>
           Limit(1);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth"
FETCH FIRST 1 ROW ONLY

Lit(; val)
Lit(val)

A SQL literal.

In a context where a SQL node is expected, missing, numbers, strings, and datetime values are automatically converted to SQL literals.

Examples

julia> q = Select(:null => missing,
                  :boolean => true,
                  :integer => 42,
                  :text => "SQL is fun!",
                  :date => Date(2000));

julia> print(render(q))
SELECT
  NULL AS "null",
  TRUE AS "boolean",
  42 AS "integer",
  'SQL is fun!' AS "text",
  '2000-01-01' AS "date"

Order(; over = nothing, by)
Order(by...; over = nothing)

Order sorts the input rows by the given key.

The Ordernode is translated to a query with an ORDER BY clause:

SELECT ...
FROM $over
ORDER BY $by...

Specify the sort order with Asc, Desc, or Sort.

Examples

List patients ordered by their age.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth"

Over(; over = nothing, arg, materialized = nothing)
Over(arg; over = nothing, materialized = nothing)

base |> Over(arg) is an alias for With(base, over = arg).

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> q = From(:condition_occurrence) |>
           Where(Get.condition_concept_id .== 320128) |>
           As(:essential_hypertension) |>
           Over(From(:person) |>
                Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                            Select(Get.person_id))));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

Partition(; over, by = [], order_by = [], frame = nothing, name = nothing)
Partition(by...; over, order_by = [], frame = nothing, name = nothing)

The Partition node relates adjacent rows.

Specifically, Partition specifies how to relate each row to the adjacent rows in the same dataset. The rows are partitioned by the given key and ordered within each partition using order_by key. The parameter frame customizes the extent of related rows. These related rows are summarized by aggregate functions Agg applied to the output of Partition. An optional parameter name specifies the field to hold the partition.

The Partition node is translated to a query with a WINDOW clause:

SELECT ...
FROM $over
WINDOW w AS (PARTITION BY $by... ORDER BY $order_by...)

Examples

Enumerate patients' visits.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id, order_by = [Get.visit_start_date]) |>
           Select(Agg.row_number(), Get.visit_occurrence_id);

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  (row_number() OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date")) AS "row_number",
  "visit_occurrence_1"."visit_occurrence_id"
FROM "visit_occurrence" AS "visit_occurrence_1"

The same example using an explicit partition name.

julia> visit_occurrence =
           SQLTable(:visit_occurrence, columns = [:visit_occurrence_id, :person_id, :visit_start_date]);

julia> q = From(:visit_occurrence) |>
           Partition(Get.person_id, order_by = [Get.visit_start_date], name = :visit_by_person) |>
           Select(Get.visit_by_person |> Agg.row_number(), Get.visit_occurrence_id);

julia> print(render(q, tables = [visit_occurrence]))
SELECT
  (row_number() OVER (PARTITION BY "visit_occurrence_1"."person_id" ORDER BY "visit_occurrence_1"."visit_start_date")) AS "row_number",
  "visit_occurrence_1"."visit_occurrence_id"
FROM "visit_occurrence" AS "visit_occurrence_1"

Calculate the moving average of the number of patients by the year of birth.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Group(Get.year_of_birth) |>
           Partition(order_by = [Get.year_of_birth],
                     frame = (mode = :range, start = -1, finish = 1)) |>
           Select(Get.year_of_birth, Agg.avg(Agg.count()));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."year_of_birth",
  (avg(count(*)) OVER (ORDER BY "person_1"."year_of_birth" RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS "avg"
FROM "person" AS "person_1"
GROUP BY "person_1"."year_of_birth"

Select(; over; args)
Select(args...; over)

The Select node specifies the output columns.

SELECT $args...
FROM $over

Set the column labels with As.

Examples

List patient IDs and their age.

julia> person = SQLTable(:person, columns = [:person_id, :birth_datetime]);

julia> q = From(:person) |>
           Select(Get.person_id,
                  :age => Fun.now() .- Get.birth_datetime);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  (now() - "person_1"."birth_datetime") AS "age"
FROM "person" AS "person_1"

Asc(; over = nothing, nulls = nothing)

Ascending order indicator.

Desc(; over = nothing, nulls = nothing)

Descending order indicator.

Sort(; over = nothing, value, nulls = nothing)
Sort(value; over = nothing, nulls = nothing)
Asc(; over = nothing, nulls = nothing)
Desc(; over = nothing, nulls = nothing)

Sort order indicator.

Use with Order or Partition nodes.

Examples

List patients ordered by their age.

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Order(Get.year_of_birth |> Desc(nulls = :first));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
ORDER BY "person_1"."year_of_birth" DESC NULLS FIRST

Var(; name)
Var(name)
Var.name        Var."name"      Var[name]       Var["name"]

A reference to a query parameter.

Specify the value for the parameter with Bind to create a correlated subquery or a lateral join.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Get.year_of_birth .> Var.YEAR);

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" > :YEAR)

Where(; over = nothing, condition)
Where(condition; over = nothing)

The Where node filters the input rows by the given condition.

Where is translated to a SQL query with a WHERE clause:

SELECT ...
FROM $over
WHERE $condition

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> q = From(:person) |>
           Where(Fun(">", Get.year_of_birth, 2000));

julia> print(render(q, tables = [person]))
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."year_of_birth" > 2000)

With(; over = nothing, args, materialized = nothing)
With(args...; over = nothing, materialized = nothing)

With assigns a name to a temporary dataset. The dataset content can be retrieved within the over query using the From node.

With is translated to a common table expression:

WITH $args...
SELECT ...
FROM $over

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           With(:essential_hypertension =>
                    From(:condition_occurrence) |>
                    Where(Get.condition_concept_id .== 320128));

julia> print(render(q, tables = [person, condition_occurrence]))
WITH "essential_hypertension_1" ("person_id") AS (
  SELECT "condition_occurrence_1"."person_id"
  FROM "condition_occurrence" AS "condition_occurrence_1"
  WHERE ("condition_occurrence_1"."condition_concept_id" = 320128)
)
SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_2"."person_id"
  FROM "essential_hypertension_1" AS "essential_hypertension_2"
))

WithExternal(; over = nothing, args, qualifiers = [], handler = nothing)
WithExternal(args...; over = nothing, qualifiers = [], handler = nothing)

WithExternal assigns a name to a temporary dataset. The dataset content can be retrieved within the over query using the From node.

The definition of the dataset is converted to a Pair{SQLTable, SQLClause} object and sent to handler, which can use it, for instance, to construct a SELECT INTO statement.

Examples

julia> person = SQLTable(:person, columns = [:person_id, :year_of_birth]);

julia> condition_occurrence =
           SQLTable(:condition_occurrence, columns = [:condition_occurrence_id,
                                                      :person_id,
                                                      :condition_concept_id]);

julia> handler((tbl, def)) =
           println("CREATE TEMP TABLE ", render(ID(tbl.name)), " AS\n",
                   render(def), ";\n");

julia> q = From(:person) |>
           Where(Fun.in(Get.person_id, From(:essential_hypertension) |>
                                       Select(Get.person_id))) |>
           WithExternal(:essential_hypertension =>
                            From(:condition_occurrence) |>
                            Where(Get.condition_concept_id .== 320128),
                        handler = handler);

julia> print(render(q, tables = [person, condition_occurrence]))
CREATE TEMP TABLE "essential_hypertension" AS
SELECT "condition_occurrence_1"."person_id"
FROM "condition_occurrence" AS "condition_occurrence_1"
WHERE ("condition_occurrence_1"."condition_concept_id" = 320128);

SELECT
  "person_1"."person_id",
  "person_1"."year_of_birth"
FROM "person" AS "person_1"
WHERE ("person_1"."person_id" IN (
  SELECT "essential_hypertension_1"."person_id"
  FROM "essential_hypertension" AS "essential_hypertension_1"
))

A component of a SQL syntax tree.

An opaque wrapper over an arbitrary SQL clause.

AGG(; name, args = [], filter = nothing, over = nothing)
AGG(name; args = [], filter = nothing, over = nothing)
AGG(name, args...; filter = nothing, over = nothing)

An application of an aggregate function.

Examples

julia> c = AGG(:max, :year_of_birth);

julia> print(render(c))
max("year_of_birth")

julia> c = AGG(:count, filter = FUN(">", :year_of_birth, 1970));

julia> print(render(c))
(count(*) FILTER (WHERE ("year_of_birth" > 1970)))

julia> c = AGG(:row_number, over = PARTITION(:year_of_birth));

julia> print(render(c))
(row_number() OVER (PARTITION BY "year_of_birth"))

AS(; over = nothing, name, columns = nothing)
AS(name; over = nothing, columns = nothing)

An AS clause.

Examples

julia> c = ID(:person) |> AS(:p);

julia> print(render(c))
"person" AS "p"

julia> c = ID(:person) |> AS(:p, columns = [:person_id, :year_of_birth]);

julia> print(render(c))
"person" AS "p" ("person_id", "year_of_birth")

FROM(; over = nothing)
FROM(over)

A FROM clause.

Examples

julia> c = ID(:person) |> AS(:p) |> FROM() |> SELECT((:p, :person_id));

julia> print(render(c))
SELECT "p"."person_id"
FROM "person" AS "p"

FUN(; name, args = [])
FUN(name; args = [])
FUN(name, args...)

An invocation of a SQL function or a SQL operator.

Examples

julia> c = FUN(:concat, :city, ", ", :state);

julia> print(render(c))
concat("city", ', ', "state")

julia> c = FUN("||", :city, ", ", :state);

julia> print(render(c))
("city" || ', ' || "state")

julia> c = FUN("SUBSTRING(? FROM ? FOR ?)", :zip, 1, 3);

julia> print(render(c))
SUBSTRING("zip" FROM 1 FOR 3)

GROUP(; over = nothing, by = [], sets = nothing)
GROUP(by...; over = nothing, sets = nothing)

A GROUP BY clause.

Examples

julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           SELECT(:year_of_birth, AGG(:count));

julia> print(render(c))
SELECT
  "year_of_birth",
  count(*)
FROM "person"
GROUP BY "year_of_birth"

HAVING(; over = nothing, condition)
HAVING(condition; over = nothing)

A HAVING clause.

Examples

julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           HAVING(FUN(">", AGG(:count), 10)) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
GROUP BY "year_of_birth"
HAVING (count(*) > 10)

ID(; over = nothing, name)
ID(name; over = nothing)
ID(qualifiers, name)

A SQL identifier. Specify over or use the |> operator to make a qualified identifier.

Examples

julia> c = ID(:person);

julia> print(render(c))
"person"

julia> c = ID(:p) |> ID(:birth_datetime);

julia> print(render(c))
"p"."birth_datetime"

julia> c = ID([:pg_catalog], :pg_database);

julia> print(render(c))
"pg_catalog"."pg_database"

JOIN(; over = nothing, joinee, on, left = false, right = false, lateral = false)
JOIN(joinee; over = nothing, on, left = false, right = false, lateral = false)
JOIN(joinee, on; over = nothing, left = false, right = false, lateral = false)

A JOIN clause.

Examples

julia> c = FROM(:p => :person) |>
           JOIN(:l => :location,
                on = FUN("=", (:p, :location_id), (:l, :location_id)),
                left = true) |>
           SELECT((:p, :person_id), (:l, :state));

julia> print(render(c))
SELECT
  "p"."person_id",
  "l"."state"
FROM "person" AS "p"
LEFT JOIN "location" AS "l" ON ("p"."location_id" = "l"."location_id")

LIMIT(; over = nothing, offset = nothing, limit = nothing, with_ties = false)
LIMIT(limit; over = nothing, offset = nothing, with_ties = false)
LIMIT(offset, limit; over = nothing, with_ties = false)
LIMIT(start:stop; over = nothing, with_ties = false)

A LIMIT clause.

Examples

julia> c = FROM(:person) |>
           LIMIT(1) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
FETCH FIRST 1 ROW ONLY

LIT(; val)
LIT(val)

A SQL literal.

In a context of a SQL clause, missing, numbers, strings and datetime values are automatically converted to SQL literals.

Examples

julia> c = LIT(missing);

julia> print(render(c))
NULL

julia> c = LIT("SQL is fun!");

julia> print(render(c))
'SQL is fun!'

NOTE(; over = nothing, text, postfix = false)
NOTE(text; over = nothing, postfix = false)

A free-form prefix of postfix annotation.

Examples

julia> c = FROM(:p => :person) |>
           NOTE("TABLESAMPLE SYSTEM (50)", postfix = true) |>
           SELECT((:p, :person_id));

julia> print(render(c))
SELECT "p"."person_id"
FROM "person" AS "p" TABLESAMPLE SYSTEM (50)

ORDER(; over = nothing, by = [])
ORDER(by...; over = nothing)

An ORDER BY clause.

Examples

julia> c = FROM(:person) |>
           ORDER(:year_of_birth) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
ORDER BY "year_of_birth"

PARTITION(; over = nothing, by = [], order_by = [], frame = nothing)
PARTITION(by...; over = nothing, order_by = [], frame = nothing)

A window definition clause.

Examples

julia> c = FROM(:person) |>
           SELECT(:person_id,
                  AGG(:row_number, over = PARTITION(:year_of_birth)));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER (PARTITION BY "year_of_birth"))
FROM "person"

julia> c = FROM(:person) |>
           WINDOW(:w1 => PARTITION(:year_of_birth),
                  :w2 => :w1 |> PARTITION(order_by = [:month_of_birth, :day_of_birth])) |>
           SELECT(:person_id, AGG(:row_number, over = :w2));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER ("w2"))
FROM "person"
WINDOW
  "w1" AS (PARTITION BY "year_of_birth"),
  "w2" AS ("w1" ORDER BY "month_of_birth", "day_of_birth")

julia> c = FROM(:person) |>
           GROUP(:year_of_birth) |>
           SELECT(:year_of_birth,
                  AGG(:avg,
                      AGG(:count),
                      over = PARTITION(order_by = [:year_of_birth],
                                       frame = (mode = :range, start = -1, finish = 1))));

julia> print(render(c))
SELECT
  "year_of_birth",
  (avg(count(*)) OVER (ORDER BY "year_of_birth" RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING))
FROM "person"
GROUP BY "year_of_birth"

SELECT(; over = nothing, top = nothing, distinct = false, args)
SELECT(args...; over = nothing, top = nothing, distinct = false)

A SELECT clause. Unlike raw SQL, SELECT() should be placed at the end of a clause chain.

Set distinct to true to add a DISTINCT modifier.

Examples

julia> c = SELECT(true, false);

julia> print(render(c))
SELECT
  TRUE,
  FALSE

julia> c = FROM(:location) |>
           SELECT(distinct = true, :zip);

julia> print(render(c))
SELECT DISTINCT "zip"
FROM "location"

ASC(; over = nothing, nulls = nothing)

Ascending order indicator.

DESC(; over = nothing, nulls = nothing)

Descending order indicator.

SORT(; over = nothing, value, nulls = nothing)
SORT(value; over = nothing, nulls = nothing)
ASC(; over = nothing, nulls = nothing)
DESC(; over = nothing, nulls = nothing)

Sort order options.

Examples

julia> c = FROM(:person) |>
           ORDER(:year_of_birth |> DESC()) |>
           SELECT(:person_id);

julia> print(render(c))
SELECT "person_id"
FROM "person"
ORDER BY "year_of_birth" DESC

UNION(; over = nothing, all = false, args)
UNION(args...; over = nothing, all = false)

A UNION clause.

Examples

julia> c = FROM(:measurement) |>
           SELECT(:person_id, :date => :measurement_date) |>
           UNION(all = true,
                 FROM(:observation) |>
                 SELECT(:person_id, :date => :observation_date));

julia> print(render(c))
SELECT
  "person_id",
  "measurement_date" AS "date"
FROM "measurement"
UNION ALL
SELECT
  "person_id",
  "observation_date" AS "date"
FROM "observation"

VALUES(; rows)
VALUES(rows)

A VALUES clause.

Examples

julia> c = VALUES([("SQL", 1974), ("Julia", 2012), ("FunSQL", 2021)]);

julia> print(render(c))
VALUES
  ('SQL', 1974),
  ('Julia', 2012),
  ('FunSQL', 2021)

VAR(; name)
VAR(name)

A placeholder in a parameterized query.

Examples

julia> c = VAR(:year);

julia> print(render(c))
:year

WHERE(; over = nothing, condition)
WHERE(condition; over = nothing)

A WHERE clause.

Examples

julia> c = FROM(:location) |>
           WHERE(FUN("=", :zip, "60614")) |>
           SELECT(:location_id);

julia> print(render(c))
SELECT "location_id"
FROM "location"
WHERE ("zip" = '60614')

WINDOW(; over = nothing, args)
WINDOW(args...; over = nothing)

A WINDOW clause.

Examples

julia> c = FROM(:person) |>
           WINDOW(:w1 => PARTITION(:year_of_birth),
                  :w2 => :w1 |> PARTITION(order_by = [:month_of_birth, :day_of_birth])) |>
           SELECT(:person_id, AGG("row_number", over = :w2));

julia> print(render(c))
SELECT
  "person_id",
  (row_number() OVER ("w2"))
FROM "person"
WINDOW
  "w1" AS (PARTITION BY "year_of_birth"),
  "w2" AS ("w1" ORDER BY "month_of_birth", "day_of_birth")

WITH(; over = nothing, recursive = false, args)
WITH(args...; over = nothing, recursive = false)

A WITH clause.

Examples

julia> c = FROM(:person) |>
           WHERE(FUN(:in, :person_id,
                          FROM(:essential_hypertension) |>
                          SELECT(:person_id))) |>
           SELECT(:person_id, :year_of_birth) |>
           WITH(FROM(:condition_occurrence) |>
                WHERE(FUN("=", :condition_concept_id, 320128)) |>
                SELECT(:person_id) |>
                AS(:essential_hypertension));

julia> print(render(c))
WITH "essential_hypertension" AS (
  SELECT "person_id"
  FROM "condition_occurrence"
  WHERE ("condition_concept_id" = 320128)
)
SELECT
  "person_id",
  "year_of_birth"
FROM "person"
WHERE ("person_id" IN (
  SELECT "person_id"
  FROM "essential_hypertension"
))

julia> c = FROM(:essential_hypertension) |>
           SELECT(*) |>
           WITH(recursive = true,
                FROM(:concept) |>
                WHERE(FUN("=", :concept_id, 320128)) |>
                SELECT(:concept_id, :concept_name) |>
                UNION(all = true,
                      FROM(:eh => :essential_hypertension) |>
                      JOIN(:cr => :concept_relationship,
                           FUN("=", (:eh, :concept_id), (:cr, :concept_id_1))) |>
                      JOIN(:c => :concept,
                           FUN("=", (:cr, :concept_id_2), (:c, :concept_id))) |>
                      WHERE(FUN("=", (:cr, :relationship_id), "Subsumes")) |>
                      SELECT((:c, :concept_id), (:c, :concept_name))) |>
                AS(:essential_hypertension, columns = [:concept_id, :concept_name]));

julia> print(render(c))
WITH RECURSIVE "essential_hypertension" ("concept_id", "concept_name") AS (
  SELECT
    "concept_id",
    "concept_name"
  FROM "concept"
  WHERE ("concept_id" = 320128)
  UNION ALL
  SELECT
    "c"."concept_id",
    "c"."concept_name"
  FROM "essential_hypertension" AS "eh"
  JOIN "concept_relationship" AS "cr" ON ("eh"."concept_id" = "cr"."concept_id_1")
  JOIN "concept" AS "c" ON ("cr"."concept_id_2" = "c"."concept_id")
  WHERE ("cr"."relationship_id" = 'Subsumes')
)
SELECT *
FROM "essential_hypertension"