Aggregates

ASTQL supports SQL aggregate functions, GROUP BY, HAVING, and window functions.

Aggregate Functions

Basic Aggregates

Function	SQL	Description
`Sum(field)`	`SUM("field")`	Sum of values
`Avg(field)`	`AVG("field")`	Average of values
`Min(field)`	`MIN("field")`	Minimum value
`Max(field)`	`MAX("field")`	Maximum value
`CountField(field)`	`COUNT("field")`	Count of non-null values
`CountDistinct(field)`	`COUNT(DISTINCT "field")`	Count of unique values

Using Aggregates

result, _ := astql.Select(instance.T("orders")).
    Fields(instance.F("user_id")).
    SelectExpr(astql.As(astql.Sum(instance.F("total")), "total_spent")).
    SelectExpr(astql.As(astql.CountField(instance.F("id")), "order_count")).
    GroupBy(instance.F("user_id")).
    Render()

// SELECT "user_id", SUM("total") AS "total_spent", COUNT("id") AS "order_count"
// FROM "orders"
// GROUP BY "user_id"

Aliases

Use As() to add an alias:

astql.As(astql.Sum(instance.F("amount")), "total_amount")
// SUM("amount") AS "total_amount"

GROUP BY

Group results by one or more fields:

result, _ := astql.Select(instance.T("orders")).
    Fields(instance.F("user_id"), instance.F("status")).
    SelectExpr(astql.As(astql.CountField(instance.F("id")), "count")).
    GroupBy(instance.F("user_id"), instance.F("status")).
    Render()

// SELECT "user_id", "status", COUNT("id") AS "count"
// FROM "orders"
// GROUP BY "user_id", "status"

HAVING

Filter grouped results:

Simple HAVING

result, _ := astql.Select(instance.T("orders")).
    Fields(instance.F("user_id")).
    SelectExpr(astql.As(astql.Sum(instance.F("total")), "sum")).
    GroupBy(instance.F("user_id")).
    Having(instance.C(instance.F("total"), astql.GT, instance.P("min_total"))).
    Render()

// SELECT "user_id", SUM("total") AS "sum"
// FROM "orders"
// GROUP BY "user_id"
// HAVING "total" > :min_total

Aggregate HAVING

For conditions on aggregate functions, use HavingAgg:

result, _ := astql.Select(instance.T("orders")).
    Fields(instance.F("user_id")).
    SelectExpr(astql.As(astql.CountField(instance.F("id")), "order_count")).
    GroupBy(instance.F("user_id")).
    HavingAgg(astql.HavingCount(astql.GT, instance.P("min_orders"))).
    Render()

// SELECT "user_id", COUNT("id") AS "order_count"
// FROM "orders"
// GROUP BY "user_id"
// HAVING COUNT(*) > :min_orders

HAVING Helpers

Function	SQL
`HavingCount(op, param)`	`COUNT(*) op :param`
`HavingCountField(field, op, param)`	`COUNT("field") op :param`
`HavingCountDistinct(field, op, param)`	`COUNT(DISTINCT "field") op :param`
`HavingSum(field, op, param)`	`SUM("field") op :param`
`HavingAvg(field, op, param)`	`AVG("field") op :param`
`HavingMin(field, op, param)`	`MIN("field") op :param`
`HavingMax(field, op, param)`	`MAX("field") op :param`

FILTER Clause

PostgreSQL's FILTER clause for conditional aggregation:

result, _ := astql.Select(instance.T("orders")).
    Fields(instance.F("user_id")).
    SelectExpr(astql.As(
        astql.SumFilter(
            instance.F("total"),
            instance.C(instance.F("status"), astql.EQ, instance.P("completed")),
        ),
        "completed_total",
    )).
    GroupBy(instance.F("user_id")).
    Render()

// SELECT "user_id",
//        SUM("total") FILTER (WHERE "status" = :completed) AS "completed_total"
// FROM "orders"
// GROUP BY "user_id"

Available filter variants:

SumFilter(field, condition)
AvgFilter(field, condition)
MinFilter(field, condition)
MaxFilter(field, condition)
CountFieldFilter(field, condition)
CountDistinctFilter(field, condition)

Window Functions

Window functions compute values across related rows.

Basic Window Functions

