sql - 将条件计数分段到相同的 'virtual' 列

Question

我正在尝试从这样的结果集中出发:

"foo" | "bar" | "baz"
----------------------
 30.0 |  20.0 | 50.0

(其中“foo”、“bar”和“baz”是各种条件 count() 语句的别名)

这样的结果集:

"segment" | "count"
-------------------
  "foo"   |   30.0
  "bar"   |   20.0
  "baz"   |   50.0

这是我的原始查询:

SELECT round(count(CASE WHEN posts.deactivated_for IS NULL THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Foo",
       round(count(CASE WHEN posts.deactivated_for ILIKE '%Died%' THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Bar",
       round(count(CASE WHEN posts.deactivated_for ILIKE '%(#%' THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Baz"
FROM posts

感谢您的帮助。

Answer 1

您可以使用横向连接:

SELECT v.*
FROM (SELECT round(count(CASE WHEN posts.deactivated_for IS NULL THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Foo",
             round(count(CASE WHEN posts.deactivated_for ILIKE '%Died%' THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Bar",
             round(count(CASE WHEN posts.deactivated_for ILIKE '%(#%' THEN 1 END) ::DECIMAL / count(*), 4) * 100 AS "Baz"
      FROM posts
     ) p, lateral
     (VALUES ('Foo', "Foo"), ('Bar', "Bar"), ('Baz', "Baz")) v(segment, cnt);

或者，如果组是离散的，您可以只使用group by:

SELECT (CASE WHEN p.deactivated_for IS NULL THEN 'Foo'
             WHEN p.deactivated_for ILIKE '%Died%' THEN 'Bar'
             WHEN p.deactivated_for ILIKE '%(#%' THEN 'Baz'
        END) as segment,
        COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()
FROM posts p
GROUP BY segment
HAVING segment IS NOT NULL;