[IMPALA-4025] add functions PERCENTILE_DISC(), PERCENTILE_CONT(), and MEDIAN()

• Type: New Feature
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: Impala 2.2.4
• Component/s: Backend, Frontend
• Labels: built-in-function sql-language
• Assignee: Yongzhi Chen
• Reporter: Greg Rahn
• Created: 25/Aug/16 22:59
• Updated: 13/Feb/20 00:17

Description

Add the following functions as both an aggregate function and window/analytic function:

• PERCENTILE_CONT
• PERCENTILE_DISC
• MEDIAN (impmented as PERCENTILE_CONT(0.5))

Syntax

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>) ]

Notes from other systems

Greenplum

PERCENTILE_CONT(_percentage_) WITHIN GROUP (ORDER BY _expression_)

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

PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]}}

http://docs.oracle.com/database/121/SQLRF/functions141.htm#SQLRF00687

Note: implemented as both an aggregate and window function

Vertica

PERCENTILE_CONT ( %_number ) WITHIN GROUP (... ORDER BY expression [ ASC | DESC ] ) OVER (... [ window-partition-clause ] )

https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/PERCENTILE_CONTAnalytic.htm

Note: window fuction only

Teradata

PERCENTILE_CONT(<value_expression>) WITHIN GROUP (ORDER BY <value_expression> [asc | desc] [nulls {first | last}])

Note: aggregation fuction only

Netezza

SELECT fn(<expr>) WITHIN GROUP (ORDER BY <value_expression> [asc|desc] [nulls {first | last}]) FROM <from_expr>[GROUP BY <group_expr>];

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

PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY expr) OVER (  [ PARTITION BY expr_list ]  )

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

Issue Links

• is duplicated by: IMPALA-3602 Implement percentile and percentile_approx statistical functions
• is duplicated by: IMPALA-5099 support percentile analytical functions
• relates to: IMPALA-1766 Misc. statistical functions

Sub-Tasks

• Impala Doc: Doc functions PERCENTILE_DISC(), PERCENTILE_CONT(), and MEDIAN(): Open