By now, you've probably seen that PostgreSQL 8.4 can produce Mandelbrot sets
like the one below, but what are Common Table Expressions really about?
Wednesday, July 1. 2009
WITH (so much drama in the CTE)
CTEs, or Common Table Expressions, are just views you create at run time. Oh,
and they can refer to themselves. That latter capability is powerful and
flexible (read confusing), so I'll go over that one later. Run-time views are
pretty handy, though.
Have you ever wondered about posting behavior on a forum? Here's a way to
find out with CTEs.
Simple!
How about poster lists at each level? We'll use more CTEs for this. Each piece
builds on the last, making the query easy to maintain:
Oh, right. That Mandelbrot set. Here it is.
And the spec-compliant code that makes it happen:
and they can refer to themselves. That latter capability is powerful and
flexible (read confusing), so I'll go over that one later. Run-time views are
pretty handy, though.
Have you ever wondered about posting behavior on a forum? Here's a way to
find out with CTEs.
WITH p1 AS ( -- CTE called p1. Use this in the main query
SELECT poster_id, count(*) AS "posts"
FROM forum_posts
GROUP BY poster_id
)
SELECT min(posts), floor(avg(posts)) AS "avg", max(posts)
FROM p1;
min | avg | max
-----+-----+-----
67 | 100 | 129
(1 row)
Simple!
How about poster lists at each level? We'll use more CTEs for this. Each piece
builds on the last, making the query easy to maintain:
WITH p1 AS (
SELECT poster_id, count(*) AS "posts"
FROM forum_posts
GROUP BY poster_id
),
p2 AS (
SELECT min(posts), floor(avg(posts)) AS "avg", max(posts)
FROM p1
),
p3 AS (
SELECT
CASE p1.posts
WHEN p2.min THEN 'least'
WHEN p2.avg THEN 'average'
WHEN p2.max THEN 'most'
END AS "Rank",
p1.poster_id,
p1.posts
FROM
p1
CROSS JOIN
p2
WHERE
p1.posts IN (p2.min, p2.avg, p2.max)
)
SELECT "Rank", array_to_string(array_agg(poster_id),',') AS posters, posts
FROM p3
GROUP BY "Rank", posts
ORDER BY posts
Rank | least
posters | 361
posts | 67
--------+--------
Rank | average
posters | 822,690,610,756,418,404,547,472,109,749,840,50,812,917,255,102,806,483,38,6,793,797,485,964,448,170,569,576,976,30,381,622,459,26,90,709,216,110,456,427,244,410,36,979,955,847,122,920,839,156,294
posts | 100
--------+--------
Rank | most
posters | 835
posts | 129
Oh, right. That Mandelbrot set. Here it is.
....................................................................................
.......................................................................................
.........................................................................................
...........................................................................................
....................................................,,,,,,,,,.................................
................................................,,,,,,,,,,,,,,,,,,.............................
..............................................,,,,,,,,,,,,,,,,,,,,,,,,..........................
............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................
..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,......................
.........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,....................
........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................
.......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................
.......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,................
......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,...............
....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,..............
...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,.............
.................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,............
................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,............
..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,...........
.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,..........
...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+% ----,,,,,,,,,,,,,,,,,,,.........
..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ +## %+%---,,,,,,,,,,,,,,,,,,.........
........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----# # +---,,,,,,,,,,,,,,,,,,........
.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------% %-----,,,,,,,,,,,,,,,,,........
.....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+ ------,,,,,,,,,,,,,,,,,.......
....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@ +-----------,,,,,,,,,,,,.......
..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++ ++-----------,,,,,,,,,,,,......
.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@ %%+@+@%------+-,,,,,,,,,,,......
................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- # ++% % @-----++--,,,,,,,,,,,.....
..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+ % %%++ %+%@-,,,,,,,,,,,.....
.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# #% ++-,,,,,,,,,,,,....
............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+ @---,,,,,,,,,,,,....
..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++% ---,,,,,,,,,,,,....
.........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ + %+---,,,,,,,,,,,,,...
........,,,,,,,,,,,,,,,,,,,,,--------------------@ +----,,,,,,,,,,,,...
.......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+ ----,,,,,,,,,,,,...
.......,,,,,,,,,,,,,,,,,,,,,--++------+---------+% +++--,,,,,,,,,,,,..
......,,,,,,,,,,,,,,,,,,,,,,--%+-----++--------- #+-,,,,,,,,,,,,..
.....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+% --,,,,,,,,,,,,..
.....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----% +--,,,,,,,,,,,,,..
....,,,,,,,,,,,,,,,,,,,,,,------+@ @ @@++++# +--,,,,,,,,,,,,,..
....,,,,,,,,,,,,,,,,,,,,,-------% #++% -,,,,,,,,,,,,,..
...,,,,,,,,,,,,,,,,,,,,,------++%# %%@ %-,,,,,,,,,,,,,,.
...,,,,,,,,,,,,,,,,,,,--------+ % +--,,,,,,,,,,,,,,.
...,,,,,,,,,,,,,,,,,,-----+--++@ # --,,,,,,,,,,,,,,.
..,,,,,,,,,,,,,,,,,-------%+++% @--,,,,,,,,,,,,,,,.
..,,,,,,,,,,,-------------+ @#@ ---,,,,,,,,,,,,,,,.
..,,,,,,,,,---@--------@-+% +---,,,,,,,,,,,,,,,.
..,,,,,------- +-++++-+%%% +----,,,,,,,,,,,,,,,.
..,,,,,,------%--------++% +----,,,,,,,,,,,,,,,.
..,,,,,,,,,,--+----------++# ---,,,,,,,,,,,,,,,.
..,,,,,,,,,,,,------------+@@@% +--,,,,,,,,,,,,,,,.
..,,,,,,,,,,,,,,,,,------- +++% %--,,,,,,,,,,,,,,,.
...,,,,,,,,,,,,,,,,,,---------+@ @ --,,,,,,,,,,,,,,.
...,,,,,,,,,,,,,,,,,,,,------- # %@ +--,,,,,,,,,,,,,,.
...,,,,,,,,,,,,,,,,,,,,,-------++@ %+ %-,,,,,,,,,,,,,,.
....,,,,,,,,,,,,,,,,,,,,,------- %++% %-,,,,,,,,,,,,,..
....,,,,,,,,,,,,,,,,,,,,,,------+# %# #@ ++++ +--,,,,,,,,,,,,,..
.....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+ +--,,,,,,,,,,,,,..
.....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------% #--,,,,,,,,,,,,..
......,,,,,,,,,,,,,,,,,,,,,,--++-----%%--------- @#--,,,,,,,,,,,,..
.......,,,,,,,,,,,,,,,,,,,,,---------------------+@ +-++,,,,,,,,,,,,...
........,,,,,,,,,,,,,,,,,,,,,--------------------+ ----,,,,,,,,,,,,...
.........,,,,,,,,,,,,,,,,,,,,----,,,------------- #+----,,,,,,,,,,,,...
..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ + +---,,,,,,,,,,,,,...
...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%# #---,,,,,,,,,,,,....
............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+# @ @---,,,,,,,,,,,,....
.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# + @--,,,,,,,,,,,,....
..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+% %+@ %+-+ +++%-,,,,,,,,,,,.....
................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++ # % -----++-,,,,,,,,,,,,.....
.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@ %++++++------%-,,,,,,,,,,,......
...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++ +------------,,,,,,,,,,,,......
....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+% +--------,,,,,,,,,,,,,,,.......
......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+# -----,,,,,,,,,,,,,,,,,,.......
.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ #----,,,,,,,,,,,,,,,,,,........
.........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+% %#---,,,,,,,,,,,,,,,,,,,........
..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@ %+%%--,,,,,,,,,,,,,,,,,,.........
............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+% %----,,,,,,,,,,,,,,,,,,..........
.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,..........
...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,...........
................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,............
..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,.............
...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,.............
.....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,..............
.....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,...............
.......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................
........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,..................
........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................
.........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,....................
..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,......................
............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................
.............................................,,,,,,,,,,,,,,,,,,,,,,,,..........................
................................................,,,,,,,,,,,,,,,,,.............................
.....................................................,,,,....................................
...........................................................................................
.........................................................................................
......................................................................................
....................................................................................
.................................................................................
..............................................................................
...........................................................................
........................................................................
And the spec-compliant code that makes it happen:
WITH RECURSIVE
x(i)
AS (
VALUES(0)
UNION ALL
SELECT i + 1 FROM x WHERE i < 101
),
Z(Ix, Iy, Cx, Cy, X, Y, I)
AS (
SELECT Ix, Iy, X::float, Y::float, X::float, Y::float, 0
FROM
(SELECT -2.2 + 0.031 * i, i FROM x) AS xgen(x,ix)
CROSS JOIN
(SELECT -1.5 + 0.031 * i, i FROM x) AS ygen(y,iy)
UNION ALL
SELECT Ix, Iy, Cx, Cy, X * X - Y * Y + Cx AS X, Y * X * 2 + Cy, I + 1
FROM Z
WHERE X * X + Y * Y < 16.0
AND I < 27
),
Zt (Ix, Iy, I) AS (
SELECT Ix, Iy, MAX(I) AS I
FROM Z
GROUP BY Iy, Ix
ORDER BY Iy, Ix
)
SELECT array_to_string(
array_agg(
SUBSTRING(
' .,,,-----++++%%%%@@@@#### ',
GREATEST(I,1),
1
)
),''
)
FROM Zt
GROUP BY Iy
ORDER BY Iy;
What about code reuse? In the case of databases code reuse implies creating a regular view since that would allow it to be reused in multiple queries without having to type out the entire view definition each time.
Oh and the Mandelbrot is amazing. I haven't even tried to dive into that code