WITHIN GROUP query syntax

[janrito]

Description

I'm seeing this problem again.

WITH dataset AS (
  SELECT
    time_bucket('%time_bucket_duration%', start_timestamp) AS x,
    (extract(ms from end_timestamp - start_timestamp)) as duration_ms
  FROM records
  WHERE attributes ? 'http.method'
)
SELECT
  x,
  approx_percentile_cont(duration_ms, 0.50) as percentile_50,
  approx_percentile_cont(duration_ms, 0.90) as percentile_90,
  approx_percentile_cont(duration_ms, 0.95) as percentile_95,
  approx_percentile_cont(duration_ms, 0.99) as percentile_99
FROM dataset
GROUP BY x
ORDER BY x

leads to
invalid query: WITHIN GROUP clause is required when calling ordered set aggregate function(approx_percentile_cont)

And

WITH dataset AS (
  SELECT
    time_bucket('%time_bucket_duration%', start_timestamp) AS x,
    (extract(ms from end_timestamp - start_timestamp)) as duration_ms
  FROM records
  WHERE attributes ? 'http.method'
)
SELECT
  x,
  approx_percentile_cont(duration_ms, 0.50) WITHIN GROUP (ORDER BY duration_ms) as percentile_50
FROM dataset
GROUP BY x
ORDER BY x

throws

