代码之家 › 专栏 › 技术社区 › Richlewis

计算多列中某个值的出现次数-Postgres

postgresql sql

Richlewis · 技术社区 · 6 年前

我有张叫的桌子 fixtures (我已经简化了这个示例),并希望使用针对 mc_* 柱。以这个为例

#mc = Match Corner # mc_per = Match Corner Percentage
| mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
| 1    | 4    | 3    | null | 3           | 100             |  66             |

在我运行查询时,它看起来像

 #mc = Match Corner # mc_per = Match Corner Percentage
| mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
| 1    | 4    | 3    | null | 3           | null            |  null           |

所以从查询开始 one_plus_mc_per 我能做到

SELECT COUNT(*) FROM fixtures WHERE coalesce(mc_0,0) >= 1 AND id = 182; 
# Using coalesce for dealing with null, will return a 0 if value null

这个返回

| count |
| 1     |

如果我对每一列单独运行这个查询,返回的结果将是

| count | count | count | count | 
| 1     | 1     | 1     | 0     |

这样,我就可以向上添加所有列值并除以匹配计数。这是有道理的(我感谢德法伊让我在前面的问题中考虑他的建议)

我的问题是我不能运行这个查询4次,例如,这是非常无效的。我的SQL fu不强,正在寻找一种方法在对数据库的一次调用中实现这一点,使我能够获取该百分比值并更新百分比列。

谢谢

2 回复 | 直到 6 年前

Richlewis 6 年前

试试这个:

SELECT 
    SUM(CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END) count_0,
    SUM(CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END) count_1,
    SUM(CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END) count_3,
    SUM(CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END) count_4,
FROM 
    fixtures 
WHERE  id = 182;

它将返回单个查询中所有列的计数

不过,我不确定有什么用 id = id 在您的查询中,它将始终为真。

如果您想要每行的列数*_mc > 0 条件,请尝试:

SELECT 
    (CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END) as count
FROM 
    fixtures 
WHERE  id = 182;

更新:

精明的 one_plus_mc_per

SELECT 
    CAST((CASE WHEN coalesce(mc_0,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_1,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_3,0) >= 1 THEN 1 ELSE 0 END +
    CASE WHEN coalesce(mc_4,0) >= 1 THEN 1 ELSE 0 END)AS DECIMAL)/match_count as one_plus_mc_per
FROM 
    fixtures 
WHERE  id = 182;

Gordon Linoff 6 年前

Postgres有很好的能力回答这类问题:

SELECT COUNT(*) FILTER (WHERE mc_0 >= 1) as count_0,
       COUNT(*) FILTER (WHERE mc_1 >= 1) as count_1,
       COUNT(*) FILTER (WHERE mc_3 >= 1) as count_3,
       COUNT(*) FILTER (WHERE mc_4 >= 1) as count_4,
       AVG ( (mc_0 >= 1)::int + (mc_1 >= 1)::int + (mc_3 >= 1)::int + (mc_4 >= 1)::int
           ) as one_plus_mc_per
FROM fixtures 
WHERE id = 182;

这个 FILTER 是ANSI标准语法。布尔转换为数字是一种非常方便的构造。