About DENSE\_RANK function
DENSE_RANK is similar as Spark SQL - RANK Window Function. It calculates the rank of a value in a group of values. It returns one plus the number of rows proceeding or equals to the current row in the ordering of a partition. The returned values are sequential in each window thus no gaps will be generated.
DENSE\_RANK without partition
The following sample SQL uses DENSE_RANK function without PARTITION BY clause:
SELECT TXN.*, DENSE_RANK() OVER (ORDER BY TXN_DT) AS ROW_RANK FROM VALUES
(101,10.01, DATE'2021-01-01'),
(101,102.01, DATE'2021-01-01'),
(102,93., DATE'2021-01-01'),
(103,913.1, DATE'2021-01-02'),
(102,913.1, DATE'2021-01-02'),
(101,900.56, DATE'2021-01-03')
AS TXN(ACCT,AMT, TXN_DT);
Result:
ACCT AMT TXN_DT ROW_RANK
101 10.01 2021-01-01 1
101 102.01 2021-01-01 1
102 93.00 2021-01-01 1
103 913.10 2021-01-02 2
102 913.10 2021-01-02 2
101 900.56 2021-01-03 3
warning The following warning message will show: WARN window.WindowExec: No Partition Defined for Window operation! Moving all data to a single partition, this can cause serious performance degradation.
DENSE\_RANK with partition
The following sample SQL returns a rank number for each records in each window (defined by PARTITION BY):
SELECT TXN.*,
DENSE_RANK() OVER (PARTITION BY TXN_DT ORDER BY AMT DESC) AS ROW_RANK
FROM VALUES
(101,10.01, DATE'2021-01-01'),
(101,102.01, DATE'2021-01-01'),
(102,93., DATE'2021-01-01'),
(103,913.1, DATE'2021-01-02'),
(102,913.1, DATE'2021-01-02'),
(101,900.56, DATE'2021-01-03')
AS TXN(ACCT,AMT, TXN_DT);
Result:
ACCT AMT TXN_DT ROW_RANK
101 102.01 2021-01-01 1
102 93.00 2021-01-01 2
101 10.01 2021-01-01 3
101 900.56 2021-01-03 1
103 913.10 2021-01-02 1
102 913.10 2021-01-02 1
Records are allocated to windows based on TXN_DTcolumn and the rank is computed based on column AMT in each window.
infoBy default, records will be sorted in ascending order. Use ORDER BY .. DESC to sort records in descending order.
Example table
The virtual table/data frame is cited from SQL - Construct Table using Literals.