invalid query: Failed to coerce arguments to satisfy a call to 'approx_percentile_cont' function: coercion from [Int32, Int32, Float64] to the signature OneOf([Exact([Int8, Float64]), Exact([Int8, Float64, Int8]), Exact([Int8, Float64, Int16]), Exact([Int8, Float64, Int32]), Exact([Int8, Float64, Int64]), Exact([Int8, Float64, UInt8]), Exact([Int8, Float64, UInt16]), Exact([Int8, Float64, UInt32]), Exact([Int8, Float64, UInt64]), Exact([Int16, Float64]), Exact([Int16, Float64, Int8]), Exact([Int16, Float64, Int16]), Exact([Int16, Float64, Int32]), Exact([Int16, Float64, Int64]), Exact([Int16, Float64, UInt8]), Exact([Int16, Float64, UInt16]), Exact([Int16, Float64, UInt32]), Exact([Int16, Float64, UInt64]), Exact([Int32, Float64]), Exact([Int32, Float64, Int8]), Exact([Int32, Float64, Int16]), Exact([Int32, Float64, Int32]), Exact([Int32, Float64, Int64]), Exact([Int32, Float64, UInt8]), Exact([Int32, Float64, UInt16]), Exact([Int32, Float64, UInt32]), Exact([Int32, Float64, UInt64]), Exact([Int64, Float64]), Exact([Int64, Float64, Int8]), Exact([Int64, Float64, Int16]), Exact([Int64, Float64, Int32]), Exact([Int64, Float64, Int64]), Exact([Int64, Float64, UInt8]), Exact([Int64, Float64, UInt16]), Exact([Int64, Float64, UInt32]), Exact([Int64, Float64, UInt64]), Exact([UInt8, Float64]), Exact([UInt8, Float64, Int8]), Exact([UInt8, Float64, Int16]), Exact([UInt8, Float64, Int32]), Exact([UInt8, Float64, Int64]), Exact([UInt8, Float64, UInt8]), Exact([UInt8, Float64, UInt16]), Exact([UInt8, Float64, UInt32]), Exact([UInt8, Float64, UInt64]), Exact([UInt16, Float64]), Exact([UInt16, Float64, Int8]), Exact([UInt16, Float64, Int16]), Exact([UInt16, Float64, Int32]), Exact([UInt16, Float64, Int64]), Exact([UInt16, Float64, UInt8]), Exact([UInt16, Float64, UInt16]), Exact([UInt16, Float64, UInt32]), Exact([UInt16, Float64, UInt64]), Exact([UInt32, Float64]), Exact([UInt32, Float64, Int8]), Exact([UInt32, Float64, Int16]), Exact([UInt32, Float64, Int32]), Exact([UInt32, Float64, Int64]), Exact([UInt32, Float64, UInt8]), Exact([UInt32, Float64, UInt16]), Exact([UInt32, Float64, UInt32]), Exact([UInt32, Float64, UInt64]), Exact([UInt64, Float64]), Exact([UInt64, Float64, Int8]), Exact([UInt64, Float64, Int16]), Exact([UInt64, Float64, Int32]), Exact([UInt64, Float64, Int64]), Exact([UInt64, Float64, UInt8]), Exact([UInt64, Float64, UInt16]), Exact([UInt64, Float64, UInt32]), Exact([UInt64, Float64, UInt64]), Exact([Float32, Float64]), Exact([Float32, Float64, Int8]), Exact([Float32, Float64, Int16]), Exact([Float32, Float64, Int32]), Exact([Float32, Float64, Int64]), Exact([Float32, Float64, UInt8]), Exact([Float32, Float64, UInt16]), Exact([Float32, Float64, UInt32]), Exact([Float32, Float64, UInt64]), Exact([Float64, Float64]), Exact([Float64, Float64, Int8]), Exact([Float64, Float64, Int16]), Exact([Float64, Float64, Int32]), Exact([Float64, Float64, Int64]), Exact([Float64, Float64, UInt8]), Exact([Float64, Float64, UInt16]), Exact([Float64, Float64, UInt32]), Exact([Float64, Float64, UInt64])]) failed No function matches the given name and argument types 'approx_percentile_cont(Int32, Int32, Float64)'. You might need to add explicit type casts. Candidate functions: approx_percentile_cont(Int8, Float64) approx_percentile_cont(Int8, Float64, Int8) approx_percentile_cont(Int8, Float64, Int16) approx_percentile_cont(Int8, Float64, Int32) approx_percentile_cont(Int8, Float64, Int64) approx_percentile_cont(Int8, Float64, UInt8) approx_percentile_cont(Int8, Float64, UInt16) approx_percentile_cont(Int8, Float64, UInt32) approx_percentile_cont(Int8, Float64, UInt64) approx_percentile_cont(Int16, Float64) approx_percentile_cont(Int16, Float64, Int8) approx_percentile_cont(Int16, Float64, Int16) approx_percentile_cont(Int16, Float64, Int32) approx_percentile_cont(Int16, Float64, Int64) approx_percentile_cont(Int16, Float64, UInt8) approx_percentile_cont(Int16, Float64, UInt16) approx_percentile_cont(Int16, Float64, UInt32) approx_percentile_cont(Int16, Float64, UInt64) approx_percentile_cont(Int32, Float64) approx_percentile_cont(Int32, Float64, Int8) approx_percentile_cont(Int32, Float64, Int16) approx_percentile_cont(Int32, Float64, Int32) approx_percentile_cont(Int32, Float64, Int64) approx_percentile_cont(Int32, Float64, UInt8) approx_percentile_cont(Int32, Float64, UInt16) approx_percentile_cont(Int32, Float64, UInt32) approx_percentile_cont(Int32, Float64, UInt64) approx_percentile_cont(Int64, Float64) approx_percentile_cont(Int64, Float64, Int8) approx_percentile_cont(Int64, Float64, Int16) approx_percentile_cont(Int64, Float64, Int32) approx_percentile_cont(Int64, Float64, Int64) approx_percentile_cont(Int64, Float64, UInt8) approx_percentile_cont(Int64, Float64, UInt16) approx_percentile_cont(Int64, Float64, UInt32) approx_percentile_cont(Int64, Float64, UInt64) approx_percentile_cont(UInt8, Float64) approx_percentile_cont(UInt8, Float64, Int8) approx_percentile_cont(UInt8, Float64, Int16) approx_percentile_cont(UInt8, Float64, Int32) approx_percentile_cont(UInt8, Float64, Int64) approx_percentile_cont(UInt8, Float64, UInt8) approx_percentile_cont(UInt8, Float64, UInt16) approx_percentile_cont(UInt8, Float64, UInt32) approx_percentile_cont(UInt8, Float64, UInt64) approx_percentile_cont(UInt16, Float64) approx_percentile_cont(UInt16, Float64, Int8) approx_percentile_cont(UInt16, Float64, Int16) approx_percentile_cont(UInt16, Float64, Int32) approx_percentile_cont(UInt16, Float64, Int64) approx_percentile_cont(UInt16, Float64, UInt8) approx_percentile_cont(UInt16, Float64, UInt16) approx_percentile_cont(UInt16, Float64, UInt32) approx_percentile_cont(UInt16, Float64, UInt64) approx_percentile_cont(UInt32, Float64) approx_percentile_cont(UInt32, Float64, Int8) approx_percentile_cont(UInt32, Float64, Int16) approx_percentile_cont(UInt32, Float64, Int32) approx_percentile_cont(UInt32, Float64, Int64) approx_percentile_cont(UInt32, Float64, UInt8) approx_percentile_cont(UInt32, Float64, UInt16) approx_percentile_cont(UInt32, Float64, UInt32) approx_percentile_cont(UInt32, Float64, UInt64) approx_percentile_cont(UInt64, Float64) approx_percentile_cont(UInt64, Float64, Int8) approx_percentile_cont(UInt64, Float64, Int16) approx_percentile_cont(UInt64, Float64, Int32) approx_percentile_cont(UInt64, Float64, Int64) approx_percentile_cont(UInt64, Float64, UInt8) approx_percentile_cont(UInt64, Float64, UInt16) approx_percentile_cont(UInt64, Float64, UInt32) approx_percentile_cont(UInt64, Float64, UInt64) approx_percentile_cont(Float32, Float64) approx_percentile_cont(Float32, Float64, Int8) approx_percentile_cont(Float32, Float64, Int16) approx_percentile_cont(Float32, Float64, Int32) approx_percentile_cont(Float32, Float64, Int64) approx_percentile_cont(Float32, Float64, UInt8) approx_percentile_cont(Float32, Float64, UInt16) approx_percentile_cont(Float32, Float64, UInt32) approx_percentile_cont(Float32, Float64, UInt64) approx_percentile_cont(Float64, Float64) approx_percentile_cont(Float64, Float64, Int8) approx_percentile_cont(Float64, Float64, Int16) approx_percentile_cont(Float64, Float64, Int32) approx_percentile_cont(Float64, Float64, Int64) approx_percentile_cont(Float64, Float64, UInt8) approx_percentile_cont(Float64, Float64, UInt16) approx_percentile_cont(Float64, Float64, UInt32) approx_percentile_cont(Float64, Float64, UInt64)```


### Python, Logfire & OS Versions, related packages (not required)

```TOML

[alexmojaki]

Minimal repro: select approx_percentile_cont(duration, 0.5) within group (order by duration) from records

[alexmojaki]

Ah, based on apache/datafusion#13511 (comment) the expression must now be omitted as the first argument. Here's a working query:

select
    span_name,
    approx_percentile_cont(0.5) within group (order by duration) as median_duration
from records
group by span_name
order by median_duration desc nulls last

[janrito]

Amazing! Thanks @alexmojaki