Is select still needed?

[Jelenkee]

As far as I can tell you can replace it with aggregate and (nearly) the same SQL is generated.
For the playground-example artists-0.prql is SQL is slightly different, but the result of the query is still the same.

Additionally I replaced the selects in the unittests with aggregate. Some tests failed but most of the time because aggregate generated a CTE. Maybe you can add some logic to check if an aggregate can be simplified.

[max-sixty]

Would it be possible to give a couple of examples?

[eitsupi]

Perhaps they are talking about the example given on the website?

I thought the same thing when I was writing the following document.
https://eitsupi.quarto.pub/use-prql-executable-code-blocks-on-quarto/#join-multiple-tables

For example, the following query (which I believe was copied from somewhere on the website)

from tracks
select [album_id, name, unit_price]
sort [-unit_price, name]
group album_id (
  aggregate [
    track_count = count,
    album_price = sum unit_price
  ]
)
join albums [==album_id]
group artist_id (
  aggregate [
    track_count = sum track_count,
    artist_price = sum album_price
  ]
)
join artists [==artist_id]
select [artists.name, artist_price, track_count]
sort [-artist_price]
derive avg_track_price = artist_price / track_count
take 3

The second and third lines are unnecessary and can be deleted as follows with the same result.

from tracks
group album_id (
  aggregate [
    track_count = count,
    album_price = sum unit_price
  ]
)
join albums [==album_id]
group artist_id (
  aggregate [
    track_count = sum track_count,
    artist_price = sum album_price
  ]
)
join artists [==artist_id]
select [artists.name, artist_price, track_count]
sort [-artist_price]
derive avg_track_price = artist_price / track_count
take 3

[max-sixty]

Ah yes, good point on that example. Shall we remove those two lines? We have select below already to demonstrate it...

[eitsupi]

Ah yes, good point on that example. Shall we remove those two lines? We have select below already to demonstrate it...

Yeah, these are just examples to showcase the functionality, so I don't think they need to be completely refined, but in this case it would make sense to remove the first select and sort.

prql/playground/src/examples.js

Lines 60 to 80 in dcfcb5f

	`from tracks
	select [album_id, name, unit_price]
	sort [-unit_price, name]
	group album_id (
	aggregate [
	track_count = count,
	album_price = sum unit_price
	]
	)
	join albums [==album_id]
	group artist_id (
	aggregate [
	track_count = sum track_count,
	artist_price = sum album_price
	]
	)
	join artists [==artist_id]
	select [artists.name, artist_price, track_count]
	sort [-artist_price]
	derive avg_track_price = artist_price / track_count
	`,

[max-sixty]

Yes agree. I would be very open to that change...

[max-sixty]

I think possibly this is linked to #2018 — does that resolve the confusion?

Is there a way we could communicate it more clearly?

[Jelenkee]

Yeah it is linked to #2018. After reading the FAQ I understand why you use different keywords for different use cases.
Nevertheless you can almost everytime replace select with aggregate, except when aggregate functions are used. You can use them with select but I can see no use case for that. Maybe the compiler should throw an error (or log a warning?) when aggregate functions are used with select.

This is an example where select can be replaced and a CTE is generated but the logic is the same

    from Genre
    select [
        x = Name,
        z = f"a {x} b' {GenreId} c",        
    ]

[max-sixty]

Yes, I think we should throw an error for using aggregate there, since it's not aggregating.

Do you want to open an issue for that? Otherwise I can

[Jelenkee]

Done
#2057