3 ms·
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 h
by dan-robertson 3y ago
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/ 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 https://www.scattered-thoughts.net/writing/against-sql and just on semantics: https://www.scattered-thoughts.net/writing/select-wat-from-sql/ https://www.scattered-thoughts.net/writing/select-wat-from-s...
- nalgeon 3y agoI think the problem here is with the query, not the language. You can immediately improve its maintainability and readability by using CTEs. https://antonz.org/cte/ https://antonz.org/cte/
- rjbwork 3y agoThis exactly the comment I was going to leave. If I get past one sub query, I will refactor to CTE.
- dan-robertson 3y agoMaintainability is not relevant to ad-hoc analytics queries. That may be dealt with once the correct query has been determined from sufficient iteration.
- throwaway2990 3y agoYou can’t mix/max a summed group? The sum would be in the same group level as min max…
- dan-robertson 3y agoI corrected my example (the inner group was meant to be by more columns)
- reese_john 3y agoYou could probably use window functions Ex: min(sum(size)) over(partition by group) min, max(sum(size)) over(partition by group) max
- croes 3y agoWhy do you need the outermost query?
- dan-robertson 3y agoI think I had originally written something like select min(size) min, max(size) max, max-min range, but that didn’t work as the newly introduced names ‘weren’t in scope’ and I didn’t want to type those aggregations out again. You’re right that it could have been avoided.
- markisus 3y agoThe author’s second example is extremely unconvincing for me. Why would I want to be forced to use a SELECT expression to calculate a mean? Relational algebra is a great abstraction. SQL, however, seems to be poorly thought out and ad hoc. It’s just the first implementation of a relational algebra language that worked. But why should we be stuck with it forever?