postgresql 在特定值的组聚合中添加标签列作为结果

q3aa0525  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(133)

我有一组数据,我按“product_group_id”,“group_name”和“class”分组。该组的产品名称是不同的。

CREATE TABLE rule (
   product_id integer
 , product_group_id integer
 , group_name text
 , class text
 , name text
);

INSERT INTO rule VALUES
  (1, 4, 'fruit', '0010', 'apple')
, (2, 4, 'fruit', '0010', 'cherry')
, (3, 4, 'fruit', '0010', 'pineapple')
, (4, 4, 'fruit', '0010', 'tomato')
, (5, 4, 'fruit', '0010', 'banana')
, (6, 4, 'fruit', '0010', 'peach')

字符串
在选择期间,我想检查“name”列表,如果“tomato”name存在于集合中,我将向新的“label”列添加“invalid”值或“valid”。上面数据示例的结果将是(tomato name exists):

product_group_id | group_name | class | label   |
-----------------+------------+-------+---------+
               4 |      fruit | 0010  | invalid |


或者,如果集合中不存在“tomato”名称,则它将是有效的:

product_group_id | group_name | class | label |
-----------------+------------+-------+-------+
               4 |      fruit | 0010  | valid |


到目前为止,我可以得到两个组有效和无效,如果番茄出现在集合中。

select r.product_group_id, r.group_name, r.class, 
  case
    when r.name='tomato' then 'invalid'
    else 'valid'
  end label
from rule r
group by r.product_group_id, r.group_name, r.class, label;

product_group_id | group_name | class |   label |
-----------------+------------+-------+---------+
               4 |      fruit | 0010  |   valid |
               4 |      fruit | 0010  | invalid |

mf98qq94

mf98qq941#

您需要使用聚合函数(例如max)来检查label上的 tomatonotgroup by

select r.product_group_id, r.group_name, r.class, 
  case
    when max(case when r.name ='tomato' then r.name end) = 'tomato' then 'invalid'
    else 'valid'
  end label
from rule r
group by r.product_group_id, r.group_name, r.class

字符串
对于延长的采样日期(两组)

INSERT INTO rule VALUES
  (1, 4, 'fruit', '0010', 'apple')
, (2, 4, 'fruit', '0010', 'cherry')
, (3, 4, 'fruit', '0010', 'pineapple')
, (4, 4, 'fruit', '0010', 'tomato')
, (5, 4, 'fruit', '0010', 'banana')
, (6, 4, 'fruit', '0010', 'peach')
, (7, 5, 'foo', '0020', 'bar');


结果如下

product_group_id|group_name|class|label  |
----------------+----------+-----+-------+
               5|foo       |0020 |valid  |
               4|fruit     |0010 |invalid|

5lhxktic

5lhxktic2#

你可以这样做。根据case语句中的where子句,对表中存在的product_group_id进行计数。如果count = 0,则返回“invalid”。

select r.product_group_id, r.group_name, r.class, 
  case 
    when 
    (select count(rule.product_group_id) from rule where rule.name = 'tomato' and rule.product_group_id = r.product_group_id and rule.group_name = r.group_name and rule.class = r.class) = 0 then 'invalid'
    -- r.name='tomato' then 'invalid'
    else 'valid'
  end as label
from rule r
group by r.product_group_id, r.group_name, r.class;

字符串
这将给你给予:
| 产品组ID|组名|类|标签|
| --|--|--|--|
| 4 |水果| 10 |有效|
如果你再加上一条记录
(7, 5, 'pasta', '0010', 'foo');
尝试tomato

select r.product_group_id, r.group_name, r.class, 
  case 
    when 
    (select count(rule.product_group_id) from rule where rule.name = 'peach' 
     and rule.product_group_id = r.product_group_id and rule.group_name = r.group_name and rule.class = r.class) = 0 then 'invalid'
    -- r.name='tomato' then 'invalid'
    else 'valid'
  end as label
from rule r
group by r.product_group_id, r.group_name, r.class;


你会得到:
| 产品组ID|组名|类|标签|
| --|--|--|--|
| 4 |水果| 10 |有效|
| 5 |意大利面| 10 |无效|

相关问题