0

I want to create a WINDOW in PostgreSQL and get the row_number() from a number until it appears again. As example, supposing I want to create a window from number 79 until 79 appears again and reset the counting, it must be like this:

number        must be        row_number number
  50                             ?        50
  79                             1        79
  85                             2        85 
  74                             3        74 
  14                             4        14
  79                             1        79
  46                             2        46
  85                             3        85   
  79                             1        79
  45                             2        45

How I can do this?

Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199

2 Answers2

1

Consider this:

-- temporary test table
CREATE TEMP TABLE tbl (id serial, nr int);
INSERT INTO tbl(nr) VALUES
 (50),(79),(85),(74),(14)
,(79),(46),(85),(79),(45);

SELECT id, nr
       ,CASE WHEN grp > 0 THEN
          row_number() OVER (PARTITION BY grp ORDER BY id)::text
        ELSE '?' END AS rn      
FROM  (
    SELECT id, nr
          ,sum(CASE WHEN nr = 79 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS grp
    FROM   tbl) x
-- WHERE grp > 0  -- to exclude numbers before the first 79

Produces exactly your result.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

There is always a CTE lurking somewhere ...

-- temporary test table
-- (thanks for that)
CREATE TEMP TABLE tbl (id serial, nr int);
INSERT INTO tbl(nr) VALUES
 (50),(79),(85),(74),(14)
,(79),(46),(85),(79),(45);

-- EXPLAIN ANALYZE
WITH next AS (
    SELECT t1.id AS id
         , t2.id AS next
    FROM tbl t1
    JOIN tbl t2 ON (t2.nr = t1.nr AND t2.id > t1.id)
        WHERE NOT EXISTS ( SELECT *
            FROM tbl nx
            WHERE nx.nr = t1.nr
            AND nx.id > t1.id
            AND nx.id < t2.id
            )
     )
SELECT t0.id
        , t0.nr
        , next.next AS next
FROM tbl t0
LEFT JOIN next ON (next.id=t0.id)
ORDER BY id
    ;
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • It's a way of life!. BTW it seems more generic than yours, which has the 79 constant built in. The windows are only there to narrow your view, not to expand it ;-) – wildplasser Nov 24 '11 at 13:41
  • It is not exactly what the OP wants (he wants ranks, possibly to extract n-grams), but I like the next-pointer. If I find the time I'll find a way to compose ngrams and report them in a histogram. Will give rise to a lot of sorting in the outer loop. – wildplasser Nov 24 '11 at 17:39
  • @wildplasser In this case, was not related to ngrams, but merging rows in database. Is related to these questions: http://stackoverflow.com/questions/8247944/sql-to-merge-rows and http://stackoverflow.com/questions/8249582/is-possible-have-different-conditions-for-each-row-in-a-query, but thanks for your help. – Renato Dinhani Nov 24 '11 at 20:22