Since the DuckDB people are here, just want to say that what you're doing is going to be a complete game-changer in the next few years, much like SQLite changed the game. Thanks for making it open source!
Friendlier SQL with DuckDB
71–80 of 134 posts
Re: Friendlier SQL with DuckDB
#72I love love love DuckDB. When I can use DuckDB + pyarrow and not import pandas, it makes my day.
Re: Friendlier SQL with DuckDB
#73Earlier quoted context omitted.
Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations. select 1 as x, x + 2 as y, y/x as z;
(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta
for example
select id, count(...something complicated) as complicated_count
from ....
order by complicated_count
Re: Friendlier SQL with DuckDB
#74Earlier quoted context omitted.
There is a bug for that and it looks someone is even working on it. https://github.com/duckdb/duckdb/issues/1547
There's also no need to make it left to right usage, as long as it's acyclic: select y-2 as x, 3 as y, y/x as z;
Would this be compiled into a graph of subqueries and window statements?
Re: Friendlier SQL with DuckDB
#75Lots of great additions. I will just highlight two: Column selection : When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports: - APPLY: apply a function to a set of columns - COLUMN: select columns by matching a regular expression (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…
Matching columns by regular expression sounds like a terrible feature. Talk about bug-prone!
Re: Friendlier SQL with DuckDB
#76If anyone is interested in improvements to SQL, checkout PRQL https://github.com/prql/prql , a pipelined relational query language. It supports: - functions, - using an alias in same `select` that defined it, - trailing commas, - date literals, f-strings and other small improvements we found unpleasant with SQL. https://lang.prql.builders/introduction.html The best part: it compiles into SQL. It's under development,…
This is neat. Have you found this new found capability at odds with "good SQL"? Eg, i run a fairly large application that has a huge DB schema, and more often than not when the SQL gets huge and ugly it often means we're asking too much of the DB. "Too much" being more easy to run into poor indexes, giving more chances for it to pull in unexpectedly large number of rows, etc. My fear with PRQL is that i'd more easily…
That's true - when you hit 4th CTE you are probably doing something wrong.
But not always. Some analytical queries may actually need such complexity. Also, during development, you would sometimes pick only first 50 rows before joining and grouping, with intention of not overloading the db. To do this you need a CTE (or nested select), but in PRQL you just add a `take 50` transform to the top.
Re: Friendlier SQL with DuckDB
#77How does DuckDB compare to SQLite (e.g. which workloads are a good fit for what? Would it be a good idea to use both?) I found https://duckdb.org/why_duckdb but I'm sure someone here can share some real world lessons learned?
I haven't used DuckDB yet but the biggest differences I've discovered if you aren't working on huge datasets where the column/row thing makes a difference (you're probably not) are:
1. SQLite has a great GUI and is really really widely supported.
2. DuckDB is properly statically typed with a much wider range of types than SQLite, which is dynamically typed and only just added support for any kind of type checking at all.
Re: Friendlier SQL with DuckDB
#78Lots of great additions. I will just highlight two: Column selection : When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports: - APPLY: apply a function to a set of columns - COLUMN: select columns by matching a regular expression (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…
EXCEPT columns would get my vote for ansi standard SQL adoption. So much time is spent selecting all but a few columns.
Re: Friendlier SQL with DuckDB
#79Earlier quoted context omitted.
Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations. select 1 as x, x + 2 as y, y/x as z;
(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it. But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta
SQL is complex enough
No, it is not. I mean it is, but not in parts where that could be seen as useful and/or convenient. [A]cyclic graph traversal/etc is one of the basic tests in a modern interview at any CRUD studio. How come it could do $%^& to any part of yours?
Re: Friendlier SQL with DuckDB
#80This looks a little odd SELECT age, sum(civility) as total_civility FROM star_wars_universe ORDER BY ALL -- ORDER BY age, total_civility there's no GROUP BY? edit: (removed edit, I blew it, sorry)
Good catch! Fixed! Needed a group by all in there.