Oli Sennhauser: MariaDB 10.2 Window Function Examples

存储架构 2016-04-19

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions
, Window Functions
, Window function
and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;
+-----+-------------+
| num | category_id |
+-----+-------------+
|   1 | ACTUAL      |
|   2 | ADJUSTMENT  |
|   3 | BUDGET      |
+-----+-------------+

ROW_NUMBER()
per PARTITION

SELECT ROW_NUMBER() OVER (PARTITION BY store_type ORDER BY SUM(sf.store_sales) DESC) AS Nbr
     , s.store_type AS "Store Type", s.store_city AS City, SUM(sf.store_sales) AS Sales
  FROM store AS s
  JOIN sales_fact AS sf ON sf.store_id = s.store_id
 GROUP BY s.store_type, s.store_city
 ORDER BY s.store_type, Rank
;
+-----+---------------------+---------------+------------+
| Nbr | Store Type          | City          | Sales      |
+-----+---------------------+---------------+------------+
|   1 | Deluxe Supermarket  | Salem         | 1091274.68 |
|   2 | Deluxe Supermarket  | Tacoma        |  993823.44 |
|   3 | Deluxe Supermarket  | Hidalgo       |  557076.84 |
|   4 | Deluxe Supermarket  | Merida        |  548297.64 |
|   5 | Deluxe Supermarket  | Vancouver     |  534180.96 |
|   6 | Deluxe Supermarket  | San Andres    |  518044.80 |
|   1 | Gourmet Supermarket | Beverly Hills |  619013.24 |
|   2 | Gourmet Supermarket | Camacho       |  357772.88 |
|   1 | Mid-Size Grocery    | Yakima        |  304590.92 |
|   2 | Mid-Size Grocery    | Mexico City   |  166503.48 |
|   3 | Mid-Size Grocery    | Victoria      |  144827.48 |
|   4 | Mid-Size Grocery    | Hidalgo       |  144272.84 |
+-----+---------------------+---------------+------------+

Function RANK()

Ranking of top 10 salaries

SELECT full_name AS Name, salary AS Salary
     , RANK() OVER(ORDER BY salary DESC) AS Rank
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+------+
| Name            | Salary   | Rank |
+-----------------+----------+------+
| Sheri Nowmer    | 80000.00 |    1 |
| Darren Stanz    | 50000.00 |    2 |
| Donna Arnold    | 45000.00 |    3 |
| Derrick Whelply | 40000.00 |    4 |
| Michael Spence  | 40000.00 |    4 |
| Maya Gutierrez  | 35000.00 |    6 |
| Pedro Castillo  | 35000.00 |    6 |
| Laurie Borges   | 35000.00 |    6 |
| Beverly Baker   | 30000.00 |    9 |
| Roberta Damstra | 25000.00 |   10 |
+-----------------+----------+------+

Function DENSE_RANK()

SELECT full_name AS Name, salary AS Salary
     , DENSE_RANK() OVER(ORDER BY salary DESC) AS Rank
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+------+
| Name            | Salary   | Rank |
+-----------------+----------+------+
| Sheri Nowmer    | 80000.00 |    1 |
| Darren Stanz    | 50000.00 |    2 |
| Donna Arnold    | 45000.00 |    3 |
| Derrick Whelply | 40000.00 |    4 |
| Michael Spence  | 40000.00 |    4 |
| Maya Gutierrez  | 35000.00 |    5 |
| Pedro Castillo  | 35000.00 |    5 |
| Laurie Borges   | 35000.00 |    5 |
| Beverly Baker   | 30000.00 |    6 |
| Roberta Damstra | 25000.00 |    7 |
+-----------------+----------+------+

Aggregation Windows

SELECT full_name AS Name, salary AS Salary
     , SUM(salary) OVER(ORDER BY salary DESC) AS "Sum sal"
  FROM employee
 ORDER BY salary DESC
 LIMIT 10
;
+-----------------+----------+-----------+
| Name            | Salary   | Sum sal   |
+-----------------+----------+-----------+
| Sheri Nowmer    | 80000.00 |  80000.00 |
| Darren Stanz    | 50000.00 | 130000.00 |
| Donna Arnold    | 45000.00 | 175000.00 |
| Derrick Whelply | 40000.00 | 255000.00 |
| Michael Spence  | 40000.00 | 255000.00 |
| Laurie Borges   | 35000.00 | 360000.00 |
| Maya Gutierrez  | 35000.00 | 360000.00 |
| Pedro Castillo  | 35000.00 | 360000.00 |
| Beverly Baker   | 30000.00 | 390000.00 |
| Roberta Damstra | 25000.00 | 415000.00 |
+-----------------+----------+-----------+

Function CUME_DIST()
and PERCENT_RANK()

