database - 每个组计数，每个组都有自定义条件

Question

我有一个应用程序，用户可以在其中创建组，然后为每个组指定条件。目前，我将条件作为类型/字段/值列存储在一个单独的表中，并从中生成一个 WHERE 子句(在我的应用程序代码中)。我可以改为创建数据库 View 或函数。

用户可以选择的一些条件示例(其中？是用户定义的):

• field1 > ?
• field2 ILIKE '?%'
• 可能需要连接(如果始终完成所有连接也没有坏处)

现在我只想得到几个这样的组(每个组都有不同的条件)的匹配行的 count(distinct id)。单个行可能匹配多个组，在这种情况下，它应该对所有组都有效。

有没有办法在单个查询中执行此操作，也许使用 View (但不是物化 View )或 pl/pgSQL(检查每一行是否匹配条件，考虑到它可以匹配多个组的条件)？ CASE 可能在这里工作吗？每行可能匹配多个组的事实似乎是有问题的。如有必要，我可以修改数据库架构。

在特定情况下，所有组都将匹配来自表的特定子集的行(即 WHERE user_id = X)。所以我想要的是一次遍历所有这些行，并为每一行确定它匹配的组(可能是多个)。

到目前为止，我能想到的最好的方法是为所有组生成查询，然后将它们UNION-ing 在一起，这样我就不需要多次往返数据库。是否有更高性能的解决方案，或者这是我在这种情况下能做的最好的解决方案？基本上我想避免 N+1 查询只是为了获得计数。

Answer 1

您可能需要 filter in aggregate expression ，这是 Postgres 9.4 中引入的一个特性。示例:

drop table example;
create table example (id int, col1 text, col2 text, qty int);
insert into example values
(1, 'a', 'b', 1),
(2, 'a', 'a', 10),
(3, 'a', 'c', 11);

select
    count(distinct id) filter (where col1 ilike 'a%') cond1,
    count(distinct id) filter (where col2 = col1) cond2,
    count(distinct id) filter (where qty = any (array[1, 2, 3])) cond3
from
    example;

 cond1 | cond2 | cond3 
-------+-------+-------
     3 |     1 |     1
(1 row)