PERCENTILE_CONT
You are viewing an old version of this article. View
the current version here.
MariaDB starting with 10.3.3
The PERCENTILE_CONT() window function was first introduced with in MariaDB 10.3.3.
Contents
Syntax
As an ordered set aggregate function:
<<code>>
<</code>>
Description
PERCENTILE_CONT()
is an ordered set aggregate function which can also be used as a window function.
Examples
CREATE TABLE book_rating (name CHAR(30), star_rating TINYINT); INSERT INTO book_rating VALUES ('Lord of the Ladybirds', 5); INSERT INTO book_rating VALUES ('Lord of the Ladybirds', 3); INSERT INTO book_rating VALUES ('Lady of the Flies', 1); INSERT INTO book_rating VALUES ('Lady of the Flies', 2); INSERT INTO book_rating VALUES ('Lady of the Flies', 5); SELECT name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY star_rating) OVER (PARTITION BY name) AS pc FROM book_rating; +-----------------------+--------------+ | name | pc | +-----------------------+--------------+ | Lord of the Ladybirds | 4.0000000000 | | Lord of the Ladybirds | 4.0000000000 | | Lady of the Flies | 2.0000000000 | | Lady of the Flies | 2.0000000000 | | Lady of the Flies | 2.0000000000 | +-----------------------+--------------+ SELECT name, PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY star_rating) OVER (PARTITION BY name) AS pc FROM book_rating; +-----------------------+--------------+ | name | pc | +-----------------------+--------------+ | Lord of the Ladybirds | 5.0000000000 | | Lord of the Ladybirds | 5.0000000000 | | Lady of the Flies | 5.0000000000 | | Lady of the Flies | 5.0000000000 | | Lady of the Flies | 5.0000000000 | +-----------------------+--------------+ SELECT name, PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY star_rating) OVER (PARTITION BY name) AS pc FROM book_rating; +-----------------------+--------------+ | name | pc | +-----------------------+--------------+ | Lord of the Ladybirds | 3.0000000000 | | Lord of the Ladybirds | 3.0000000000 | | Lady of the Flies | 1.0000000000 | | Lady of the Flies | 1.0000000000 | | Lady of the Flies | 1.0000000000 | +-----------------------+--------------+
See Also
- MEDIAN - a special case of
PERCENTILE_CONT
equivalent toPERCENTILE_CONT(0.5)
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.