SELECT s.store_state AS State, s.store_city AS City, SUM(e.salary) AS Salary
     , CUME_DIST() OVER (PARTITION BY State ORDER BY Salary) AS CumeDist
     , PERCENT_RANK() OVER (PARTITION BY State ORDER BY Salary) AS PctRank
  FROM employee AS e
  JOIN store AS s on s.store_id = e.store_id
 WHERE s.store_country = 'USA'
 GROUP BY s.store_name
 ORDER BY s.store_state, Salary DESC
;
+-------+---------------+-----------+--------------+--------------+
| State | City          | Salary    | CumeDist     | PctRank      |
+-------+---------------+-----------+--------------+--------------+
| CA    | Alameda       | 537000.00 | 1.0000000000 | 1.0000000000 |
| CA    | Los Angeles   | 221200.00 | 0.8000000000 | 0.7500000000 |
| CA    | San Diego     | 220200.00 | 0.6000000000 | 0.5000000000 |
| CA    | Beverly Hills | 191800.00 | 0.4000000000 | 0.2500000000 |
| CA    | San Francisco |  30520.00 | 0.2000000000 | 0.0000000000 |
| OR    | Salem         | 260220.00 | 1.0000000000 | 1.0000000000 |
| OR    | Portland      | 221200.00 | 0.5000000000 | 0.0000000000 |
| WA    | Tacoma        | 260220.00 | 1.0000000000 | 1.0000000000 |
| WA    | Spokane       | 223200.00 | 0.8571428571 | 0.8333333333 |
| WA    | Bremerton     | 221200.00 | 0.7142857143 | 0.6666666667 |
| WA    | Seattle       | 220200.00 | 0.5714285714 | 0.5000000000 |
| WA    | Yakima        |  74060.00 | 0.4285714286 | 0.3333333333 |
| WA    | Bellingham    |  23220.00 | 0.2857142857 | 0.1666666667 |
| WA    | Walla Walla   |  21320.00 | 0.1428571429 | 0.0000000000 |
+-------+---------------+-----------+--------------+--------------+

Function NTILE()

SELECT promotion_name, media_type
     , TO_DAYS(end_date)-TO_DAYS(start_date) AS Duration
     , NTILE(4) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quartile
     , NTILE(5) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quintile
     , NTILE(100) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS precentile
  FROM promotion
 WHERE promotion_name = 'Weekend Markdown'
 LIMIT 10
;
+------------------+-------------------------+----------+----------+----------+------------+
| promotion_name   | media_type              | Duration | quartile | quintile | precentile |
+------------------+-------------------------+----------+----------+----------+------------+
| Weekend Markdown | In-Store Coupon         |        2 |        1 |        1 |          9 |
| Weekend Markdown | Daily Paper             |        3 |        3 |        4 |         29 |
| Weekend Markdown | Radio                   |        3 |        4 |        4 |         36 |
| Weekend Markdown | Daily Paper, Radio      |        2 |        2 |        2 |         13 |
| Weekend Markdown | Daily Paper, Radio, TV  |        2 |        2 |        3 |         20 |
| Weekend Markdown | TV                      |        2 |        3 |        3 |         26 |
| Weekend Markdown | Sunday Paper            |        3 |        3 |        4 |         28 |
| Weekend Markdown | Daily Paper, Radio, TV  |        3 |        3 |        4 |         34 |
| Weekend Markdown | Daily Paper             |        2 |        1 |        2 |         10 |
| Weekend Markdown | Street Handout          |        2 |        2 |        2 |         18 |
| Weekend Markdown | Bulk Mail               |        3 |        4 |        5 |         37 |
| Weekend Markdown | Cash Register Handout   |        2 |        2 |        2 |         14 |
| Weekend Markdown | Daily Paper, Radio, TV  |        3 |        3 |        4 |         31 |
| Weekend Markdown | Sunday Paper            |        2 |        3 |        3 |         27 |
| Weekend Markdown | Sunday Paper, Radio, TV |        1 |        1 |        1 |          4 |
+------------------+-------------------------+----------+----------+----------+------------+
Shinguz's blog

责编内容by:Shinguz's blog (源链)。感谢您的支持!

您可能感兴趣的

New Webinar: Disaster Recovery Planning for MySQL ... Everyone should have a disaster recovery plan for MySQL & MariaDB! ...
MariaDB Galera cluster released While I was at Oscon and later at Froscon, one of the most frequent questions I ...
MariaDB 10.3.8 发布,MySQL 分支版本 MariaDB 10.3.8 发布了。MariaDB主要由开源社区在维护,采用 GPL 授权许可。 MariaDB 的目的是完全兼容 MySQL,包括 ...
MariaDB Server 10.3.1 Alpha available Get Started Download MariaDB and start working immediately! You also can get s...
MySQL 5.6 to MariaDB 10.2.13 It’s hard to believe that a relational database in personal use at home wil...