使用 PARTITION BY 进行分组

Grouping with PARTITION BY

Having global calculations on every row is handy, but often it is not enough. We may want to compute the average per category or per customer, while keeping the original detail on every row.

This is where PARTITION BY inside the OVER(...) clause comes in.

How PARTITION BY works

Conceptually it behaves similarly to GROUP BY, but instead of collapsing rows in the result, it only defines the boundary of the "window" of data on which the function will operate to compute the result for the single row.

SELECT
  product_name,
  category_id,
  price,
  AVG(price) OVER(PARTITION BY category_id) AS cat_avg_price
FROM products;

Each product row keeps its own details, and the cat_avg_price column will show the average limited to the products in its same category.