Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
if object_id(N'RatingAggregate', N'U') is not null
drop table RatingAggregate
go
if object_id(N'Ratings', N'U') is not null
drop table Ratings
go
if object_id(N'Movies', N'U') is not null
drop table Movies
go
create table Movies(
Id int not null identity(1,1) primary key,
Name varchar(max) not null
)
go
create table Ratings(
Id int not null identity(1,1) primary key,
Value int not null,
MovieId int not null foreign key references Movies
)
go
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)
)
go
insert into Movies(Name) values
('Deadpool'),
('Lord of the rings'),
('Sharknado'),
('Hobbit'),
('Hobbit 5')
go
if object_id(N'RatingAggregateUpdate') is not null
drop trigger RatingAggregateUpdate
go
create trigger RatingAggregateUpdate
on Ratings after insert
as
begin
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
end
go
insert into Ratings(Value, MovieId) values
(5, 1),
(5, 1),
(1, 2),
(1, 2),
(3, 3),
(3, 3),
(4, 4),
(4, 4)
go
insert into Ratings(Value, MovieId) values
(7, 1),
(7, 1),
(3, 2),
(3, 2),
(5, 3),
(5, 3),
(6, 4)
go
select movies.Id, movies.Name, COALESCE(ra.Value, 0) as Rating
from Movies movies left outer join RatingAggregate ra
on movies.Id = ra.MovieId
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
--movies with names and ratings: cross apply
select m.Id, m.Name, COALESCE(innerMovie.Rating, 0.0) from Movies m
outer apply (
select AVG(CAST(r.Value as float)) as Rating
from Ratings r
where m.Id = r.MovieId
group by r.MovieId
) innerMovie
--movies with names and ratings: left outer join with subquery
select m.Id, m.Name, COALESCE(aggregatedRatings.Rating, 0.0)
from Movies m
left outer join (
select r.MovieId as MovieId, AVG(CAST(r.Value as float)) as Rating
from Ratings r
group by r.MovieId
) aggregatedRatings on aggregatedRatings.MovieId = m.Id
--movies with names and ratings: CTE + outer apply
;with AggregatedRatings as
( select r.MovieId as MovieId, COALESCE(AVG(CAST(r.Value as float)), 0.0) as Rating
from Ratings r
group by r.MovieId )
select MovieId, Name, Rating
from AggregatedRatings ar
outer apply
( select Name from Movies where Id = ar.MovieId ) a
--movies with names and ratings: CTE + left outer join
;with AggregatedRatings as
( select r.MovieId as MovieId, AVG(CAST(r.Value as float)) as Rating
from Ratings r
group by r.MovieId )
select MovieId, Name, COALESCE(Rating, 0.0) as Rating
from Movies movies
left outer join AggregatedRatings ar on
movies.Id = ar.MovieId
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment