收集每个产品的标签组合，并指定每个组合case的总结：demo

select productid,
       case array_agg(distinct inventory_label)
              filter(where inventory_label is not null)
         when '{InStock}'            then 'InStock'
         when '{InStock,OutOfStock}' then 'Partial'
         else 'OutOfStock'
       end as inventory_label
from product_skus_inventories
group by productid;

这可以用作更新源，如演示的最后所示。
将其设置为view或materialized view，将其用作insert into...on conflict do update或merge的源（也在演示中），可能会更有意义。

Alternatives到我的initial answer，在@Mike Organek的建议下尝试其他聚合函数：

• count(distinct inventory_label)组合：demo

select productid,
       case count(distinct inventory_label) --this auto-skips `null`
           when 2 then 'Partial'            --2 means {InStock,OutOfStock}
           when 1 then min(inventory_label) --we want whatever that 1 was
           else        'OutOfStock'                
       end
from product_skus_inventories 
group by productid;

• 对于bit s，使用编码：demo
• 2（二进制10）对于InStock，
• 1（二进制01）（对于OutOfStock），
• 当两者都找到时，从bit_or()得到11（十进制3），或者10（2）只得到InStock

select productid,
       case bit_or((inventory_label='InStock')::int+01)  --F=01, T=10
         when 3 then 'Partial'--3 is binary 11,found 10 as well as 01
         when 2 then 'InStock'--2 is InStock, because bit_or found 10's only
         else        'OutOfStock'
       end as inventory_label
from product_skus_inventories
group by productid;

我更喜欢array_agg(distinct inventory_label)，因为它的可读性。这些都很简单，但那个也是自我评论。尽管如此，count()比filter短，bit_or()是faster。

您可以创建一个表的视图，该视图生成状态，然后在需要值时可以轻松查询。如果您需要运行UPDATE来具体化库存标签，则可以使用JOIN LATERAL中的SELECT来获取要插入的给定product_id的状态。
状态逻辑如下：
1.检查给定product_id的行数是否与筛选出out_of_stock后的行数匹配。
1.因为我们知道至少有1个out_of_stock存在于这个product_id中，因为前面的子句不匹配，所以我们只需要检查是否存在至少1个in_stock或NULL。
1.否则，out_of_stock。

CREATE VIEW product_summary as (
    SELECT DISTINCT product_id, _status.inventory_label 
    FROM product_skus_inventories psi_outer
    JOIN LATERAL (
        SELECT CASE WHEN (COUNT(*) = (COUNT(*) FILTER (WHERE psi_inner.inventory_label IS NULL OR psi_inner.inventory_label='in_stock'))) THEN 'in_stock' 
                    WHEN (EXISTS (SELECT 1 FROM product_skus_inventories _psi WHERE psi_outer.product_id = _psi.product_id AND (_psi.inventory_label IS NULL OR _psi.inventory_label='in_stock'))) THEN 'partial'
                    ELSE 'out_of_stock'
                    END AS inventory_label
        FROM product_skus_inventories psi_inner
        WHERE psi_inner.product_id = psi_outer.product_id
    ) AS _status ON true
);

基本查询

带原表定义

不要对所有行进行计数或聚合。基本上，您正在寻找一个布尔结果。这两个聚合函数恰好符合您的任务描述 * 完全 *：

• bool_and()包含case “1."
• bool_or()覆盖案例 “2."

创建一个快速而简单的查询：

SELECT productid
     , CASE WHEN bool_and(inventory_label = 'InStock') THEN 'InStock'
            WHEN bool_or (inventory_label = 'InStock') THEN 'Partial'
            ELSE                                            'OutOfStock' END AS label
FROM   product_skus_inventories
GROUP  BY 1
ORDER  BY 1;  -- optional

fiddle
值得注意的是，源代码中的'OutOfStock'和null实际上是等效的，并且可以互换。

使用boolean更好

product_skus_inventories.inventory_label可以是“in”，“out”或null。这是一个**boolean**的数量。使用合适的上级数据类型。仅占用1个字节，使所有内容更短，更简单，更快。
要转换表（触发具有排他锁的完整表重写），请执行以下操作：

ALTER TABLE product_skus_inventories
ALTER COLUMN inventory_label TYPE boolean USING inventory_label = 'InStock'; -- that's all!

然后运行一次VACUUM ANALYZE product_skus_inventories。
现在，查询变得更加简单和快速：

SELECT productid
     , CASE WHEN bool_and(inventory_label) THEN 'InStock'
            WHEN bool_or(inventory_label)  THEN 'Partial'
            ELSE                                'OutOfStock' END AS label
FROM   product_skus_inventories
GROUP  BY 1
ORDER  BY 1;  -- optional

fiddle

“更新”汇总表

适当的操作是UPSERT，实际上是Postgres中的. INSERT ... ON CONFLICT ... DO UPDATE ...。
假定此表定义：
一个三个三个一个
fiddle
请注意添加的WHERE子句，它跳过了所有不会改变状态的更新，通常会使查询成本降低很多。

• 如何（或可以）对多列执行SELECT DISTINCT操作？