MySQL ROW_NUMBER() Vysvětleno (MySQL 8.0): Řazení, dotazy Top‑N a deduplikace

1. Úvod

MySQL verze 8.0 přinesla mnoho nových funkcí a jednou z nejvýznamnějších je podpora okenních funkcí. V tomto článku se zaměříme na jednu z nejčastěji používaných funkcí: ROW_NUMBER().

Funkce ROW_NUMBER() poskytuje silné možnosti pro analýzu dat a reportování, usnadňuje řazení a hodnocení dat podle konkrétních podmínek. Tento článek vysvětluje vše od základního použití a praktických příkladů až po alternativní přístupy pro starší verze MySQL.

Cíloví čtenáři

• Začátečníci až středně pokročilí uživatelé se základními znalostmi SQL
• Inženýři a analytici dat, kteří zpracovávají a analyzují data pomocí MySQL
• Každý, kdo zvažuje migraci na nejnovější verzi MySQL

Výhody ROW_NUMBER()

Tato funkce vám umožní přiřadit jedinečné číslo každému řádku na základě konkrétních podmínek. Například můžete snadno napsat dotazy jako „vytvořit žebříček v sestupném pořadí prodeje“ nebo „extrahovat a uspořádat duplicitní data“ stručným způsobem.

Ve starších verzích jste často museli psát složité dotazy pomocí uživatelem definovaných proměnných. S ROW_NUMBER() je váš SQL jednodušší a čitelnější.

V tomto článku použijeme konkrétní příklady dotazů a vysvětlíme je přístupem vhodným pro začátečníky. V další sekci se podíváme podrobněji na základní syntaxi a chování této funkce.

2. Co je funkce ROW_NUMBER()?

Funkce ROW_NUMBER(), nově přidaná v MySQL 8.0, je typ okenní funkce, která přiřazuje sekvenční čísla řádkům. Může číslovat řádky podle konkrétního pořadí a/nebo v rámci každé skupiny, což je mimořádně užitečné pro analýzu dat a reportování. Zde podrobně vysvětlíme základní syntaxi s praktickými příklady.

Základní syntaxe ROW_NUMBER()

Nejprve je základní formát ROW_NUMBER() následující.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Význam jednotlivých prvků

• ROW_NUMBER() : Přiřadí sekvenční číslo každému řádku.
• OVER : Klíčové slovo používané k definování okna pro okenní funkci.
• PARTITION BY : Skupinuje data podle zadaného sloupce. Volitelné. Pokud je vynecháno, číslování se použije na všechny řádky.
• ORDER BY : Definuje řazení použité k přiřazení čísel, tj. kritéria řazení.

Základní příklad

Například předpokládejme, že máte tabulku nazvanou „sales“ s následujícími daty.

Pro přiřazení sekvenčních čísel v rámci každého oddělení v sestupném pořadí prodeje použijte následující dotaz.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Výsledek

Z tohoto výsledku můžete vidět, že žebříčky podle prodeje v rámci každého oddělení jsou zobrazeny.

Jak použít PARTITION BY

V předchozím příkladu jsou data seskupena podle sloupce „department“. To přiřadí samostatnou sekvenci pro každé oddělení.

Pokud PARTITION BY vynecháte, číslování se přiřadí napříč všemi řádky jako jedna sekvence.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Výsledek

Charakteristiky a úskalí ROW_NUMBER()

• Jedinečné číslování : I když jsou hodnoty stejné, přiřazená čísla jsou jedinečná.
• Zpracování NULL : Pokud ORDER BY obsahuje NULL, v vzestupném řazení se objeví jako první a v sestupném jako poslední.
• Dopad na výkon : U velkých datových sad může být ORDER BY nákladné, proto je důležité mít vhodné indexy.

3. Praktické případy použití

Zde jsou praktické scénáře využívající funkci ROW_NUMBER() v MySQL. Tato funkce je užitečná v mnoha reálných situacích, například při žebříčkování dat a zpracování duplicit.

