sql - 重用具有不同 WHERE 子句的子查询

Question

我有一个查询，我看起来像这样

WITH sub_base AS (
SELECT type,
CASE WHEN a < 10 THEN 'GOOD'
     WHEN a BETWEEN 10 AND 100 THEN 'OK'
     ELSE 'BAD'
END AS a_q,
...
CASE WHEN z < 5 THEN 'GOOD'
     WHEN z BETWEEN 5 AND 50 THEN 'OK'
     ELSE 'BAD'
END AS z_q
FROM tbl
),

SELECT sub_one AS (
SELECT 'one' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
WHERE type = 'one'
),

SELECT sub_two AS (
SELECT 'two' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
WHERE type = 'two'
),

SELECT sub_all AS (
SELECT 'all' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
)

SELECT * FROM sub_one
UNION
SELECT * FROM sub_two
UNION
SELECT * FROM sub_all

我想重用 sub_one, sub_two, sub_all 中的子查询，因为除了 SELECT 中的 type 和 WHERE 条件外，它们几乎完全相同。

如何避免复制粘贴这些子查询？

Answer 1

使用GROUP BY:

SELECT type,
       COUNT(CASE WHEN a_q = 'GOOD' THEN 1) as a_good,
       COUNT(CASE WHEN a_q = 'OK' THEN 1) as a_ok,
       COUNT(CASE WHEN a_q = 'BAD' THEN 1) as a_bad,
       ...
       COUNT(CASE WHEN z_q = 'GOOD' THEN 1) as z_good,
       COUNT(CASE WHEN z_q = 'OK' THEN 1) as z_ok,
       COUNT(CASE WHEN z_q = 'BAD' THEN 1) as z_bad,
FROM sub_base
WHERE type IN ('one', 'two', 'three')  -- may not be needed
GROUP BY type;

在 Postgres 中，我会进一步简化它。您可以使用 FILTER 或对二进制值求和:

SELECT type,
       SUM( (a_q = 'GOOD')::int ) as a_good,
       SUM( (a_q = 'OK')::int ) as a_ok,
       SUM( (a_q = 'BAD')::int ) as a_bad,
       ...
       SUM( (z_q = 'GOOD')::int ) as z_good,
       SUM( (z_q = 'OK')::int ) as z_ok,
       SUM( (z_q = 'BAD')::int ) as z_bad,
FROM sub_base
WHERE type IN ('one', 'two', 'three')  -- may not be needed
GROUP BY type;

我也会放弃 CTE，直接将逻辑放在外部查询中，但这更像是一种风格问题。

编辑:

您可以使用另一个 CTE:

WITH . . . ,
     t as (
      SELECT type,
             SUM( (a_q = 'GOOD')::int ) as a_good,
             SUM( (a_q = 'OK')::int ) as a_ok,
             SUM( (a_q = 'BAD')::int ) as a_bad,
             ...
             SUM( (z_q = 'GOOD')::int ) as z_good,
             SUM( (z_q = 'OK')::int ) as z_ok,
             SUM( (z_q = 'BAD')::int ) as z_bad,
      FROM sub_base
      WHERE type IN ('one', 'two', 'three')  -- may not be needed
      GROUP BY type
     )
SELECT t.*
FROM t
UNION ALL
SELECT 'Total', SUM(a_good), SUM(a_ok), SUM(a_bad), . . .
FROM t;