Skip to content

v0.25.0 Having

Choose a tag to compare

@marcgrue marcgrue released this 01 Aug 22:32
· 65 commits to main since this release

Molecule now supports operations on aggregated values - what having in SQL is for.

So we would for instance find number of Customers in Countries that have more than 5 customers with the following SQL query:

SELECT COUNT(CustomerID), Country
FROM Customers
  GROUP BY Country
HAVING COUNT(CustomerID) > 5;

We can now express this with a molecule:

Customer.id(count).>(5).country

Operations on aggregation values

Customer.id(count)(5).country     // Countries with 5 customers
Customer.id(count).not(5).country // Countries not with 5 customers
Customer.id(count).<(5).country   // Countries with less than 5 customers
Customer.id(count).>(5).country   // Countries with more than 5 customers
Customer.id(count).<=(5).country  // Countries with maximum 5 customers
Customer.id(count).>=(5).country  // Countries with minimum 5 customers

Sorting of aggregated values

And once we have filtered the aggregated value, we can also sort by the aggregated values:

Customer.id(count).not(5).d1.country // Countries not with 5 customers, descending customer count
Customer.id(count).<(5).a1.country   // Countries with less than 5 customers, ascending customer count
Customer.id(count).>(5).d1.country   // Countries with more than 5 customers, descending customer count
Customer.id(count).<=(5).a1.country  // Countries with maximum 5 customers, ascending customer count
Customer.id(count).>=(5).d1.country  // Countries with minimum 5 customers, descending customer count

Aggregations with operations

The following aggregate functions with optional operations are available for all cardinality-one attributes:

count
countDistinct
min
max
sample

The following aggregate functions with optional operations are available for all cardinality-one number attributes:

sum
median
avg
variance
stddev

Aggregations without operations

Aggregated Sets of values don't have operations:

min(n)
max(n)
samples(n)
distinct