# EXPLAIN EXTENDED

How to create fast database queries

## Happy New Year!

Some say SQL is not good at graphics.

Well, they have some point. Database engines lack scanner drivers, there is no easy way to do sepia, and magic wand, let's be honest, is just poor.

However, you can make some New Year paintings with SQL.

Let's make a New Year clock showing 12 o'clock in Oracle.

### #1. Circle

First, we need a circle shape. In Photoshop, you could just select it from the toolbar, but in SQL, we need to use some math.

In math, a circle is defined by this formula: `x2 + y2 = R2`.

`x2 + y2` is, as we know, the square of a distance from the center of the coordinate grid, and `R` is the radius. This means all points at distance `R` from the center, which of course is a circle shape.

SQL outputs data in tabular format. We will draw out art string by string, and for each line we need to know where to put the paint (just some ASCII symbols). Since it's a circle, every line intersects it at most twice, and we shall put the symbols at the places where the circle and the line intersect. To know how many symbols are there from the beginning of the string, this formula is used:

`ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle`

To make our circle more circle-shaped, we need to choose the characters that correspond to the angle of the circle line in each given place. These characters are the most similar to the lines: `=/|\`

Different characters correspond to different angles. To figure out the angle from the line number, we should use `ACOS`:

`ROUND(ACOS(angle / 20) / 3.1415926 * 4) * SIGN((line - 21) / 20) + 3 AS sign`

This would split the circle into several sectors, and for each of these sectors a corresponding character will be chosen.

Finally, we need to avoid the gaps in the lines. To do this, we will not only fill the intersections, but all space from the previous (or next) intersection to the current. To calculated the width of the string on each line that will be filled with the characters, we will use `LAG` and `LEAD`, the analytic functions.

Now, let's put it all together:

```WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line &lt; 21 THEN
COALESCE(LAG(angle) OVER (ORDER BY line), 0)
WHEN line &gt; 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level &lt;= 41
) q
) q
)
SELECT  *
FROM    circle

```
LAYER LINE DRAWING
1 1 ==
1 2 ==============
1 3 //// \\\\
1 4 /// \\\
1 5 // \\
1 6 // \\
1 7 // \\
1 8 // \\
1 9 // \\
1 10 // \\
1 11 / \
1 12 // \\
1 13 / \
1 14 || ||
1 15 | |
1 16 | |
1 17 || ||
1 18 | |
1 19 | |
1 20 | |
1 21 | |
1 22 | |
1 23 | |
1 24 | |
1 25 || ||
1 26 | |
1 27 | |
1 28 || ||
1 29 \ /
1 30 \\ //
1 31 \ /
1 32 \\ //
1 33 \\ //
1 34 \\ //
1 35 \\ //
1 36 \\ //
1 37 \\ //
1 38 \\\ ///
1 39 \\\\ ////
1 40 ==============
1 41 ==

### #2. Dial

Now, we shall draw the clock dial on a separate layer (yes, you can draw on layers in SQL).

We will use Roman numerals for the dial. Oracle has an internal function to format the numbers as Roman numerals, but SQL graphics is the first time I'm using this feature in practice.

The principle is the same: we should calculate the angle of each number, then the line and the column that intersect the dial at the given angle and put the number there.

We will only output the lines containing actual numbers. Each line will contain one or two numbers (1 and 6 go on their own lines, the others go in pairs). We can use `MIN` and `MAX` to distinguish them.

Here's what the lines of our dial will look like:

```WITH    dial AS
(
SELECT  2,
line,
RPAD(' ', 20 - angle, ' ') ||
RPAD(rnf, angle * 2 - LENGTH(rns)) ||
RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
FROM    (
SELECT  line, angle,
DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
FROM    (
SELECT  level + 2 AS line,
ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
FROM    dual
CONNECT BY
level &lt;= 37
) lines
JOIN    (
SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
FROM    dual
CONNECT BY
level &lt;= 12
) hours
ON      hline = line
GROUP BY
line, angle
) q
)
SELECT  *
FROM    dial
ORDER BY
line

