FunSQL.jl

A correlated query is an inner query that depends on data from the outer query. In FunSQL, you implement this using the Bind node and Var parameters.

To create a correlated query:

1. Define the inner query using Var placeholders (e.g., Var.PERSON_ID) to refer to external data.
2. Wrap the inner query in a Bind call, mapping the Var symbols to the actual expressions/columns from the outer query.
3. Use the resulting query within an operator like Fun.exists in the outer query.

using FunSQL: Bind

# Define the correlated subquery logic
CorrelatedCondition(person_id, start_date, end_date) =
    From(:condition_occurrence) |
    Where(Fun.and(Get.person_id .== Var.PERSON_ID,
                  Fun.between(Get.condition_start_date, Var.START_DATE, Var.END_DATE))) |
    Bind(:PERSON_ID => person_id,
         :START_DATE => start_date,
         :END_DATE => end_date)

# Use it in the outer query
q = From(:visit_occurrence) |
    Where(Fun.exists(CorrelatedCondition(Get.person_id, Get.visit_start_date, Get.visit_end_date)))

The Partition node allows you to relate each row to other rows in the same dataset without collapsing them into a single row (unlike Group). This is used for window functions.

• Partitioning: Use Partition(key, ...) to divide rows into groups.
• Ordering: Use the order_by keyword argument to specify the sort order within the partition.
• Window Frame: Use the frame keyword argument to specify the subset of rows relative to the current row. The frame is a tuple: (mode = :rows, start = ..., finish = ...). For example, start = -Inf and finish = -1 represents all rows prior to the current row.
• Aggregates in Windows: You can apply regular aggregate functions within a Select clause after a Partition to calculate summary values for the window frame.

using FunSQL: Partition

q = From(:visit_occurrence) |>
    Partition(Get.person_id,
              order_by = [Get.visit_start_date],
              frame = (mode = :rows, start = -Inf, finish = -1)) |>
    Select(Get.person_id,
           Get.visit_start_date,
           Get.visit_end_date,
           :gap => Fun.julianday(Get.visit_start_date) .- Fun.julianday(Agg.max(Get.visit_end_date)))

FunSQL is a data-oriented query builder. This distinguishes it from syntax-oriented builders (like Active Record or Laravel) in how they handle the order of operations:

1. Data-oriented (FunSQL, EF/LINQ, dbplyr): The pipeline represents a sequence of data transformations. The order of nodes matters because each node refines the output of the previous one. For example, placing Limit before Where changes the result from "all filtered records" to "the first N records, then filtered". FunSQL achieves this by converting the pipeline into nested SQL queries (using subqueries in FROM clauses) to respect SQL's rigid clause order while allowing arbitrary pipeline composition.

2. Syntax-oriented (Active Record, Laravel): The pipeline incrementally populates a fixed SQL syntax tree (a struct with slots for SELECT, FROM, WHERE, etc.). The order of calls does not change the final SQL because they are all just filling different slots in the same structure.

Why use FunSQL? While syntax-oriented builders are easier to implement, they inherit SQL's rigid grammar and deficiencies. Data-oriented builders like FunSQL allow for more natural, compositional data processing pipelines that can be mapped to complex SQL features like correlated subqueries and lateral joins.

FunSQL allows you to build complex queries by assembling small, reusable, and testable components. Instead of writing a single monolithic SQL statement, you can define functions that return query objects (using From, Join, Where, etc.), validate them individually by executing them against a database connection, and then compose them into larger pipelines.

Key patterns for incremental assembly:

• Define Concepts: Create functions that represent medical or domain concepts (e.g., using SNOMED() or VISIT()).
• Define Occurrences: Create functions that represent rows in a table filtered by those concepts (e.g., using From(:table) |> Join(...)).
• Compose with Logic: Use higher-order functions like Where(Fun.exists(...)) to correlate different occurrence datasets.
• Apply Filters: Use window functions or partitioning (e.g., Partition(...) |> Define(...)) to create complex filtering logic like time-gap requirements.

# Example of composing a complex query from smaller parts
MyocardialInfarctionDuringInpatientVisit() =
    MyocardialInfarctionOccurrence() |
    Where(Fun.exists(CorrelatedInpatientVisit(Get.person_id, Get.condition_start_date)))

# Execute the composed query
DBInterface.execute(conn, MyocardialInfarctionDuringInpatientVisit()) |> DataFrame

When performing a Join, the As node is used to disambiguate columns between the two branches.

Important Behavior:

• By default, columns wrapped in an As node are not present in the final output of the Join node.
• To include columns from both branches of a join, you must use a Select node following the join.
• When using Select to combine columns from joined tables, you must ensure all column names are unique to avoid a FunSQL.DuplicateLabelError.

To avoid duplicate labels, you can use the tail keyword in Get to reference columns from a specific branch, and filter out keys that already exist in the first branch.

# 1. Join with disambiguation (columns in As are hidden by default)
q = From(:person) |> As(:person) |>
     Join(From(:visit_occurrence),
          on = Get.person.person_id .== Get.person_id)

# 2. Select columns from both branches while avoiding duplicates
const person_table = conn.catalog[:person]
const visit_occurrence_table = conn.catalog[:visit_occurrence]

q_with_all_columns = q |> 
    Select(
        Get.(keys(person_table.columns))..., 
        Get.(filter(!in(keys(person_table.columns)), collect(keys(visit_occurrence_table.columns))), 
             tail = Get.person)...
    )

To summarize data (e.g., calculating totals or averages), use the Group node to partition rows into disjoint groups, followed by the Agg node to apply aggregate functions to those groups.

• Grouping by keys: Pass arguments to Group(...) to define the grouping key. The output contains all distinct values of the grouping key.
• Global aggregation: To apply an aggregate function to the entire dataset (equivalent to omitting GROUP BY in SQL), use Group() without arguments.
• Aggregate functions: Created using the Agg node (e.g., Agg.count(), Agg.avg(), Agg.max()).
• Flexibility: Unlike SQL, FunSQL allows aggregate functions in any context where an expression is permitted, including Where clauses (which replaces the need for a HAVING clause).

using FunSQL: Agg, Group

# Grouping by a key
q = From(:person) |>
    Group(Get.year_of_birth) |>
    Select(Get.year_of_birth, Agg.count())

# Global aggregation (no GROUP BY)
q = From(:person) |>
    Group() |>
    Select(Agg.avg(Get.year_of_birth))

The Join node correlates rows from two datasets.

• Join: Requires the first pipeline to be attached via |>, the second pipeline to be provided as an argument, and an on condition.
• LeftJoin: An alias for Join(..., left = true), used to ensure all records from the primary dataset are kept.
• Disambiguation: When joining tables with overlapping column names, use the As node or the => operator to nest one dataset's columns into a record, preventing name collisions.

Alternatively, you can specify both pipelines using tail (primary) and joinee (secondary) keyword arguments.

using FunSQL: From, LeftJoin, As, Get

# Using LeftJoin with disambiguation via As
From(:person) |
    LeftJoin(From(:location) |> As(:location),
             on = Get.location_id .== Get.location.location_id) |
    Select(Get.person_id, Get.location.state)

# Alternative syntax using keyword arguments
Join(tail = From(:person),
     joinee = :location => From(:location),
     on = Get.location_id .== Get.location.location_id,
     left = true) |
Select(Get.person_id, Get.location.state)