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 = db_table(db, mtcars_path);

Aggregate Functions in @summarize

Lets use the DuckDB kurtosis aggregate function

@chain t(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 wrap the function call in agg()

@chain t(mtcars) begin
     @group_by(cyl)
     @mutate(kurt = agg(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 t(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 t(mtcars) begin
    @mutate(row_id = row_number(),
        _by = cyl,
        _order = mpg # _frame is not used in this example
        )
    @collect
end

32×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
7	Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	7
8	Volvo 142E	21.4	4	121.0	109	4.11	2.78	18.6	1	1	4	2	1
9	Toyota Corona	21.5	4	120.1	97	3.7	2.465	20.01	1	0	3	1	2
10	Datsun 710	22.8	4	108.0	93	3.85	2.32	18.61	1	1	4	1	3
11	Merc 230	22.8	4	140.8	95	3.92	3.15	22.9	1	0	4	2	4
12	Merc 240D	24.4	4	146.7	62	3.69	3.19	20.0	1	0	4	2	5
13	Porsche 914-2	26.0	4	120.3	91	4.43	2.14	16.7	0	1	5	2	6
14	Fiat X1-9	27.3	4	79.0	66	4.08	1.935	18.9	1	1	4	1	7
15	Honda Civic	30.4	4	75.7	52	4.93	1.615	18.52	1	1	4	2	8
16	Lotus Europa	30.4	4	95.1	113	3.77	1.513	16.9	1	1	5	2	9
17	Fiat 128	32.4	4	78.7	66	4.08	2.2	19.47	1	1	4	1	10
18	Toyota Corolla	33.9	4	71.1	65	4.22	1.835	19.9	1	1	4	1	11
19	Cadillac Fleetwood	10.4	8	472.0	205	2.93	5.25	17.98	0	0	3	4	1
20	Lincoln Continental	10.4	8	460.0	215	3.0	5.424	17.82	0	0	3	4	2
21	Camaro Z28	13.3	8	350.0	245	3.73	3.84	15.41	0	0	3	4	3
22	Duster 360	14.3	8	360.0	245	3.21	3.57	15.84	0	0	3	4	4
23	Chrysler Imperial	14.7	8	440.0	230	3.23	5.345	17.42	0	0	3	4	5
24	Maserati Bora	15.0	8	301.0	335	3.54	3.57	14.6	0	1	5	8	6
25	Merc 450SLC	15.2	8	275.8	180	3.07	3.78	18.0	0	0	3	3	7
26	AMC Javelin	15.2	8	304.0	150	3.15	3.435	17.3	0	0	3	2	8
27	Dodge Challenger	15.5	8	318.0	150	2.76	3.52	16.87	0	0	3	2	9
28	Ford Pantera L	15.8	8	351.0	264	4.22	3.17	14.5	0	1	5	4	10
29	Merc 450SE	16.4	8	275.8	180	3.07	4.07	17.4	0	0	3	3	11
30	Merc 450SL	17.3	8	275.8	180	3.07	3.73	17.6	0	0	3	3	12
31	Hornet Sportabout	18.7	8	360.0	175	3.15	3.44	17.02	0	0	3	2	13
32	Pontiac Firebird	19.2	8	400.0	175	3.08	3.845	17.05	0	0	3	2	14

The above query could have alternatively been written as

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

32×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
7	Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	7
8	Volvo 142E	21.4	4	121.0	109	4.11	2.78	18.6	1	1	4	2	1
9	Toyota Corona	21.5	4	120.1	97	3.7	2.465	20.01	1	0	3	1	2
10	Datsun 710	22.8	4	108.0	93	3.85	2.32	18.61	1	1	4	1	3
11	Merc 230	22.8	4	140.8	95	3.92	3.15	22.9	1	0	4	2	4
12	Merc 240D	24.4	4	146.7	62	3.69	3.19	20.0	1	0	4	2	5
13	Porsche 914-2	26.0	4	120.3	91	4.43	2.14	16.7	0	1	5	2	6
14	Fiat X1-9	27.3	4	79.0	66	4.08	1.935	18.9	1	1	4	1	7
15	Honda Civic	30.4	4	75.7	52	4.93	1.615	18.52	1	1	4	2	8
16	Lotus Europa	30.4	4	95.1	113	3.77	1.513	16.9	1	1	5	2	9
17	Fiat 128	32.4	4	78.7	66	4.08	2.2	19.47	1	1	4	1	10
18	Toyota Corolla	33.9	4	71.1	65	4.22	1.835	19.9	1	1	4	1	11
19	Cadillac Fleetwood	10.4	8	472.0	205	2.93	5.25	17.98	0	0	3	4	1
20	Lincoln Continental	10.4	8	460.0	215	3.0	5.424	17.82	0	0	3	4	2
21	Camaro Z28	13.3	8	350.0	245	3.73	3.84	15.41	0	0	3	4	3
22	Duster 360	14.3	8	360.0	245	3.21	3.57	15.84	0	0	3	4	4
23	Chrysler Imperial	14.7	8	440.0	230	3.23	5.345	17.42	0	0	3	4	5
24	Maserati Bora	15.0	8	301.0	335	3.54	3.57	14.6	0	1	5	8	6
25	Merc 450SLC	15.2	8	275.8	180	3.07	3.78	18.0	0	0	3	3	7
26	AMC Javelin	15.2	8	304.0	150	3.15	3.435	17.3	0	0	3	2	8
27	Dodge Challenger	15.5	8	318.0	150	2.76	3.52	16.87	0	0	3	2	9
28	Ford Pantera L	15.8	8	351.0	264	4.22	3.17	14.5	0	1	5	4	10
29	Merc 450SE	16.4	8	275.8	180	3.07	4.07	17.4	0	0	3	3	11
30	Merc 450SL	17.3	8	275.8	180	3.07	3.73	17.6	0	0	3	3	12
31	Hornet Sportabout	18.7	8	360.0	175	3.15	3.44	17.02	0	0	3	2	13
32	Pontiac Firebird	19.2	8	400.0	175	3.08	3.845	17.05	0	0	3	2	14

---

This page was generated using Literate.jl.