result, _ := astql.Select(instance.T("employees")).
    Fields(instance.F("name"), instance.F("department"), instance.F("salary")).
    SelectExpr(
        astql.RowNumber().
            OverBuilder(astql.Window().
                PartitionBy(instance.F("department")).
                OrderBy(instance.F("salary"), astql.DESC)).
            As("rank"),
    ).
    Render()

// SELECT "name", "department", "salary",
//        ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
// FROM "employees"

Window Function Types

Function	SQL
`RowNumber()`	`ROW_NUMBER()`
`Rank()`	`RANK()`
`DenseRank()`	`DENSE_RANK()`
`Ntile(param)`	`NTILE(:param)`
`Lag(field, offset)` or `Lag(field, offset, default)`	`LAG(field, :offset)` or `LAG(field, :offset, :default)`
`Lead(field, offset)` or `Lead(field, offset, default)`	`LEAD(field, :offset)` or `LEAD(field, :offset, :default)`
`FirstValue(field)`	`FIRST_VALUE(field)`
`LastValue(field)`	`LAST_VALUE(field)`

Aggregate Window Functions

// Running total
astql.SumOver(instance.F("amount")).
    OverBuilder(astql.Window().
        PartitionBy(instance.F("account_id")).
        OrderBy(instance.F("date"), astql.ASC)).
    As("running_total")

// SUM("amount") OVER (PARTITION BY "account_id" ORDER BY "date" ASC) AS "running_total"

Available: SumOver, AvgOver, CountOver, MinOver, MaxOver

Window Specification

Build window specs with Window():

spec := astql.Window().
    PartitionBy(instance.F("department")).
    OrderBy(instance.F("hire_date"), astql.ASC).
    OrderByNulls(instance.F("salary"), astql.DESC, astql.NullsLast).
    Rows(astql.FrameUnboundedPreceding, astql.FrameCurrentRow).
    Build()

Frame Bounds

Constant	SQL
`FrameUnboundedPreceding`	`UNBOUNDED PRECEDING`
`FrameCurrentRow`	`CURRENT ROW`
`FrameUnboundedFollowing`	`UNBOUNDED FOLLOWING`

Math Functions

Available Functions

Function	SQL
`Round(field)` or `Round(field, precision)`	`ROUND("field")` or `ROUND("field", :precision)`
`Floor(field)`	`FLOOR("field")`
`Ceil(field)`	`CEIL("field")`
`Abs(field)`	`ABS("field")`
`Power(field, exponent)`	`POWER("field", :exponent)`
`Sqrt(field)`	`SQRT("field")`

Example

result, _ := astql.Select(instance.T("products")).
    SelectExpr(astql.As(astql.Round(instance.F("price"), instance.P("decimals")), "rounded")).
    SelectExpr(astql.As(astql.Floor(instance.F("rating")), "floor_rating")).
    Render()

// SELECT ROUND("price", :decimals) AS "rounded",
//        FLOOR("rating") AS "floor_rating"
// FROM "products"

CASE Expressions

Conditional logic in SELECT:

caseExpr := astql.Case().
    When(
        instance.C(instance.F("score"), astql.GE, instance.P("high")),
        instance.P("grade_a"),
    ).
    When(
        instance.C(instance.F("score"), astql.GE, instance.P("mid")),
        instance.P("grade_b"),
    ).
    Else(instance.P("grade_c")).
    As("grade").
    Build()

result, _ := astql.Select(instance.T("students")).
    Fields(instance.F("name")).
    SelectExpr(caseExpr).
    Render()

// SELECT "name",
//        CASE WHEN "score" >= :high THEN :grade_a
//             WHEN "score" >= :mid THEN :grade_b
//             ELSE :grade_c END AS "grade"
// FROM "students"

Type Casting

Cast fields to different types:

astql.Cast(instance.F("created_at"), astql.CastDate)
// CAST("created_at" AS DATE)

Available cast types: CastText, CastInteger, CastBigint, CastNumeric, CastBoolean, CastDate, CastTimestamp, CastTimestampTZ, CastUUID, CastJSON, CastJSONB, and more.

COALESCE and NULLIF

// COALESCE - first non-null value
astql.Coalesce(instance.P("preferred"), instance.P("default"))
// COALESCE(:preferred, :default)

// NULLIF - returns NULL if values are equal
astql.NullIf(instance.P("value"), instance.P("sentinel"))
// NULLIF(:value, :sentinel)