Rank partition by the same value

Question

I am working with GreenPlum engine, I am using pgAdmin to query the data, I have only read permissions so I can't make a function or a procedure, even I don't know why I can't work with variables.

This is my data and the desired result:

Pais    Campaña     Representante   Actividad   Racha   **Desired value**
96      20150302    758593197           1       1           1
96      20150303    758593197           1       2           2
96      20150304    758593197           1       3           3
96      20150305    758593197           0       1           1
96      20150306    758593197           1       4           1
96      20150307    758593197           0       2           1
96      20150308    758593197           0       3           2
96      20150309    758593197           1       5           1
96      20150310    758593197           0       4           1
96      20150311    758593197           0       5           2
96      20150312    758593197           0       6           3
96      20150313    758593197           0       7           4
96      20150314    758593197           1       6           1

This is one of my attempts:

Select 
    Pais,Campaña,Representante,Actividad,
    rank() over(partition by Pais,Representante,Actividad
                order by Pais,Campaña,Representante) as Racha
From TEMP20151109
Order By Campaña;

The desired value is a count of the consecutiveness of the Actividad value, I need to reset the count when Actividad is 0.

It would help if your attempted query's field names matched your sample data. Right now, it's hard to make sense of it. — sstan, Nov 10 '15 at 01:45
Possible duplicate of [T-sql Reset Row number on Field Change](http://stackoverflow.com/questions/13405264/t-sql-reset-row-number-on-field-change) — Vladimir Baranov, Nov 10 '15 at 05:12
Your desired result suggests you want to reset the count with every *change* in `Actividad`, not `when 'Actividad' is 0`. Correct? — Erwin Brandstetter, Nov 10 '15 at 05:29
@VladimirBaranov: The suggested duplicate is for SQL Server and there is a more efficient solution. — Erwin Brandstetter, Nov 10 '15 at 05:34
@ErwinBrandstetter, Postgres also has `LAG` and `SUM() OVER()`, doesn't it? So, that solution for SQL Server would work in Postgres. As for what is more efficient, it is hard to guess and should be tested on the real data and real hardware. My gut feeling is that they will be pretty similar. My point was that this is a pretty common question. — Vladimir Baranov, Nov 10 '15 at 05:55
@VladimirBaranov: You are right, it's a common question and Postgres has all of it, too. But I am not just *guessing* about performance. The syntax for recursive CTEs is slightly different in Postgres (2nd query in Michael's answer). As for the 1st: the query below should perform substantially better for at least 3 reasons: 1. CTEs are optimization barriers in Postgres, only use them when actually *needed*. 2. Below query needs a single subquery where the linked answer uses two CTEs. 3. `row_number()` is typically faster than other window functions in Postgres. Some other minor details. — Erwin Brandstetter, Nov 10 '15 at 15:10

score 5 · Accepted Answer · edited May 23 '17 at 11:54

The trick is to form groups of consecutive rows with the same Actividad, then you can easily compute the row number (rn):

SELECT Pais, Campana, Representante, Actividad
     , row_number() OVER (PARTITION BY Pais, Representante, Actividad, grp
                          ORDER BY Campana) AS rn
FROM  (
   SELECT Pais, Campana, Representante, Actividad
        , row_number() OVER (PARTITION BY Pais, Representante ORDER BY Campana)
       -  row_number() OVER (PARTITION BY Pais, Representante, Actividad
                             ORDER BY Campana) AS grp
   FROM   tbl
   ) sub
ORDER  BY Campana;

You have to repeat columns (or expressions) in the PARTITION BY clause in the outer query in addition to grp. It's comparatively cheap to repeat the same order of rows in the outer window function and just append another column, thus building on pre-sorted data.

Some of the columns might be dropped from the PARTITION BY clause depending on the missing table definition (which columns are unique?) and WHERE conditions.

SQL Fiddle with extended test case.

Very similar case:

How to add a running count to rows in a 'streak' of consecutive days

Related answer with detailed explanation on dba.SE:

Select longest continuous sequence

BTW, it never makes sense to repeat columns in the ORDER BY clause of a window function that are used in the PARTITION clause (Pais and Representante in your original query). That's just noise without effect.

There are many 'Representante' so the partition by should be like: row_number() OVER (PARTITION BY Representante,Actividad, grp ORDER BY Campana) AS rn Please change that, your awnser help me a lot. — MelgoV, Nov 10 '15 at 20:29
@MelgoV: Right, we need to repeat columns in the outer `PARTITION BY` to make it work for all cases. — Erwin Brandstetter, Nov 10 '15 at 21:29

Rank partition by the same value

1 Answers1