"SQL and the Pernicious Effect of Awkward Syntax"

[aljazerzen]

This is a discussion from Discord thread that I don't know how to link to.

max-sixty — Yesterday at 20:44
https://carlineng.com/?postid=sql-bad-syntax#blog

aljazerzen — Yesterday at 21:41
This is the best I can do with PRQL:

from sales_by_date
select [
  d_year = s"YEAR({d_date})",
  d_month = s"MONTH(d_date)",
  total_sales
]
group [d_year, d_month] (
  aggregate month_sales = (sum total_sales)
)
group d_year (
  derive year_sales = (sum month_sales)
)
sort [d_year, d_month]

It is more readable than what he has?
It's less readable than maloy...

max-sixty — Yesterday at 21:42
Malloy does the nesting quite well
(that PRQL query doesn't actually nest the data; the year column is dense)
Let me try and write a "future PRQL" version of it (it'll be imperfect)

max-sixty — Yesterday at 21:56
I think we could have something like (doesn't work atm)

from sales_by_date
# Not sure about this `extract`, but something for pulling fields out   would be good?
derive year = date | extract year 
group [year] (
  aggregate [
    yearly_sales = sum total_sales,
    by_month = array [  # Does this need an `aggregate` / `derive`?
      month = month
      monthly_sales = (sum month_sales)
  ]  # Need to add the `sort` somewhere, maybe a nested expression?
)

aljazerzen — Yesterday at 21:57
Hmm, this could work
You are doing regular aggregation, with array_agg in pg, but the values in the array have been aggregated before..
But you are missing group for month?
How does array know what is the partition?
Guessing from non-aggregated fields of array, maybe?
what about this:

from sales_by_date
derive [year = date | extract year, month = date | extract month]
group [year] (
  group [month] (
    aggregate monthly_sales = (sum month_sales)
  )
  aggregate [
    yearly_sales = sum total_sales,
    by_month = array [month, monthly_sales]
  ]
)

max-sixty — Today at 01:59
Yes nice. So does the double group imply an array_agg there?

max-sixty — Today at 03:46
One thing that can be confusing, I think @snth has brought up before (and i'm definitely not proposing some big change, just transparency on how I'm thinking through this example):

We're creating monthly_sales there without a derive, because aggregate doesn't have a derive

Indeed — maybe we should say that aggregate is a form of derive
The more I think through your query, the more it makes sense, I think it's great.

I would say — I had to think through through it for a while.

In particular, that the expression within the first group is nicely self-contained. So this could be its own query:

  group [month] (
    aggregate monthly_sales = (sum month_sales)
  )
  aggregate [
    yearly_sales = sum total_sales,
    by_month = array [month, monthly_sales]
  ]

It's important to think of this as a pipeline. Once I did that, it really flows.

max-sixty — Today at 04:36
One odd twist in his post is that his SQL examples aren't the same result; they don't use arrays — not sure why — the nesting is good and is ugly in SQL — would make his point stronger!
Eeek they are not great at participating on HackerNews — https://news.ycombinator.com/item?id=32738874 — people disagree with them and they kinda tell them they're wrong! And then get dragged for it. I don't know Lloyd well enough to give him that feedback, but I'm sure he could do better.

At least PRQL gets some positive comments on the thread!

snth — Today at 07:25
Thanks for pinging me. I've got quite a lot of thoughts on this. Before I respond, I wonder if we should move this to a github discussion? The reason I suggest that is that you can have subthreads so it might be easier to keep multiple strands of this discussion separate?

max-sixty — Today at 07:57
Yes, good idea!

[aljazerzen]

As I see PRQL, this query can be decomposed exaclty as you suggest @max-sixty:

from sales_by_date
derive [year = date | extract year, month = date | extract month]
group [year] (
  # the group pipeline acts as it would start with
  # from sales_of_some_specific_year
  group [month] (
    # and this inner group acts as it would receive:
    # from sales_of_specific_month
    aggregate monthly_sales = (sum month_sales)
    # aggregate uses sum functions and implicitly applies
    # select [monthly_sales]
  )
  # when group finishes with aggregate, it implicitly applies:
  # select [month, monthly_sales]
  # note that we are still within outer group

  aggregate [
    yearly_sales = sum total_sales,
    by_month = array [month, monthly_sales]
  ]
  # aggregate uses sum and array (which translates to array_agg) 
  # and implicitly applies: select [yearly_sales, by_month]
)
# group finished with aggregate, so it implicitly applies:
# select [year, yearly_sales, by_month]

[aljazerzen]

window is a form of derive -> any columns are added to the existing set of columns.

You mean group is a form of derive?

[aljazerzen]

Yes, in some sense, aggregate is a form of derive (or select).

But is also manipulates number of rows, which is not true for derive and select:

	Keeps # of columns	Changes # of columns
Keeps # of rows	sort	select, derive
Changes # of rows	filter, take	aggregate

[snth]

You mean group is a form of derive?

I guess yes, because you produce implicit window functions from group but I would still prefer to think of them as window transforms in order to differentiate them from aggregate transforms because aggregate (mostly) also requires a group preceding it so I don't feel I can make a general assertion about group (I might be missing something here).

[aljazerzen]

Don't think about window function as something special. They are just column expressions that are computed from the whole column, instead of each row separately.

And window adjusts how much of the column can be used at once.

[snth]

That's actually a great way to put it! Thanks

So group splits the dataset into groups, then you can apply some transformations either 1) to each row, 2) aggregate the whole group to one row, 3) the whole group aka window functions, or 4) the whole group but only referencing data from a subset aka frame, and then everything gets combined together again into your resultset.

