SQL window functions

This list includes window functions currently supported by Network Reporting.

avg

Returns the average (arithmetic mean) of the input expression values. The AVG function works with numeric values and ignores NULL values.

AVG ( [ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, AVG(count_medstaff__v) OVER(PARTITION BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The average count of the medical staff in the group is returned along with each row. The average count is reset for each 

hco_type__v.

count

Counts the rows defined by the expression. The COUNT function has two variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression.

COUNT ( * | [ ALL ] expression) OVER ( [ PARTITION BY expr_list ] )

SELECT DISTINCT primary_country__v, hco_type__v, COUNT (corporate_name__v) OVER (PARTITION BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The count of each HCO type in the primary country is returned.

dense_rank

Determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank. The DENSE_RANK function differs from RANK in one respect: If two or more rows tie, there is no gap in the sequence of ranked values. For example, if two rows are ranked 1, the next rank is 2.

DENSE_RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

SELECT vid__v, primary_country__v, hcp_type__v, DENSE_RANK () OVER(PARTITION BY primary_country__v ORDER BY hcp_type__v) AS rank FROM hcp WHERE COALESCE(hcp_type__v,'') <> ''

Result

The data is partitioned by the primary country and results are then ordered by the HCP type. Each HCP type has a rank based on the order of the HCP types. The rank is reset for each country. Rankings will change when the partition and order by is changed.

first_value

Given an ordered set of rows, FIRST_VALUE returns the value of the specified expression with respect to the first row in the window frame.

FIRST_VALUE ( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, FIRST_VALUE(corporate_name__v ignore nulls) OVER(PARTITION BY hco_type__v order by created_date__v rows between unbounded preceding and current row) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The first corporate name in the group is returned along with each row in the group.

lag

Returns the values for a row at a given offset above (before) the current row in the partition.

LAG (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

SELECT primary_country__v, hco_type__v, corporate_name__v, created_date__v, LAG(corporate_name__v) OVER( ORDER BY hco_type__v), LAG(created_date__v) OVER( ORDER BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The corporate name and the assoicated record of the record prior is returned along with each record.

last_value

Given an ordered set of rows, the LAST_VALUE function returns the value of the expression with respect to the last row in the frame.

LAST_VALUE ( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, LAST_VALUE(corporate_name__v ignore nulls) OVER(PARTITION BY hco_type__v order by created_date__v rows between unbounded preceding and current row) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The last corporate name in the group is returned along with each row in the group.

lead

Returns the values for a row at a given offset below (after) the current row in the partition.

LEAD (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

SELECT primary_country__v, hco_type__v, corporate_name__v, created_date__v, LEAD(corporate_name__v) OVER( ORDER BY hco_type__v), LEAD(created_date__v) OVER( ORDER BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The corporate name and the associated record of the record after is returned along with each record.

max

The MAX window function returns the maximum of the input expression values. The MAX function works with numeric values and ignores NULL values.

MAX ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, MAX (count_medstaff__v) OVER (PARTITION BY hco_type__v), MAX (count_medstaff__v) OVER (PARTITION BY hco_type__v) - count_medstaff__v AS "Count Difference" FROM hco WHERE primary_country__v = 'US' AND count_medstaff__v IS NOT NULL

Result

The maximum count of medstaff is returned along with each row. The difference between the count of the medstaff for the record and the maximum count of medstaff is also returned with each row.

median

Calculates the median value for the range of values in a window or partition. NULL values in the range are ignored. MEDIAN is an inverse distribution function that assumes a continuous distribution model.

MEDIAN ( median_expression ) OVER ( [ PARTITION BY partition_expression ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, MEDIAN(count_medstaff__v) OVER(PARTITION BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The median count of medstaff is returned along with each row.

min

The MIN window function returns the minimum of the input expression values. The MIN function works with numeric values and ignores NULL values.

MIN ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, MIN (count_medstaff__v) OVER (PARTITION BY hco_type__v), count_medstaff__v - MIN (count_medstaff__v) OVER (PARTITION BY hco_type__v)AS "Count Difference" FROM hco WHERE primary_country__v = 'US' AND count_medstaff__v IS NOT NULL

Result

The minimum count of medstaff is returned along with each row. The difference between the count of the medstaff for the record and the minmum count of medstaff is also returned with each row.

percent_rank

Calculates the percent rank of a given row. The percent rank is determined using this formula:(x - 1) / (the number of rows in the window or partition - 1) where x is the rank of the current row. 

PERCENT_RANK () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )

SELECT vid__v, primary_country__v, hcp_type__v, PERCENT_RANK () OVER(PARTITION BY primary_country__v ORDER BY hcp_type__v) AS rank FROM hcp WHERE COALESCE(hcp_type__v,'') <> ''

Result

The data is partitioned by the primary country and results are then ordered by the HCP type. Each HCP type has a rank based on the order of the HCP types. The rank is reset for each country. Rankings will change when the partition and order by is changed. The rank is given in the form of a percentage.

rank

Determines the rank of a value in a group of values, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank. Amazon Redshift adds the number of tied rows to the tied rank to calculate the next rank and thus the ranks might not be consecutive numbers. For example, if two rows are ranked 1, the next rank is 3.

RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

SELECT vid__v, primary_country__v, hcp_type__v, RANK () OVER(PARTITION BY primary_country__v ORDER BY hcp_type__v) AS rank FROM hcp WHERE COALESCE(hcp_type__v,'') <> ''

Result

The data is partitioned by the primary country and results are then ordered by the HCP type. Each HCP type has a rank based on the order of the HCP types. The rank is reset for each country. Rankings will change when the partition and order by is changed.

ratio_to_report

Calculates the ratio of a value to the sum of the values in a window or partition. The ratio to report value is determined using the formula: 

value of ratio_expression argument for the current row / sum of ratio_expression argument for the window or partition

RATIO_TO_REPORT ( ratio_expression ) OVER ( [ PARTITION BY partition_expression ] )

SELECT primary_country__v, hco_type__v, corporate_name__v, count_medstaff__v, RATIO_TO_REPORT(count_medstaff__v) OVER (PARTITION BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL AND count_medstaff__v <> 0

Result

The ratio of the count of record to the numer of recrods for each group is returned.

row_number

Determines the ordinal number of the current row within a group of rows, counting from 1, based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the ordinal numbers are reset for each group of rows. Rows with equal values for the ORDER BY expressions receive the different row numbers nondeterministically.

ROW_NUMBER () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] )

SELECT vid__v, primary_country__v, hcp_type__v, ROW_NUMBER () OVER(PARTITION BY primary_country__v ORDER BY hcp_type__v) AS rank FROM hcp WHERE COALESCE(hcp_type__v,'')<>''

Result

The data is partitioned by the primary country and results are then ordered by the HCP type. Each row in the group is given a row number, as if it is a rank for the row. In this case, each row is identified by its network entity ID, so it can be treated as a rank for the entity ID. The row number is reset for each country.

sum

The SUM window function returns the sum of the input column or expression values. The SUM function works with numeric values and ignores NULL values.

SUM ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] )

SELECT DISTINCT primary_country__v, hco_type__v, SUM(count_medstaff__v) OVER (PARTITION BY hco_type__v) FROM hco WHERE count_medstaff__v IS NOT NULL

Result

The sum of the medical staff per HCO type in each country is returned.