3-1. Žebříčkování v rámci každé skupiny

For example, consider the case where you want to “rank employees by sales within each department” using sales data. Use the following dataset as an example.

Query example: Sales ranking by department

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Result:

In this way, each department gets its own sequence in descending order of sales, making it easy to generate rankings.

3-2. Extracting the Top N rows

Next, let’s look at a case where you want to “extract the top 3 employees by sales within each department.”

Query example: Extract Top N rows

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Result:

This example retrieves only the top 3 rows by sales within each department. As you can see, ROW_NUMBER() is suitable not only for ranking but also for filtering top results.

3-3. Finding and removing duplicate data

Databases sometimes contain duplicate records. In such cases, you can also handle them easily using ROW_NUMBER().

Query example: Detect duplicates

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

This query detects duplicates when multiple records exist for the same employee name.

Query example: Delete duplicates

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Summary

ROW_NUMBER() is useful in a variety of scenarios, such as:

1. Ranking within each group
2. Extracting Top N rows
3. Detecting and deleting duplicates

This makes complex data processing and analysis simpler and more efficient.

4. Comparison with Other Window Functions

In MySQL 8.0, in addition to ROW_NUMBER(), there are window functions like RANK() and DENSE_RANK() that can be used for ranking and position calculations. While they have similar roles, their behavior and results differ. Here we’ll compare each function and explain when to use them.

4-1. RANK() function

The RANK() function assigns ranks, giving the same rank to equal values and skipping the next rank number.

Basic syntax

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Example

Using the following data, calculate sales ranks.

Query example: Using RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Result:

Key points:

• A and B with the same sales amount (800) are both treated as rank “1”.
• The next rank “2” is skipped, so C becomes rank “3”.

4-2. DENSE_RANK() function

The DENSE_RANK() function also assigns the same rank to equal values, but it does not skip the next rank number.

Basic syntax

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Example

Using the same data as above, try the DENSE_RANK() function.

Query example: Using DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Result:

Key points:

• A a B se stejným objemem prodeje (800) jsou oba považováni za hodnost „1“.
• Na rozdíl od RANK() další hodnost začíná na „2“, takže kontinuita hodnocení je zachována.

4-3. Jak se liší ROW_NUMBER()

Funkce ROW_NUMBER() se liší od ostatních dvou tím, že přiřazuje jedinečné číslo i když jsou hodnoty stejné.

Příklad

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Výsledek:

Klíčové body:

• I když jsou hodnoty stejné, každý řádek získá jedinečné číslo, takže neexistují duplicitní hodnosti.
• To je užitečné, když potřebujete přísnou kontrolu řazení nebo jedinečnost na úrovni řádku.

4-4. Stručné shrnutí použití

Shrnutí

ROW_NUMBER(), RANK() a DENSE_RANK() by měly být použity vhodně podle konkrétní situace.

1. ROW_NUMBER() je nejlepší, když potřebujete jedinečná čísla pro každý řádek.
2. RANK() je užitečný, když chcete, aby při shodě hodnoty sdílely hodnost a chcete zdůraznit mezery v hodnostech.
3. DENSE_RANK() je vhodný, když chcete kontinuální hodnosti bez mezer.

5. Alternativy pro verze MySQL pod 8.0

Ve verzích starších než MySQL 8.0 nejsou podporovány funkce ROW_NUMBER() a další okenní funkce. Nicméně podobné chování můžete dosáhnout pomocí uživatelsky definovaných proměnných. Tato sekce vysvětluje praktické alternativy pro verze MySQL pod 8.0.

5-1. Sekvenční číslování pomocí uživatelsky definovaných proměnných

V MySQL 5.7 a starších můžete použít uživatelsky definované proměnné k přiřazení sekvenčních čísel pro každý řádek. Podívejme se na následující příklad.

Příklad: Hodnocení prodeje podle oddělení

Ukázková data:

Dotaz:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Výsledek:

