API with NestJS #78. Generating statistics using aggregate functions in raw SQL

October 10, 2022

This entry is part 78 of 121 in the API with NestJS

So far, we’ve been mostly writing SQL queries that either store or retrieve the data from the database. Besides that, we can rely on PostgreSQL to process the data and get the computed results. By doing that, we can learn more about the rows in our tables. In this article, we look into how we can use aggregate functions to generate statistics about our data.

For the code from this article check out this repository.

The purpose of aggregate functions

The job of an aggregate function is to compute a single result from multiple input rows. One of the most popular aggregate functions is count(). When used with an asterisk, it measures the total number of rows in the table.

1	SELECT count(*) FROM users

When we provide the count function with a column name, it counts the number of rows with a non-NULL value for that column.

1	SELECT count(address_id) AS number_of_users_with_address FROM users

The count() function was handy in the previous article when we implemented pagination.

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostModel from './post.model';

@Injectable()

class PostsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async get(offset = 0, limit: number | null = null, idsToSkip = 0) {

const databaseResponse = await this.databaseService.runQuery(

WITH selected_posts AS (

SELECT * FROM posts

WHERE id > $3

ORDER BY id ASC

OFFSET $1

LIMIT $2

total_posts_count_response AS (

SELECT COUNT(*)::int AS total_posts_count FROM posts

)

SELECT * FROM selected_posts, total_posts_count_response

[offset, limit, idsToSkip],

);

const items = databaseResponse.rows.map(

(databaseRow) => new PostModel(databaseRow),

);

const count = databaseResponse.rows[0]?.total_posts_count || 0;

return {

items,

count,

};

}

// ...

}

export default PostsRepository;

Grouping data in the table

Aggregate functions work great when we perform them on groups of data

1 2	SELECT author_id, count(*) FROM posts GROUP BY author_id

When we do the above, PostgreSQL divides the data into groups and runs the aggregate function on each group individually.

We could make our query even more helpful and order our results. By doing that, we can ensure the authors with the highest number of posts are at the top of the list.

SELECT author_id, count(*) AS posts_count FROM posts

GROUP BY author_id

ORDER BY posts_count DESC

Let’s create a model that can hold the above data.

postAuthorStatistics.model.ts

export interface PostAuthorStatisticsModelData {

author_id: number;

posts_count: number;

}

class PostAuthorStatisticsModel {

authorId: number;

postsCount: number;

constructor(postAuthorStatisticsData: PostAuthorStatisticsModelData) {

this.authorId = postAuthorStatisticsData.author_id;

this.postsCount = postAuthorStatisticsData.posts_count;

}

export default PostAuthorStatisticsModel;

Let’s create a separate statistics repository to prevent our PostsRepository class from getting too big.

postsStatistics.repository.ts

import {

Injectable,

} from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostAuthorStatisticsModel from './postAuthorStatistics.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getPostsAuthorStatistics() {

const databaseResponse = await this.databaseService.runQuery(

SELECT author_id, count(*)::int AS posts_count FROM posts

GROUP BY author_id

ORDER BY posts_count DESC

[],

);

return databaseResponse.rows.map(

(databaseRow) => new PostAuthorStatisticsModel(databaseRow),

);

}

export default PostsStatisticsRepository;

The count() function returns the value using the bigint data type. Because of that, we convert it to a regular integer. If you want to know more, check out the previous article.

We also need to point to our new repository in the PostsService class.

posts.service.ts

import { Injectable } from '@nestjs/common';

import PostsRepository from './posts.repository';

import PostsStatisticsRepository from './postsStatistics.repository';

@Injectable()

export class PostsService {

constructor(

private readonly postsRepository: PostsRepository,

private readonly postsStatisticsRepository: PostsStatisticsRepository,

) {}

getPostAuthorStatistics() {

return this.postsStatisticsRepository.getPostsAuthorStatistics();

}

// ...

}

The last step is to use it in the controller.

posts.controller.ts

import {

ClassSerializerInterceptor,

Controller,

Get,

Query,

UseInterceptors,

} from '@nestjs/common';

import { PostsService } from './posts.service';

import GetPostsByAuthorQuery from './getPostsByAuthorQuery';

import PaginationParams from '../utils/paginationParams';

@Controller('posts')

@UseInterceptors(ClassSerializerInterceptor)

export default class PostsController {

constructor(private readonly postsService: PostsService) {}

@Get()

getPosts(

@Query() { authorId }: GetPostsByAuthorQuery,

@Query() { offset, limit, idsToSkip }: PaginationParams,

) {

return this.postsService.getPosts(authorId, offset, limit, idsToSkip);

}

@Get('statistics')

getStatistics() {

return this.postsService.getPostAuthorStatistics();

}

// ...

}

Other aggregate functions

There are more aggregate functions besides count(). Let’s go through them.

max and min

Using the max() function, we can find the largest value of the selected column. Respectively, the min() function returns the smallest value of the column.

Since we don’t have any numerical columns in our posts, let’s pair the above functions with length(). This way, we can get the longest and shortest posts of a particular author.

The length() function returns the length of a string.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostAuthorStatisticsModel from './postAuthorStatistics.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getPostsAuthorStatistics() {

const databaseResponse = await this.databaseService.runQuery(

SELECT

author_id,

count(*)::int AS posts_count,

max(length(post_content)) AS longest_post_length,

min(length(post_content)) AS shortest_post_length

FROM posts

GROUP BY author_id

ORDER BY posts_count DESC

[],

);

return databaseResponse.rows.map(

(databaseRow) => new PostAuthorStatisticsModel(databaseRow),

);

}

export default PostsStatisticsRepository;

Running max() or min() on a text column returns a string based on the alphabetical order.

sum

With the sum() function, we can return a total sum of a particular column. Since it only works with numerical values, we also need the length() function.

Since the sum() function also returns the value in the bigint format, we transform it to a regular integer. We can do it because we don’t expect values bigger than 2³¹⁻¹.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostAuthorStatisticsModel from './postAuthorStatistics.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getPostsAuthorStatistics() {

const databaseResponse = await this.databaseService.runQuery(

SELECT

author_id,

count(*)::int AS posts_count,

max(length(post_content)) AS longest_post_length,

min(length(post_content)) AS shortest_post_length,

sum(length(post_content))::int AS all_posts_content_sum

FROM posts

GROUP BY author_id

ORDER BY posts_count DESC

[],

);

return databaseResponse.rows.map(

(databaseRow) => new PostAuthorStatisticsModel(databaseRow),

);

}

export default PostsStatisticsRepository;

avg

The avg() function calculates the average of the values in a group. Let’s combine it with the length() function to calculate the average length of all posts of a particular author.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostAuthorStatisticsModel from './postAuthorStatistics.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getPostsAuthorStatistics() {

const databaseResponse = await this.databaseService.runQuery(

SELECT

author_id,

count(*)::int AS posts_count,

max(length(post_content)) AS longest_post_length,

min(length(post_content)) AS shortest_post_length,

sum(length(post_content))::int AS all_posts_content_sum,

avg(length(post_content))::real AS average_post_content_length

FROM posts

GROUP BY author_id

ORDER BY posts_count DESC

[],

);

return databaseResponse.rows.map(

(databaseRow) => new PostAuthorStatisticsModel(databaseRow),

);

}

export default PostsStatisticsRepository;

A significant thing about the avg() function is that it returns the data in the numeric type. It can store many digits and is very useful when exactness is crucial. Parsing this data type to JSON converts it to a string by default. Since we don’t need many digits after the decimal, we convert it to the real data type.

Aggregating data from more than one table

So far, we’ve been grouping and aggregating data in one table. However, a typical case might be when we want to aggregate data using more than one table. In this case, we need to use the JOIN keyword.

SELECT addresses.country, COUNT(*) AS number_of_users FROM users

LEFT JOIN addresses ON users.address_id = addresses.id

GROUP BY addresses.country

Above, we perform an outer join using the LEFT JOIN keyword. If you want to know more, check out API with NestJS #73. One-to-one relationships with raw SQL queries

Filtering using aggregate functions and grouping

So far, to filter the results from the database, we’ve been using the WHERE keyword.

1 2	SELECT * FROM posts WHERE id = 1

When we want to filter using aggregate functions and grouping, we need to look at the execution order of SQL clauses.

FROM
WHERE
GROUP BY
HAVING
DISTINCT
SELECT
ORDER BY
LIMIT

Since PostgreSQL executes the WHERE clause before GROUP BY, we can’t use it with aggregate functions.

SELECT author_id, count(*) as posts_count FROM posts

WHERE count(*) > 100

GROUP BY author_id

ERROR: aggregate functions are not allowed in WHERE
LINE 2: WHERE count(*) > 100

Instead, we need to use the HAVING keyword.

SELECT author_id, count(*) AS posts_count FROM posts

GROUP BY author_id

HAVING count(*) > 100

Using aliases

An important caveat is that we can’t use column aliases with the HAVING keyword. So, for example, the following code wouldn’t work:

SELECT author_id, count(*) AS posts_count FROM posts

GROUP BY author_id

HAVING posts_count > 100

Instead, we need to use the count() function twice and rely on PostgreSQL to optimize it.

Summary

In this article, we’ve gone through how to use aggregate functions together with grouping. When doing so, we’ve implemented an endpoint that returns statistics about a particular table. We also wrote an example that uses an aggregate function and grouping when joining two tables. Finally, we’ve also learned how to filter our data using aggregate functions and why we can’t do that with the WHERE keyword.

There is still more to cover when writing raw SQL with NestJS, so stay tuned!

Series Navigation<< API with NestJS #77. Offset and keyset pagination with raw SQL queriesAPI with NestJS #79. Implementing searching with pattern matching and raw SQL >>