SQL: ROW_NUMBER(), RANK(), DENSE_RANK()

При запросах к БД через SQL часто бывает нужно ранжировать строки таблицы по какому-либо значению и потом, например применять фильтр. Для этого очень удобно использовать функции с окнами: ROW_NUMBER(), RANK(), DENSE_RANK(). Как использовать смотрим в Jupyter Notebook на тестовой БД SQLite. Открываем ноутбук, пишем:

Импорт библиотек

import pandas as pd
import sqlite3
from contextlib import contextmanager
from IPython.display import display

Создаем тестовый DataFrame

data = pd.DataFrame({ 'student': ['Жек Воробьёв', 'Жек Воробьёв', 'Жек Воробьёв', 'Лёня Каннибов', 'Лёня Каннибов', 'Лёня Каннибов', 'Толя Старков', 'Толя Старков', 'Толя Старков', 'Шаурмен Белый', 'Шаурмен Белый', 'Шаурмен Белый', 'Женя Корнев', 'Женя Корнев', 'Женя Корнев', 'Фёдр Сумкин', 'Фёдр Сумкин', 'Фёдр Сумкин'],

'subject': ['Мат.стат.', 'Теор.вер.', 'Лин.ал.', 'Мат.стат.', 'Теор.вер.', 'Лин.ал.', 'Мат.стат.', 'Теор.вер.', 'Лин.ал.', 'Мат.стат.', 'Теор.вер.', 'Лин.ал.', 'Мат.стат.', 'Теор.вер.', 'Лин.ал.', 'Мат.стат.', 'Теор.вер.', 'Лин.ал.'],

'score': [91, 82, 84, 85, 85, 92, 88, 88, 91, 79, 80, 91, 91, 85, 86, 89, 87, 63]})

Контекстный менеджер для работы с БД

@contextmanager
def sqlite_db(db_path='my_database.db'):
···conn = None
···try:
······conn = sqlite3.connect(db_path)
······yield conn
···finally:
······if conn:
······conn.close()

Сохраняем данные в SQLite

with sqlite_db() as conn:
···data.to_sql('student_scores', conn, index=False, if_exists='replace')

Запросы с оконными функциями

queries = {"ROW_NUMBER": """
SELECT
student, subject, score,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) as row_num
FROM
student_scores""",

"RANK": """
SELECT
student, subject, score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) as rank
FROM
student_scores""",

"DENSE_RANK": """
SELECT
student, subject, score,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) as dense_rank
FROM student_scores"""}

Выполняем и отображаем все запросы

with sqlite_db() as conn:
···for name, query in queries.items():
······print(f"\n=== {name} ===")
······display(pd.read_sql(query, conn))

Вывод функции

ROW_NUMBER()

RANK()

DENSE_RANK()

🐾 Код на GitHub.