Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Google's new pipe syntax in SQL

simonwillison.net

Post reply on HN
Print view View original

101–110 of 192 posts

Re: Google's new pipe syntax in SQL

#101

Post by otabdeveloper4 »

eternauta3k wrote: post #79

Do manually-generated SQL strings have a place outside of interactive use? I use them in my small projects but I wonder if a query builder isn't better for larger systems.

Query building for an analytics database is impossible.

These queries are always hand-rolled because you pay the analysts to optimize them.

Re: Google's new pipe syntax in SQL

#103

Post by willvarfar »

aidos wrote: post #93
willvarfar wrote: post #84

Earlier quoted context omitted.

Yeap I didn't know DuckDB supported it already! Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier. FROM table ...

What’s group-by-all? Sounds like distinct?

Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient.

BigQuery supports GROUP BY ALL and it really cleans up lots of queries. E.g.

   SELECT foo, bar, SUM(baz)
   FROM x
   GROUP BY ALL
(eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)

Re: Google's new pipe syntax in SQL

#104

Post by otabdeveloper4 »

BeefWellington wrote: post #69

Every time this FROM-first syntax style crops up it's always the most basic simple query (one table, no projections / subselects / consideration to SP/Views). Just for once I want to see complete examples of the syntax on an actual advanced query of any kind right away. Sure, toss out one simple case, but then show me how it looks when I have to join 4-5 reference tables to a fact table and then filter based on those…

FROM order is, like, the least offensive and least wrong thing about SQL.

Bikeshedding par excellence.

Re: Google's new pipe syntax in SQL

#107

Post by kbouck »

andrewguy9 wrote: post #16
tehlike wrote: post #15

LINQ, PRQL, Kusto has all preceeded this. While LINQ is mostly restricted to .NET, PRQL is not. https://prql-lang.org/ It's a welcome change in the industry. I made this prediction a couple years back: https://x.com/tehlike/status/1517533067497201666

I’m a big kusto user, and it’s wonderful to have pipes in a query language. If you haven’t tried it, it’s great!

Indeed. Elastic has also recently released a piped query language called ES|QL. Feels similar to Kusto.

I find piped queries both easier to write, and read.

Re: Google's new pipe syntax in SQL

#108

Post by genezeta »

aidos wrote: post #93
willvarfar wrote: post #84

Earlier quoted context omitted.

Yeap I didn't know DuckDB supported it already! Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier. FROM table ...

What’s group-by-all? Sounds like distinct?

It's different from distinct. Distinct just eliminates duplicates but does not group entries.

Suppose...

  SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY brand, model, revision
This is not solved by using distinct as you would not get the correct count.

Group By All allows you to write it a bit more compact...

  SELECT brand, model, revision, SUM(quantity)
   FROM stock
   GROUP BY ALL

Re: Google's new pipe syntax in SQL

#109

Post by h0l0cube »

victorbjorklund wrote: post #106

Looks just like writing sql using Ecto in Elixir: "users" |> where([u], u.age > 18) |> select([u], u.name) https://hexdocs.pm/ecto/Ecto.Query.html

Thought this too. The example queries look very much like Ecto statements. I miss the ergonomics and flexibility of Ecto when I use database wrappers on other platforms.
Post reply on HN