5-2. Extrahování nejvyšších N řádků

Abychom získali nejvyšších N řádků, můžete použít uživatelsky definované proměnné podobným způsobem.

Dotaz:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Výsledek:

Tento dotaz přiřadí hodnosti podle oddělení a poté extrahuje pouze řádky v rámci top 3.

5-3. Detekce a mazání duplicit

Můžete také řešit duplicitní data pomocí uživatelsky definovaných proměnných.

Příklad dotazu: Detekce duplicit

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Příklad dotazu: Mazání duplicit

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Omezení při používání uživatelsky definovaných proměnných

1. Závislost na relaci

• Uživatelsky definované proměnné jsou platné pouze v rámci aktuální relace. Nelze je znovu použít v různých dotazech nebo relacích.

2. Závislost na pořadí zpracování

• Uživatelsky definované proměnné závisí na pořadí vykonání, takže správné nastavení ORDER BY je klíčové.

3. Čitelnost a udržovatelnost SQL

• Dotazy se mohou stát složitými, proto je v MySQL 8.0 a novějších doporučeno používat okenní funkce.

Shrnutí

In MySQL verzích pod 8.0 můžete použít uživatelem definované proměnné k implementaci sekvenčního číslování a řazení místo okenních funkcí. Nicméně, protože dotazy mají tendenci být složitější, je nejlepší zvážit migraci na novější verzi, kdykoli je to možné.

6. Upozornění a osvědčené postupy

MySQL‑ova ROW_NUMBER() funkce a alternativy založené na proměnných jsou velmi pohodlné, ale existují důležité body, na které je třeba myslet, aby byly prováděny přesně a efektivně. Tato sekce vysvětluje praktická upozornění a osvědčené postupy pro optimalizaci výkonu.

6-1. Úvahy o výkonu

1. Náklady na ORDER BY

ROW_NUMBER() se vždy používá s ORDER BY. Protože vyžaduje řazení, může se doba zpracování výrazně zvýšit u velkých datových sad.

Zmírnění:

• Použijte indexy: Přidejte indexy na sloupce použité v ORDER BY, aby se urychlilo řazení.
• Použijte LIMIT: Načtěte pouze tolik řádků, kolik skutečně potřebujete, čímž snížíte množství zpracovávaných dat.

Příklad:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Zvýšené využití paměti a diskové I/O

Okenní funkce jsou zpracovávány pomocí dočasných tabulek a paměti. Jak objem dat roste, může se zvýšit spotřeba paměti a disková I/O.

Zmírnění:

• Rozdělte dotazy: Rozdělte zpracování na menší dotazy a extrahujte data krok po kroku, abyste snížili zátěž.
• Použijte dočasné tabulky: Uložte extrahovaná data do dočasné tabulky a provádějte agregaci odtud, aby se rozložila zátěž.

6-2. Tipy pro ladění dotazů

1. Zkontrolujte plán provedení

V MySQL můžete použít EXPLAIN ke kontrole plánu provedení dotazu. To vám pomůže ověřit, zda jsou indexy používány správně.

Příklad:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Výstup příkladu:

Pokud vidíte Using index, znamená to, že index je používán vhodně.

2. Optimalizujte indexy

Ujistěte se, že přidáváte indexy na sloupce použité v ORDER BY a WHERE. Věnujte zvláštní pozornost následujícímu.

• Jednosloupcové indexy: Vhodné pro jednoduché podmínky řazení
• Kompozitní indexy: Efektivní, když jsou podmínky zahrnující více sloupců

Příklad:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Používejte dávkové zpracování

Místo zpracování obrovské datové sady najednou můžete snížit zátěž zpracováním dat po dávkách.

Příklad:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Udržování konzistence dat

1. Aktualizace a přepočet

Když jsou řádky vloženy nebo smazány, může se číslování změnit. Vytvořte mechanismus pro přepočet čísel podle potřeby.

