Re: Final Patch for GROUPING SETS
| От | Pavel Stehule |
|---|---|
| Тема | Re: Final Patch for GROUPING SETS |
| Дата | |
| Msg-id | CAFj8pRBUO78OcfRBLMkC_RUUzTa=pN49r8TMZinzV4QQM-gmAQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Final Patch for GROUPING SETS (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
| Ответы |
Re: Final Patch for GROUPING SETS
(Andrew Gierth <andrew@tao11.riddles.org.uk>)
|
| Список | pgsql-hackers |
Hi
I checked this patch, and it working very wellpostgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place);
name | place | sum | grouping | grouping
-------+------------+-------+----------+----------
bmw | czech rep. | 100 | 0 | 0
bmw | germany | 1000 | 0 | 0
bmw | | 1100 | 0 | 1
opel | czech rep. | 7000 | 0 | 0
opel | germany | 7000 | 0 | 0
opel | | 14000 | 0 | 1
skoda | czech rep. | 10000 | 0 | 0
skoda | germany | 5000 | 0 | 0
skoda | | 15000 | 0 | 1
| | 30100 | 1 | 1
(10 rows)
* redundant sets should be ignored
postgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place), name;
name | place | sum | grouping | grouping
-------+------------+-------+----------+----------
bmw | czech rep. | 100 | 0 | 0
bmw | germany | 1000 | 0 | 0
bmw | | 1100 | 0 | 1
bmw | | 1100 | 0 | 1
opel | czech rep. | 7000 | 0 | 0
opel | germany | 7000 | 0 | 0
opel | | 14000 | 0 | 1
opel | | 14000 | 0 | 1
skoda | czech rep. | 10000 | 0 | 0
skoda | germany | 5000 | 0 | 0
skoda | | 15000 | 0 | 1
skoda | | 15000 | 0 | 1
(12 rows)
postgres=# explain select name, place, sum(count), grouping(name), grouping(place) from cars group by rollup(name, place), name;
QUERY PLAN
------------------------------------------------------------------------
GroupAggregate (cost=10000000001.14..10000000001.38 rows=18 width=68)
Grouping Sets: (name, place), (name), (name)
-> Sort (cost=10000000001.14..10000000001.15 rows=6 width=68)
Sort Key: name, place
-> Seq Scan on cars (cost=0.00..1.06 rows=6 width=68)
Planning time: 0.235 ms
(6 rows)
postgres=# select name, place, sum(count), grouping(name), grouping(place) from cars group by grouping sets((name, place), (name), (name),(place), ());
name | place | sum | grouping | grouping
-------+------------+-------+----------+----------
bmw | czech rep. | 100 | 0 | 0
bmw | germany | 1000 | 0 | 0
bmw | | 1100 | 0 | 1
bmw | | 1100 | 0 | 1
opel | czech rep. | 7000 | 0 | 0
opel | germany | 7000 | 0 | 0
opel | | 14000 | 0 | 1
opel | | 14000 | 0 | 1
skoda | czech rep. | 10000 | 0 | 0
skoda | germany | 5000 | 0 | 0
skoda | | 15000 | 0 | 1
skoda | | 15000 | 0 | 1
| | 30100 | 1 | 1
| czech rep. | 17100 | 1 | 0
| germany | 13000 | 1 | 0
(15 rows)
name | place | sum | grouping | grouping
-------+------------+-------+----------+----------
bmw | czech rep. | 100 | 0 | 0
bmw | germany | 1000 | 0 | 0
bmw | | 1100 | 0 | 1
bmw | | 1100 | 0 | 1
opel | czech rep. | 7000 | 0 | 0
opel | germany | 7000 | 0 | 0
opel | | 14000 | 0 | 1
opel | | 14000 | 0 | 1
skoda | czech rep. | 10000 | 0 | 0
skoda | germany | 5000 | 0 | 0
skoda | | 15000 | 0 | 1
skoda | | 15000 | 0 | 1
| | 30100 | 1 | 1
| czech rep. | 17100 | 1 | 0
| germany | 13000 | 1 | 0
(15 rows)
Fantastic work
Regards
Pavel
Pavel
2014-08-25 7:21 GMT+02:00 Andrew Gierth <andrew@tao11.riddles.org.uk>:
Here is the new version of our grouping sets patch. This version
supersedes the previous post.
We believe the functionality of this version to be substantially
complete, providing all the standard grouping set features except T434
(GROUP BY DISTINCT). (Additional tweaks, such as extra variants on
GROUPING(), could be added for compatibility with other databases.)
Since the debate regarding reserved keywords has not produced any
useful answer, the main patch here makes CUBE and ROLLUP into
col_name_reserved keywords, but a separate small patch is attached to
make them unreserved_keywords instead.
So there are now 5 files:
gsp1.patch - phase 1 code patch (full syntax, limited functionality)
gsp2.patch - phase 2 code patch (adds full functionality using the
new chained aggregate mechanism)
gsp-doc.patch - docs
gsp-contrib.patch - quote "cube" in contrib/cube and contrib/earthdistance,
intended primarily for testing pending a decision on
renaming contrib/cube or unreserving keywords
gsp-u.patch - proposed method to unreserve CUBE and ROLLUP
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
В списке pgsql-hackers по дате отправления: