Skip to content

Advanced features

This page documents other advanced features that beam supports across backends that support them.

SQL2003 T611: Elementary OLAP operations

This optional SQL2003 feature allows attaching arbitrary FILTER (WHERE ..) clauses to aggregates. During querying only rows matching the given expression are included in computing the aggregate. This can often be simulated in other databases by an appropriate CASE expression, but beam will not do this translation.

aggregate_ (\i -> (group_ (invoiceCustomer i), as_ @Int32 $ countAll_ `filterWhere_` (invoiceTotal i >. 500), as_ @Int32 $ countAll_ `filterWhere_` (invoiceTotal i <. 100))) $
all_ (invoice chinookDb)

SELECT "t0"."CustomerId" AS "res0",
       COUNT(*) FILTER (
                        WHERE ("t0"."Total") > ('500.0')) AS "res1",
       COUNT(*) FILTER (
                        WHERE ("t0"."Total") < ('100.0')) AS "res2"
FROM "Invoice" AS "t0"
GROUP BY "t0"."CustomerId"

These combine as you'd expect with window functions. For example, to return each invoice along with the average total of all invoices by the same customer where the invoice was billed to an address in Los Angeles,

withWindow_ (\i -> frame_ (partitionBy_ (invoiceCustomer i)) noOrder_ noBounds_)
            (\i w -> (i, avg_ (invoiceTotal i) `filterWhere_` (addressCity (invoiceBillingAddress i) ==. just_ "Los Angeles") `over_` w))
            (all_ (invoice chinookDb))

SELECT "t0"."InvoiceId" AS "res0",
       "t0"."CustomerId" AS "res1",
       "t0"."InvoiceDate" AS "res2",
       "t0"."BillingAddress" AS "res3",
       "t0"."BillingCity" AS "res4",
       "t0"."BillingState" AS "res5",
       "t0"."BillingCountry" AS "res6",
       "t0"."BillingPostalCode" AS "res7",
       "t0"."Total" AS "res8",
       AVG("t0"."Total") FILTER (
                                 WHERE ("t0"."BillingCity") IS NOT DISTINCT
                                   FROM ('Los Angeles')) OVER (PARTITION BY "t0"."CustomerId") AS "res9"
FROM "Invoice" AS "t0"

Danger"

FILTER (WHERE ..) must be applied directly to a SQL aggregate function, but this isn't enforced at compile time. This may be fixed in a later version of beam.

This extension also provides various window functions for SQL. The only one beam currently implements is RANK() via the rank_ function. Contributions are appreciated!

Null Ordering

This optional SQL2003 feature allows nulls to appear before or after non-null values in the sort ordering.

limit_ 10 $
orderBy_ (\e -> (asc_ (addressState (employeeAddress e)), nullsLast_ (desc_ (addressCity (employeeAddress e))))) $
all_ (employee chinookDb)

SELECT "t0"."EmployeeId" AS "res0",
       "t0"."LastName" AS "res1",
       "t0"."FirstName" AS "res2",
       "t0"."Title" AS "res3",
       "t0"."ReportsTo" AS "res4",
       "t0"."BirthDate" AS "res5",
       "t0"."HireDate" AS "res6",
       "t0"."Address" AS "res7",
       "t0"."City" AS "res8",
       "t0"."State" AS "res9",
       "t0"."Country" AS "res10",
       "t0"."PostalCode" AS "res11",
       "t0"."Phone" AS "res12",
       "t0"."Fax" AS "res13",
       "t0"."Email" AS "res14"
FROM "Employee" AS "t0"
ORDER BY "t0"."State" ASC,
         "t0"."City" DESC NULLS LAST
LIMIT 10

SQL2003 T612: Advanced OLAP operations

This provides both the PERCENT_RANK() and CUME_DIST() functions as percentRank_ and cumeDist_ respectively.