SQL in a Nutshell, 4th Edition

Chapter 8. SQL Built-in Aggregate and Window Functions

In the previous chapter we discussed the different kinds of functions that exist in a database and focused our attention on scalar and set-returning (table) functions. In this chapter we will delve into the use of aggregate and window functions and detail which ones are defined in the SQL standard, providing detailed descriptions and examples for each platform. In addition, each database supports a long set of its own internal window and aggregate functions that are outside the scope of the SQL standard; listings of these functions for each vendor are provided at the end of each chapter.

In the case of MariaDB and MySQL, we will refer only to MySQL except in cases where MariaDB deviates from MySQL behavior.

Many database platforms also support the ability to create user-defined aggregate and window functions (UDFs). For more information on UDFs, refer to Chapter 9. JSON and XML functions are covered in Chapter 10 and will not be discussed in this chapter.

How to Use This Chapter

When researching a function in this chapter:

1. Check the SQL syntax and read the description, even if you are looking for a specific platform implementation.

2. Read the platform-specific implementation information.

Elements of the vendors’ implementations that do not differ from the standard are not discussed in the individual platform sections; these sections only call out any differences from the standard. For details not provided in a particular ...