如何在postgresql中对未知数量的布尔值执行AND？

I have a table with a foreign key and a boolean value (and a bunch of other columns that aren't relevant here), as such:

我有一个带有外键和布尔值的表(以及一堆其他与此无关的列),如下所示:

CREATE TABLE myTable
(
  someKey integer,
  someBool boolean
);

insert into myTable values (1, 't'),(1, 't'),(2, 'f'),(2, 't');

Each someKey could have 0 or more entries. For any given someKey, I need to know if a) all the entries are true, or b) any of the entries are false (basically an AND).

每个someKey可以有0个或更多条目。对于任何给定的someKey,我需要知道a)所有条目是否为真,或者b)任何条目都是假的(基本上是AND)。

I've come up with the following function:

我想出了以下功能:

CREATE FUNCTION do_and(int4) RETURNS boolean AS
$func$
declare
    rec record;
    retVal boolean = 't'; -- necessary, or true is returned as null (it's weird)
begin
    if not exists (select someKey from myTable where someKey = $1) then
        return null; -- and because we had to initialise retVal, if no rows are     found true would be returned
    end if;

    for rec in select someBool from myTable where someKey = $1 loop
        retVal := rec.someBool AND retVal;
    end loop;

    return retVal;
end;
$func$ LANGUAGE 'plpgsql' VOLATILE;

... which gives the correct results:

......给出了正确的结果:

select do_and(1) => t
select do_and(2) => f
select do_and(3) => null

I'm wondering if there's a nicer way to do this. It doesn't look too bad in this simple scenario, but once you include all the supporting code it gets lengthier than I'd like. I had a look at casting the someBool column to an array and using the ALL construct, but I couldn't get it working... any ideas?

我想知道是否有更好的方法来做到这一点。在这个简单的场景中看起来并不太糟糕,但是一旦你包含了所有支持代码,它就会比我想要的更长。我看看将someBool列转换为数组并使用ALL构造,但我无法使其工作......任何想法?

8 个解决方案

#1

No need to redefine functions PostgreSQL already provides: bool_and() will do the job:

无需重新定义PostgreSQL已经提供的函数:bool_and()将完成这项工作:

select bool_and(someBool)
  from myTable
  where someKey = $1
  group by someKey;

(Sorry, can't test it now)

(抱歉,现在无法测试)

#2

Similar to the previous one, but in one query, this will do the trick, however, it is not clean nor easily-understandable code:

与前一个类似,但在一个查询中,这将起到作用,但是,它不是干净的,也不容易理解的代码:

SELECT someKey, 
  CASE WHEN sum(CASE WHEN someBool THEN 1 ELSE 0 END) = count(*)
                    THEN true 
                    ELSE false END as boolResult
FROM  table
GROUP BY someKey

This will get all the responses at once, if you only want one key just add a WHERE clause

如果您只想要一个键只添加一个WHERE子句,这将立即获得所有响应

#3

I just installed PostgreSQL for the first time this week, so you'll need to clean up the syntax, but the general idea here should work:

我本周第一次安装了PostgreSQL,所以你需要清理语法,但这里的一般想法应该有效:

return_value = NULL

IF EXISTS
(
     SELECT
          *
     FROM
          My_Table
     WHERE
          some_key = $1
)
BEGIN
     IF EXISTS
     (
          SELECT
               *
          FROM
               My_Table
          WHERE
               some_key = $1 AND
               some_bool = 'f'
     )
          SELECT return_value = 'f'
     ELSE
          SELECT return_value = 't'
END

The idea is that you only need to look at one row to see if any exist and if at least one row exists you then only need to look until you find a false value to determine that the final value is false (or you get to the end and it's true). Assuming that you have an index on some_key, performance should be good I would think.

这个想法是你只需要查看一行以查看是否存在任何行,如果存在至少一行,则只需要查找,直到找到错误值以确定最终值为假(或者您到达结束,这是真的)。假设你有some_key的索引,我认为性能应该是好的。

#4

(Very minor side-point: I think your function should be declared STABLE rather than VOLATILE, since it just uses data from the database to determine its result.)

(非常小的侧点:我认为你的函数应该被声明为STABLE而不是VOLATILE,因为它只是使用数据库中的数据来确定它的结果。)

As someone mentioned, you can stop scanning as soon as you encounter a "false" value. If that's a common case, you can use a cursor to actually provoke a "fast finish":

有人提到,你可以在遇到“假”值时立即停止扫描。如果这是一种常见情况,您可以使用光标实际上激发“快速完成”:

CREATE FUNCTION do_and(key int) RETURNS boolean
  STABLE LANGUAGE 'plpgsql' AS $$
DECLARE
  v_selector CURSOR(cv_key int) FOR
    SELECT someBool FROM myTable WHERE someKey = cv_key;
  v_result boolean;
  v_next boolean;
BEGIN
  OPEN v_selector(key);
  LOOP
    FETCH v_selector INTO v_next;
    IF not FOUND THEN
      EXIT;
    END IF;
    IF v_next = false THEN
      v_result := false;
      EXIT;
    END IF;
    v_result := true;
  END LOOP;
  CLOSE v_selector;
  RETURN v_result;
END
$$;

This approach also means that you are only doing a single scan on myTable. Mind you, I suspect you need loads and loads of rows in order for the difference to be appreciable.

这种方法也意味着您只在myTable上进行一次扫描。请注意,我怀疑你需要负载和行的负载,以便差异可观。

#5

You can also use every, which is just an alias to bool_and:

你也可以使用every,这只是bool_and的别名:

select every(someBool)
from myTable
where someKey = $1
group by someKey;

Using every makes your query more readable. An example, show all persons who just eat apple every day:

使用every可以使查询更具可读性。举个例子,展示每天只吃苹果的人:

select personId
from personDailyDiet
group by personId
having every(fruit = 'apple');

every is semantically the same as bool_and, but it's certainly clear that every is more readable than bool_and:

每个语义都与bool_and相同,但是很明显,每个语句都比bool_and更具可读性:

select personId
from personDailyDiet
group by personId
having bool_and(fruit = 'apple');

#6

Maybe count 'all' items with somekey=somevalue and use it in a boolean comparison with the count of all 'True' occurences for somekey?

也许使用somekey = somevalue计算'all'项目并将其用于布尔比较中,并将某些键的所有'True'出现的计数?

Some non-tested pseudo-sql to show what i mean...

一些未经测试的伪sql来表明我的意思......

select foo1.count_key_items = foo2.count_key_true_items
from
   (select count(someBool) as count_all_items from myTable where someKey = '1') as foo1,
   (select count(someBool) as count_key_true_items from myTable where someKey = '1' and someBool) as foo2

#7

CREATE FUNCTION do_and(int4)
  RETURNS boolean AS
$BODY$
  SELECT
    MAX(bar)::bool
  FROM (
    SELECT
      someKey,
      MIN(someBool::int) AS bar
    FROM
      myTable
    WHERE
      someKey=$1
    GROUP BY
      someKey

    UNION

    SELECT
      $1,
      NULL
  ) AS foo;
$BODY$
  LANGUAGE 'sql' STABLE;

In case you don't need the NULL value (when there aren't any rows), simply use the query below:

如果您不需要NULL值(当没有任何行时),只需使用以下查询:

SELECT
  someKey,
  MIN(someBool::int)::bool AS bar
FROM
  myTable
WHERE
  someKey=$1
GROUP BY
  someKey

#8

SELECT  DISTINCT ON (someKey) someKey, someBool
FROM    myTable m
ORDER BY
        someKey, someBool NULLS FIRST

This will select the first ordered boolean value for each someKey.

这将为每个someKey选择第一个有序的布尔值。

If there is a single FALSE or a NULL, it will be returned first, meaning that the AND failed.

如果存在单个FALSE或NULL,则将首先返回它,这意味着AND失败。

If the first boolean is a TRUE, then all other booleans are also TRUE for this key.

如果第一个布尔值为TRUE,则此键的所有其他布尔值也为TRUE。

Unlike the aggregate, this will use the index on (someKey, someBool).

与聚合不同,这将使用索引(someKey,someBool)。

To return an OR, just reverse the ordering:

要返回OR,只需颠倒顺序:

SELECT  DISTINCT ON (someKey) someKey, someBool
FROM    myTable m
ORDER BY
        someKey, someBool DESC NULLS FIRST

#1