PostgreSQL：如何筛选指定场地所有时段均取消的活动？

核心逻辑 #

要找出指定location_id下，所有关联时段的preference字段中statusMeta均为CS（取消状态）的活动，核心是按活动分组后，验证该活动的所有时段都满足取消条件。

基础查询语句 #

SELECT 
    e.id AS event_id,
    e.name AS event_name,
    COUNT(sa.id) AS total_slots
FROM pulse.slot_archive sa
-- 关联event表获取活动基本信息
JOIN pulse.event e ON sa.event_id = e.id
-- 过滤目标场地
WHERE sa.location_id = 'YOUR_TARGET_LOCATION_ID'
-- 按活动维度分组
GROUP BY e.id, e.name
-- 验证该活动所有时段的statusMeta都是CS
HAVING bool_and(sa.preference->>'statusMeta' = 'CS')

语句说明 #

1. 字段提取：使用->>操作符从JSON类型的preference字段中提取statusMeta值；如果preference是字符串类型，需先转JSON：sa.preference::json->>'statusMeta'
2. 聚合验证：bool_and函数会检查分组内所有记录的条件是否都为真，确保该活动的所有时段都已取消
3. 关联扩展：如果需要关联event_meta或event_booking_archive，直接添加JOIN即可，例如：

SELECT 
    e.id AS event_id,
    e.name AS event_name,
    em.meta_value,
    COUNT(sa.id) AS total_slots
FROM pulse.slot_archive sa
JOIN pulse.event e ON sa.event_id = e.id
LEFT JOIN pulse.event_meta em ON e.id = em.event_id
WHERE sa.location_id = 'YOUR_TARGET_LOCATION_ID'
GROUP BY e.id, e.name, em.meta_value
HAVING bool_and(sa.preference->>'statusMeta' = 'CS')

替代验证方式 #

如果不习惯用bool_and，也可以用计数对比的方式实现相同逻辑：

HAVING COUNT(*) = SUM(CASE WHEN sa.preference->>'statusMeta' = 'CS' THEN 1 ELSE 0 END)

内容的提问来源于stack exchange，提问作者Vishnu