I have following table in a Redshift. I am trying to implement a filter clause in windows function that is described over here - Referencing current row in FILTER clause of window function but filter clause does not work in Redshift
Trying to run following query in Redshift:
select date, super_location , super_location, location, condition,
,count(location) FILTER (where condition = 1)
over (partition by super_location,super_location order by date) as count
from table1
Sample Data:
date | super_location | sub_super_location| location| condition
-----------|---------------|-------------------| -------- | ----------
2022-06-28 | AAA | AA | A1 | 1
2022-06-27 | AAA | AA | A1 | 0
2022-06-26 | AAA | AA | A1 | 0
2022-06-28 | AAA | AA | A2 | 1
2022-06-27 | AAA | AA | A2 | 1
I want to count locations where condition = 1 and partition by super location, sub super location and order_date. The count should be visible in every row. So the resulting dataset should look like this -
Resulting Dataset:
Order_date | super_location | sub_super_location | location | condition |count
-----------|----------------|------------------- | -------- | ----------|-----
2022-06-28 | AAA1 | AA1 | A1 | 1 |2 (counting A1 and A2 on 06/28 where condition = 1)
2022-06-27 | AAA1 | AA1 | A1 | 0 |1 (counting A2 on 06/27 where where condition = 1)
2022-06-26 | AAA1 | AA1 | A1 | 0 |0 (no location with condition = 1 on 06/26)
2022-06-28 | AAA1 | AA1 | A2 | 1 |2 (counting A1 and A2 on 06/28 where condition = 1)
2022-06-27 | AAA1 | AA1 | A1 | 1 |1 (counting A1 on 06/27 where condition = 1)
