Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-4025

add functions PERCENTILE_DISC(), PERCENTILE_CONT(), and MEDIAN()

    XMLWordPrintableJSON

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ychena Yongzhi Chen
                Reporter:
                grahn Greg Rahn
              • Votes:
                3 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated: