Read models and aggregates using SQL

In IT world subjects like DDD, CQRS, event sourcing are subject that are still extremely popular. It is hard to be on a IT conference and haven't got an option to see at least one presentation that is somehow related to one of aforementioned techniques. Though, it can be still interesting! And as I said in my previous post: I'm on the mission to get my SQL to the higher level. So can those two worlds be combined? Sure they can!
After your introduction of CQRS into your system, you can try to separate read model from the write model completely. Like - move all the aggregates from the "write" database to some other (usually no-sql) database. It seems to be obvious that ongoing aggregation of values can be highly more efficient than selecting all rows and performing some reducion - for example on every request.
What I'm going to do? I'm going to write some SQL to create an aggregate - and update it using a database trigger every time some other table will be modified. That will cause sort of separation between read and write models, but I leave one database for both.

Step 1. Create tables

Movies and their ratings will be our case study. Movie can be rated, and aggregate is an average rating for a movie. We need Movies table, Ratings table, and RatingAggregate to hold computed values.

create table Movies(  
    Id int not null identity(1,1) primary key,
    Name varchar(max) not null
create table Ratings(  
    Id int not null identity(1,1) primary key,
    Value int not null,
    MovieId int not null foreign key references Movies
create table RatingAggregate(  
    MovieId int not null primary key foreign key references Movies,
    Value float not null default(0.0),
    RatingsCount int not null default(0) 
Step 2. Add some data

Pretty obvious, right?

insert into Movies(Name) values  
            ('Lord of the rings'),
Step 3. Create a trigger

Average can be defined as:
Average_new = ((Average_before * number of ratings) + new rating) / (number of ratings + 1)
At the beginning we can initialize average and number of ratings to zeros. After that we perform the necessary updates.

create trigger RatingAggregateUpdate  
            on Ratings after insert
set transaction isolation level serializable  
--begin transaction

    -- initialize those which are not present yet
    insert into RatingAggregate(MovieId, Value, RatingsCount)
        select distinct i.MovieId as MovieId, 
                        0.0 as Value, 
                        0 as RatingsCount 
                        from inserted i left join RatingAggregate ra
                        on i.MovieId = ra.MovieId
                        where ra.MovieId IS NULL

    -- update values
    update RatingAggregate 
        set Value = ((ra.Value*ra.RatingsCount)+ratingsForMovies.RatingsSum)/(ra.RatingsCount+ratingsForMovies.RatingsCount),
            RatingsCount = ra.RatingsCount + ratingsForMovies.RatingsCount
        from RatingAggregate ra inner join 
            (select i.MovieId, SUM(i.Value) as RatingsSum, COUNT(*) as RatingsCount
                from inserted i
                group by i.MovieId) as ratingsForMovies
        on ra.MovieId = ratingsForMovies.MovieId    
--commit transaction

You may say - hey what is happening here. When trigger is fired we get "inserted" values. That is a set, not just a single value! That's why we have to group those inserted values. But chill out - those "ones" from the initial average definition are replaced by sum of the ratings and number of the ratings grouped by the movie. That's all.

Step 4. Insert some ratings

Let's just simulate that our users has added some ratings:

insert into Ratings(Value, MovieId) values  
            (5, 1),
            (5, 1),
            (1, 2),
            (1, 2),
            (3, 3),
            (3, 3),
            (4, 4),
            (4, 4)

insert into Ratings(Value, MovieId) values  
            (7, 1),
            (7, 1),
            (3, 2),
            (3, 2),
            (5, 3),
            (5, 3),
            (6, 4),
            (6, 4)

(I'm using two insert statements here for testing purposes)

Step 5. Select aggregated data

Finally, we can use our read model and find the average ratings for the movies.

select movies.Name, COALESCE(ra.Value, 0) as Rating  
    from Movies movies left outer join RatingAggregate ra 
    on movies.Id = ra.MovieId
Again: Why?

Calculating average ratings for movies using the built-in AVG function is obvious:

select movies.Id, COALESCE(AVG(CAST(r.Value as float)), 0.0) as Rating  
    from Movies movies
        left outer join Ratings r 
        on movies.Id = r.MovieId
    group by movies.Id

Taaaadaaaam, done.
But that is just an average, right? Things usually can became much more complicated. In every other case we can use User Defined Functions or... this fancy trigger-based solution ;>

To sum up: we can create aggregates using database triggers ;)

Click for source code

P.S. Some more selects

Careful reader probably have found out that when using an aggregate I had selected movies' names, but only ids in the AVG-based example. That's right - if I want to get names in the second case I'd have to make one more step. The step can be implemented in more than one way (I tried in 4 ways, but they are actually all more or less alike). Dive into code and check now :) Farewell!