how can I use window function - difficult case in PostgreSQL

Question

I have a table like below:

group    sequence   action          
1        1          promotion_1               
1        2          promotion_1               
1        3          promotion_2               
1        4          act1                      
1        5          act1                     
1        6          act2                      
1        7          promotion_1               
1        8          act1                                  
1        9          act2                     
1       10          promotion_1               
1       11          act1                     
2        1          promotion_2                 
2        2          act1

I would like to create a ranking, which will show range of promotion. So I need to have the same number for promotion and every action after this promotion:

group    sequence   action         parameter   
1        1          promotion_1    1             
1        2          promotion_1    2              
1        3          promotion_2    3              
1        4          act1           3              
1        5          act1           3            
1        6          act2           3            
1        7          promotion_1    4            
1        8          act1           4                        
1        9          act2           4            
1       10          promotion_1    5            
1       11          act1           5           
2        1          promotion_2    1              
2        2          act1           1

I read about window functions and their possibilities (for example over()), but I can create only ranking with group by "action".

Mike · Accepted Answer · 2017-05-03 18:34:04Z

2

select T.*,
       sum(case when action like 'promotion%' then 1 else 0 end)
         over(partition by "group" order by sequence rows unbounded preceding) parameter
  from Table T

edited May 3, 2017 at 18:34

answered May 3, 2017 at 18:06

Mike

2,0051 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alarson008 Over a year ago

I get error: Aggregate window functions with an ORDER BY clause require a frame clause. Could you help me?

David דודו Markovitz Over a year ago

Add rows unbounded preceding or rows between unbounded preceding and current row right after order by sequence

Mike Over a year ago

@alarson008 Corrected. Thank to DuduMarkovitz

David דודו Markovitz · Accepted Answer · 2017-05-03 18:29:07Z

1

filter (Starting with PostgeSQL 9.4)

select  *
       ,count (*) filter (where action like 'promotion%') over 
        (
            partition by  "group" 
            order by      sequence
            rows          unbounded preceding    
        )

from    mytable  T

+-------+----------+-------------+-------+
| group | sequence |   action    | count |
+-------+----------+-------------+-------+
|     1 |        1 | promotion_1 |     1 |
|     1 |        2 | promotion_1 |     2 |
|     1 |        3 | promotion_2 |     3 |
|     1 |        4 | act1        |     3 |
|     1 |        5 | act1        |     3 |
|     1 |        6 | act2        |     3 |
|     1 |        7 | promotion_1 |     4 |
|     1 |        8 | act1        |     4 |
|     1 |        9 | act2        |     4 |
|     1 |       10 | promotion_1 |     5 |
|     1 |       11 | act1        |     5 |
|     2 |        1 | promotion_2 |     1 |
|     2 |        2 | act1        |     1 |
+-------+----------+-------------+-------+

edited May 3, 2017 at 18:29

answered May 3, 2017 at 18:21

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

4 Comments

alarson008 Over a year ago

I've got error "ERROR: syntax error at or near "(" Position: 36". When I copy example code with filter from internet: SELECT count() AS unfiltered, count() FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); - I get the same error message..

David דודו Markovitz Over a year ago

Not relevant to your version. Are you using Redshift by any chance (or you just enjoying vintage versions)?

alarson008 Over a year ago

hmm... I use Redshift (amazon redshift)

David דודו Markovitz Over a year ago

Fixed your tags.

Collectives™ on Stack Overflow

how can I use window function - difficult case in PostgreSQL

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related