DuckDB and essential SQL: SELECT, JOIN, GROUP BY and window functions on Parquet.

SQL without a server

SQL (Structured Query Language) describes what data you want, not how to get it: the engine decides the read order, the indexes and the algorithms. It is the language of brokers' order and position databases, of market data warehouses and of a good share of cloud research tools. Knowing how to read and write it is as basic as pandas.

To use it you need an engine. Classic databases (PostgreSQL, MySQL) are a separate server that the program connects to. DuckDB is something else: an analytical database installed as a library (uv add duckdb) that runs inside the Python process, like SQLite, but designed for queries that read and aggregate millions of rows by columns. And it reads files directly: a Parquet or a CSV file is queried by its path, without importing it first.

The course version is DuckDB 1.5.6 (pinned in curso-code/uv.lock).

import duckdb

con = duckdb.connect()   # in-memory database
print(duckdb.__version__)
con.sql("""
    SELECT fecha, cierre, volumen
    FROM 'sint_diario.parquet'
    ORDER BY fecha
    LIMIT 3
""")

1.5.6
┌─────────────────────┬────────┬─────────┐
│        fecha        │ cierre │ volumen │
│      timestamp      │ double │  int64  │
├─────────────────────┼────────┼─────────┤
│ 2021-01-04 00:00:00 │  99.14 │ 2322609 │
│ 2021-01-05 00:00:00 │  99.25 │ 1517954 │
│ 2021-01-06 00:00:00 │  97.38 │ 3245658 │
└─────────────────────┴────────┴─────────┘

duckdb.connect() with no arguments creates an in-memory database, which disappears when the program ends; with a path (duckdb.connect("research.duckdb")) it saves it to a file. con.sql returns a relation: the result of the query, which prints as a table and can be converted to other formats. Under each column appears its SQL type: timestamp (date with time), double (floating-point number) and int64.

SELECT, WHERE and ORDER BY

A query always has the same structure. What changes with experience is knowing that the order in which it is written is not the order in which it runs:

flowchart TB
  A[FROM and JOIN] --> B[WHERE]
  B --> C[GROUP BY and HAVING]
  C --> D[SELECT and windows]
  D --> E[ORDER BY and LIMIT]

First the tables are read and combined, then rows are discarded with WHERE, grouped, the SELECT columns are computed and finally the result is sorted and trimmed. That order explains frequent errors: WHERE cannot use an alias defined in the SELECT or the result of a window function, because they do not exist yet when it is evaluated.

con.sql("""
    SELECT fecha::DATE AS dia, cierre, volumen
    FROM 'sint_diario.parquet'
    WHERE year(fecha) = 2022 AND volumen > 3500000
    ORDER BY volumen DESC
    LIMIT 5
""")

┌────────────┬────────┬─────────┐
│    dia     │ cierre │ volumen │
│    date    │ double │  int64  │
├────────────┼────────┼─────────┤
│ 2022-04-22 │  99.38 │ 5400080 │
│ 2022-08-26 │ 112.63 │ 5010487 │
│ 2022-11-28 │  96.98 │ 4662485 │
│ 2022-05-23 │ 109.61 │ 4342762 │
│ 2022-12-05 │  99.44 │ 3676322 │
└────────────┴────────┴─────────┘

fecha::DATE converts the timestamp into a date (the Parquet file stored it with a time, as seen in lesson 0.5.4) and AS dia names the resulting column. year() is one of DuckDB's date functions. Conditions are combined with AND, OR and NOT, without the mandatory parentheses of pandas.

DuckDB reads from the Parquet file only the columns and blocks the query needs, just like Polars lazy mode: EXPLAIN before a query shows the plan, with the projected columns (Projections) and the filters pushed down to the reader (Filters).

GROUP BY: yearly summaries

GROUP BY groups the rows with the same key value, and the aggregate functions (count, min, max, avg, sum…) reduce each group to one row. Every SELECT column must be in the GROUP BY or inside an aggregation:

con.sql("""
    SELECT year(fecha)               AS ano,
           count(*)                  AS sesiones,
           round(min(cierre), 2)     AS minimo,
           round(max(cierre), 2)     AS maximo,
           arg_max(cierre, fecha)    AS ultimo,
           round(avg(volumen) / 1e6, 2) AS vol_medio_m
    FROM 'sint_diario.parquet'
    GROUP BY ano
    ORDER BY ano
""")

