Functions have two types of parameters:

1. Positional parameters, which require an argument.
2. Named parameters, which optionally take an argument, otherwise using their default value.

SELECT
  *,
  (temp_f - 32) / 1.8 AS temp_c
FROM
  cities

This function is named interp, and has two positional parameters named high and x, and one named parameter named low which takes a default argument of 0. It calculates the proportion of the distance that x is between low and high.

func interp low:0 high x -> (x - low) / (high - low)
derive [
  sat_proportion_1 = (interp 1600 sat_score),
  sat_proportion_2 = (interp low:0 1600 sat_score),
]

SELECT
  (sat_score - 0) / 1600 AS sat_proportion_1,
  (sat_score - 0) / 1600 AS sat_proportion_2
FROM
  students

PRQL

SQL

SELECT
  *,
  (sat_score - 0) / 1600 AS sat_proportion_1,
  (sat_score - 0) / 1600 AS sat_proportion_2
FROM
  students

and

PRQL

func fahrenheit_to_celsius temp -> (temp - 32) / 1.8
from cities
derive temp_c = (temp_f | fahrenheit_to_celsius)

SQL

SELECT
  *,
  (temp_f - 32) / 1.8 AS temp_c
FROM

PRQL

SQL

SELECT
  *,
  ((temp_c - 32) / 1.8 - 0) / 100 AS boiling_proportion
  kettles

Scope

Functions can binding to any variables in scope when the function is executed. For example, here cost_total refers to the column that’s introduced in the from.

func cost_share cost -> cost / cost_total
from costs
select [materials, labor, overhead, cost_total]
derive [
  materials_share = (cost_share materials),
  labor_share = (cost_share labor),
  overhead_share = (cost_share overhead),
]

SELECT
  materials,
  labor,
  overhead,
  cost_total,
  materials / cost_total AS materials_share,
  labor / cost_total AS labor_share,
  overhead / cost_total AS overhead_share
FROM
  costs