Selecto v0.3.16

Selecto.Window (Selecto v0.3.16)

Window functions provide powerful analytical capabilities for PostgreSQL queries.

Window functions allow you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, window functions do not group rows into a single output row — they retain individual row identity.

examples

 Examples 

# Ranking functions
selecto
|> Selecto.window_function(:row_number, over: [partition_by: ["category"], order_by: ["sales_date"]])
|> Selecto.window_function(:rank, over: [partition_by: ["region"], order_by: [{"total_sales", :desc}]])

# Offset functions  
selecto
|> Selecto.window_function(:lag, ["sales_amount", 1], over: [partition_by: ["customer_id"], order_by: ["sales_date"]])
|> Selecto.window_function(:lead, ["sales_amount"], over: [order_by: ["sales_date"]], as: "next_month_sales")

# Aggregate window functions
selecto  
|> Selecto.window_function(:sum, ["sales_amount"], over: [partition_by: ["region"], order_by: ["sales_date"]])
|> Selecto.window_function(:avg, ["sales_amount"], over: [order_by: ["sales_date"], frame: {:rows, :unbounded_preceding, :current_row}])

Link to this section Summary

Functions

add_window_function(selecto, function, arguments \\ [], options)

Add a window function to the Selecto query.

parse_frame_public(frame_spec)

Parse a window frame specification.

Link to this section Functions

Link to this function

add_window_function(selecto, function, arguments \\ [], options)

Add a window function to the Selecto query.

parameters

 Parameters

  • selecto - The Selecto struct
  • function - Window function type (atom)
  • arguments - Arguments for the function (optional for ranking functions)
  • options - Window function options

options

 Options

  • :over - Window specification (required)
    • :partition_by - Fields to partition by
    • :order_by - Fields and directions to order by
    • :frame - Window frame specification
  • :as - Output column alias

examples

 Examples 

# Row number within each category, ordered by date
selecto |> Selecto.window_function(:row_number, 
  over: [partition_by: ["category"], order_by: ["created_at"]])

# Running total of sales by customer
selecto |> Selecto.window_function(:sum, ["amount"], 
  over: [partition_by: ["customer_id"], order_by: ["date"]], 
  as: "running_total")

# Previous month's sales for comparison
selecto |> Selecto.window_function(:lag, ["amount", 1], 
  over: [partition_by: ["customer_id"], order_by: ["month"]], 
  as: "prev_month")
Link to this function

parse_frame_public(frame_spec)

Parse a window frame specification.

Public function for testing frame parsing.