布尔列上的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（）运行查询，因为我将此计数嵌入到了hading（）子句中，例如：

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 ?

推荐答案

在< href = http://www.postgresql.org/docs/current /static/sql-select.html#SQL-HAVING rel = noreferrer> 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（表达式） ...表达式值不为空的输入行数

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

或如果使用< a href = https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.4#Aggregate_FILTER_clause rel = noreferrer> PostgreSQL 9.4或更高版本，其中汇总了 FILTER 子句：

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

count(*) FILTER (WHERE something > 0)

您还可以使用一（和零）之和。

使用< a href = http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.FunctionElement.filter rel = noreferrer>已过滤的汇总函数：

.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（）可能有点令人困惑，因为条件是您不想想要计算的。您可以使用一个使条件更自然的表达式，但这留给读者。这两个是更可移植的解决方案，但另一方面， 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屋！