CUME_DIST [analytic]

计算 窗口内同一分区中当前行的累计分布或相对于其他行的排序。

CUME_DIST() 返回大于 0 且小于等于 1 的数字，其中该数字表示指定行在由 n 行组成的组中的相对位置。对于第 x 行（假定 ASC 排序），CUME_DIST 的 x 为数值小于等于 x 的行数除以整个分区的总行数。例如，在由三行组成的组中，返回的累计分布值应为 1/3、2/3 和 3/3。

行为类型

不可变

语法

CUME_DIST ( ) OVER (
    [ window-partition-clause ]
    window-order-clause  )

参数

OVER()
请参阅分析函数。

示例

以下示例返回第一季度每月内不同交易类型销售额的累计分布。

=> SELECT calendar_month_name AS month, tender_type, SUM(sales_quantity),
       CUME_DIST()
   OVER (PARTITION BY calendar_month_name ORDER BY SUM(sales_quantity)) AS
CUME_DIST
   FROM store.store_sales_fact JOIN date_dimension
   USING(date_key) WHERE calendar_month_name IN ('January','February','March')
   AND tender_type NOT LIKE 'Other'
   GROUP BY calendar_month_name, tender_type;


  month   | tender_type |  SUM   | CUME_DIST
----------+-------------+--------+-----------
 March    | Credit      | 469858 |      0.25
 March    | Cash        | 470449 |       0.5
 March    | Check       | 473033 |      0.75
 March    | Debit       | 475103 |         1
 January  | Cash        | 441730 |      0.25
 January  | Debit       | 443922 |       0.5
 January  | Check       | 446297 |      0.75
 January  | Credit      | 450994 |         1
 February | Check       | 425665 |      0.25
 February | Debit       | 426726 |       0.5
 February | Credit      | 430010 |      0.75
 February | Cash        | 430767 |         1
(12 rows)

另请参阅