You are here

Home » Blogs » Shinguz's blog

MariaDB 10.2 Window Function Examples

Mon, 2016-04-18 22:39 — Shinguz

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 |
+------------------+-------------------------+----------+----------+----------+------------+

Taxonomy upgrade extras: 
mariadb
reporting
Analytics
Window Function
OLAP
Data Mart
data warehouse