基于CASE的PostgreSQL子查询追加：按t_factors数组过滤crash表

表结构与数据 #

表名：crash

过滤需求 #

基于t_factors数组实现动态过滤：

• 数组包含1时，需满足alcohol_inv = true
• 数组包含2时，需满足drugs_inv = true
• 同时包含1和2时，需同时满足上述两个条件
• 若t_factors为null，则不应用该过滤规则

现有查询语句 #

WITH myvars(t_state,t_factors,) AS(values(
    'WA',
    '{1,2}', --factors
))             
SELECT 
    dr_sex,
    COUNT(*) as all_crashes,
    COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
    COUNT(t1.id) filter (WHERE severity_id = 3) as si_crashes,
    COUNT(t1.id) filter (WHERE severity_id = 4) as fatal_crashes
FROM 
    crash t1
    ,myvars
WHERE 
    (jurisdiction = t_state OR t_state is null)
    AND (( CASE
        WHEN 1 = ANY (t_factors) THEN '[subqry for alcohol_inv = true]'
        WHEN 2 = ANY (t_factors) THEN '[subqry for drugs_inv = true]'END) factor 
        OR 
        t_factors is null)
    AND severity_id > 1
    AND dr_sex = ANY( '{Male, Female}'::text[] )
GROUP BY dr_sex

无需使用子查询，直接通过逻辑条件组合即可实现需求，以下是优化后的查询语句：

WITH myvars(t_state,t_factors) AS(values(
    'WA',
    '{1,2}' --factors
))             
SELECT 
    dr_sex,
    COUNT(*) as all_crashes,
    COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
    COUNT(t1.id) filter (WHERE severity_id = 3) as si_crashes,
    COUNT(t1.id) filter (WHERE severity_id = 4) as fatal_crashes
FROM 
    crash t1
    ,myvars
WHERE 
    (jurisdiction = t_state OR t_state is null)
    -- 简洁的动态过滤逻辑
    AND (
        t_factors IS NULL
        OR (
            (NOT 1 = ANY(t_factors) OR alcohol_inv = true)
            AND (NOT 2 = ANY(t_factors) OR drugs_inv = true)
        )
    )
    AND severity_id > 1
    AND dr_sex = ANY( '{Male, Female}'::text[] )
GROUP BY dr_sex

逻辑说明 #

• 若t_factors为null，直接跳过该过滤条件
• 若数组包含1，则必须满足alcohol_inv = true；若不包含1，该条件自动成立
• 若数组包含2，则必须满足drugs_inv = true；若不包含2，该条件自动成立
• 天然实现了"同时包含1和2时需同时满足两个条件"的规则

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