Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Where clause inside an over clause in postgres

Is it possible to use the where clause inside an overclause as below ?

SELECT SUM(amount) OVER(partition by prod_name WHERE dateval > dateval_13week)

I cannot use preceding and following inside over clause as my dates are not in the order. All I need to fetch is the records that are less than 13week date value of the current record.

EDIT : 
sum(CASE WHEN dateval >= dateval_13week and dateval <=current_row_dateval then amount else 0 end) over (partition by prod_name order by week_end desc)

Just to elaborate, earlier I was partitioning the records with the below query when I had all my dates in a sequence. Now I have the dates in random order and there are some missing dates.

sum(amount) over 
        (partition by prod_name order by prod_name,week_end desc rows between 0 preceding and 12 following)
like image 561
user2569524 Avatar asked Mar 03 '14 14:03

user2569524


3 Answers

Adding to @D Stanley answer you can use FILTER clause for aggregate function in Postgre:

SELECT SUM(amount) FILTER (WHERE dateval > dateval_13week)
           OVER(partition by prod_name)
like image 197
Алексей Виноградов Avatar answered Oct 23 '22 00:10

Алексей Виноградов


You could simulate the WHERE in your SUM parameter:

SELECT SUM(CASE WHEN dateval > dateval_13week THEN amount ELSE 0 END) 
           OVER(partition by prod_name)
like image 40
D Stanley Avatar answered Oct 22 '22 22:10

D Stanley


You cannot filter the rows with the WHERE clause, inside the OVER partition clause. You can fix the query selecting only the rows that are needed to you, using CASE and performing a sum of the amount where the condition is satisfied.

like image 28
Alberto Solano Avatar answered Oct 23 '22 00:10

Alberto Solano