┌───────┬──────────┬────────┬────────┬────────┬─────────────┐
│  ano  │ sesiones │ minimo │ maximo │ ultimo │ vol_medio_m │
│ int64 │  int64   │ double │ double │ double │   double    │
├───────┼──────────┼────────┼────────┼────────┼─────────────┤
│  2021 │      260 │  95.38 │ 115.78 │ 114.21 │        1.89 │
│  2022 │      260 │  94.45 │ 125.98 │  104.5 │         1.9 │
│  2023 │      260 │ 101.72 │  121.8 │ 119.35 │         1.8 │
│  2024 │      262 │ 118.36 │ 137.15 │ 136.44 │        1.87 │
│  2025 │      261 │ 122.38 │ 149.74 │ 142.22 │        1.74 │
└───────┴──────────┴────────┴────────┴────────┴─────────────┘

arg_max(cierre, fecha) returns the cierre of the row with the maximum fecha in the group: the last close of each year. It is a very useful DuckDB function with time series, and it avoids a subquery. 2022 has the highest maximum of the first three years (125.98) and also the lowest minimum of the whole period (94.45): the year of the decline. DuckDB allows the ano alias in the GROUP BY, a convenience not every SQL engine supports.

HAVING filters after grouping, on the results of the aggregations. To keep the years whose minimum fell below 100, the condition goes in HAVING min(cierre) < 100, not in WHERE.

JOIN: trades and prices

JOIN combines rows from two tables that meet a condition, like merge in pandas (lesson 0.5.3). The two tables can come from different files, even in different formats: the trade log is in CSV and the prices are in Parquet.

con.sql("""
    SELECT o.id, o.fecha_entrada, o.lado,
           o.precio_entrada, p.cierre, p.volumen
    FROM 'operaciones_sint.csv' AS o
    JOIN 'sint_diario.parquet' AS p
      ON p.fecha = o.fecha_entrada
    WHERE o.simbolo = 'SINT'
    ORDER BY o.id
    LIMIT 3
""")

┌───────┬───────────────┬─────────┬────────────────┬────────┬─────────┐
│  id   │ fecha_entrada │  lado   │ precio_entrada │ cierre │ volumen │
│ int64 │     date      │ varchar │     double     │ double │  int64  │
├───────┼───────────────┼─────────┼────────────────┼────────┼─────────┤
│     2 │ 2021-09-02    │ largo   │         108.64 │ 108.64 │ 1454984 │
│     3 │ 2021-10-27    │ corto   │         112.07 │ 112.07 │ 1644654 │
│     4 │ 2021-11-10    │ corto   │          104.3 │  104.3 │ 2575784 │
└───────┴───────────────┴─────────┴────────────────┴────────┴─────────┘

AS o and AS p are aliases so the file names are not repeated. The ON condition compares a date from the CSV (DATE) with a timestamp from the Parquet file (TIMESTAMP); DuckDB converts the first to midnight and the comparison works. With timestamps that carry a time, the same join would stop matching without raising an error: always check the types of the keys.

Aggregation works on the result of the join as on any table. The net result of each symbol, with the trade sign written with CASE:

con.sql("""
    SELECT simbolo,
           count(*) AS operaciones,
           round(sum(
               CASE WHEN lado = 'largo' THEN 1 ELSE -1 END
               * (precio_salida - precio_entrada) * cantidad
               - comision), 2) AS total
    FROM 'operaciones_sint.csv'
    GROUP BY simbolo
    ORDER BY total DESC
""")

┌─────────┬─────────────┬──────────┐
│ simbolo │ operaciones │  total   │
│ varchar │    int64    │  double  │
├─────────┼─────────────┼──────────┤
│ SINT    │          30 │  4405.08 │
│ RVAR    │           4 │    17.09 │
│ TECH    │           3 │   -35.02 │
│ ORO     │           3 │ -3237.96 │
└─────────┴─────────────┴──────────┘

The totals are the same as with pandas and Polars in the previous lessons: SINT gains 4,405.08 and ORO loses 3,237.96. Three different tools, the same number: when a computation is rewritten in another tool, comparing the result with the original one is the cheapest test that no error has crept in.

Window functions

A window function computes, for each row, a value from other related rows, without grouping them: the table keeps all its rows. It is written with OVER (...), which defines three things:

• PARTITION BY: the independent groups (for example, a symbol). Without it, the whole table is one partition.
• ORDER BY: the order of the rows within the partition. In time series, always the date.
• The frame (ROWS BETWEEN … AND …): which rows, relative to the current one, enter the computation.

The daily return needs the previous close. LAG(cierre) brings it to the current row:

