有什么等同于Postgresql在其他RDBMS上的每个聚合函数？ [英] Is there any equivalent to Postgresql EVERY aggregate function on other RDBMS?

问题描述

每个聚合的文档：

every（expression）：如果所有输入值都为true，则为true，否则为false

every(expression) : true if all input values are true, otherwise false

http：//www.postgresql .org / docs / 9.1 / static / functions-aggregate.html

EVERY在语义上等同于COUNT（conditionIsTrue）= COUNT（*）

EVERY is semantically equivalent to COUNT(conditionIsTrue) = COUNT(*)

select person_id, 

    every(visited_site = 'http://stackoverflow.com') as visited_same_site_forever,

    count(case when visited_site = 'http://stackoverflow.com' then '^_^' end) 
    = count(*) as visited_same_site_forever2

from z
group by person_id
order by person_id

b $ b

输出：

Output:

 person_id | visited_same_site_forever | visited_same_site_forever2 
-----------+---------------------------+----------------------------
        88 | f                         | f
     55327 | t                         | t
    256196 | f                         | f

资料来源：

create table z(person_id int, visited_site varchar(100), datetime_visited timestamp);

insert into z values
(55327,'http://stackoverflow.com','Jan 1, 2010'),
(55327,'http://stackoverflow.com','Feb 14, 2012'),
(55327,'http://stackoverflow.com','May 1, 2012'),
(256196,'http://stackoverflow.com','February 1, 2012'),
(256196,'http://stackoverflow.com','February 2, 2012'),
(256196,'http://slashdot.org','May 2, 2012'),
(88,'http://theregister.co.uk','April 1, 2012'),
(88,'http://slashdot.org','April 2, 2012');

推荐答案

模拟 EVERY / code>与 CASE 和 SUM（）

其实，本文介绍了 EVERY（）如何通过 CASE 和 SUM（） 。以下两个语句是等效的：

Emulating EVERY() with CASE and SUM()

In fact, this article describes how EVERY() can be emulated via CASE and SUM(). The following two statements are equivalent:

SELECT EVERY(id < 10)
FROM book

SELECT CASE SUM(CASE WHEN id < 10 THEN 0 ELSE 1 END) 
         WHEN 0 THEN 1 
         ELSE 0 
       END
FROM book;

EVERY（） window function：

The same is true for the EVERY() window function:

SELECT 
  book.*, 
  EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book

SELECT
  book.*,
  CASE SUM(CASE WHEN title LIKE '%a' THEN 0 ELSE 1 END)
       OVER(PARTITION BY author_id)
    WHEN 0 THEN 1 
    ELSE 0
  END
FROM book;

SQL标准

$ c> SQL：2008 标准提到 EVERY 聚合函数：

SQL Standard

The SQL:2008 standard mentions the EVERY aggregate function:

10.9 <aggregate function>

[...]

<aggregate function> ::=
  COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
  | <general set function> [ <filter clause> ]
  | <binary set function> [ <filter clause> ]
  | <ordered set function> [ <filter clause> ]

<general set function> ::=
  <set function type> <left paren> [ <set quantifier> ]
  <value expression> <right paren>

<set function type> ::=
  <computational operation>

<computational operation> ::=
  AVG
  | MAX
  | MIN
  | SUM
  | EVERY
  | [...]

但是高级SQL标准功能并不经常被数据库实现。 Oracle 11g 例如，不支持它， SQL Server 2012 也没有。

But "advanced" SQL standard features are not often implemented by databases. Oracle 11g for instance, doesn't support it, neither does SQL Server 2012.

使用 HSQLDB < a>，但是，你可能更幸运。 HSQLDB 2.x非常符合标准，还 MySQL知道 BIT_AND（） 聚合函数，它是 EVERY（）的非标准别名

With HSQLDB, however, you may be more lucky. HSQLDB 2.x is very standards-compliant, also MySQL knows the BIT_AND() aggregate function, which is a non-standard alias to EVERY(), also supported by Postgres.

注意，一些数据库允许编写用户定义的聚合函数，所以你也可以实现 EVERY ）自己。

Note, some databases allow for writing user-defined aggregate functions, so you may as well implement EVERY() yourself.

这篇关于有什么等同于Postgresql在其他RDBMS上的每个聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！