Tuple functions

The standard library defines the following functions for manipulating tuples:

tuple_reduce

Applies a two-parameter function cumulatively across a tuple, in order to reduce the tuple to a single value.

from invoices
derive {
  cleared = tuple_reduce std.and {
    received, processed, packed, shipped
  }
}

SELECT
  *,
  received
  AND processed
  AND packed
  AND shipped AS cleared
FROM
  invoices

When the initial: named parameter is provided, its value will be used to initialize the reduction operation, and will be the default value if the tuple is empty.

from test
derive {
  mysum = tuple_reduce initial:0 add {1, 2, 3}
}

SELECT
  *,
  0 + 1 + 2 + 3 AS mysum
FROM
  test

If initial is not provided, when the tuple has exactly one entry, its value will be returned; when the tuple is empty, an error will be raised.

tuple_map

Applies a function to each entry in a tuple, returning a tuple. Aliases defined in the tuple will be passed through to the output.

prql target:sql.duckdb

from invoices
select (
  tuple_map (date.to_text "%d/%m/%Y") {
    rcv_txt = received_on,
    prc_txt = processed_on,
  }
)

SELECT
  strftime(received_on, '%d/%m/%Y') AS rcv_txt,
  strftime(processed_on, '%d/%m/%Y') AS prc_txt
FROM
  invoices

tuple_zip

Combines two tuples into one tuple by aligning them in parallel and creating tuples out of each aligned pair. This can be used in conjunction with tuple_map and tuple_reduce in various ways:

from invoices
derive (
  tuple_zip {x, y} {u, v}
  tuple_map (tuple_reduce (func a b -> a + b))
)

SELECT
  *,
  x + u,
  y + v
FROM
  invoices

tuple_uniq

Iteratively deduplicates a tuple by alias (or, when an alias is not defined, by its referenced column name). This can help to have more control over situations when a column may be being overwritten.

For example, the following includes all columns from both invoices and shipments in the final result, even those that have overlapping names:

let shipments = (from shipments | select {id, invoice_id, date_of, shipped_on})

from invoices
select {id, date_of, processed}
join shipments (this.id == that.invoice_id)

WITH table_0 AS (
  SELECT
    id,
    invoice_id,
    date_of,
    shipped_on
  FROM
    shipments
)
SELECT
  invoices.id,
  invoices.date_of,
  invoices.processed,
  shipments.id,
  shipments.invoice_id,
  shipments.shipped_on
FROM
  invoices
  INNER JOIN table_0 AS shipments ON invoices.id = shipments.invoice_id

Adding select (tuple_uniq take:late {invoices.*, shipments.*}) will allow the columns from shipments to appear in the output taking precedence over those from invoices.

let shipments = (from shipments | select {id, invoice_id, date_of, shipped_on})

from invoices
select {id, date_of, processed}
join shipments (this.id == that.invoice_id)
select (tuple_uniq take:late {invoices.*, shipments.*})

WITH table_0 AS (
  SELECT
    id,
    invoice_id,
    date_of,
    shipped_on
  FROM
    shipments
)
SELECT
  shipments.id,
  shipments.date_of,
  invoices.processed,
  shipments.invoice_id,
  shipments.shipped_on
FROM
  invoices
  INNER JOIN table_0 AS shipments ON invoices.id = shipments.invoice_id

Using take:early rather than take:late flips the priority.

let shipments = (from shipments | select {id, invoice_id, date_of, shipped_on})

from invoices
select {id, date_of, processed}
join shipments (this.id == that.invoice_id)
select (tuple_uniq take:early {invoices.*, shipments.*})

WITH table_0 AS (
  SELECT
    id,
    invoice_id,
    date_of,
    shipped_on
  FROM
    shipments
)
SELECT
  invoices.id,
  invoices.date_of,
  invoices.processed,
  shipments.invoice_id,
  shipments.shipped_on
FROM
  invoices
  INNER JOIN table_0 AS shipments ON invoices.id = shipments.invoice_id

Items in a tuple without a name or an alias will be dropped.

from test
select (tuple_uniq {x, 5, y})

SELECT
  x,
  y
FROM
  test

tuple_reverse

Reverses the order of a tuple.

from test
select (tuple_reverse {x, y, z})

SELECT
  z,
  y,
  x
FROM
  test