oracle-databasewindow-functionsgaps-and-islandsdense-rank

Numbering long chains of repeating values in a column of a table in Oracle using window functions


I have a similar question as: Numbering long chains of repeating values in a column of a table in PostgreSQL using window functions , but then for Oracle.

So, I want to rewrite the following PostgreSQL sql to Oracle sql.

select id, col,    
  count(*) filter(where col is distinct from lag_col) over(order by id) grp 
from (     
    select t.*, lag(col) over(order by id) as lag_col     
    from mytable t 
) t order by id

Solution

  • From Oracle 12, you can use MATCH_RECOGNIZE:

    SELECT id, col, grp
    FROM   mytable
    MATCH_RECOGNIZE(
      ORDER BY id
      MEASURES MATCH_NUMBER() AS grp
      ALL ROWS PER MATCH
      PATTERN  (same_col+)
      DEFINE   same_col AS FIRST(col) = col
    );
    

    Or, in earlier version you can use the LAG and SUM analytic functions:

    SELECT id, col,
           SUM(has_changed) OVER (ORDER BY id) AS grp
    FROM   (
      SELECT t.*,
             CASE LAG(col) OVER (ORDER BY id) WHEN col THEN 0 ELSE 1 END AS has_changed
      FROM   mytable t
    ) t
    

    Which, for the sample data:

    CREATE TABLE mytable (id, col) AS
    SELECT  1, 0 FROM DUAL UNION ALL
    SELECT  2, 0 FROM DUAL UNION ALL
    SELECT  3, 0 FROM DUAL UNION ALL
    SELECT  4, 1 FROM DUAL UNION ALL
    SELECT  5, 1 FROM DUAL UNION ALL
    SELECT  6, 1 FROM DUAL UNION ALL
    SELECT  7, 0 FROM DUAL UNION ALL
    SELECT  8, 0 FROM DUAL UNION ALL
    SELECT  9, 1 FROM DUAL UNION ALL
    SELECT 10, 0 FROM DUAL;
    

    Both output:

    ID COL GRP
    1 0 1
    2 0 1
    3 0 1
    4 1 2
    5 1 2
    6 1 2
    7 0 3
    8 0 3
    9 1 4
    10 0 5

    fiddle

    Note: the second method also works in PostgreSQL fiddle.