con.sql("""
    CREATE TABLE sint AS
    SELECT fecha::DATE AS fecha, cierre,
           cierre / LAG(cierre) OVER (ORDER BY fecha) - 1 AS r
    FROM 'sint_diario.parquet'
""")
con.sql("""
    SELECT fecha, cierre, round(r, 6) AS r
    FROM sint ORDER BY fecha LIMIT 3
""")

┌────────────┬────────┬───────────┐
│   fecha    │ cierre │     r     │
│    date    │ double │  double   │
├────────────┼────────┼───────────┤
│ 2021-01-04 │  99.14 │      NULL │
│ 2021-01-05 │  99.25 │   0.00111 │
│ 2021-01-06 │  97.38 │ -0.018841 │
└────────────┴────────┴───────────┘

CREATE TABLE ... AS stores the result in a table of the in-memory database, so the read is not repeated in the following queries. The first row has no previous close and its return is NULL, like the pandas NaN. LAG only looks backwards; its counterpart, LEAD, looks forward and is the most direct way to introduce look-ahead bias into a signal.

Moving averages with an explicit frame

A 50-session moving average is the average of the current row and the 49 previous ones. It is written with an explicit ROWS frame, which counts rows (sessions) and not dates:

con.sql("""
    SELECT row_number() OVER (ORDER BY fecha) AS fila,
           fecha, cierre,
           round(AVG(cierre) OVER w, 2) AS media_50,
           COUNT(cierre) OVER w AS n
    FROM sint
    WINDOW w AS (ORDER BY fecha
                 ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
    QUALIFY fila IN (1, 2, 49, 50, 51)
    ORDER BY fecha
""")

┌───────┬────────────┬────────┬──────────┬───────┐
│ fila  │   fecha    │ cierre │ media_50 │   n   │
│ int64 │    date    │ double │  double  │ int64 │
├───────┼────────────┼────────┼──────────┼───────┤
│     1 │ 2021-01-04 │  99.14 │    99.14 │     1 │
│     2 │ 2021-01-05 │  99.25 │     99.2 │     2 │
│    49 │ 2021-03-11 │ 101.76 │     97.6 │    49 │
│    50 │ 2021-03-12 │ 104.72 │    97.74 │    50 │
│    51 │ 2021-03-15 │ 106.21 │    97.88 │    50 │
└───────┴────────────┴────────┴──────────┴───────┘

WINDOW w AS (...) defines the frame once for several columns, and row_number() numbers the rows so that only a few are shown with QUALIFY (the filter applied after the windows; explained below). The n column reveals the most important difference from pandas rolling(50): SQL computes with whatever rows are in the frame. The first row has a "50-session average" that is its own close, and row 49 averages 49 closes; pandas would return NaN on the first 49. From row 50 (97.74) the two match. To get a null with incomplete windows, wrap it in CASE WHEN COUNT(cierre) OVER w = 50 THEN AVG(cierre) OVER w END, or filter by n as in exercise 3.

The difference between a frame that looks at the past and one that uses the whole table shows up in a single row:

con.sql("""
    SELECT fecha, cierre,
           round(AVG(cierre) OVER (), 2) AS media_toda,
           round(AVG(cierre) OVER (
               ORDER BY fecha
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ), 2) AS media_hasta_hoy
    FROM sint
    QUALIFY fecha = DATE '2021-06-01'
""")

┌────────────┬────────┬────────────┬─────────────────┐
│   fecha    │ cierre │ media_toda │ media_hasta_hoy │
│    date    │ double │   double   │     double      │
├────────────┼────────┼────────────┼─────────────────┤
│ 2021-06-01 │  99.56 │     117.26 │           100.2 │
└────────────┴────────┴────────────┴─────────────────┘

On June 1, 2021, the average of the whole series (117.26) includes four and a half years of future prices and is well above the actual average up to that day (100.2). A rule such as "buy if the close is below its average" would look excellent with the first column on an asset that rises, and could not be traded. QUALIFY filters after computing the windows: with WHERE, the filter would be applied first and the average would be computed over a single row.

Running maximum and drawdown

The drawdown needs the maximum of all closes up to today: a frame that starts at UNBOUNDED PRECEDING. And the rolling volatility is the standard deviation of returns over a 21-row frame:

riesgo = con.sql("""
    SELECT fecha, cierre,
           cierre / MAX(cierre) OVER (
               ORDER BY fecha
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) - 1 AS dd,
           STDDEV_SAMP(r) OVER (
               ORDER BY fecha
               ROWS BETWEEN 20 PRECEDING AND CURRENT ROW
           ) * sqrt(252) AS vol_21
    FROM sint
""")
con.sql("""
    SELECT round(min(dd), 4) AS peor_dd,
           arg_min(fecha, dd) AS fondo,
           round(max(vol_21), 4) AS vol_max
    FROM riesgo
""")