[aljazerzen]

One odd twist in his post is that his SQL examples aren't the same result; they don't use arrays — not sure why — the nesting is good and is ugly in SQL — would make his point stronger!

That's probably because he's doing analytics and copy-pastes results into a report in the end. So their exact format does not matter, since they are transformed by human™.

[snth]

I'm not sure I understand both your comments here but with regards to the two output sets that he produces, let's list them for easy reference because I think this is important and the representation affects what you can do with it:

1. "Dense" Form
   
2. "Rollup" Form
   
3. "Nested" Form
   

[aljazerzen]

I just mean that if you are exporting the data to a report you have to manually style anyway, you can transform dense into nested or vice versa with a spreadsheet.

It's not what we are after, but for many people, this is how they use software.

[snth]

If you want to do things like calculate "what percentage of yearly_sales each monthly_sales amount is", then 1) Dense form is nice because you can do the calculation in each row in a single pass of the table. 2) Rollup form suffers here because you basically have to do a self-join to get at the relevant yearly_sales amount, so while you save on not having duplicate data, it makes your runtime performance worse.

Finally 3) Nested form would seem to be the best of both worlds. However he just gives a screenshot and doesn't really specify how this is represented in the database. Is this because it doesn't really fit in the traditional relational model, although as you pointed out these days RDBMs do have array_agg functions and can store array fields but I don't know how universal that is. To me it seems that here you really move beyond the relational database model which is why Analytics databases specifying in OLAP exist and these often come with their own DSL as they aim to answer different kinds of questions, viz. MDX and DAX.

There is a lot of chatter about the "semantic layer" at the moment. Big ones I'm aware off are Malloy vs dbt but I haven't really delved into these yet. I think people also shouldn't ignore DAX from Microsoft. They've been doing this for a while and are at least on their second iteration, having learned from the MDX experience.

[snth]

I just mean that if you are exporting the data to a report you have to manually style anyway, you can transform dense into nested or vice versa with a spreadsheet.

Yes, I agree. I was kinda trying to make the same point in my comment (which I was writing while you added your comment).

[snth]

I think the semantic layer is hard, judging by the fact that no one seems to have really solved it satisfactorily. I am therefore hesitant to go too far into it. It's fun to think about but I'd prefer to stick to trying to address the problem of how to efficiently express particular queries like we have been doing. The impression I get from the semantic layer discussions, is that people would like to define their measures once and then the model should figure out how to calculate or visually represent that metric in whatever expression or pivot table you choose to embed it. That to me just seems too ill defined to be able to solve that in general. What if I add another dimension? How does that affect what I have already? Sometimes I want to see something as a percentage of a total, sometimes relative to a previous period, ...

What I want is that once I have a particular calculation or query that I want the answer to, how can I express that efficiently? Ideally reusing some previous code of problems I have solved before (-> functions).