Comment by jakozaur

Comment by jakozaur 13 hours ago

11 replies

View on Hacker News

It would be even better if SQL had pipe syntax. SQL is amazing, but its ordering isn’t intuitive, and only CTEs provide a reliable way to preview intermediate results. With pipes, each step could clearly show intermediate outputs.

Example:

FROM orders |> WHERE order_date >= '2024-01-01' |> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id |> WHERE total_spent > 1000 |> INNER JOIN customers USING(customer_id) |> CALL ENRICH.APOLLO(EMAIL > customers.email) |> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country

tstack 10 hours ago

The PRQL[1] syntax is built around pipelines and works pretty well.

I added a similar "get results as you type" feature to the SQLite integration in the Logfile Navigator (lnav)[2]. When entering PRQL queries, the preview will show the results for the current and previous stages of the pipeline. When you move the cursor around, the previews update accordingly. I was waiting years for something like PRQL to implement this since doing it with regular SQL requires more knowledge of the syntax and I didn't want to go down that path.

[1] - https://prql-lang.org [2] - https://lnav.org/2024/03/29/prql-support.html

Reply View 2 replies
da_chicken 3 hours ago

While I would certainly agree with you that putting the FROM clause first would be a significant improvement to SQL and was a genuine design mistake, this otherwise feels more like just wanting SQL to be less declarative and more imperative. Wanting it to be more like LINQ and less like relational algebra.

That, I think, is most developers' real sticking point with SQL. It's not object-relational impedance mismatch between their application and the data store, it's imperative-declarative impedance mismatch between their preferred or demonstrated talent. They are used to thinking about problems in exactly one way, so when they struggle to adapt to a different way of thinking about the problems they assume their familiarity is what's more correct.

I think this is why the same developers insist that XML/HTML is "just a markup language." Feeding a document into an executable to produce output isn't really significantly different than feeding imperative language into a compiler. The only real difference is that one is Turing complete, but Turning completeness is not a requirement of programming languages.

Reply View 0 replies
metadata 12 hours ago

Google SQL has it now:

https://cloud.google.com/blog/products/data-analytics/simpli...

It's pretty neat:

    FROM mydataset.Produce
    |> WHERE sales > 0
    |> AGGREGATE SUM(sales) AS total_sales, COUNT(\*) AS num_sales
       GROUP BY item;
Edit: formatting
Reply View 2 replies
  • ryguyrg 11 hours ago

    note that DuckDB allows that reverse ordering (FROM-first)

    FROM table SELECT foo, bar WHERE zoo=“goo”

    Reply View 1 reply
    • viggity 9 hours ago

      it makes intellisense/autocomplete work a hell of a lot easier. LINQ in dotnet does the same thing.

      Reply View 0 replies
wodenokoto 11 hours ago

I haven’t tested but I believe there’s a prql extension for duckdb

Reply View 0 replies