Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

PERCENTILE_CONT 开窗函数示例

以下示例使用 WINSALES 表。有关 WINSALES 表的说明,请参阅开窗函数示例

Copy
select sellerid, qty, percentile_cont(0.5) within group (order by qty) over() as median from winsales; sellerid | qty | median ----------+-----+-------- 1 | 10 | 20.0 1 | 10 | 20.0 3 | 10 | 20.0 4 | 10 | 20.0 3 | 15 | 20.0 2 | 20 | 20.0 3 | 20 | 20.0 2 | 20 | 20.0 3 | 30 | 20.0 1 | 30 | 20.0 4 | 40 | 20.0 (11 rows)
Copy
select sellerid, qty, percentile_cont(0.5) within group (order by qty) over(partition by sellerid) as median from winsales; sellerid | qty | median ----------+-----+-------- 2 | 20 | 20.0 2 | 20 | 20.0 4 | 10 | 25.0 4 | 40 | 25.0 1 | 10 | 10.0 1 | 10 | 10.0 1 | 30 | 10.0 3 | 10 | 17.5 3 | 15 | 17.5 3 | 20 | 17.5 3 | 30 | 17.5 (11 rows)

以下示例计算华盛顿州的卖家的门票销售的 PERCENTILE_CONT 和 PERCENTILE_DISC。

Copy
SELECT sellerid, state, sum(qtysold*pricepaid) sales, percentile_cont(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over(), percentile_disc(0.6) within group (order by sum(qtysold*pricepaid::decimal(14,2) ) desc) over() from sales s, users u where s.sellerid = u.userid and state = 'WA' and sellerid < 1000 group by sellerid, state; sellerid | state | sales | percentile_cont | percentile_disc ----------+-------+---------+-----------------+----------------- 127 | WA | 6076.00 | 2044.20 | 1531.00 787 | WA | 6035.00 | 2044.20 | 1531.00 381 | WA | 5881.00 | 2044.20 | 1531.00 777 | WA | 2814.00 | 2044.20 | 1531.00 33 | WA | 1531.00 | 2044.20 | 1531.00 800 | WA | 1476.00 | 2044.20 | 1531.00 1 | WA | 1177.00 | 2044.20 | 1531.00 (7 rows)