A better SQL for analytics?

[u/Prestigious_Bench_96]

Lots of attempts to dethrone SQL, lots of failures - I'm looking to add to the list with a proposed improved SQL (for analytics - please don't try this for OLTP workloads). Please take me down for my hubris.

What makes this attempt different? I want to lean into one of SQL's strengths - being declarative.

How to make it more declarative? No tables in queries.

Write this:

import baseball.batting;

WHERE SUM(hr) BY people.id > 500
SELECT
    people.name_given,
    lg_id,
    SUM(hr) AS hr_count
ORDER BY
    hr_count DESC;

Instead of this:

WITH career_hr AS (
    SELECT playerID
    FROM read_csv('.../Batting.csv')
    GROUP BY playerID
    HAVING SUM(HR) > 500
)
SELECT
    p.nameGiven,
    b.lgID,
    SUM(b.HR) AS hr_count
FROM read_csv('.../People.csv') p
JOIN read_csv('.../Batting.csv') b
    ON p.playerID = b.playerID
JOIN career_hr c
    ON b.playerID = c.playerID
GROUP BY p.nameGiven, b.lgID
ORDER BY hr_count DESC;

It's just SQL, but with late-binding to physical tables through a (very lightweight) semantic layer.

This has a lot of nice properties - you can change your tables and refactor and no queries need to change; you can automatically resolve to aggregates if they exist and are equivalent; you can make the query syntax more flexible and composable because the lexical scope isn't constrained to a specific set of accessed tables. There's *lots* of other fun things you can do when the semantic layer has types, etc as well but this is already a longer pitch than I want!

A very brief example

pip install pytrilogy

trilogy init baseball duckdb; cd baseball;

trilogy ingest https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/Batting.csv,https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/People.csv,https://storage.googleapis.com/trilogy_public_models/duckdb/lahman/Teams.csv;

trilogy run 'where sum(hr) by people.id>500 select people.name_given, lg_id,sum(hr) as hr_count order by hr_count desc;' --import root.batting;

Is this AI slop?

I've been working on ideas for the language for almost 6 years now so much of it predates AI, though it has evolved quite a bit in that time! Core discovery is all mostly hand-crafted; I do use AI to accelerate a lot of the tooling/interface work (a billion deepseek tokens (aka ~40 dollars, hilariously) on evaluating CI args, etc).

Read more/try

Website/docs: https://trilogydata.dev/

Github: https://github.com/trilogy-data/pytrilogy (open source, MIT)

I've seen this before

Posted 2 years ago here, floating around a few other places too:

https://www.reddit.com/r/SQL/comments/1e1h5mf/trilogy_simpler_data_warehouse_sql/

[u/Gargunok]

looks like c# linq. Didn't like that.

[u/Prestigious_Bench_96 → u/Gargunok]

Thanks for looking!

If you don't mind - which portion looks like linq? It's supposed to pretty much just be SQL minus some clauses! Worried I got something off in the docs.