4 Advanced aggregation and analysis of data

This chapter covers

Preparing, cleaning and aggregating data while ingesting
Using window functions to create new aggregates over different partitions of any dataset
Understanding the different types of sub-queries
Using Common Table Expressions (CTEs)
Applying filters to any aggregate

The goal of this chapter is to give you some ideas on how an analytical database such as DuckDB can be used to provide reports that would take a considerably larger amount of code written in an imperative programming language. While we will build upon the foundation laid in chapter 3, we will leave a simple SELECT xzy FROM abc behind quickly. Investing your time in learning modern SQL won’t be wasted. The constructs presented here can be used everywhere where DuckDB can be run or embedded and therefore enrich your application.

4.1 Pre-aggregate data while ingesting

4.2 Summarizing data

4.3 On sub-queries

4.3.1 Sub-queries as expressions

4.4 Grouping sets

4.5 Window functions

4.5.1 Defining partitions

4.5.2 Framing

4.5.3 Named windows

4.5.4 Accessing preceding or following rows in a partition

4.6 Conditions and filtering outside the WHERE clause

4.6.1 Using the HAVING clause

4.6.2 Using the QUALIFY clause

4.6.3 Using the FILTER clause

4.7 The PIVOT statement

4.8 Using the ASOF JOIN ("as of")

4.9 Using Table functions

4.10 Using LATERAL joins

4.11 Summary