Question 287601 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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).

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    This works for either RDBMS:

    SELECT ord, name
     , count(step or NULL) OVER (ORDER BY ord) AS myrank
FROM  (
   SELECT ord, name, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;

    Postgres fiddle
    Oracle fiddle

    Notably, count() only counts not-null values, and

    true  OR null → true
false OR null → null

    Counting the number of changes in the name column generates the desired group numbers.

    Optimized for Postgres:

    SELECT ord, name
     , count(*) FILTER (WHERE step) OVER (ORDER BY ord) + 1 AS myrank
FROM  (
   SELECT *, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search