Příklad:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Použití pohledu vám pomáhá udržovat aktuální pořadí na základě nejnovějších dat.

6-4. Příklad dotazu podle osvědčených postupů

Níže je příklad osvědčených postupů, které zohledňují výkon a udržovatelnost.

Příklad: Extrahovat prvních N řádků

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Tato struktura používá výraz Common Table Expression (CTE) ke zlepšení čitelnosti a znovupoužitelnosti.

Shrnutí

Při používání ROW_NUMBER() nebo jejích alternativ mějte na paměti následující body:

1. Zlepšete rychlost pomocí optimalizace indexů.
2. Identifikujte úzká místa kontrolou plánu provádění.
3. Plánujte aktualizace dat a udržujte konzistenci.
4. Používejte dávkové zpracování a CTE k rozložení zátěže.

Použití těchto osvědčených postupů umožní efektivní zpracování pro analýzu a reportování dat ve velkém měřítku.

7. Závěr

V tomto článku jsme se zaměřili na funkci ROW_NUMBER() v MySQL, vysvětlili vše od základního použití a praktických příkladů po alternativy pro starší verze, včetně omezení a osvědčených postupů. V této sekci shrneme hlavní body a shrneme praktické poznatky.

7-1. Proč je ROW_NUMBER() užitečná

Funkce ROW_NUMBER() je zejména výhodná pro analýzu a reportování dat následujícími způsoby:

1. Postupné číslování v rámci skupin: Snadno vytvoříte žebříčky prodeje podle oddělení nebo žebříčky založené na kategoriích.
2. Extrahování nejlepších N řádků: Efektivně filtrujete a extrahujete data na základě konkrétních podmínek.
3. Detekce a mazání duplicit: Užitečné pro čištění a organizaci dat.

Protože zjednodušuje složité dotazy, výrazně zlepšuje čitelnost a udržovatelnost SQL.

7-2. Porovnání s ostatními okenními funkcemi

Ve srovnání s okenními funkcemi jako RANK() a DENSE_RANK() se ROW_NUMBER() liší tím, že přiřazuje jedinečné číslo i pro identické hodnoty.

Výběr správné funkce:
Výběr nejlepší funkce pro váš účel umožňuje efektivní zpracování dat.

7-3. Práce se staršími verzemi MySQL

Pro prostředí pod MySQL 8.0 jsme také představili přístupy využívající uživatelsky definované proměnné. Nicméně byste měli zvážit následující omezení:

• Snížená čitelnost kvůli složitějšímu SQL
• Optimalizace dotazů může být v některých případech obtížnější
• Může být nutné další zpracování pro udržení konzistence dat

Pokud je to možné, důrazně zvažte migraci na MySQL 8.0 nebo novější a používání okenních funkcí.

7-4. Klíčové body pro optimalizaci výkonu

1. Používejte indexy: Přidejte indexy na sloupce použité v ORDER BY pro zvýšení rychlosti.
2. Kontrolujte plány provádění: Ověřte výkon předem pomocí EXPLAIN.
3. Zavádějte dávkové zpracování: Zpracovávejte velké datové sady v menších částech pro rozložení zátěže.
4. Používejte pohledy a CTE: Zlepšete znovupoužitelnost a zjednodušte složité dotazy.

Použitím těchto technik můžete dosáhnout efektivního a stabilního zpracování dat.

7-5. Závěrečné poznámky

ROW_NUMBER() je výkonný nástroj, který může výrazně zlepšit efektivitu analýzy dat.
V tomto článku jsme pokryli vše od základní syntaxe a praktických příkladů po omezení a alternativy.

Doporučujeme vám spustit dotazy sami a sledovat tento článek. Zlepšení vašich SQL dovedností vám pomůže s jistotou řešit složitější analýzu a reportování dat.

Příloha: Referenční zdroje

• Oficiální dokumentace: MySQL Window Functions
• Online SQL prostředí: SQL Fiddle (nástroj, který vám umožní spouštět a testovat SQL online)