You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Alex Rodoni (JIRA)" <ji...@apache.org> on 2018/09/14 20:06:00 UTC
[jira] [Commented] (IMPALA-4025) add functions PERCENTILE_DISC(),
PERCENTILE_CONT(), and MEDIAN()
[ https://issues.apache.org/jira/browse/IMPALA-4025?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16615318#comment-16615318 ]
Alex Rodoni commented on IMPALA-4025:
-------------------------------------
[~tianyiwang] Is this ready to be documented for 3.1?
> add functions PERCENTILE_DISC(), PERCENTILE_CONT(), and MEDIAN()
> ----------------------------------------------------------------
>
> Key: IMPALA-4025
> URL: https://issues.apache.org/jira/browse/IMPALA-4025
> Project: IMPALA
> Issue Type: New Feature
> Components: Backend, Frontend
> Affects Versions: Impala 2.2.4
> Reporter: Greg Rahn
> Assignee: Tianyi Wang
> Priority: Major
> Labels: built-in-function, sql-language
>
> Add the following functions as both an aggregate function and window/analytic function:
> * PERCENTILE_CONT
> * PERCENTILE_DISC
> * MEDIAN (impmented as PERCENTILE_CONT(0.5))
> h6. Syntax
> {code}
> PERCENTILE_CONT(<percentile>) WITHIN GROUP (ORDER BY <expression> [ASC|DESC] [NULLS {FIRST | LAST}]) [ OVER ([<window-partition-clause>])]
> PERCENTILE_DISC(<percentile>) WITHIN GROUP (ORDER BY <expression> [ASC|DESC] [NULLS {FIRST | LAST}]) [ OVER ([<window-partition-clause>])]
> MEDIAN(expr) [ OVER (<window-partition-clause>) ]
> {code}
> h6. Notes from other systems
> *Greenplum*
> {code}
> PERCENTILE_CONT(_percentage_) WITHIN GROUP (ORDER BY _expression_)
> {code}
> http://gpdb.docs.pivotal.io/4320/admin_guide/query.html
> Greenplum Database provides the MEDIAN aggregate function, which returns the fiftieth percentile of the PERCENTILE_CONT result and special aggregate expressions for inverse distribution functions as follows:
> Currently you can use only these two expressions with the keyword WITHIN GROUP.
> Note: aggregation fuction only
> *Oracle*
> {code}
> PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]}}
> {code}
> http://docs.oracle.com/database/121/SQLRF/functions141.htm#SQLRF00687
> Note: implemented as both an aggregate and window function
> *Vertica*
> {code}
> PERCENTILE_CONT ( %_number ) WITHIN GROUP (... ORDER BY expression [ ASC | DESC ] ) OVER (... [ window-partition-clause ] )
> {code}
> https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/PERCENTILE_CONTAnalytic.htm
> Note: window fuction only
> *Teradata*
> {code}
> PERCENTILE_CONT(<value_expression>) WITHIN GROUP (ORDER BY <value_expression> [asc | desc] [nulls {first | last}])
> {code}
> Note: aggregation fuction only
> *Netezza*
> {code}
> SELECT fn(<expr>) WITHIN GROUP (ORDER BY <value_expression> [asc|desc] [nulls {first | last}]) FROM <from_expr>[GROUP BY <group_expr>];
> {code}
> https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_inverse_distribution_funcs_family_syntax.html
> Note: aggregation fuction only
> *Redshift*
> {code}
> PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY expr) OVER ( [ PARTITION BY expr_list ] )
> {code}
> https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_inverse_distribution_funcs_family_syntax.html
> Note: window fuction only
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org