Kwker

Tutorial: ORDER BY in DuckDB

DuckDB runs SQL on your machine. kwker.duck does three of its most common jobs, ORDER BY, ORDER BY ... LIMIT and GROUP BY, with Kwker, and hands back a DuckDB relation you can keep querying. It follows DuckDB's rules for NULL and NaN.

In this tutorial you sort and summarize a table of taxi trips, and then check every answer against DuckDB's own SQL on two million rows.

It takes about 15 minutes. You need Python with DuckDB, pyarrow and Kwker installed (Installation).

Step 1: a table of trips

Create a small table in an in-memory database. Each trip has a city, a fare and a distance; two fares are missing.

PythonNeeds duckdb, pyarrow: runs on your machine.
import duckdb
import kwker.duck as kd

con = duckdb.connect()
con.sql("""
    CREATE TABLE trips AS
    SELECT city, fare::DOUBLE AS fare, km::DOUBLE AS km FROM (VALUES
        ('rome', 18.5, 4.1), ('oslo', 31.0, 9.8), ('rome', NULL, 2.2), ('lima', 12.0, 3.0),
        ('oslo', 22.5, 6.4), ('lima', 12.0, 2.9), ('rome', 44.0, 15.5), ('oslo', NULL, 1.0)
    ) AS t(city, fare, km)
""")
trips = con.table("trips")
print(trips.count("*").fetchone()[0], "trips")
Output
8 trips

Step 2: ORDER BY

kd.sort takes the relation and the columns to sort by, like ORDER BY city, fare DESC. It returns a DuckDB relation, so you can print it or keep using it in SQL. Missing fares come last, as in DuckDB.

PythonRuns on your machine.
by_city = kd.sort(trips, ["city", "fare"], descending=[False, True], connection=con)
print(by_city.fetchall())
Output
[('lima', 12.0, 3.0), ('lima', 12.0, 2.9), ('oslo', 31.0, 9.8), ('oslo', 22.5, 6.4), ('oslo', None, 1.0), ('rome', 44.0, 15.5), ('rome', 18.5, 4.1), ('rome', None, 2.2)]

Unlike DuckDB's own sort, rows with equal keys keep their input order: the two Lima trips at 12.0 stay in the order they were inserted.

Step 3: ORDER BY ... LIMIT

For the three most expensive trips, kd.top_k finds the first rows of the order without sorting the rest: ORDER BY fare DESC LIMIT 3.

PythonRuns on your machine.
print(kd.top_k(trips, ["fare"], 3, descending=True, connection=con).fetchall())
Output
[('rome', 44.0, 15.5), ('oslo', 31.0, 9.8), ('oslo', 22.5, 6.4)]

Step 4: GROUP BY

kd.group_by gives one row per city, in city order: the number of trips, the revenue and the median distance. DuckDB's rules apply: the sum skips missing fares, and the count of rows does not.

PythonRuns on your machine.
per_city = kd.group_by(trips, ["city"], {"trips": "size", "revenue": ("fare", "sum"), "median_km": ("km", "median")},
                       connection=con)
print(per_city.fetchall())
Output
[('lima', 2, 24.0, 2.95), ('oslo', 3, 53.5, 6.4), ('rome', 3, 62.5, 4.1)]

The result is a relation too, so SQL can continue from it: here, the cities that earned more than 50.

PythonRuns on your machine.
print(con.sql("SELECT city FROM per_city WHERE revenue > 50").fetchall())
Output
[('oslo',), ('rome',)]

Step 5: two million rows, checked against SQL

Make two million trips in 100 cities and run the same GROUP BY both ways. Then compare the sort: DuckDB's sort is not stable, so compare the sorted key columns, not whole rows.

PythonRuns on your machine.
con.sql("""
    CREATE TABLE big AS
    SELECT 'c' || (hash(i) % 100) AS city,
           CASE WHEN i % 97 = 0 THEN NULL ELSE (hash(i * 7) % 10000) / 100.0 END AS fare,
           (hash(i * 13) % 3000) / 100.0 AS km
    FROM range(2000000) t(i)
""")
big = con.table("big")

ours = kd.group_by(big, ["city"], {"trips": "size", "revenue": ("fare", "sum")}, threads=4, connection=con).fetchall()
sql = con.sql("SELECT city, count(*), sum(fare) FROM big GROUP BY city ORDER BY city").fetchall()
print(len(ours), "cities")
print(all(a[0] == b[0] and a[1] == b[1] and abs(a[2] - b[2]) < 1e-6 * abs(b[2]) for a, b in zip(ours, sql)))

keys = kd.sort(big, ["fare"], descending=True, threads=4, connection=con).project("fare").fetchall()
print(keys == con.sql("SELECT fare FROM big ORDER BY fare DESC NULLS LAST").fetchall())
Output
100 cities
True
True

What you built

Next steps