Traditional aggregate functions operate on either individual rows or collapse entire datasets into single results. In contrast, window functions compute values for each row within a defined subset of data. These functions rely on three fundamental components: partitioning, ordering, and framing.

function OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame_specification]
)

• Partitioning: Defined by the PARTITION BY clause, this divides the dataset into distinct groups. When omitted, the entire result set forms one partition.
• Ordering: Controled by ORDER BY, this arranges rows within each partition. For consistent results with duplicate values, use multiple sorting columns like ORDER BY timestamp, operation_type.
• Framing: Further restricts the rows considered within a partition. This component doesn't apply to ranking functions. Syntax follows {ROWS | RANGE} {frame_start | frame_between}, such as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Aggregate Functions in Windows

All standard aggregation functions work with in window contexts. Using SUM as an example:

SUM(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame_specification]
)

Returns: double precision value

Example: Calculate each employee's salary proportion within their department.

SELECT
  dept_name,
  employee_name,
  income,
  ROUND(income * 1.0 / SUM(income) OVER(PARTITION BY dept_name), 3) AS income_ratio
FROM company_data

Alternative approach without window functions:

SELECT
  base.dept_name,
  base.employee_name,
  base.income,
  ROUND(base.income * 1.0 / dept_totals.total_income, 3) AS income_ratio
FROM company_data base
JOIN (
  SELECT dept_name, SUM(income) AS total_income
  FROM company_data
  GROUP BY dept_name
) AS dept_totals ON base.dept_name = dept_totals.dept_name
ORDER BY base.dept_name, base.employee_name;

Cumulative Distribution Function

The CUME_DIST function calculates the relative position of a value within ordered data by counting how many rows have values less than or equal to the current row's value.

CUME_DIST() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

Returns: double between (0, 1]

Example: Analyze size distribution of objects in OSS bucket 'bucket00788'.

bucket=bucket00788 |
SELECT
  item_name,
  file_size,
  CUME_DIST() OVER (
    PARTITION BY item_name
    ORDER BY file_size
  ) AS distribution_percent
FROM oss_log_storage

Another scenario calculating salary distributions:

SELECT
  dept_name,
  worker_name,
  wage,
  CUME_DIST() OVER (ORDER BY wage) AS percentile_rank
FROM employee_records;

Dense Rank Function

DENSE_RANK assigns sequential rankings to rows within partitions. Identical values receive the same rank, and subsequent ranks continue without gaps.

DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

Returns: bigint

Example: Determine salary rankings within departments.

* |
SELECT
  team_name,
  member_name,
  compensation,
  DENSE_RANK() OVER(
    PARTITION BY team_name
    ORDER BY compensation DESC
  ) AS pay_tier
ORDER BY team_name, pay_tier

Ntile Function

NTILE distributes rows into specified number of approximately equal groups based on ordering.

NTILE(groups_count) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
)

Returns: bigint

Example: Divide object data into quartiles.

target_object=model-245-da918c |
SELECT
  resource_name,
  data_volume,
  NTILE(4) OVER (
    PARTITION BY resource_name
    ORDER BY data_volume
  ) AS quartile_group
FROM storage_repository

Percent Rank vs Cumulative Distribution

While both measure relative positions, they differ in calculation methods:

• CUME_DIST computes relative_position / total_rows (returns values in (0,1])
• PERCENT_RANK uses (relative_position - 1) / (total_rows - 1)

For duplicate values: - CUME_DIST considers the last occurrence - PERCENT_RANK uses the first occurrence

Additional Ranking Functions

Other commonly used window functions include:

• RANK() - Standard ranking with gaps for ties
• ROW_NUMBER() - Sequential numbering regardless of value equality
• FIRST_VALUE() - Returns first value in the window
• LAST_VALUE() - Returns last value in the window
• LAG() - Accesses previous row's value
• LEAD() - Accesses next row's value
• NTH_VALUE() - Returns value at specified position