モジュールのレッスン (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 回の試行後に解決策が利用可能になります