Windows功能过滤子句在RedShift中
我有一个红移的下表。我正在尝试在此处描述的Windows函数中实现一个过滤子句 - 在窗口函数的过滤器子句中引用当前行但是过滤器子句在红移中不起作用，
在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
示例数据：
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想要计算条件= 1的位置，并按超级位置，子超级位置和订单_DATE进行分区。在每一行中应可见计数。因此，由此产生的数据集应该看起来像这样 -
结果数据集：
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)
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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论