Created
May 12, 2025 09:01
-
-
Save MikyPo/3ba00e48dc52cba5e01780862a226e77 to your computer and use it in GitHub Desktop.
ROW_NUMBER-RANK-DENSE_RANK
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment