Re: Explanation of tree-generating query
| От | David G. Johnston |
|---|---|
| Тема | Re: Explanation of tree-generating query |
| Дата | |
| Msg-id | CAKFQuwY0o2BwcXX_VarbUhA7jcoSvGpk4K-p7y_c_RF-PAQfxA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Explanation of tree-generating query (Guyren Howe <guyren@gmail.com>) |
| Список | pgsql-general |
I feel like I'm pretty decent with Postgres. But I saw the following query on the excellent Periscope blog. I've no idea how it works, and the various symbols involved are difficult to look up either with google or in the documentation. I believe the @ sign is probably ABS, but the <= clause in the consequent of a when-then is something I haven't seen before. I'm comfortable with the rest. Can someone explain how this works?with a as ( select * from generate_series(0, 3, 1) ) , b as ( select * from generate_series(-3, 3, 1) ) , tree as ( select a.generate_series as t , b.generate_series as branch from a, b where case when mod(a.generate_series, 2) = 1 then @ b.generate_series <= a.generate_series and mod(@ b.generate_series, 2) = 1 else @ b.generate_series <= a.generate_series and mod(@ b.generate_series, 2) = 0 end )
I hope this wasn't for someones homework... :)
The "<=" operator is the basic less-than-or-equal operator for numbers
generate_series is a function that provides one row for every result of stepping (by 1 in this case) from the starting value to the end value inclusive.
"and" is, loosely, the boolean operator of the same name
"mod" := modulus; the remainder when performing integer division. In this case the remainder when dividing by 2 is a test of odd/even
"b.generate_series" - the default name of the column in the query "SELECT * FROM generate_series(...)" is the name of the function that was executed - at least for this function: other functions can be defined to provide different names.
A := [0, 3]
B := [-3, 3]
FOR EACH combination of a,b (so 4 x 7 = 28 rows)
CASE WHEN (a IS ODD)
THEN (true if abs(b) <= a AND (b IS ODD), otherwise false)
ELSE (true if abs(b) <= a AND (b IS EVEN), otherwise false)
END
Since the CASE expression is in the WHERE clause the result required must be a boolean - or NULL.
So in all cases only rows where abs(b) is less-than-or-equal-to a are returned (I suppose this is like a symmetric matrix so you only need half of the answers...)
Likewise, only return rows where the "even-ness" of a and b are the same (both even or both odd)
An alternate way to express the non-CTE portion of the query would be:
pseudo-code:
CREATE FUNCTION is_even(num int) RETURNS boolean
AS
$$
SELECT mod(num, 2) = 0;
$$
SELECT a.g_s AS t, b.g_s AS branch
FROM a
CROSS JOIN b
WHERE (abs(b) <= a)
AND is_even(b) = is_even(a);
In times like this it would nice to be able to define temporary functions just like you can use CTEs to define temporary views...not that big a deal though.
Hope that helps.
David J.
В списке pgsql-general по дате отправления: