布尔列上的 SQLAlchemy func.count [英] SQLAlchemy func.count on boolean column

问题描述

如何轻松计算特定列为 true 的行数和 false 的行数?

How can I count easily the number of rows where a particular column is true and the number where it is false ?

我不能(或者我可以吗?)使用 count() 运行查询，因为我将此计数嵌入到了 have() 子句中，例如:

I can't (or can I ?) run the query with count() because I'm embedding this count in a having() clause, like :

.having(func.count(Question.accepted) >
        func.count(not_(Question.accepted)))

但通过上述方式，函数计算不等式两边的每一行.

but with the above way, the function counts every line on both sides of the inequality.

我试过这样的事情

.having(func.count(func.if_(Question.accepted, 1, 0)) >
        func.count(func.if_(Question.accepted, 0, 1)))

但我收到一个错误

函数 if(boolean, integer, integer) 不存在

function if(boolean, integer, integer) does not exist

(似乎它在 postgresql 中不存在).

(seems it doesn't exist in postgresql).

如何轻松计算列为真和为假的行数?

How can I count easily the number of rows where column is true and false ?

推荐答案

在 HAVING 子句 非常合法，因为 HAVING 消除了组行.可以通过使用 NULL 不计数的属性来实现条件计数:

Using aggregate functions in a HAVING clause is very much legal, since HAVING eliminates group rows. Conditional counting can be achieved either by using the property that NULLs don't count:

count(expression) ... 表达式的值不为空的输入行数

count(expression) ... number of input rows for which the value of expression is not null

或者如果使用 PostgreSQL 9.4 或更高版本，带有 聚合FILTER 子句:

or if using PostgreSQL 9.4 or later, with the aggregate FILTER clause:

count(*) FILTER (WHERE something > 0)

您也可以使用一(和零)之和.

使用过滤聚合函数:

.having(func.count(1).filter(Question.accepted) >
        func.count(1).filter(not_(Question.accepted)))

较旧的 PostgreSQL 和/或 SQLAlchemy

if"的 SQL 模拟是 CASE 表达式 或者在这种情况下 nullif() 函数.它们都可以与 NULL 不计算在内的事实一起使用:

Older PostgreSQL and/or SQLAlchemy

The SQL analog for "if" is either CASE expression or in this case nullif() function. Both of them can be used together with the fact that NULLs don't count:

from sqlalchemy import case

...

.having(func.count(case([(Question.accepted, 1)])) >
        func.count(case([(not_(Question.accepted), 1)])))

或:

.having(func.count(func.nullif(Question.accepted, False)) >
        func.count(func.nullif(Question.accepted, True)))

使用 nullif() 可能有点令人困惑，因为条件"是您不想想要计算的.您可以设计一个使条件更自然的表达式，但这留给读者.这 2 个是更便携的解决方案，但另一方面 FILTER 子句是标准的，虽然没有广泛使用.

Using nullif() can be a bit confusing as the "condition" is what you don't want to count. You could device an expression that would make the condition more natural, but that's left for the reader. These 2 are more portable solutions, but on the other hand the FILTER clause is standard, though not widely available.

这篇关于布尔列上的 SQLAlchemy func.count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！