┌─────────┬────────────┬─────────┐
│ peor_dd │   fondo    │ vol_max │
│ double  │    date    │ double  │
├─────────┼────────────┼─────────┤
│ -0.2503 │ 2022-10-20 │  0.3395 │
└─────────┴────────────┴─────────┘

The same −25.0 % with its trough on October 20, 2022 and the same maximum volatility of 33.9 % as in pandas and Polars. A relation can be queried by the name of its Python variable (FROM riesgo): DuckDB chains the two queries without materializing the first one. As with the moving average, the first rows have a volatility computed with fewer than 21 returns (STDDEV_SAMP ignores the initial NULL), where pandas gave NaN; the maximum, far from the start, is the same.

From SQL to pandas and Polars, and back

A DuckDB query usually ends in a DataFrame. The relation's methods do it directly:

import pandas as pd

df_pd = riesgo.df()
df_pl = riesgo.pl()
print(type(df_pd).__name__, df_pd.shape, df_pd["fecha"].dtype)
print(type(df_pl).__name__, df_pl.shape, df_pl.schema["fecha"])

precios = pd.read_csv("sint_diario.csv", parse_dates=["fecha"])
con.sql("SELECT count(*) AS sesiones FROM precios WHERE cierre > 120")

DataFrame (1303, 4) datetime64[us]
DataFrame (1303, 4) Date
┌──────────┐
│ sesiones │
│  int64   │
├──────────┤
│      523 │
└──────────┘

.df() returns pandas and .pl() returns Polars (there is also .arrow(), which returns Arrow data, and .fetchall(), which returns Python tuples). The last query goes the other way: precios is not a database table but a pandas DataFrame in a Python variable, and DuckDB finds it by its name and queries it in place, without copying it. The 523 sessions above 120 are the ones from lesson 0.5.2.

The three tools combine without friction because they share Apache Arrow as their in-memory format. A common flow: DuckDB filters and aggregates the large files, and pandas receives the already small table for the analysis.

Exercises

1. With a query on sint_diario.parquet, compute the mean daily return and the number of sessions for each day of the week (use dayname(fecha) and LAG). Compare with the table from lesson 0.5.3.

con.sql("""
    SELECT dayname(fecha) AS dia,
           count(r) AS sesiones,
           round(avg(r) * 100, 3) AS media_pct
    FROM sint
    GROUP BY dia, isodow(fecha)
    ORDER BY isodow(fecha)
""")

┌───────────┬──────────┬───────────┐
│    dia    │ sesiones │ media_pct │
│  varchar  │  int64   │  double   │
├───────────┼──────────┼───────────┤
│ Monday    │      260 │     0.045 │
│ Tuesday   │      261 │    -0.052 │
│ Wednesday │      261 │     0.019 │
│ Thursday  │      260 │     0.051 │
│ Friday    │      260 │     0.108 │
└───────────┴──────────┴───────────┘

2. Join each SINT trade with the close of the exit day and check that the exit price is that close in all of them. Use LEFT JOIN and count the trades without a match.

con.sql("""
    SELECT count(*) AS operaciones,
           count(p.cierre) AS con_precio,
           sum(CASE WHEN o.precio_salida = p.cierre
                    THEN 1 ELSE 0 END) AS iguales
    FROM 'operaciones_sint.csv' AS o
    LEFT JOIN sint AS p ON p.fecha = o.fecha_salida
    WHERE o.simbolo = 'SINT'
""")

┌─────────────┬────────────┬─────────┐
│ operaciones │ con_precio │ iguales │
│    int64    │   int64    │ int128  │
├─────────────┼────────────┼─────────┤
│          30 │         30 │      30 │
└─────────────┴────────────┴─────────┘

3. Write a crossover signal without look-ahead bias: 1 if the close is above its 50-session average (with the full window) and 0 otherwise. In what percentage of the sessions with a full window was SINT above it?

con.sql("""
    WITH m AS (
        SELECT fecha, cierre,
               AVG(cierre) OVER w AS media,
               COUNT(cierre) OVER w AS n
        FROM sint
        WINDOW w AS (ORDER BY fecha
                     ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
    )
    SELECT count(*) AS sesiones,
           round(avg(CASE WHEN cierre > media THEN 1 ELSE 0 END), 3)
               AS por_encima
    FROM m
    WHERE n = 50
""")

┌──────────┬────────────┐
│ sesiones │ por_encima │
│  int64   │   double   │
├──────────┼────────────┤
│     1254 │      0.649 │
└──────────┴────────────┘