postgresql - 使用 Postgresql 计算列中的唯一值和重复值

Question

目标：查找列中唯一和重复的计数worker_ref_id。

我在 MySQL 中找到了解决方案，但在IFPostgreSQL 中不存在。那么，我将如何在 PostgreSQL 中做到这一点？

我有下表：

|worker_ref_id|bonus_amount|
|            1|        5000|
|            2|        3000|
|            3|        4000|
|            1|        4500|
|            2|        3500|

我想要以下输出：

|Unique|Duplicates|
|1     |2         |

我得到了正确的答案，但它显示为两行而不是两列和一行：

SELECT COUNT(*) AS "Duplicate" FROM (SELECT worker_ref_id,
                  COUNT(worker_ref_id) AS "Count"
                  FROM bonus
                  GROUP BY worker_ref_id
                  HAVING COUNT(worker_ref_id) > 1) AS mySub
UNION

SELECT COUNT(*) AS "Unique" FROM (SELECT worker_ref_id,
                      COUNT(worker_ref_id) AS "Count"
                      FROM bonus
                      GROUP BY worker_ref_id
                      HAVING COUNT(worker_ref_id) = 1) AS mySub2

标签： postgresql

Answer 1

我们可以使用 CTE 分两步执行此操作：

WITH cte AS (
    SELECT worker_ref_id, COUNT(*) AS cnt
    FROM bonus
    GROUP BY worker_ref_id
)

SELECT
    COUNT(*) FILTER (WHERE cnt = 1) AS "Unique",
    COUNT(*) FILTER (WHERE cnt > 1) AS Duplicates
FROM cte;