There is nothing like a real task to illustrate how to use the SQL 2012 LEAD analytical function, the new window frame facility of the OVER
clause and a bit about constructing CHECK CONSTRAINT
s.
When I was growing up, one of my favorite sports was bowling. It was only later in life after I moved overseas that I realized that bowling actually has another connotations, both in the game of cricket and in lawn Bowls. Bowling is, in Europe called Ten-pin bowling or skittles.
In bowling, the way that the game is scored is rather complicated, with some arcane conventions. Because of this, it makes a good example to illustrate the power of the analytic functions in SQL 2012, especially when used together with constraints.
Scoring in Bowling
In bowling, the objective is to roll a ball down the lane and knock down all of the ten pins sitting at the end of the lane in each frame. Ideally, you do this in one roll of the ball, but you get two tries.
While in the modern age, we have computerized bowling lanes that tally and provide an overhead projection of your score in bowling, in the old days a bowling game was scored using a score sheet that looks like this.
In bowling, balls are rolled and tallied in pairs, with the exception of the tenth frame where there are cases when you will be allowed to roll three balls. Each of the numbered boxes above represent a frame. There are three types of frame depending on the number of pins you knock down:
- Strike – when the first ball you roll for the frame knocks down all of the ten pins (represented on the score sheet as an X).
- Spare – when the first ball you roll knocks down (none or) some but not all of the ten pins, and you knock down the rest of the pins with your second ball (represented on the score sheet as a slash for the second ball).
- Open – when you fail to knock down all ten pins on two attempts.
Strikes and spares are called “marks” (probably because they are represented by X’s and slashes), and if you get a mark in frame ten you’ll be allowed to roll three balls in that frame. We’ll explain the details of scoring in a moment, but for right now let’s focus on how many balls you can roll in a game.
The maximum number of balls you can roll in a game is 21, which results if you get a spare or open frame for the first nine frames, then roll a spare in the tenth frame allowing you to roll one additional ball in that frame. You’ll note that this is the same as the number of small boxes in the score sheet (two in each of the frames one through nine, plus three in the tenth frame).
The minimum number of balls you can roll in a game of bowling is 12, if you were to manage to roll a strike in each frame (knocking down all the pins on the first ball means you don’t need to throw another ball in that frame) and roll three strikes in the tenth frame. In this case, you would achieve a perfect game. The last two balls in the tenth frame are required to score the first strike in that frame.
Scoring for each frame depends on the type of frame you rolled:
- Open – the frame score is the sum of the prior frame score plus the pin count for the two balls you rolled in the frame.
- Spare – the frame score is the sum of the prior frame score plus ten (the spare) plus the next ball you roll, or the first ball of the next frame.
- Strike – the frame score is the sum of the prior frame score plus ten (the strike) plus the number of pins you knock down on the next two balls. This could come from the next frame only or the next two frames if you roll two strikes in a row. In the tenth frame, if you roll a strike on your first ball you get to roll two more balls and these are all added together to get a maximum of 30 points for that frame.
I can still remember my dad teaching me how to score bowling!
Let’s look at a couple of example frames for Big Al and show how strikes and spares appear on the score sheet, and how this tallies out to a score.
In this game, Big Al has rolled only nine pins (an open frame) in frame one and in the second frame he knocks down three pins on his first ball, plus seven more on his second ball (a spare). Frame two cannot be scored until he rolls one more ball.
Let’s say that Big Al rolls a six in frame three on the spare in frame two (so now frame two can be scored), follows with another spare and then a strike in frame four as shown above. Sixteen (3 + 7 + 6) is added to his score for frame one to get 25 in frame 2, while his spare plus strike combination in frames three and four net him twenty additional points in frame three.
Now let’s say he gets on a roll and bowls another strike. Frame four cannot yet be scored until he rolls one more ball, because of the scoring rule that a strike scores ten pins plus the next two balls.
In bowling parlance, two strikes in a row is known as a “double.” So let’s say he really goes on a roll and bowls another strike.
Now we can score frame four, but frames five and six cannot yet be scored. Big Al’s three strikes in a row is called a “triple” or more commonly a “turkey.” Now we’ll fill out frames seven, eight and nine, and score the results.
At this point, Big Al is doing pretty good. All he needs is eight pins to bowl what is known as “scratch.” People that can average 180 pins or more per game are known as “scratch bowlers,” which in league bowling means they are assigned no handicap, which is usually calculated as 80% of 180 minus their league average.
Let’s look at a few possible outcomes for the tenth frame.
In this case, Big Al did not get a strike or a spare: He got an open frame instead, so he doesn’t get to roll a third ball. Still, his game is quite good at a total score of 181.
If Big Al rolls a spare in frame ten, he gets to roll a third ball. So his total score for that frame is nineteen. Another excellent game.
If he bowls a strike on his first ball, he gets to roll two more balls. In this case, he managed a spare on top of the strike, to add twenty to his score in frame nine.
A perfect tenth frame consists of another turkey, adding thirty points to his frame nine score for a total of 202. Bowling a 200 game in bowling is kind of a magic number. Any time you can do that you’ve had a great game!
Here is what a perfect game looks like.
Nearly as rare as a perfect game, there is this symmetrical combination of alternating strikes and spares, resulting in a total score of 200.
This is called a “Dutch” game, for reasons I was never able to fathom. Like any other sport, bowling has its own lexicon of cool slang including gutter, curve and hook balls.
In case you’re wondering, I’ve never been a scratch bowler (without a handicap) but my high score was 233 and that came along with my personal best of seven strikes in a row! What can I say, I really messed up the other frames of that game to get only a 233.
Now that you’ve learned the ins-and-outs of scoring a game of bowling, let’s see how we might do this in SQL.
A Bowling Scores Table and some Validations
First let’s create a table to store our bowling scores, and provide you a drop statement you can use later to clean up your sandbox.
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.BowlingScores ( GameID INT NOT NULL ,PlayerID INT NOT NULL ,Frame SMALLINT NOT NULL ,PinsBall1 SMALLINT NOT NULL ,PinsBall2 SMALLINT NULL ,PinsBall3 SMALLINT NULL ,PRIMARY KEY (GameID, PlayerID, Frame) ); GO -- DROP TABLE dbo.BowlingScores; |
Note that the first ball in any particular frame must always be present (NOT NULL), while rolling additional balls in a frame may be optional (so they are allowed to be NULL).
A game in bowling only has ten frames, so we’ll add a CHECK CONSTRAINT
for that.
1 2 3 4 5 |
ALTER TABLE dbo.BowlingScores ADD CONSTRAINT bs_frame CHECK ( Frame BETWEEN 1 AND 10 ); |
The first ball in any frame can knock down as few as zero pins (known as a “gutter ball”) or as many as ten (a strike).
1 2 3 4 5 |
ALTER TABLE dbo.BowlingScores ADD CONSTRAINT bs_ball1 CHECK ( PinsBall1 BETWEEN 0 AND 10 ); |
The CHECK CONSTRAINT
s get a little more tricky at this point because they depend on whether you’re in the tenth frame or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
ALTER TABLE dbo.BowlingScores ADD CONSTRAINT bs_ball2 CHECK ( ( -- In Frame 10, the second ball must always be rolled Frame = 10 AND PinsBall2 IS NOT NULL AND -- If the first ball is a strike, the second ball can be gutter through strike (PinsBall1 = 10 AND PinsBall2 BETWEEN 0 AND 10) OR -- If the first ball is not a strike, the total pins cannot exceed ten (PinsBall2 BETWEEN 0 AND 10-PinsBall1) ) OR -- A strike in frames 1-9 requires that the second ball is not rolled (NULL) ( PinsBall1 = 10 AND PinsBall2 IS NULL ) OR -- Total pins for frames 1-9 must be <= 10 PinsBall2 BETWEEN 0 AND 10-PinsBall1 ); |
Hopefully the comments above guide you through what is being checked to ensure a valid pin count for the second ball of each frame.
Once again, for the third ball we must break the check into pieces depending on whether it occurs in frame ten.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ALTER TABLE dbo.BowlingScores ADD CONSTRAINT bs_ball3 CHECK ( -- Before Frame 10 no score is allowed (Frame < 10 AND PinsBall3 IS NULL) OR -- In Frame 10, if two strikes are made the third ball must be gutter through strike (Frame = 10 AND PinsBall1 = 10 AND PinsBall2 = 10 AND PinsBall3 BETWEEN 0 AND 10) OR -- In Frame 10, if the first ball is a strike, the third ball can't score more than a spare (Frame = 10 AND PinsBall1 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10-PinsBall2) OR -- In Frame 10, if the first two balls are a spare, the third ball must be gutter to strike (Frame = 10 AND PinsBall1+PinsBall2 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10) OR -- In Frame 10, if the first two balls are an open frame, the third ball cannot be rolled (Frame = 10 AND PinsBall1+PinsBall2 < 10 AND PinsBall3 IS NULL) ); |
Because the PRIMARY KEY
consists of game number, person ID and frame number, it won’t be possible to insert the same frame for a game/player more than once. About the only thing we can’t verify using CHECK CONSTRAINT
s is a missing frame, or when the next frame is inserted but the second ball of the prior frame was not recorded (and that frame was not a strike).
As with any code you write, you should test your CHECK CONSTRAINT
s thoroughly to make sure they don’t let any bad data get through. Here’s a series of test INSERT
s that should all fail for the reasons specified in the comments.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Frame number should be between 1 and 10 INSERT INTO dbo.BowlingScores VALUES(1, 1, 0, 9, NULL, NULL); GO -- First ball in a frame cannot be more than 10 INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 11, NULL, NULL); GO -- First ball in a frame cannot be < 0 INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, -1, NULL, NULL); GO -- First two balls in a frame cannot be > 10 INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 3, NULL); GO -- Third ball not allowed unless this is frame 10 INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 1, 1); GO -- In Frame 10, this is not allowed INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 3, 1); GO -- In Frame 10, this is not allowed either INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 2, 11); GO |
A Quick Overview of the T-SQL LEAD Analytical Function
The T-SQL LEAD
analytical function consists of the call to LEAD
with one to three arguments supplied, and an OVER
clause.
1 |
LEAD(field_name, row_offset, default_value) OVER (...) |
The second and third arguments are optional:
-
row_offset
– default is 1 (return the next row). default_value
– default value returned if there is no next row (instead of NULL).
The OVER
clause requires an ORDER BY
, but PARTITION BY i
s optional.
Scoring a Bowling Game
Let’s start with some sample data that is captured on a per-ball basis, based on Big Al’s game above. The objective is to write a T-SQL query that will display the score for the frame even if the frame is incomplete. This is actually a bit harder to do than it would be if you had to insert the complete results for a frame.
1 2 3 4 5 6 7 8 9 10 |
-- The first three balls for Big Al before he makes his spare INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 7, NULL, NULL); UPDATE dbo.BowlingScores SET PinsBall2 = 2 WHERE GameID = 1 AND PlayerID = 1 AND Frame = 1; INSERT INTO dbo.BowlingScores VALUES(1, 1, 2, 3, NULL, NULL); |
We can begin creating our set-based scoring algorithm using the SQL 2012 window frame feature of the OVER clause to calculate a running sum across frames. As a starting point, we’ll mostly ignore strikes and spares, but we’ll create a flag (FrameType) for use later to tell us what kind of frame has been bowled.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
WITH PinCounts AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType = CASE -- Strike WHEN PinsBall1 = 10 THEN 2 -- Spare WHEN PinsBall1 + PinsBall2 = 10 THEN 1 -- Open frame ELSE 0 END -- Pick up next ball for scoring a spare or strike ,NextBall1 = LEAD(PinsBall1, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike ,NextBall2 = LEAD(PinsBall2, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike if -- previous frame was a strike ,NextBall3 = LEAD(PinsBall1, 2) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) FROM dbo.BowlingScores ), FrameScore AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType, NextBall1, NextBall2, NextBall3 -- Logic to calculate the pins for this frame will go here ,FramePins = PinsBall1 + PinsBall2 FROM PinCounts ) SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameScore = CASE WHEN FrameType = 0 AND PinsBall2 IS NOT NULL THEN SUM(FramePins) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ROWS UNBOUNDED PRECEDING ) END FROM FrameScore; |
Note that we have included some results (NextBall1,
NextBall2
and NextBall3
) that we are not yet using but will use later when we add the logic for scoring strikes and spares. The LEAD
function returns a NULL
value in cases where the next frame has not been recorded. We’ve also included a stub Common Table Expression (CTE) named FrameScore
where we will place most of the scoring logic.
This gives us the correct result for scoring Big Al’s first three balls.
1 2 3 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 NULL NULL NULL |
While the results are just what we need, our CASE
logic in the final SELECT
will require some additional work to handle the scoring when spares and strikes are the result in a frame.
On his next ball, Big Al gets seven more pins making a spare.
1 2 3 |
UPDATE dbo.BowlingScores SET PinsBall2 = 7 WHERE GameID = 1 AND PlayerID = 1 AND Frame = 2; |
If we run the same query, these are our results:
1 2 3 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL NULL |
We see that frame two has still not been scored, which is the correct result because Big Al must bowl another ball before it can be scored. On his next ball (on the first ball of frame three), Big Al knocks down six pins.
1 2 |
INSERT INTO dbo.BowlingScores VALUES(1, 1, 3, 6, NULL, NULL); |
To score his spare in frame two properly, we must do two things to our query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
WITH PinCounts AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType = CASE -- Strike WHEN PinsBall1 = 10 THEN 2 -- Spare WHEN PinsBall1 + PinsBall2 = 10 THEN 1 -- Open frame ELSE 0 END -- Pick up next ball for scoring a spare or strike ,NextBall1 = LEAD(PinsBall1, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike ,NextBall2 = LEAD(PinsBall2, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike if -- previous frame was a strike ,NextBall3 = LEAD(PinsBall1, 2) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) FROM dbo.BowlingScores ), FrameScore AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType, NextBall1, NextBall2, NextBall3 -- Logic to calculate the pins for this frame will go here ,FramePins = CASE -- Calculate the score for a spare WHEN PinsBall1 + PinsBall2 = 10 THEN PinsBall1 + PinsBall2 + NextBall1 -- Calculate the score for an open frame ELSE PinsBall1 + PinsBall2 END FROM PinCounts ) SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameScore = CASE WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR -- Display the result for a completed spare (FrameType = 1 AND NextBall1 IS NOT NULL) THEN SUM(FramePins) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ROWS UNBOUNDED PRECEDING ) END FROM FrameScore; |
Our changes are:
- In the CTE named
FrameScore
we changed the calculation ofFramePins
to be aCASE
statement that looks ahead to the next ball to calculate total pins correctly when the total for two balls is ten (a spare). - We’ve modified the calculation of
FrameScore
to contain the score in a frame with a spare if the next ball has been rolled.
We see that the results are now calculated correctly for his spare.
1 2 3 4 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL 25 1 1 3 6 NULL NULL NULL |
When Big Al gets four more pins in the third frame to make his spare:
1 2 3 |
UPDATE dbo.BowlingScores SET PinsBall2 = 4 WHERE GameID = 1 AND PlayerID = 1 AND Frame = 3; |
Our scoring algorithm remains correct.
1 2 3 4 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL 25 1 1 3 6 4 NULL NULL |
Big Al then gets a strike in frame four.
1 2 |
INSERT INTO dbo.BowlingScores VALUES(1, 1, 4, 10, NULL, NULL); |
Our scoring algorithm still works, but it will need some changes to make it score the next balls properly.
1 2 3 4 5 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL 25 1 1 3 6 4 NULL 45 1 1 4 10 NULL NULL NULL |
Here is that modified query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
WITH PinCounts AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType = CASE -- Strike WHEN PinsBall1 = 10 THEN 2 -- Spare WHEN PinsBall1 + PinsBall2 = 10 THEN 1 -- Open frame ELSE 0 END -- Pick up next ball for scoring a spare or strike ,NextBall1 = LEAD(PinsBall1, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike ,NextBall2 = LEAD(PinsBall2, 1) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike if -- previous frame was a strike ,NextBall3 = LEAD(PinsBall1, 2) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) FROM dbo.BowlingScores ), FrameScore AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType, NextBall1, NextBall2, NextBall3 -- Logic to calculate the pins for this frame will go here ,FramePins = CASE -- Calculate the score for a strike WHEN PinsBall1 = 10 THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3) -- Calculate the score for a spare WHEN PinsBall1 + PinsBall2 = 10 THEN PinsBall1 + PinsBall2 + NextBall1 -- Calculate the score for an open frame ELSE PinsBall1 + PinsBall2 END FROM PinCounts ) SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameScore = CASE WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR -- Display the result for a completed spare (FrameType = 1 AND NextBall1 IS NOT NULL) OR -- Display the result for a completed strike (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL) THEN SUM(FramePins) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ROWS UNBOUNDED PRECEDING ) END FROM FrameScore; |
We’ve added the scoring logic for a strike (adding the next two balls) in the FrameScore
CTE and modified the CASE
in the last select to display the score for a strike once it is completed. Let’s complete Big Al’s turkey with two more strikes.
1 2 3 4 5 |
INSERT INTO dbo.BowlingScores VALUES(1, 1, 5, 10, NULL, NULL); INSERT INTO dbo.BowlingScores VALUES(1, 1, 6, 10, NULL, NULL); |
Our results show that the first strike has now been scored properly, and the interested reader who is following along can verify that other combinations of pin counts also work.
1 2 3 4 5 6 7 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL 25 1 1 3 6 4 NULL 45 1 1 4 10 NULL NULL 75 1 1 5 10 NULL NULL NULL 1 1 6 10 NULL NULL NULL |
We’ll dispense with the statements that add an individual ball at a time and simply insert the next three frames for Big Al’s game.
1 2 3 4 5 6 7 8 |
INSERT INTO dbo.BowlingScores VALUES(1, 1, 7, 9, 1, NULL); INSERT INTO dbo.BowlingScores VALUES(1, 1, 8, 10, NULL, NULL); INSERT INTO dbo.BowlingScores VALUES(1, 1, 9, 8, 1, NULL); |
Big Al’s score now looks as follows (correct when matched against the scoring sheet we showed earlier):
1 2 3 4 5 6 7 8 9 10 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 1 1 1 7 2 NULL 9 1 1 2 3 7 NULL 25 1 1 3 6 4 NULL 45 1 1 4 10 NULL NULL 75 1 1 5 10 NULL NULL 104 1 1 6 10 NULL NULL 124 1 1 7 9 1 NULL 144 1 1 8 10 NULL NULL 163 1 1 9 8 1 NULL 172 |
Scoring the Tenth Frame
To score the tenth frame properly, just a little tweak is required to our query (in the PinCounts
CTE).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
WITH PinCounts AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType = CASE -- Strike WHEN PinsBall1 = 10 THEN 2 -- Spare WHEN PinsBall1 + PinsBall2 = 10 THEN 1 -- Open frame ELSE 0 END -- Pick up next ball for scoring a spare or strike ,NextBall1 = LEAD(PinsBall1, 1 -- For frame 10, next row returns NULL ,CASE WHEN Frame = 10 AND PinsBall1 = 10 THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3) WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10 THEN PinsBall3 END ) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike ,NextBall2 = LEAD(PinsBall2, 1 -- For frame 10, next row returns NULL ,PinsBall3 ) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike if -- previous frame was a strike ,NextBall3 = LEAD(PinsBall1, 2) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) FROM dbo.BowlingScores ), FrameScore AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType, NextBall1, NextBall2, NextBall3 -- Logic to calculate the pins for this frame will go here ,FramePins = CASE -- Calculate the score for a strike WHEN PinsBall1 = 10 THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3) -- Calculate the score for a spare WHEN PinsBall1 + PinsBall2 = 10 THEN PinsBall1 + PinsBall2 + NextBall1 -- Calculate the score for an open frame ELSE PinsBall1 + PinsBall2 END FROM PinCounts ) SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameScore = CASE WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR -- Display the result for a completed spare (FrameType = 1 AND NextBall1 IS NOT NULL) OR -- Display the result for a completed strike (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL) THEN SUM(FramePins) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ROWS UNBOUNDED PRECEDING ) END FROM FrameScore; |
The new logic that applies to frame ten can be seen as the third argument to LEAD
(the result to return instead of NULL
) for NextBall1
and NextBall2
in the PinCounts
CTE.
Let’s see how this works for each of the outcomes of frame tens we suggested to complete Big Al’s game. I will show only the tenth frame for each.
1 2 3 4 5 6 7 8 9 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore -- Open frame for Frame 10 1 1 10 6 3 NULL 181 -- Spare in Frame 10 followed by third ball of 9 pins 1 1 10 7 3 9 191 -- Strike in Frame 10 followed by spare in balls 2 and 3 1 1 10 10 7 3 192 -- Turkey in Frame 10 1 1 10 10 10 10 202 |
The interested reader can verify that if frame ten is not complete, the FrameScore
is NULL
(for all possible cases).
Scoring a Game of Strike/Spare/Strike (in Consecutive Frames)
Let’s say that Big Al is on another roll in game two. We can insert a second game quite easily, making it consecutive strikes and spares.
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO dbo.BowlingScores SELECT 2, 1, 1, 10, NULL, NULL UNION ALL SELECT 2, 1, 2, 3, 7, NULL UNION ALL SELECT 2, 1, 3, 10, NULL, NULL UNION ALL SELECT 2, 1, 4, 6, 4, NULL UNION ALL SELECT 2, 1, 5, 10, NULL, NULL UNION ALL SELECT 2, 1, 6, 9, 1, NULL UNION ALL SELECT 2, 1, 7, 10, NULL, NULL UNION ALL SELECT 2, 1, 8, 8, 2, NULL UNION ALL SELECT 2, 1, 9, 10, NULL, NULL UNION ALL SELECT 2, 1, 10, 7, 3, 10; |
Our bowling-scoring algorithm shows us this for the result:
1 2 3 4 5 6 7 8 9 10 11 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 2 1 1 10 NULL NULL 20 2 1 2 3 7 NULL 40 2 1 3 10 NULL NULL 60 2 1 4 6 4 NULL 80 2 1 5 10 NULL NULL 100 2 1 6 9 1 NULL 120 2 1 7 10 NULL NULL 140 2 1 8 8 2 NULL 160 2 1 9 10 NULL NULL 180 2 1 10 7 3 10 200 |
Scoring a Perfect Game
In the final game of his three game series, Big Al rolls a perfect game, basically the game of his life!
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO dbo.BowlingScores SELECT 3, 1, 1, 10, NULL, NULL UNION ALL SELECT 3, 1, 2, 10, NULL, NULL UNION ALL SELECT 3, 1, 3, 10, NULL, NULL UNION ALL SELECT 3, 1, 4, 10, NULL, NULL UNION ALL SELECT 3, 1, 5, 10, NULL, NULL UNION ALL SELECT 3, 1, 6, 10, NULL, NULL UNION ALL SELECT 3, 1, 7, 10, NULL, NULL UNION ALL SELECT 3, 1, 8, 10, NULL, NULL UNION ALL SELECT 3, 1, 9, 10, NULL, NULL UNION ALL SELECT 3, 1, 10, 10, 10, 10; |
1 2 3 4 5 6 7 8 9 10 11 |
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore 3 1 1 10 NULL NULL 30 3 1 2 10 NULL NULL 60 3 1 3 10 NULL NULL 90 3 1 4 10 NULL NULL 120 3 1 5 10 NULL NULL 150 3 1 6 10 NULL NULL 180 3 1 7 10 NULL NULL 210 3 1 8 10 NULL NULL 240 3 1 9 10 NULL NULL 270 3 1 10 10 10 10 300 |
If you ever do this in league bowling, trust me you will have too much to drink that night!
Averaging the Scores for a League Bowling Night
Typically in a league bowling night, each player bowls three games. To average all the games for the players, the same query can be used with some simple modifications (like encapsulating the former query into a second CTE).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
WITH PinCounts AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType = CASE -- Strike WHEN PinsBall1 = 10 THEN 2 -- Spare WHEN PinsBall1 + PinsBall2 = 10 THEN 1 -- Open frame ELSE 0 END -- Pick up next ball for scoring a spare or strike ,NextBall1 = LEAD(PinsBall1, 1 -- For frame 10, next row returns NULL ,CASE WHEN Frame = 10 AND PinsBall1 = 10 THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3) WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10 THEN PinsBall3 END ) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike ,NextBall2 = LEAD(PinsBall2, 1 -- For frame 10, next row returns NULL ,PinsBall3 ) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) -- Pick up second following ball for scoring a strike if -- previous frame was a strike ,NextBall3 = LEAD(PinsBall1, 2) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ) FROM dbo.BowlingScores ), FrameScore AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameType, NextBall1, NextBall2, NextBall3 -- Logic to calculate the pins for this frame will go here ,FramePins = CASE -- Calculate the score for a strike WHEN PinsBall1 = 10 THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3) -- Calculate the score for a spare WHEN PinsBall1 + PinsBall2 = 10 THEN PinsBall1 + PinsBall2 + NextBall1 -- Calculate the score for an open frame ELSE PinsBall1 + PinsBall2 END FROM PinCounts ), GameScores AS ( SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3 ,FrameScore = CASE WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR -- Display the result for a completed spare (FrameType = 1 AND NextBall1 IS NOT NULL) OR -- Display the result for a completed strike (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL) THEN SUM(FramePins) OVER ( PARTITION BY GameID, PlayerID ORDER BY Frame ROWS UNBOUNDED PRECEDING ) END FROM FrameScore ) SELECT PlayerID, AverageScore=AVG(FrameScore) FROM GameScores WHERE Frame = 10 GROUP BY PlayerID; |
The above query will show the three game averages for all players, assuming that you had that data available. In this case, we’ve used Big Al’s first game of 181 to calculate the average.
1 2 |
PlayerID AverageScore 1 227 |
A Query to Display the Game Results Like a Score Sheet
Using the same three CTEs developed above (PinCounts, FrameScore, GameScores
) we can construct a final SELECT
that flattens the results for each player/game. The CTEs are omitted for brevity.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SELECT GameID, PlayerID ,F1B1 = MAX(CASE Frame WHEN 1 THEN Ball1 END) ,F1B2 = MAX(CASE Frame WHEN 1 THEN Ball2 END) ,F2B1 = MAX(CASE Frame WHEN 2 THEN Ball1 END) ,F2B2 = MAX(CASE Frame WHEN 2 THEN Ball2 END) ,F3B1 = MAX(CASE Frame WHEN 3 THEN Ball1 END) ,F3B2 = MAX(CASE Frame WHEN 3 THEN Ball2 END) ,F4B1 = MAX(CASE Frame WHEN 4 THEN Ball1 END) ,F4B2 = MAX(CASE Frame WHEN 4 THEN Ball2 END) ,F5B1 = MAX(CASE Frame WHEN 5 THEN Ball1 END) ,F5B2 = MAX(CASE Frame WHEN 5 THEN Ball2 END) ,F6B1 = MAX(CASE Frame WHEN 6 THEN Ball1 END) ,F6B2 = MAX(CASE Frame WHEN 6 THEN Ball2 END) ,F7B1 = MAX(CASE Frame WHEN 7 THEN Ball1 END) ,F7B2 = MAX(CASE Frame WHEN 7 THEN Ball2 END) ,F8B1 = MAX(CASE Frame WHEN 8 THEN Ball1 END) ,F8B2 = MAX(CASE Frame WHEN 8 THEN Ball2 END) ,F9B1 = MAX(CASE Frame WHEN 9 THEN Ball1 END) ,F9B2 = MAX(CASE Frame WHEN 9 THEN Ball2 END) ,F10B1 = MAX(CASE Frame WHEN 10 THEN Ball1 END) ,F10B2 = MAX(CASE Frame WHEN 10 THEN Ball2 END) ,F10B3 = MAX(CASE Frame WHEN 10 THEN Ball3 END) FROM GameScores a CROSS APPLY ( SELECT Ball1 = CASE PinsBall1 WHEN 10 THEN 'X' ELSE CAST(PinsBall1 AS VARCHAR) END ,Ball2 = CASE WHEN PinsBall2 IS NULL THEN '' WHEN PinsBall1+PinsBall2 = 10 THEN '/' WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall2 AS VARCHAR) ELSE '' END ,Ball3 = CASE WHEN PinsBall3 IS NULL THEN '' WHEN PinsBall3 = 10 THEN 'X' WHEN PinsBall2+PinsBall3 = 10 THEN '/' WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall3 AS VARCHAR) ELSE '' END ) b GROUP BY GameID, PlayerID; |
This is referred to as a crosstab query because the result pivots the rows to columns. The CROSS APPLY
is used to calculate intermediate results that greatly simplifies the redundancy that would have otherwise been present in each MAX
function.
If you’ve never seen or written a crosstab query before, we encourage you to add in the results for the total frame score where it makes sense to do so.
Alternative Data Modeling
A data modeling purist may argue that it would be better to have a BallID
column in the table, with one column to record the pin score for that ball. Then BallID
could be 1 or 2 in any frame, and 3 would be allowed in frame ten. This also allows for one row to be recorded in frames one through nine when a strike is obtained by the bowler.
However that approach would not allow for the same level of validations performed by the CHECK CONSTRAINT
s that we’ve provided.
Another approach would be to include a column for FrameScore
in the table. Since FrameScore
can easily be calculated as we’ve shown, that is not a normalized data model. However if you chose to use that approach, you could also calculate the FrameScore
in an AFTER INSERT, UPDATE TRIGGER
. Because of the need to “look forward,” you would not be able to use a computed column.
Conclusions and What we have Learned
More than anything, I hope that you’ve concluded that learning complex SQL can be fun!
In this article, we have demonstrated quite a number of somewhat advanced T-SQL features including:
- How to construct complex
CHECK CONSTRAINT
s to ensure that data being inserted into your target table is valid. - Using the SQL 2012 window frame capability of the
OVER
clause to calculating a running total. - Using the SQL 2012
LEAD
analytic function to “look ahead” to later rows in a set. - Pivoting rows into columns using a crosstab query.
Because we have built up our query one little bit at a time working through a scoring example, it may be possible to further simplify the query and make it a little bit shorter. We’ll leave that as an exercise for our more intrepid readers, and we are interested to see those results posted to the comments section of this article.
Our methodology demonstrated how you can take a relatively complex problem and break it into sub-problems that you can solve one little bit at a time, and this is an essential skill in formulating and writing complex queries in any dialect of the SQL language.
Load comments