Details
-
Task
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
None
Description
Should we support DISTINCT for aggregate functions that are computed over a window?
PostgreSQL doesn't support this:
test=# select a, count(distinct b) over (order by a) from t1 ;
|
ERROR: DISTINCT is not implemented for window functions
|
LINE 1: select a, count(distinct b) over (order by a) from t1 ;
|
^
|
http://www.postgresql.org/docs/9.5/static/sql-expressions.html says "Aggregate window functions, unlike normal aggregate functions, do not allow DISTINCT or ORDER BY to be used within the function argument list"
SQL Server doesn't support it:
https://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions
(verified with SQL Server 2008 and 2014)
but one can find many requests for them to support this.
Sybase documentation says:
No aggregation functions in the rank query are allowed to specify DISTINCT
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/BCGEEBHA.htm
Does this mean they don't support it?
Oracle 12.1 gives this error
ORA-30487: ORDER BY not allowed here
|
Apache Drill DOESNT support it:
https://issues.apache.org/jira/browse/DRILL-3182
Attachments
Issue Links
- blocks
-
MCOL-645 MariaDB WF Add DISTINCT to Window Functions SUM, AVG, COUNT
- Closed
- is part of
-
MDEV-6115 window functions as in the SQL standard
- Closed
-
MDEV-12987 complete window function support for columnstore parity
- Open