如何在PostgreSQL的FILTER子句中使用正则提取函数？

我需要从dept字段的()中提取数据，以此作为统计列进行数据统计。

基础表结构 #

----------------------------
| id | active | dept       |
----------------------------
| 1  | true   | TEST (AFG) |
| 2  | true   | TEST (AFG) |
| 3  | true   | TEST (AFG) |
| 4  | true   | TEST (POD) |
| 5  | true   | TEST (POD) |
| 6  | true   | TEST (KMN) |
| 7  | true   | TEST (AGO) |
| 8  | true   | TEST (AGO) |
| 9  | false  | TEST (AGO) |
| 10 | true   | TEST (AGO) |
| 11 | true   | TEST (AGO) |
| 12 | true   | TEST (SUD) |
| 13 | true   | TEST (SUD) |
| 14 | true   | TEST (MOL) |
----------------------------

当前尝试的SQL语句 #

我原本用来统计活跃/非活跃请求的SQL如下：

SELECT
    'Active Request' AS Title,
    (regexp_matches(dept, '\((.*?)\)'))[1] as dept,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
GROUP BY dept
UNION
SELECT
    'Inactive Request' AS Title,
    (regexp_matches(dept, '\((.*?)\)'))[1] as dept,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'false'
GROUP BY dept;

遇到的问题 #

我尝试直接在FILTER子句中使用正则提取函数时，执行以下SQL：

SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'POD') AS POD,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
GROUP BY dept;

会抛出错误：

Query Error: error: set-returning functions are not allowed in FILTER

目标统计结果 #

我希望得到如下格式的统计结果：

----------------------------------------------------------------
| Title            | AFG | AGO | KMN | MOL | POD | SUD | TOTAL | 
----------------------------------------------------------------
| Active Request   | 3   | 4   | 1   | 1   | 2   | 2   | 13    |
| Inactive Request | 0   | 1   | 0   | 0   | 0   | 0   | 1     |
----------------------------------------------------------------

目前我可以通过(regexp_matches(dept, '\((.*?)\)'))[1]提取括号中的值，但无法在FILTER子句中直接使用该结果。

解决方案 #

问题出在regexp_matches是返回集合的函数，不能在FILTER子句中使用。可以改用非集合返回的字符串提取函数，或者先通过CTE/子查询提前提取出部门代码，再进行统计。

方法一：使用substring直接在FILTER中提取 #

SELECT
    CASE active
        WHEN 'true' THEN 'Active Request'
        ELSE 'Inactive Request'
    END AS Title,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'POD') AS POD,
    COUNT(*) FILTER (WHERE substring(dept from '\((.*?)\)') = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
GROUP BY active
ORDER BY Title DESC;

方法二：用CTE提前提取部门代码（更清晰） #

WITH req_with_dept_code AS (
    SELECT
        active,
        substring(dept from '\((.*?)\)') AS dept_code
    FROM req
)
SELECT
    CASE active
        WHEN 'true' THEN 'Active Request'
        ELSE 'Inactive Request'
    END AS Title,
    COUNT(*) FILTER (WHERE dept_code = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept_code = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept_code = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept_code = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept_code = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept_code = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req_with_dept_code
GROUP BY active
ORDER BY Title DESC;

这两种方法都能避免集合返回函数的问题，直接得到你想要的统计结果。

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