OVER and PARTITION BY

The OVER Clause and Partitioning

OVER (...) turns an aggregate into a window function that answers on every row instead of collapsing them. OVER () is one window over the whole result; PARTITION BY splits it into independent windows, like GROUP BY without the collapse:

Each product beside its category average and its share of the resultSQL

SELECT category_id AS cat, sku, price,
       ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS cat_avg,
       ROUND(100 * price / SUM(price) OVER (), 1)           AS pct
FROM products WHERE category_id IN (2, 5) ORDER BY cat, price DESC;

Output

+-----+-----------+-------+---------+------+
| cat | sku       | price | cat_avg | pct  |
+-----+-----------+-------+---------+------+
|   2 | BK-LAR-01 | 49.00 |   43.63 | 33.0 |
|   2 | BK-LNX-01 | 42.00 |   43.63 | 28.3 |
|   2 | BK-PHP-01 | 39.90 |   43.63 | 26.9 |
|   5 | AC-MUG-01 | 12.50 |    8.75 |  8.4 |
|   5 | AC-STK-01 |  4.99 |    8.75 |  3.4 |
+-----+-----------+-------+---------+------+
5 rows in set (0.001 sec)

The shares total 100 over these five rows, not the catalog: windows run after WHERE, GROUP BY and HAVING (SELECT Execution Order). So RANK() OVER (...) in WHERE fails with ERROR 3593 (HY000): You cannot use the window function 'rank' in this context.; compute it in a CTE and filter outside. COUNT(DISTINCT ...) OVER and a windowed GROUP_CONCAT fail with error 1235.

MENU