I like golf. Actually, I love golf. No...I'm completely obsessed with golf. I follow all of the tours, players, and news. I'll wake up at 3 o'clock in the morning on weekends to watch The Open Championship being played in Great Britain in July. It's OK, I don't expect you to understand.
As with any sport, in golf there is no end of statistics, scores, and mounds upon mounds of data to read. There is a specific type of scoring in golf called Match Play. The gist of the system is that two players playing against each other on the same course will record their score for each hole. The player with the lower (better) score on a hole will go "one up" and the player with the higher (worse) score will go "1 down". As play progresses the "up" and "down" scoring is cumulative. Once a player is "up" more points than there are holes remaining, that player has won the match as their opponent would be unable to win enough remaining holes to close the gap. If you'd like to read more about match play scoring you can do so here.
If you imagine how a golfer's scores would be recorded in a database table, a very simplified approach would be a table that looked like this.
PLAYER_SCORECARD
| Player | Hole | Score |
| Bob | 1 | 4 |
| Bob | 2 | 4 |
| Bob | 3 | 3 |
| Bob | 4 | 3 |
| Bob | 5 | 5 |
| Bob | 6 | 5 |
| Bob | 7 | 6 |
| Bob | 8 | 3 |
| Bob | 9 | 4 |
| ... |
and so on.
Typically, when the scores for a Match Play type match are displayed for two opponents, it is done in a "horizontal" manner.
| Hole | 1 | 2 | 3 | ... |
| 1 up | 2 up | 1 up | ... | |
| Bob | 3 | 2 | 4 | ... |
| Joe | 4 | 3 | 3 | ... |
| 1 dn | 2 dn | 1 dn | ... |
Since the scores for players are not stored horizontally in our PLAYER_SCORECARD table this is a great example of how the PIVOT function introduced in 11g can be very helpful.
Let's say we have two players' scores per hole in our table.
SQL> SELECT * FROM player_scorecard; PLAYER_NAM HOLE_NUM SCORE ---------- ---------- ---------- Bob 1 4 Bob 2 3 Bob 3 3 Bob 4 5 Bob 5 5 Bob 6 6 Bob 7 3 Bob 8 4 Bob 9 4 Bob 10 4 Bob 11 2 Bob 12 4 Bob 13 5 Bob 14 4 Bob 15 2 Bob 16 3 Bob 17 4 Bob 18 5 Joe 1 3 Joe 2 4 Joe 3 3 Joe 4 4 Joe 5 4 Joe 6 5 Joe 7 5 Joe 8 2 Joe 9 6 Joe 10 4 Joe 11 3 Joe 12 4 Joe 13 4 Joe 14 3 Joe 15 3 Joe 16 4 Joe 17 4 Joe 18 4 36 rows selected.
Now what we want to do is show what the score would be given that Bob and Joe were opponents in a Match Play tournament.
The first thing we need to do is "stack" each player's scores per hole next to each other so that we can determine who had the better score for a given hole.
The first thing we need to do is "stack" each player's scores per hole next to each other so that we can determine who had the better score for a given hole.
SQL> SELECT p1.hole_num,
2 p1.p1_player_name,
3 p1.p1_score,
4 p2.p2_player_name,
5 p2.p2_score
6 FROM (
7 SELECT p.hole_num, p.player_name p1_player_name, p.score p1_score
8 FROM player_scorecard p
9 WHERE p.player_name = 'Bob'
10 ) p1,
11 (
12 SELECT p.hole_num, p.player_name p2_player_name, p.score p2_score
13 FROM player_scorecard p
14 WHERE p.player_name = 'Joe'
15 ) p2
16 WHERE p1.hole_num = p2.hole_num;
HOLE_NUM P1_PLAYER_ P1_SCORE P2_PLAYER_ P2_SCORE
---------- ---------- ---------- ---------- ----------
1 Bob 4 Joe 3
2 Bob 3 Joe 4
3 Bob 3 Joe 3
4 Bob 5 Joe 4
5 Bob 5 Joe 4
6 Bob 6 Joe 5
7 Bob 3 Joe 5
8 Bob 4 Joe 2
9 Bob 4 Joe 6
10 Bob 4 Joe 4
11 Bob 2 Joe 3
12 Bob 4 Joe 4
13 Bob 5 Joe 4
14 Bob 4 Joe 3
15 Bob 2 Joe 3
16 Bob 3 Joe 4
17 Bob 4 Joe 4
18 Bob 5 Joe 4
18 rows selected.
Now that we've done that, we can determine if a player has gone "one up" or "one down" on a hole by comparing their score to their opponent's score. In addition, using the analytic form of the SUM() function we can keep a running total of how much each player is up or down relative to their opponent.
SQL> SELECT p1.hole_num,
2 p1.p1_player_name,
3 p1.p1_score,
4 SUM(CASE WHEN p1.p1_score < p2.p2_score THEN -1
5 WHEN p1.p1_score > p2.p2_score THEN 1
6 ELSE 0
7 END) OVER (ORDER BY p1.hole_num) p1_up_down,
8 p2.p2_player_name,
9 p2.p2_score,
10 SUM(CASE WHEN p1.p1_score > p2.p2_score THEN -1
11 WHEN p1.p1_score < p2.p2_score THEN 1
12 ELSE 0
13 END) OVER (ORDER BY p1.hole_num) p2_up_down
14 FROM (
15 SELECT p.hole_num, p.player_name p1_player_name, p.score p1_score
16 FROM player_scorecard p
17 WHERE p.player_name = 'Bob'
18 ) p1,
19 (
20 SELECT p.hole_num, p.player_name p2_player_name, p.score p2_score
21 FROM player_scorecard p
22 WHERE p.player_name = 'Joe'
23 ) p2
24 WHERE p1.hole_num = p2.hole_num;
HOLE_NUM P1_PLAYER_ P1_SCORE P1_UP_DOWN P2_PLAYER_ P2_SCORE P2_UP_DOWN
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 Bob 4 1 Joe 3 -1
2 Bob 3 0 Joe 4 0
3 Bob 3 0 Joe 3 0
4 Bob 5 1 Joe 4 -1
5 Bob 5 2 Joe 4 -2
6 Bob 6 3 Joe 5 -3
7 Bob 3 2 Joe 5 -2
8 Bob 4 3 Joe 2 -3
9 Bob 4 2 Joe 6 -2
10 Bob 4 2 Joe 4 -2
11 Bob 2 1 Joe 3 -1
12 Bob 4 1 Joe 4 -1
13 Bob 5 2 Joe 4 -2
14 Bob 4 3 Joe 3 -3
15 Bob 2 2 Joe 3 -2
16 Bob 3 1 Joe 4 -1
17 Bob 4 1 Joe 4 -1
18 Bob 5 2 Joe 4 -2
18 rows selected.
Now we have a result set that we can PIVOT and display the match play scores horizontally. We can also include the "up" and "down" text by looking at the SIGN of each player's match play score per hole.
SQL> WITH scores AS
2 (
3 SELECT p1.hole_num,
4 p1.p1_player_name,
5 p1.p1_score,
6 SUM(CASE WHEN p1.p1_score < p2.p2_score THEN -1
7 WHEN p1.p1_score > p2.p2_score THEN 1
8 ELSE 0
9 END) OVER (ORDER BY p1.hole_num) p1_up_down,
10 p2.p2_player_name,
11 p2.p2_score,
12 SUM(CASE WHEN p1.p1_score > p2.p2_score THEN -1
13 WHEN p1.p1_score < p2.p2_score THEN 1
14 ELSE 0
15 END) OVER (ORDER BY p1.hole_num) p2_up_down
16 FROM (
17 SELECT p.hole_num, p.player_name p1_player_name, p.score p1_score
18 FROM player_scorecard p
19 WHERE p.player_name = 'Bob'
20 ) p1,
21 (
22 SELECT p.hole_num, p.player_name p2_player_name, p.score p2_score
23 FROM player_scorecard p
24 WHERE p.player_name = 'Joe'
25 ) p2
26 WHERE p1.hole_num = p2.hole_num
27 )
28 SELECT player,
29 ABS("1") ||' '||DECODE(SIGN("1"), 1,'up',-1,'down','even') hole1,
30 ABS("2") ||' '||DECODE(SIGN("2"), 1,'up',-1,'down','even') hole2,
31 ABS("3") ||' '||DECODE(SIGN("3"), 1,'up',-1,'down','even') hole3,
32 ABS("4") ||' '||DECODE(SIGN("4"), 1,'up',-1,'down','even') hole4,
33 ABS("5") ||' '||DECODE(SIGN("5"), 1,'up',-1,'down','even') hole5,
34 ABS("6") ||' '||DECODE(SIGN("6"), 1,'up',-1,'down','even') hole6,
35 ABS("7") ||' '||DECODE(SIGN("7"), 1,'up',-1,'down','even') hole7,
36 ABS("8") ||' '||DECODE(SIGN("8"), 1,'up',-1,'down','even') hole8,
37 ABS("9") ||' '||DECODE(SIGN("9"), 1,'up',-1,'down','even') hole9,
38 ABS("10")||' '||DECODE(SIGN("10"),1,'up',-1,'down','even') hole10,
39 ABS("11")||' '||DECODE(SIGN("11"),1,'up',-1,'down','even') hole11,
40 ABS("12")||' '||DECODE(SIGN("12"),1,'up',-1,'down','even') hole12,
41 ABS("13")||' '||DECODE(SIGN("13"),1,'up',-1,'down','even') hole13,
42 ABS("14")||' '||DECODE(SIGN("14"),1,'up',-1,'down','even') hole14,
43 ABS("15")||' '||DECODE(SIGN("15"),1,'up',-1,'down','even') hole15,
44 ABS("16")||' '||DECODE(SIGN("16"),1,'up',-1,'down','even') hole16,
45 ABS("17")||' '||DECODE(SIGN("17"),1,'up',-1,'down','even') hole17,
46 ABS("18")||' '||DECODE(SIGN("18"),1,'up',-1,'down','even') hole18
47 FROM (
48 SELECT *
49 FROM (
50 SELECT p1_player_name player,
51 p1_up_down,
52 hole_num
53 FROM scores
54 )
55 PIVOT (MAX(p1_up_down) FOR hole_num IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18))
56 UNION ALL
57 SELECT *
58 FROM (
59 SELECT p2_player_name,
60 p2_up_down,
61 hole_num
62 FROM scores
63 )
64 PIVOT (MAX(p2_up_down) FOR hole_num IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18))
65 );
PLAYER HOLE1 HOLE2 HOLE3 HOLE4 HOLE5 HOLE6 HOLE7 HOLE8 HOLE9 HOLE10 HOLE11 HOLE12 HOLE13 HOLE14 HOLE15 HOLE16 HOLE17 HOLE18
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
Bob 1 up 0 even 0 even 1 up 2 up 3 up 2 up 3 up 2 up 2 up 1 up 1 up 2 up 3 up 2 up 1 up 1 up 2 up
Joe 1 down 0 even 0 even 1 down 2 down 3 down 2 down 3 down 2 down 2 down 1 down 1 down 2 down 3 down 2 down 1 down 1 down 2 down
Granted, the output does not look exactly like the table I created above. It's missing the hole number and the individual player scores for each hole. Using the same principles it would not be too hard to add those pieces in. I'll leave that as an exercise for the reader.
No comments:
Post a Comment