postgresql - PostgreSQL计算3个连接的百分比

我正在计算一个百分比值来表示进度。我将在同一列（但值不同）上加入来自3个独立计数的数据。我是如此的接近，但它一直给我零。

SELECT a.count1 / (a.count1 + b.count2 + c.count3) FROM
(SELECT count(*) AS "count1"
FROM "stitch_jira"."issues"
WHERE "stitch_jira"."issues"."fields__status__statusCategory__name" = 'To Do') a
CROSS JOIN
(SELECT count(*) AS "count2"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'In Progress')) b
CROSS JOIN
(SELECT count(*) AS "count3"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'Done')) c

有什么建议可以告诉我为什么我在这里得到零分吗？它应该会产生“0.4…”

最佳答案

您可以使用conditinal聚合重写整个查询，并转换为十进制/乘以1.0：

SELECT
 1.0*(COUNT(*) FILTER(WHERE fields__status__statusCategory__name='To Do'))/
 (COUNT(*) FILTER(WHERE fields__status__statusCategory__name
                   IN('To Do','In Progress','Done')))
FROM "stitch_jira"."issues"

关于postgresql - PostgreSQL计算3个连接的百分比，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54962730/