6 ms·
(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 kn
by zasdffaa 4y ago
(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
- yread 4y agofor example select id, count(...something complicated) as complicated_count from .... order by complicated_count would help
- jsmith99 4y ago'ORDER BY 2' would work here, but using the named column is a lot nicer.
- tomjakubowski 4y agoWow, TIL. Great tip for those random one-off queries you have to bash out when investigating a problem.
- zasdffaa 4y agoPlease never let this vile shortcut work its way into your production code.
- sagarm 4y agoI've seen quite a few production queries that use indexes in GROUP BY and ORDER BY; it's quite common. Probably partially because linters/code review/etc are lightweight to nonexistent amongst the analysts/data science types that I tend to work with.
- zasdffaa 4y agoIndexes are used all over for grouping an ordering, I was objecting only to the syntax of ORDER BY <number>
- zasdffaa 4y agoSQL already supports "order by complicated_count". Did you mean group by? This isn't really the large, convincing example I was looking for btw.
- sagarm 4y agoMany dialects already support using aliases in GROUP BY and HAVING too, btw. IMO it's most useful (though somewhat more difficult to implement) to be able to use the aliases with window functions or large case/when statements, something like SELECT page, SUM(clicks) AS total_clicks, 100. * total_clicks / (SUM(total_clicks) OVER ()) AS click_pct, 100. * SUM(total_clicks) OVER (ORDER BY total_clicks DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SUM(total_clicks) OVER ()) AS cumulative_click_pct FROM weblog GROUP BY page;
- zasdffaa 4y agoInteresting, ta. My code rarely looks like that so thanks for the insight. Was exactrly what I was looking for.
- wruza 4y agoSQL 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?
- zasdffaa 4y ago> How come it could do $%^& to any part of yours? Because just implementing the standard stuff nearly did my $^&% nut. Also I know about graphs & posets, and it's potentially a little more complex than it seems. The variables select x * x as y, 1 as x is meh, but what about select (select tbl.z from tbl where tbl.y = y) as subq, x * yy as y, xx + 1 as x, subq + yy as zzz from ( select xx, yy from ... ) I just don't fancy supporting that.
- wruza 4y agowhat about That’s no different than the first snippet, if you aren’t parsing it with regexps, of course. The resulting AST identifiers would simply refer to not only column names, but also to other defined expressions. This is the case for both snippets. It’s either cyclic or not, and when not, it is easy to substitute/cse/etc as usual. The complexity of these expressions is irrelevant.
- zasdffaa 4y agohttps://news.ycombinator.com/item?id=31364281 https://news.ycombinator.com/item?id=31364281
- wenc 4y agoIt’s not trivial but as someone who has implemented something similar (for an equation based modeling language) it’s not super complicated if you use the right abstractions. It’s basically traversing the AST and doing substitutions.
- 4y ago
- karmakaze 4y agoThe thing that makes SQL simple for me is that I can think in set operations devoid of proceduralness. Once we make things more and more sequential the more it is like programming than a formula.
- layer8 4y agoWhat does "do my $%^& nut" even mean? (looks like Perl ;))
- zasdffaa 4y ago:-) English idiom. Nut = head. Doing my head in, basically.