模块课程（3/4）
排名：ROW_NUMBER、RANK 和 DENSE_RANK
Rankings: ROW_NUMBER, RANK and DENSE_RANK
Beyond aggregating, window functions shine at ordering and ranking. There are functions designed specifically to work inside windows (OVER() becomes mandatory) that assign sequential numbers or ranking positions.
To make them work against a criterion, we have to use the ORDER BY clause inside OVER().
The ranking trio
ROW_NUMBER(): Assigns a plain sequential number (1, 2, 3...) to every row, breaking ties arbitrarily.RANK(): Produces the "Olympics" effect: 1, 2, 2, 4. It leaves a gap (the "3" is skipped) and assigns the same rank in case of ties.DENSE_RANK(): Densifies ranks: 1, 2, 2, 3. There is never a gap after a tie.
SELECT
product_name,
price,
RANK() OVER(ORDER BY price DESC) AS price_rank
FROM products;By combining PARTITION BY and ORDER BY together inside OVER() we can get "the top 3 for each category"!
Extract 'first_name', 'last_name' and 'id' from the customers table ('customers') (assume 'id' is the identifier number). Add a 'rank' column using DENSE_RANK() based on 'id' descending (from highest to lowest).
显示提示
DENSE_RANK() OVER(ORDER BY id DESC).
3 次尝试后可用的解决方案
Build a ranking for each 'category_id' based on the price of the products ('products'). Extract 'category_id', 'id', 'price' and add 'price_rank' using ROW_NUMBER() in descending 'price' order, partitioning by 'category_id'.
显示提示
Combine PARTITION BY category_id ORDER BY price DESC inside the OVER.
3 次尝试后可用的解决方案