布尔条件分组依据，窗口函数 [英] Boolean Condition Group By, Window Functions

问题描述

我正在尝试根据组中的条件添加新列.我们可以做点什么 BOOL()超过(在ID中为"D"的PARTITION)就像GROUP BY id 一样，检查值'D'val列

I am trying to add a new column based on a condition in a group. Could we do something like BOOL() OVER (PARTITION BY id 'D' in val) That is something like GROUP BY id and check if the value 'D" val column

输入:

-------------
| id  | val |
------------|
|  1  |  A  |
|  1  |  B  |
|  1  |  D  |
|  2  |  B  |
|  2  |  C  |
|  2  |  A  |

输出

-------------------
| id  | val | res |
------------|-----|
|  1  |  A  |  1  |
|  1  |  B  |  1  |
|  1  |  D  |  1  |
|  2  |  B  |  0  |
|  2  |  C  |  0  |
|  2  |  A  |  0  |

推荐答案

在Postgres中，您可以为此使用 bool_or():

In Postgres, you can use bool_or() for this:

select t.*, bool_or(val = 'D') over(partition by id) res
from mytable t

DB Fiddle上的演示 :

id | val | res
-: | :-- | :--
 1 | A   | t  
 1 | B   | t  
 1 | D   | t  
 2 | B   | f  
 2 | C   | f  
 2 | A   | f  

这将为您提供布尔结果.如果您希望将其作为整数值，则:

This gives you a boolean result. If you want it as an integer value instead, then:

(bool_or(val = 'D') over(partition by id))::int

这篇关于布尔条件分组依据，窗口函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！