I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid.
I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can usually still express queries in SQL that other systems do not allow.
For these kinds of queries I think there are just better ways to express them. Another issue with sql is that has some quite strange semantics.[1]
An example query I wrote yesterday is:
select group, min, max, (max-min)/1e9 range
from
(select group, min(size) min, max(size) max
from
(select time, instance, sum(size) size, regexp_replace(name,…) group
from X
group by regexp_replace(name,…), time, instance)
group by group)
order by range desc
limit 10
Which is neither pleasant to write nor iterate on interactively.
With something like dplyr instead:
X %>% mutate(group=regexp_replace(name,…))
%>% group_by(group,time,instance)
%>% summarize(size=sum(size))
%>% group_by(group)
%>% summarize(min=min(size),max=max(size),range=(min-max)/1e9)
%>% arrange(-range)
%>% head(n=10)
And that can be built up interactively pretty easily by adding onto the end of the pipeline.
I would also note that, due to sql being painful, the query is not exactly the one I wanted and instead I would have wanted something better capturing the change over time, but the thought of doing that in SQL seemed too unpleasant.
An example of an actual query language that tries to be better for analytics: https://prql-lang.org/
[1] from someone who spent a lot of time working on databases and sql: https://www.scattered-thoughts.net/writing/against-sql and just on semantics: https://www.scattered-thoughts.net/writing/select-wat-from-s...