Aggregate and Window Functions

TidierDB supports all aggregate functions accross the supported databases, as well as window functions.

Aggregate Functions

@summarize, by default, supports all aggregate functions built into a SQL database, with the exception that any ' that would be used in SQL should be replaced wiht ".

using TidierDB
db = connect(duckdb());
mtcars_path = "https://gist.githubusercontent.com/seankross/a412dfbd88b3db70b74b/raw/5f23f993cd87c283ce766e7ac6b329ee7cc2e1d1/mtcars.csv";
mtcars = dt(db, mtcars_path);

Aggregate Functions in @summarize

Lets use the DuckDB kurtosis aggregate function

@chain mtcars begin
     @group_by cyl
     @summarize(kurt = kurtosis(mpg))
     @collect
end

3×2 DataFrame

Row	cyl	kurt
	Int64	Float64
1	6	-1.82944
2	4	-1.43411
3	8	0.330061

Aggregate Functions in @mutate

By default, @mutate/@transmute supports (however, you can easily expand this list)

• maximum, minimum, mean, std, sum, cumsum

To use aggregate sql functions that are built in to any database backend, but exist outside of the TidierDB parser list above, simply prefix the function with ~ or wrap the function call in agg()

@chain mtcars begin
     @group_by(cyl)
     @mutate(kurt = ~kurtosis(mpg))
     @select cyl mpg kurt
     @head()
     @collect
end

6×3 DataFrame

Row	cyl	mpg	kurt
	Int64	Float64	Float64
1	6	21.0	-1.82944
2	6	21.0	-1.82944
3	6	21.4	-1.82944
4	6	18.1	-1.82944
5	6	19.2	-1.82944
6	6	17.8	-1.82944

Alternatively , if you anticipate regularly using specific aggregate functions, you can update the underlying parser avoid using agg all together

push!(TidierDB.window_agg_fxns, :kurtosis);
@chain mtcars begin
     @group_by(cyl)
     @mutate(kurt = kurtosis(mpg))
     @select cyl mpg kurt
     @head()
     @collect
end

6×3 DataFrame

Row	cyl	mpg	kurt
	Int64	Float64	Float64
1	6	21.0	-1.82944
2	6	21.0	-1.82944
3	6	21.4	-1.82944
4	6	18.1	-1.82944
5	6	19.2	-1.82944
6	6	17.8	-1.82944

Window Functions

TidierDB's @mutate/@transmute support all of the window functions below

• lead, lag, dense_rank, nth_value, ntile, rank_dense, row_number, first_value, last_value, cume_dist

When ordering a window function, @arrange should not be used. Rather, use @window_order or, preferably, _order and _frame in @mutate.

@chain mtcars begin
    @mutate(row_id = row_number(),
        _by = cyl,
        _order = mpg # _frame is not used in this example
        )
    @head()
    @collect
end

6×13 DataFrame

Row	model	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb	row_id
	String	Float64	Int64	Float64	Int64	Float64	Float64	Float64	Int64	Int64	Int64	Int64	Int64
1	Merc 280C	17.8	6	167.6	123	3.92	3.44	18.9	1	0	4	4	1
2	Valiant	18.1	6	225.0	105	2.76	3.46	20.22	1	0	3	1	2
3	Merc 280	19.2	6	167.6	123	3.92	3.44	18.3	1	0	4	4	3
4	Ferrari Dino	19.7	6	145.0	175	3.62	2.77	15.5	0	1	5	6	4
5	Mazda RX4	21.0	6	160.0	110	3.9	2.62	16.46	0	1	4	4	5
6	Mazda RX4 Wag	21.0	6	160.0	110	3.9	2.875	17.02	0	1	4	4	6

The above query could have alternatively been written as

@chain mtcars begin
    @group_by cyl
    @window_order mpg
    @mutate(row_id = row_number())
    @head()
    @collect
end

6×13 DataFrame

Row	model	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb	row_id
	String	Float64	Int64	Float64	Int64	Float64	Float64	Float64	Int64	Int64	Int64	Int64	Int64
1	Merc 280C	17.8	6	167.6	123	3.92	3.44	18.9	1	0	4	4	1
2	Valiant	18.1	6	225.0	105	2.76	3.46	20.22	1	0	3	1	2
3	Merc 280	19.2	6	167.6	123	3.92	3.44	18.3	1	0	4	4	3
4	Ferrari Dino	19.7	6	145.0	175	3.62	2.77	15.5	0	1	5	6	4
5	Mazda RX4	21.0	6	160.0	110	3.9	2.62	16.46	0	1	4	4	5
6	Mazda RX4 Wag	21.0	6	160.0	110	3.9	2.875	17.02	0	1	4	4	6

---

This page was generated using Literate.jl.