Re: [GENERAL] DONT_CARE Aggregate
On Wed, Dec 19, 2012 at 5:28 PM, Robert James srobertja...@gmail.com wrote: And even better: An aggregate which will return the first instance that meets a certain predicate? Take a look at DISTINCT ON. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DONT_CARE Aggregate
Sergey - That's an interesting option, but I'm not sure how to use it as an aggregate. Could you give an example? On 12/20/12, Sergey Konoplev gray...@gmail.com wrote: On Wed, Dec 19, 2012 at 5:28 PM, Robert James srobertja...@gmail.com wrote: And even better: An aggregate which will return the first instance that meets a certain predicate? Take a look at DISTINCT ON. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 5:45 AM, Robert James srobertja...@gmail.comwrote: Sergey - That's an interesting option, but I'm not sure how to use it as an aggregate. Could you give an example? Here is an example: buildinghac= SELECT itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 1181 | B-0106 363 | B-0106 185 | B-0106 483 | B-0106 67 | B-0106 125 | B-0106 303 | B-0106 245 | B-0106 68 | B-0107 304 | B-0107 (10 rows) buildinghac= SELECT DISTINCT ON ( buildingnbr ) itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 245 | B-0106 364 | B-0107 1170 | B-0111 361 | B-0112 128 | B-0116 1013 | B-0117 129 | B-0118 368 | B-0300 1141 | B-0307 74 | B-0423 (10 rows) -- Regards, Richard Broersma Jr.
Re: [GENERAL] DONT_CARE Aggregate
I see. What if I need to do this along with an Aggregate Query. Eg something like: SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c), DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p) ... GROUP BY x,y,z On 12/20/12, Richard Broersma richard.broer...@gmail.com wrote: On Thu, Dec 20, 2012 at 5:45 AM, Robert James srobertja...@gmail.comwrote: Sergey - That's an interesting option, but I'm not sure how to use it as an aggregate. Could you give an example? Here is an example: buildinghac= SELECT itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 1181 | B-0106 363 | B-0106 185 | B-0106 483 | B-0106 67 | B-0106 125 | B-0106 303 | B-0106 245 | B-0106 68 | B-0107 304 | B-0107 (10 rows) buildinghac= SELECT DISTINCT ON ( buildingnbr ) itemnbr, buildingnbr FROM Actionitems ORDER BY buildingnbr LIMIT 10; itemnbr | buildingnbr -+- 245 | B-0106 364 | B-0107 1170 | B-0111 361 | B-0112 128 | B-0116 1013 | B-0117 129 | B-0118 368 | B-0300 1141 | B-0307 74 | B-0423 (10 rows) -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 12:00 PM, Robert James srobertja...@gmail.comwrote: I see. What if I need to do this along with an Aggregate Query. Eg something like: SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c), DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p) ... GROUP BY x,y,z ah, I get what you're trying to do. If you truly don't care about the value of C, then just use MIN() or MAX(). for the conditional part, use a CASE statement, along with MAX or MIN, like this: SELECT MIN(CASE WHEN P=TRUE THEN D ELSE NULL END) Because MIN() and MAX() ignore NULL values. (Except for the special case where all the values are null.) -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney.
Re: [GENERAL] DONT_CARE Aggregate
On Thu, Dec 20, 2012 at 3:28 AM, Robert James srobertja...@gmail.com wrote: Is there an aggregate that will return an arbitrary instance? That is, not necessarily the max or min, just any one? (Which might perform better than max or min) More importantly: Is there one which will return an arbitrary instance as long as it's not NULL There's an extension on PGXN which implements first()/last() aggregates in C: http://pgxn.org/dist/first_last_agg/ It should be slightly faster than min()/max(), but the difference is probably not significant in more complex queries. Regards, Marti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DONT_CARE Aggregate
Marti Raudsepp ma...@juffo.org writes: On Thu, Dec 20, 2012 at 3:28 AM, Robert James srobertja...@gmail.com wrote: Is there an aggregate that will return an arbitrary instance? That is, not necessarily the max or min, just any one? (Which might perform better than max or min) More importantly: Is there one which will return an arbitrary instance as long as it's not NULL There's an extension on PGXN which implements first()/last() aggregates in C: http://pgxn.org/dist/first_last_agg/ It should be slightly faster than min()/max(), but the difference is probably not significant in more complex queries. Another thing to consider is that the presence of any generic aggregate forces a full-table scan, since the system doesn't know that the aggregate has any particular behavior. MIN/MAX on the other hand can be optimized into index probes, if they are on indexed columns. If the query otherwise uses only MIN/MAX aggregates, it's not hard to believe that adding a FIRST() or LAST() instead of a MIN/MAX aggregate could make the query significantly slower, not faster. However, if you're targeting queries containing a variety of aggregates, or if any of them are on unindexed columns, then this special case may not be of much interest. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
