Skip to content

Apache DataFusion SQL Query Engine

5.5 KB
Raw Blame
// SPDX-License-Identifier: Apache-2.0
// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership
// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements

//! See `main.rs` for how to run it.

use datafusion::error::Result;
use datafusion::functions_aggregate::expr_fn::{avg, count, max, min};
use datafusion::functions_aggregate::min_max::max_udaf;
use datafusion::logical_expr::expr::WindowFunction;
use datafusion::prelude::*;
use datafusion_examples::utils::datasets::ExampleDataset;

/// This example demonstrates aggregation with the DataFrame API.
///
/// `DataFrame::select` accepts aggregate expressions: a list made only of
/// aggregates (and scalar expressions over them) is planned as a global
/// aggregation, exactly like `aggregate(vec![], ...)`. Grouping still goes
/// through `aggregate(group_by, aggs)`.
///
/// * [global_aggregate]: `aggregate(vec![], aggs)` vs `select(aggs)`
/// * [expressions_over_aggregates]: compute on top of aggregates in a single `select`
/// * [grouped_aggregate]: GROUP BY uses `aggregate`; `select` never infers a GROUP BY
/// * [aggregate_vs_window]: aggregate (one row) vs window (one row per input row)
pub async fn aggregate_example() -> Result<()> {
    let ctx = SessionContext::new();
    let dataset = ExampleDataset::Cars;
    ctx.register_csv("cars", dataset.path_str()?, CsvReadOptions::default())
        .await?;

    global_aggregate(&ctx).await?;
    expressions_over_aggregates(&ctx).await?;
    grouped_aggregate(&ctx).await?;
    aggregate_vs_window(&ctx).await?;
    Ok(())
}

/// SELECT count(car) AS n,
///        avg(speed) AS avg_speed,
///        max(speed) AS max_speed
/// FROM cars
async fn global_aggregate(ctx: &SessionContext) -> Result<()> {
    let df = ctx.table("cars").await?;
    let aggs = vec![
        count(col("car")).alias("n"),
        avg(col("speed")).alias("avg_speed"),
        max(col("speed")).alias("max_speed"),
    ];

    // The explicit form: `aggregate` with no group expressions
    println!("global aggregation via aggregate(vec![], aggs)");
    df.clone().aggregate(vec![], aggs.clone())?.show().await?;

    // The same query via `select`: a list containing only aggregates is a
    // global aggregation and returns exactly one row
    println!("global aggregation via select(aggs)");
    df.select(aggs)?.show().await?;

    Ok(())
}

/// SELECT max(speed) - min(speed) AS speed_range,
///        avg(speed) * 3.6        AS avg_speed_kmh
/// FROM cars
async fn expressions_over_aggregates(ctx: &SessionContext) -> Result<()> {
    let df = ctx.table("cars").await?;

    // Two steps: `aggregate` computes the aggregates, then `select` builds
    // expressions over their output columns, referenced by alias
    println!("expressions over aggregates via aggregate(...).select(...)");
    df.clone()
        .aggregate(
            vec![],
            vec![
                max(col("speed")).alias("max_speed"),
                min(col("speed")).alias("min_speed"),
                avg(col("speed")).alias("avg_speed"),
            ],
        )?
        .select(vec![
            (col("max_speed") - col("min_speed")).alias("speed_range"),
            (col("avg_speed") * lit(3.6)).alias("avg_speed_kmh"),
        ])?
        .show()
        .await?;

    // One step: `select` accepts scalar expressions over aggregates directly,
    // so no intermediate aliases and no second call are needed
    println!("expressions over aggregates via select(...)");
    df.select(vec![
        (max(col("speed")) - min(col("speed"))).alias("speed_range"),
        (avg(col("speed")) * lit(3.6)).alias("avg_speed_kmh"),
    ])?
    .show()
    .await?;

    Ok(())
}

/// SELECT car,
///        max(speed) AS max_speed
/// FROM cars
/// GROUP BY car
async fn grouped_aggregate(ctx: &SessionContext) -> Result<()> {
    let df = ctx.table("cars").await?;

    // Grouping is always explicit via `aggregate(group_by, aggs)`
    println!("grouped aggregation via aggregate(group_by, aggs)");
    df.aggregate(vec![col("car")], vec![max(col("speed")).alias("max_speed")])?
        .show()
        .await?;

    // `select` never infers a GROUP BY from the non-aggregated columns:
    //
    //     df.select(vec![col("car"), max(col("speed"))])
    //
    // is planned as an ordinary projection and fails when executed with
    // "Aggregate function 'max(cars.speed)' is not supported in this position".
    // Use `aggregate(group_by, aggs)` as above for grouped results.

    Ok(())
}

/// SELECT car,
///        speed,
///        max(speed) OVER (PARTITION BY car) AS max_speed
/// FROM cars
async fn aggregate_vs_window(ctx: &SessionContext) -> Result<()> {
    let df = ctx.table("cars").await?;

    // An aggregate in `select` collapses the input to a single row ...
    println!("aggregate in select: one row for the whole table");
    df.clone()
        .select(vec![max(col("speed")).alias("max_speed")])?
        .show()
        .await?;

    // ... whereas the same function as a window expression keeps every input
    // row and attaches the per-partition value to each of them
    let max_over_car = Expr::WindowFunction(Box::new(WindowFunction::new(
        max_udaf(),
        vec![col("speed")],
    )))
    .partition_by(vec![col("car")])
    .build()?;

    println!("window function in select: one row per input row");
    df.select(vec![
        col("car"),
        col("speed"),
        max_over_car.alias("max_speed"),
    ])?
    .show()
    .await?;

    Ok(())
}