Last active
January 26, 2026 09:24
-
-
Save buxx/1c7c510d4b7e8c8d39484e5a9ac3c2de to your computer and use it in GitHub Desktop.
SQL window
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 sqlite3 | |
| import time | |
| if __name__ == "__main__": | |
| connection = sqlite3.connect(":memory:") | |
| # connection = sqlite3.connect("toto.db") | |
| cursor = connection.cursor() | |
| cursor.execute("create table my_table (value NUMBER, x NUMBER, level NUMBER)") | |
| size = 10_000_000 | |
| xmin = 4_999_999 | |
| xmax = 5_000_001 | |
| expected = [(4999997,), (4999999,), (5000001,), (5000003,)] | |
| # size = 10_000 | |
| # xmin = 4_999 | |
| # xmax = 5_001 | |
| # expected = [(4997,), (4999,), (5001,), (5003,)] | |
| # size = 1_000 | |
| # xmin = 499 | |
| # xmax = 501 | |
| # expected = [(497,), (499,), (501,), (503,)] | |
| data = [(0, 0, 0)] * size | |
| for i in range(size): | |
| data[i] = (i, i, 1 if (i % 2) == 0 else 2) | |
| cursor.executemany("insert into my_table values (?,?,?)", data) | |
| connection.commit() | |
| queries = [ | |
| ( | |
| "", | |
| f""" | |
| SELECT value | |
| FROM my_table | |
| WHERE x >= {xmin} AND x <= {xmax} AND level == 2 | |
| ORDER BY ROWID | |
| """, | |
| ), | |
| # !!!! SELECT * ===> SELECT value | |
| # !!!! lag(x) ===> lag(value) | |
| ( | |
| "siggemannen", | |
| f""" | |
| SELECT value | |
| FROM ( | |
| SELECT *, lag(value) OVER(ORDER BY rowId) AS prevX, lead(value) OVER(ORDER BY rowId) AS nextX | |
| FROM my_table | |
| WHERE level = 2 | |
| ) x | |
| WHERE x between {xmin} and {xmax} or prevx between {xmin} and {xmax} or nextx between {xmin} and {xmax}; | |
| """, | |
| ), | |
| ( | |
| "Lajos Arpad", | |
| f""" | |
| SELECT value | |
| FROM my_table | |
| WHERE x >= {xmin} AND x <= {xmax} AND level == 2 | |
| union select * from | |
| (SELECT t.value | |
| FROM my_table t | |
| LEFT JOIN my_table t2 | |
| ON t2.x = t.x AND t2.level = t.level AND t2.ROWID > t.ROWID | |
| WHERE t.x < {xmin} AND t.level == 2 ORDER BY t.ROWID DESC LIMIT 1) | |
| union select * from | |
| (SELECT t.value | |
| FROM my_table t | |
| LEFT JOIN my_table t2 | |
| ON t2.x = t.x AND t2.level = t.level AND t2.ROWID < t.ROWID | |
| WHERE t.x > {xmax} AND t.level == 2 LIMIT 1) | |
| """, | |
| ), | |
| # ( | |
| # "Guillaume Outters", | |
| # f""" | |
| # WITH | |
| # filtered AS | |
| # ( | |
| # SELECT | |
| # *, | |
| # ROW_NUMBER() OVER (ORDER BY ROWID) AS pos, -- ← **ONLY 1 USE** of a window function, ensuring consistency | |
| # x >= {xmin} AND x <= {xmax} AS spotted -- ← **SPOT** (btw this could be rewritten x BETWEEN xmin AND xmax) | |
| # FROM my_table | |
| # WHERE level = 2 -- ← **FILTER** | |
| # ) | |
| # SELECT value | |
| # FROM filtered fringecandidate | |
| # WHERE EXISTS | |
| # ( | |
| # SELECT 1 FROM filtered spotted WHERE spotted.spotted -- Does a spot exist… | |
| # AND fringecandidate.pos BETWEEN spotted.pos - 1 AND spotted.pos + 1 -- … at or near the position of the evaluated row? | |
| # ); | |
| # """, | |
| # ), | |
| ( | |
| "White Owl 1", | |
| f""" | |
| with | |
| exact_match as ( SELECT value FROM my_table WHERE x >= {xmin} AND x <= {xmax} AND level == 2), | |
| prev_row as (SELECT max(value) as val FROM my_table | |
| WHERE level == 2 AND | |
| value < (select min(value) from exact_match) | |
| ), | |
| next_row as (SELECT min(value) as val FROM my_table | |
| WHERE level == 2 AND | |
| value > (select max(value) from exact_match) | |
| ) | |
| select val as value from prev_row | |
| union | |
| select value from exact_match | |
| union | |
| select val from next_row | |
| """, | |
| ), | |
| ( | |
| "White Owl 2", | |
| f""" | |
| with | |
| exact_match as ( SELECT x, value FROM my_table WHERE x >= {xmin} AND x <= {xmax} AND level == 2), | |
| prev_row as (SELECT value as val FROM my_table | |
| WHERE level == 2 AND | |
| x < (select min(x) from exact_match) | |
| ORDER BY x DESC | |
| LIMIT 1 | |
| ), | |
| next_row as (SELECT value as val FROM my_table | |
| WHERE level == 2 AND | |
| x > (select max(x) from exact_match) | |
| ORDER BY x | |
| LIMIT 1 | |
| ) | |
| select val as value from prev_row | |
| union | |
| select value from exact_match | |
| union | |
| select val from next_row | |
| """, | |
| ), | |
| ] | |
| reference_duration = None | |
| for name, query in queries: | |
| start = time.time() | |
| result = cursor.execute(query).fetchall() | |
| duration = time.time() - start | |
| if reference_duration is None: | |
| reference_duration = duration | |
| print(f"reference duration: {reference_duration}") # noqa: T201 | |
| else: | |
| difference = duration - reference_duration | |
| if result == expected: | |
| msg = "OK ✅" | |
| else: | |
| msg = "KO ❌" | |
| # print(f"{name}: {msg} +{difference} ({result})") # noqa: T201 | |
| print(f"{name}: {msg} +{difference}") # noqa: T201 | |
| connection.close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment