Обсуждение: Advance SQL subquery
Hello, I have a table like : date | value ------------------------------- 2009-09-19 | 1 2009-09-20 | 2 2009-09-21 | 6 2009-09-22 | 9 2009-09-23 | 1 I'd like a request which gives me the sum of each last n days. For example, if I want the sum of each 3 days, I want this result: date | sum_value ------------------------------- 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) I try to make a subquery which is apply on each row of a query, but it does work. Has anybody an idea? Thanks in advance! Anthony
On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV <avequeau@gmail.com> wrote: > Hello, > > I have a table like : > > date | value > ------------------------------- > 2009-09-19 | 1 > 2009-09-20 | 2 > 2009-09-21 | 6 > 2009-09-22 | 9 > 2009-09-23 | 1 > > I'd like a request which gives me the sum of each last n days. > For example, if I want the sum of each 3 days, I want this result: > > date | sum_value > ------------------------------- > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > I try to make a subquery which is apply on each row of a query, but it > does work. select date, (select count(*) from foo where date between f.date - 1 and f.date + 1) from foo f;
On 23 sep, 17:32, mmonc...@gmail.com (Merlin Moncure) wrote: > On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV <avequ...@gmail.com> wrote: > > Hello, > > > I have a table like : > > > date | value > > ------------------------------- > > 2009-09-19 | 1 > > 2009-09-20 | 2 > > 2009-09-21 | 6 > > 2009-09-22 | 9 > > 2009-09-23 | 1 > > > I'd like a request which gives me the sum of each last n days. > > For example, if I want the sum of each 3 days, I want this result: > > > date | sum_value > > ------------------------------- > > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > > I try to make a subquery which is apply on each row of a query, but it > > does work. > > select date, (select count(*) from foo where date between f.date - 1 > and f.date + 1) from foo f; > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general It works. Thank you!
On Wed, Sep 23, 2009 at 05:13:34AM -0700, AnthonyV wrote: > Hello, > > I have a table like : > > date | value > ------------------------------- > 2009-09-19 | 1 > 2009-09-20 | 2 > 2009-09-21 | 6 > 2009-09-22 | 9 > 2009-09-23 | 1 > > I'd like a request which gives me the sum of each last n days. > For example, if I want the sum of each 3 days, I want this result: > > date | sum_value > ------------------------------- > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19) > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20) > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21) > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22) > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23) > > I try to make a subquery which is apply on each row of a query, but it > does work. > > Has anybody an idea? We've implemented part of the SQL standard windowing functions, but not the part (ROWS BETWEEN M PRECEDING AND N FOLLOWING) that would make this most convenient. What you can do instead is something like this: SELECT "date", ( value + COALESCE(lag(value,1) OVER w, 0) + COALESCE(lag(value,2) OVER w, 0) ) AS sum FROM your_log WINDOW w AS (ORDER BY "date") ORDER BY "date"; When we add (ROWS BETWEEN M PRECEDING AND N FOLLOWING) to the window, you'll be able to use sum() and parameterize it like this: SELECT "date", SUM (value) OVER w FROM your_log WINDOW w AS ( ORDER BY "date" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) ORDER BY "date"; Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate