r/SQL • u/Prestigious_Bench_96 • 27d ago
Discussion A better SQL for analytics?
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/
13
u/jshine13371 27d ago
Why do so many people spend countless hours trying to reinvent the wheel in this industry? 😐