sqlpostgresqloracle-databaserankgaps-and-islands

Ranking by groups of continuous equal values


With this query:

with tbl as
(
  select  1 ord, 'A' name from dual
  union all
  select  2 ord, 'A' name from dual
  union all
  select  3 ord, 'A' name from dual
  union all
  select  4 ord, 'B' name from dual
  union all
  select  5 ord, 'B' name from dual
  union all
  select  6 ord, 'A' name from dual
  union all
  select  7 ord, 'A' name from dual
  union all
  select  8 ord, 'C' name from dual
  union all
  select  9 ord, 'C' name from dual
  union all
  select 10 ord, 'B' name from dual
  union all
  select 11 ord, 'B' name from dual
  union all
  select 12 ord, 'B' name from dual
) 
select ord, name, myrank(...)
from tbl
order by
  ord;

I want to get these results:

       ORD NAME     MYRANK
---------- ---- ----------
         1 A             1
         2 A             1
         3 A             1
         4 B             2
         5 B             2
         6 A             3
         7 A             3
         8 C             4
         9 C             4
        10 B             5
        11 B             5
        12 B             5

Same rank for continuous equal values. Different groups of same continuous equal values have different ranks. Rank increases monotonically in order of "ord".

For Oracle and PostgreSQL (ultimate query for both systems is preferable).

Edit: I forgot to mention version: Oracle 11g PostgreSQL 12


Solution

  • From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

    SELECT ord, name, myrank
    FROM   tbl
    MATCH_RECOGNIZE(
      ORDER BY ord
      MEASURES
        MATCH_NUMBER() AS myrank
      ALL ROWS PER MATCH
      PATTERN (same_name+)
      DEFINE
        same_name AS FIRST(name) = name
    );
    

    In all versions, you can use the LAG and SUM analytic functions to output the same:

    SELECT ord,
           name,
           SUM(has_changed) OVER (ORDER BY ord) AS myrank
    FROM   (
      SELECT ord,
             name,
             CASE
             WHEN name = LAG(name) OVER (ORDER BY ord)
             THEN 0
             ELSE 1
             END AS has_changed
      FROM   tbl
    ) t;
    

    Which, for the sample data, both output:

    ORD NAME MYRANK
    1 A 1
    2 A 1
    3 A 1
    4 B 2
    5 B 2
    6 A 3
    7 A 3
    8 C 4
    9 C 4
    10 B 5
    11 B 5
    12 B 5

    The second query also works in PostgreSQL.

    Oracle fiddle PostgreSQL fiddle