Skip to main content

uqa_execution/aggregation/
finalize.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Built-in aggregate finalization and percentile/statistical helpers.
8
9use super::{
10    cast_value, core_value_to_json, value_as_f64, AggregateAccumulator, AggregateValueBuffer,
11    DecimalValue, SQLError, ScalarExpr, Value,
12};
13use uqa_core::ArrayValue;
14use uqa_sql::expr::enums::EnumLabelCatalog;
15use uqa_sql::expr::value_to_json_text_with_catalog;
16
17pub fn aggregate_value(
18    name: &str,
19    acc: &AggregateAccumulator,
20    enums: Option<&dyn EnumLabelCatalog>,
21) -> Result<Value, SQLError> {
22    aggregate_value_with_args(name, acc, &[], enums)
23}
24
25#[expect(
26    clippy::too_many_lines,
27    reason = "preserves aggregate NULL and type order"
28)]
29/// Finalize a built-in aggregate. JSON aggregates call their inputs' output functions, so enum inputs render through `enums`.
30pub fn aggregate_value_with_args(
31    name: &str,
32    acc: &AggregateAccumulator,
33    args: &[ScalarExpr],
34    enums: Option<&dyn EnumLabelCatalog>,
35) -> Result<Value, SQLError> {
36    if !acc.distinct.is_empty() {
37        let budget = acc.distinct.values.budget_bytes;
38        let mut unique = acc.replay_with_budget(budget)?;
39        unique.enum_comparison.clone_from(&acc.enum_comparison);
40        let result = acc
41            .distinct
42            .for_each(enums, |value| {
43                if acc.registered.is_some() {
44                    let Value::List(arguments) = value else {
45                        return Err(SQLError::Internal(
46                            "registered DISTINCT input is not an argument tuple".into(),
47                        ));
48                    };
49                    unique.observe_registered(arguments.clone(), Vec::new(), enums)
50                } else {
51                    unique.observe_with_enum_catalog(value, enums)
52                }
53            })
54            .and_then(|()| aggregate_value_with_args(name, &unique, args, enums));
55        return super::state::with_cleanup(result, unique.close());
56    }
57    if let Some(value) = acc.registered_value(enums) {
58        return value;
59    }
60    if !acc.state_plan.retains_values() && acc.values.next_sequence != 0 {
61        let mut ordered = AggregateAccumulator {
62            state_plan: acc.state_plan,
63            enum_comparison: acc.enum_comparison.clone(),
64            ..AggregateAccumulator::default()
65        };
66        acc.values.for_each_ordered(enums, |record| {
67            ordered.observe_with_enum_catalog(&record.value, enums)
68        })?;
69        return aggregate_value_with_args(name, &ordered, args, enums);
70    }
71    let lname = uqa_sql::semantics::builtin_aggregate_dispatch_name(name).to_ascii_lowercase();
72
73    let value = match lname.as_str() {
74        "count" => Value::Int(
75            i64::try_from(acc.count)
76                .map_err(|_| SQLError::TypeMismatch("aggregate count exceeds BIGINT".into()))?,
77        ),
78        "sum" => {
79            if acc.count == 0 {
80                Value::Null
81            } else if let Some(sum) = acc.interval_result()? {
82                Value::Temporal(sum.value())
83            } else if acc.numeric_inputs.decimal_without_float() {
84                acc.decimal_sum.clone().map_or(Value::Null, Value::Decimal)
85            } else if acc.numeric_inputs.all_integers() {
86                Value::Int(i64::try_from(acc.integer_sum).map_err(|_| {
87                    SQLError::TypeMismatch("integer aggregate result exceeds BIGINT".into())
88                })?)
89            } else {
90                Value::Float(acc.sum)
91            }
92        }
93        "avg" => {
94            if acc.count == 0 {
95                Value::Null
96            } else if let Some(sum) = acc.interval_result()? {
97                // `interval_avg` divides the sum by the count as a double precision factor.
98                Value::Temporal(sum.divide(acc.count as f64)?.value())
99            } else if acc.numeric_inputs.decimal_without_float() {
100                let divisor = DecimalValue::from_i64(i64::try_from(acc.count).map_err(|_| {
101                    SQLError::TypeMismatch("aggregate count exceeds BIGINT".into())
102                })?);
103                let average = acc
104                    .decimal_sum
105                    .as_ref()
106                    .and_then(|sum| sum.checked_div_postgres(&divisor))
107                    .ok_or_else(|| SQLError::TypeMismatch("decimal AVG overflow".into()))?;
108                Value::Decimal(average)
109            } else if acc.numeric_inputs.all_integers() {
110                let sum = DecimalValue::from_i128(acc.integer_sum)
111                    .ok_or_else(|| SQLError::TypeMismatch("integer AVG overflow".into()))?;
112                let divisor = DecimalValue::from_i128(i128::from(acc.count)).ok_or_else(|| {
113                    SQLError::TypeMismatch("aggregate count exceeds NUMERIC".into())
114                })?;
115                let average = sum
116                    .checked_div_postgres(&divisor)
117                    .ok_or_else(|| SQLError::TypeMismatch("integer AVG overflow".into()))?;
118                Value::Decimal(average)
119            } else {
120                Value::Float(acc.sum / acc.count as f64)
121            }
122        }
123        "min" => acc.min.clone().unwrap_or(Value::Null),
124        "max" => acc.max.clone().unwrap_or(Value::Null),
125        "string_agg" => super::string_agg::finish(&acc.values.ordered_values(enums)?, enums)?,
126        "array_agg" => {
127            let ordered_values = acc.values.ordered_values(enums)?;
128            if ordered_values.is_empty() {
129                return Ok(Value::Null);
130            }
131            if let super::AggregateStatePlan::BufferedArrays = acc.state_plan {
132                return super::array_inputs::ArrayInputShape::stack(ordered_values, enums);
133            }
134            ArrayValue::try_new(ordered_values)
135                .map(Value::Array)
136                .ok_or_else(|| SQLError::Routine {
137                    sqlstate: "2202E".into(),
138                    message: "cannot accumulate arrays of different dimensionality".into(),
139                })?
140        }
141        "json_agg" | "jsonb_agg" => {
142            let ordered_values = acc.values.ordered_values(enums)?;
143            if ordered_values.is_empty() {
144                return Ok(Value::Null);
145            }
146            let text = format!(
147                "[{}]",
148                ordered_values
149                    .iter()
150                    .map(|value| value_to_json_text_with_catalog(value, enums))
151                    .collect::<Result<Vec<_>, SQLError>>()?
152                    .join(", ")
153            );
154            if lname == "jsonb_agg" {
155                cast_value(&Value::Str(text), "jsonb")?
156            } else {
157                Value::Json(text)
158            }
159        }
160        "json_object_agg" | "jsonb_object_agg" => {
161            let ordered_values = acc.values.ordered_values(enums)?;
162            let mut fields = Vec::with_capacity(ordered_values.len());
163            for value in ordered_values {
164                let Value::List(pair) = value else {
165                    return Err(SQLError::Internal(
166                        "JSON object aggregate retained a non-pair value".into(),
167                    ));
168                };
169                if pair.len() != 2 {
170                    return Err(SQLError::Internal(
171                        "JSON object aggregate retained a malformed pair".into(),
172                    ));
173                }
174                if matches!(pair[0], Value::Null) {
175                    return Err(super::rewrite::json_object_aggregate_null_key(&lname));
176                }
177                let key =
178                    serde_json::Value::String(aggregate_json_key_with_catalog(&pair[0], enums)?)
179                        .to_string();
180                fields.push((key, value_to_json_text_with_catalog(&pair[1], enums)?));
181            }
182            if fields.is_empty() {
183                Value::Null
184            } else if lname == "jsonb_object_agg" {
185                let text = fields
186                    .into_iter()
187                    .map(|(key, value)| format!("{key}:{value}"))
188                    .collect::<Vec<_>>()
189                    .join(",");
190                cast_value(&Value::Str(format!("{{{text}}}")), "jsonb")?
191            } else {
192                let text = fields
193                    .into_iter()
194                    .map(|(key, value)| format!("{key} : {value}"))
195                    .collect::<Vec<_>>()
196                    .join(", ");
197                Value::Json(format!("{{ {text} }}"))
198            }
199        }
200        "bool_and" => match acc.bool_and {
201            Some(b) => Value::Bool(b),
202            None => Value::Null,
203        },
204        "bool_or" => match acc.bool_or {
205            Some(b) => Value::Bool(b),
206            None => Value::Null,
207        },
208        "stddev" | "stddev_samp" => {
209            if acc.statistics_count < 2 {
210                return Ok(Value::Null);
211            }
212            statistical_standard_deviation(acc, true)?
213        }
214        "stddev_pop" => {
215            if acc.statistics_count == 0 {
216                return Ok(Value::Null);
217            }
218            statistical_standard_deviation(acc, false)?
219        }
220        "variance" | "var_samp" => {
221            if acc.statistics_count < 2 {
222                return Ok(Value::Null);
223            }
224            statistical_variance(acc, true)?
225        }
226        "var_pop" => {
227            if acc.statistics_count == 0 {
228                return Ok(Value::Null);
229            }
230            statistical_variance(acc, false)?
231        }
232        "percentile_cont" => {
233            let frac = percentile_fraction(args)?;
234            percentile_cont_with_catalog(&acc.values, frac, enums)?
235                .map_or(Value::Null, Value::Float)
236        }
237        "percentile_disc" => {
238            let frac = percentile_fraction(args)?;
239            percentile_disc_with_catalog(&acc.values, frac, enums)?.unwrap_or(Value::Null)
240        }
241        "mode" => mode_value_with_catalog(&acc.values, enums)?,
242        _ => return Err(SQLError::UnknownFunction(format!("aggregate `{name}`"))),
243    };
244    Ok(value)
245}
246
247pub fn percentile_fraction(args: &[ScalarExpr]) -> Result<f64, SQLError> {
248    let fraction = match args.first() {
249        Some(
250            ScalarExpr::Literal(Value::Float(f))
251            | ScalarExpr::TypedLiteral {
252                value: Value::Float(f),
253                ..
254            },
255        ) => *f,
256        Some(
257            ScalarExpr::Literal(Value::Int(n))
258            | ScalarExpr::TypedLiteral {
259                value: Value::Int(n),
260                ..
261            },
262        ) => *n as f64,
263        Some(
264            ScalarExpr::Literal(Value::Decimal(d))
265            | ScalarExpr::TypedLiteral {
266                value: Value::Decimal(d),
267                ..
268            },
269        ) => d.to_f64().ok_or_else(|| {
270            SQLError::TypeMismatch("percentile fraction is outside floating-point range".into())
271        })?,
272        Some(value) => {
273            return Err(SQLError::TypeMismatch(format!(
274                "percentile fraction must be a numeric literal, got {value:?}"
275            )))
276        }
277        None => {
278            return Err(SQLError::BadArity {
279                name: "percentile".into(),
280                expected: "fraction argument".into(),
281                actual: 0,
282            })
283        }
284    };
285    if !fraction.is_finite() || !(0.0..=1.0).contains(&fraction) {
286        return Err(SQLError::TypeMismatch(format!(
287            "percentile fraction must be between 0 and 1, got {fraction}"
288        )));
289    }
290    Ok(fraction)
291}
292
293pub fn aggregate_json_key(value: &Value) -> Result<String, SQLError> {
294    aggregate_json_key_with_catalog(value, None)
295}
296
297pub fn aggregate_json_key_with_catalog(
298    value: &Value,
299    catalog: Option<&dyn EnumLabelCatalog>,
300) -> Result<String, SQLError> {
301    uqa_sql::expr::validate_json_object_key_type_with_catalog(value, catalog)?;
302    Ok(match value {
303        Value::Datum(datum) => {
304            let value = uqa_sql::expr::datums::read_with_value_catalog_and_control(
305                datum,
306                catalog,
307                &uqa_core::memory::ProductionControl::uncontrolled(),
308            )?;
309            return aggregate_json_key_with_catalog(&value, catalog);
310        }
311        Value::Null | Value::Void => String::new(),
312        Value::Bool(b) => b.to_string(),
313        Value::Int(i) => i.to_string(),
314        Value::Float(f) => f.to_string(),
315        Value::Decimal(d) => d.to_sql_string(),
316        Value::Str(s) => s.clone(),
317        Value::FixedChar(s) => s.trim_end_matches(' ').to_string(),
318        Value::Enum(value) => uqa_sql::expr::enums::enum_label_text(catalog, value)?,
319        Value::Bytes(bytes) => String::from_utf8_lossy(bytes).into_owned(),
320        Value::Json(text) | Value::JsonB(text) => text.clone(),
321        Value::Temporal(_) | Value::Array(_) | Value::LegacyVector(_) | Value::BitString(_) => {
322            uqa_sql::expr::value_to_string(value)?
323        }
324        Value::List(_) | Value::Row(_) | Value::Record(_) | Value::Map(_) => {
325            serde_json::to_string(&core_value_to_json(value)?).map_err(|error| {
326                SQLError::Internal(format!("cannot render JSON object aggregate key: {error}"))
327            })?
328        }
329    })
330}
331
332fn statistical_variance(
333    accumulator: &AggregateAccumulator,
334    sample: bool,
335) -> Result<Value, SQLError> {
336    if accumulator.statistics_has_float {
337        let divisor = if sample {
338            accumulator.statistics_count as f64 - 1.0
339        } else {
340            accumulator.statistics_count as f64
341        };
342        return Ok(Value::Float(accumulator.statistics_m2 / divisor));
343    }
344    numeric_statistical_variance(accumulator, sample).map(Value::Decimal)
345}
346
347fn statistical_standard_deviation(
348    accumulator: &AggregateAccumulator,
349    sample: bool,
350) -> Result<Value, SQLError> {
351    if accumulator.statistics_has_float {
352        let divisor = if sample {
353            accumulator.statistics_count as f64 - 1.0
354        } else {
355            accumulator.statistics_count as f64
356        };
357        return Ok(Value::Float((accumulator.statistics_m2 / divisor).sqrt()));
358    }
359    let variance = numeric_statistical_variance(accumulator, sample)?;
360    if variance.is_nan() {
361        return Ok(Value::Decimal(variance));
362    }
363    let scale = variance
364        .display_scale()
365        .ok_or_else(|| SQLError::TypeMismatch("numeric standard deviation is not finite".into()))?;
366    variance
367        .sqrt_to_scale(scale)
368        .map(Value::Decimal)
369        .ok_or_else(|| SQLError::TypeMismatch("numeric standard deviation is undefined".into()))
370}
371
372fn numeric_statistical_variance(
373    accumulator: &AggregateAccumulator,
374    sample: bool,
375) -> Result<DecimalValue, SQLError> {
376    let count = DecimalValue::from_i128(i128::from(accumulator.statistics_count))
377        .ok_or_else(|| SQLError::TypeMismatch("statistical aggregate count overflow".into()))?;
378    let sum = accumulator.statistics_sum.as_ref().ok_or_else(|| {
379        SQLError::Internal("numeric statistical aggregate omitted its sum".into())
380    })?;
381    let sum_squares = accumulator.statistics_sum_squares.as_ref().ok_or_else(|| {
382        SQLError::Internal("numeric statistical aggregate omitted its square sum".into())
383    })?;
384    let numerator = count
385        .checked_mul(sum_squares)
386        .and_then(|total| {
387            sum.checked_mul(sum)
388                .and_then(|square| total.checked_sub(&square))
389        })
390        .ok_or_else(|| SQLError::TypeMismatch("numeric statistical result overflow".into()))?;
391    if numerator <= DecimalValue::from_i64(0) && !accumulator.statistics_nonzero_deviation {
392        return Ok(DecimalValue::from_i64(0));
393    }
394    let denominator = if sample {
395        let count_minus_one = DecimalValue::from_i128(i128::from(accumulator.statistics_count - 1))
396            .ok_or_else(|| SQLError::TypeMismatch("statistical aggregate count overflow".into()))?;
397        count.checked_mul(&count_minus_one)
398    } else {
399        count.checked_mul(&count)
400    }
401    .ok_or_else(|| SQLError::TypeMismatch("numeric statistical divisor overflow".into()))?;
402    let variance = numerator
403        .checked_div_postgres(&denominator)
404        .ok_or_else(|| SQLError::TypeMismatch("numeric statistical division failed".into()))?;
405    if variance <= DecimalValue::from_i64(0) {
406        return variance.checked_sub(&variance).ok_or_else(|| {
407            SQLError::TypeMismatch("numeric statistical zero normalization failed".into())
408        });
409    }
410    Ok(variance)
411}
412
413pub fn percentile_cont(values: &AggregateValueBuffer, frac: f64) -> Result<Option<f64>, SQLError> {
414    percentile_cont_with_catalog(values, frac, None)
415}
416
417fn percentile_cont_with_catalog(
418    values: &AggregateValueBuffer,
419    frac: f64,
420    enums: Option<&dyn EnumLabelCatalog>,
421) -> Result<Option<f64>, SQLError> {
422    if values.next_sequence == 0 {
423        return Ok(None);
424    }
425    let position = frac * (values.next_sequence as f64 - 1.0);
426    let low = position.floor() as u64;
427    let high = position.ceil() as u64;
428    let mut low_value = None;
429    let mut high_value = None;
430    let mut index = 0_u64;
431    values.for_each_ordered(enums, |record| {
432        if index == low || index == high {
433            let value = super::observation::read(&record.value, enums)?;
434            let value = value_as_f64(&value)?;
435            if index == low {
436                low_value = Some(value);
437            }
438            if index == high {
439                high_value = Some(value);
440            }
441        }
442        index = index
443            .checked_add(1)
444            .ok_or_else(|| SQLError::Internal("percentile aggregate index overflow".into()))?;
445        Ok(())
446    })?;
447    let low_value = low_value.ok_or_else(|| {
448        SQLError::Internal("percentile lower value missing from aggregate spill".into())
449    })?;
450    let high_value = high_value.ok_or_else(|| {
451        SQLError::Internal("percentile upper value missing from aggregate spill".into())
452    })?;
453    let weight = position - low as f64;
454    Ok(Some(low_value * (1.0 - weight) + high_value * weight))
455}
456
457pub fn percentile_disc(
458    values: &AggregateValueBuffer,
459    frac: f64,
460) -> Result<Option<Value>, SQLError> {
461    percentile_disc_with_catalog(values, frac, None)
462}
463
464fn percentile_disc_with_catalog(
465    values: &AggregateValueBuffer,
466    frac: f64,
467    enums: Option<&dyn EnumLabelCatalog>,
468) -> Result<Option<Value>, SQLError> {
469    if values.next_sequence == 0 {
470        return Ok(None);
471    }
472    let rank = ((frac * values.next_sequence as f64).ceil() as u64)
473        .max(1)
474        .min(values.next_sequence);
475    let mut value = None;
476    let mut index = 0_u64;
477    values.for_each_ordered(enums, |record| {
478        index = index
479            .checked_add(1)
480            .ok_or_else(|| SQLError::Internal("percentile aggregate index overflow".into()))?;
481        if index == rank {
482            value = Some(record.value);
483        }
484        Ok(())
485    })?;
486    Ok(value)
487}
488
489pub fn mode_value(values: &AggregateValueBuffer) -> Result<Value, SQLError> {
490    mode_value_with_catalog(values, None)
491}
492
493fn mode_value_with_catalog(
494    values: &AggregateValueBuffer,
495    enums: Option<&dyn EnumLabelCatalog>,
496) -> Result<Value, SQLError> {
497    if values.next_sequence == 0 {
498        return Ok(Value::Null);
499    }
500    let mut current_value = Value::Null;
501    let mut current_count = 0_u64;
502    let mut best_value = Value::Null;
503    let mut best_count = 0_u64;
504    values.for_each_ordered(enums, |record| {
505        // Group with the same equality as sorting; retain the first group on ties.
506        if current_count != 0
507            && !uqa_sql::expr::equal_selected_values_with_catalog(
508                &current_value,
509                &record.value,
510                values
511                    .key_types
512                    .as_deref()
513                    .and_then(|types| types.first())
514                    .and_then(Option::as_ref),
515                &uqa_core::memory::ProductionControl::uncontrolled(),
516                enums,
517            )?
518        {
519            if current_count > best_count {
520                best_count = current_count;
521                best_value = current_value.clone();
522            }
523            current_count = 0;
524        }
525        if current_count == 0 {
526            current_value = record.value;
527        }
528        current_count = current_count
529            .checked_add(1)
530            .ok_or_else(|| SQLError::Internal("mode aggregate count overflow".into()))?;
531        Ok(())
532    })?;
533    if current_count > best_count {
534        best_value = current_value;
535    }
536    Ok(best_value)
537}