```
2 3 XII
2 5 XI I
2 12 X II
2 21 IX III
2 30 VIII IV
2 37 VII V
2 39 VI

Note that the shape is distorted: this is because of the gaps in the line numbers. It will be taken care of later.

### #3. Hands and the center pin

Hands and the center pin are simple: we will use `|||` for the hour hand, `|` for the minute hand and `O` for the pin. On a New Year clock the hands, fortunately, are vertical and directed in one way.

Here's the hour hand:

```WITH    hourhand AS
(
SELECT  3 AS layer,
level + 10 AS line,
RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
FROM    dual
CONNECT BY
level &lt;= 10
)
SELECT  *
FROM    hourhand
ORDER BY
line
```
LAYER LINE DRAWING
3 11 |||
3 12 |||
3 13 |||
3 14 |||
3 15 |||
3 16 |||
3 17 |||
3 18 |||
3 19 |||
3 20 |||

, the minute hand:

```WITH    minutehand AS
(
SELECT  4 AS layer,
level + 4 AS line,
RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
FROM    dual
CONNECT BY
level &lt;= 16
)
SELECT  *
FROM    minutehand
ORDER BY
line
```
LAYER LINE DRAWING
4 5 |
4 6 |
4 7 |
4 8 |
4 9 |
4 10 |
4 11 |
4 12 |
4 13 |
4 14 |
4 15 |
4 16 |
4 17 |
4 18 |
4 19 |
4 20 |

and the pin:

```WITH    pin AS
(
SELECT  5 AS layer,
21 AS line,
FROM    dual
)
SELECT  *
FROM    pin
ORDER BY
line
```
LAYER LINE DRAWING
5 21 O

### #4. Merging the layers.

Now we need to merge the layers.

The last layers should go on the foreground, the first ones on the background. Space symbol should be transparent: if there is a character on the background layer, it should be visible through it.

To do it we we apply this trick:

1. Output all rows from all layers in a single query, using `UNION ALL`
2. Split each row into the individual characters, one character per record
3. Using `ROW_NUMBER()`, the analytical function, order the characters within each line and columns so that visible characters from higher layers go first, and spaces and characters from the lower layers go last:

`ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn`

4. Filter the characters on `rn` so that only the topmost character from the each line and column is returned
5. Concatenate all characters line-wise, using a recursive query and `SYS_CONNECT_BY_PATH`. This function requires a separator, and we will use a question mark, `?`, for this purpose, since it's not used in our art.
6. Remove the separator from the concatenated strings, using `REPLACE`
7. Output the result

### #5. Result

Here's our query and drawing:

```WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line &lt; 21 THEN
COALESCE(LAG(angle) OVER (ORDER BY line), 0)
WHEN line &gt; 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level &lt;= 41
) q
) q
),
dial AS
(
SELECT  2,
line,
RPAD(' ', 20 - angle, ' ') ||
RPAD(rnf, angle * 2 - LENGTH(rns)) ||
RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
FROM    (
SELECT  line, angle,
DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
FROM    (
SELECT  level + 2 AS line,
ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
FROM    dual
CONNECT BY
level &lt;= 37
) lines
JOIN    (
SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
FROM    dual
CONNECT BY
level &lt;= 12
) hours
ON      hline = line
GROUP BY
line, angle
) q
),
hourhand AS
(
SELECT  3 AS layer,
level + 10 AS line,
RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
FROM    dual
CONNECT BY
level &lt;= 10
),
minutehand AS
(
SELECT  4 AS layer,
level + 4 AS line,
RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
FROM    dual
CONNECT BY
level &lt;= 16
),
pin AS
(
SELECT  5 AS layer,
21 AS line,
FROM    dual
),
m AS
(
SELECT  line, col, cc
FROM    (
SELECT  line, col, cc,
ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn
FROM    (
SELECT  cols.*, layers.*, SUBSTR(drawing, col, 1) AS cc
FROM    (
SELECT  level AS col
FROM    dual
CONNECT BY
level &lt;= 50
) cols
CROSS JOIN
(
SELECT  *
FROM    circle
UNION ALL
SELECT  *
FROM    dial
UNION ALL
SELECT  *
FROM    hourhand
UNION ALL
SELECT  *
FROM    minutehand
UNION ALL
SELECT  *
FROM    pin
) layers
)
)
WHERE   rn = 1
)
SELECT  REPLACE(drawing, '?') AS drawing
FROM    (
SELECT  SYS_CONNECT_BY_PATH(cc, '?') AS drawing, line, CONNECT_BY_ISLEAF AS leaf
FROM    m mi
mi.col = 1
CONNECT BY
mi.line = PRIOR mi.line
AND mi.col = PRIOR mi.col + 1
)
WHERE   leaf = 1
ORDER BY
line
```
DRAWING
==
==============
//// XII \\\\
/// \\\
// XI | I \\
// | \\
// | \\
// | \\
// | \\
// | \\
/ ||| \
//X ||| II\\
/ ||| \
|| ||| ||
| ||| |
| ||| |
|| ||| ||
| ||| |
| ||| |
| ||| |
| IX O III |
| |
| |
| |
|| ||
| |
| |
|| ||
\ /
\\VIII IV//
\ /
\\ //
\\ //
\\ //
\\ //
\\ //
\\ VII V //
\\\ ///
\\\\ VI ////
==============
==
Happy New Year!

Written by Quassnoi

December 31st, 2010 at 11:00 pm

Posted in Miscellaneous

### 9 Responses to 'Happy New Year!'

1. You are definitely insane ;)
Happy New Year (if is not 14 for you ;) ts

31 Dec 10 at 23:35

2. :)

This is awesome. You are a MySQL rockstar! Andy

1 Jan 11 at 01:49

3. WOW! You are the SQL god! :-O

1 Jan 11 at 05:23

4. You are a mysql wizard. Mo

1 Jan 11 at 19:14

5. Thank you all guys and happy New Year!

@ts: no, it’s 1st, like all over the world :) It’s Christmas, a religious holiday, that is on Jan 7th (Dec 24th Julian style).

@Andy and @Mo: it’s actually Oracle. It’s a little bit harder on MySQL (though of course still possible!) Quassnoi

2 Jan 11 at 01:44

6. LOL Alex! You definitely know how to impress. I’m not sure if this wins you SQL innovation award of the year, or nerd of the year! Happy Holidays mate. Ian

2 Jan 11 at 10:46

7. @Ian and all those interested: the previous New Year article had 4 similar paintings: https://explainextended.com/2009/12/31/happy-new-year/ Quassnoi

2 Jan 11 at 12:45

8. Love the fireworks! Can’t say I would have ever thought, “hey, let’s use SQL to draw firework glyphs!” :)

Perhaps you could title yourself “SQL Artist Extraordinaire”. Ian

2 Jan 11 at 14:19

9. very very nice. you are always rock. Halim

4 Jan 11 at 14:17