Обсуждение: rolling window without aggregation

<div class="WordSection1"><p class="MsoNormal">Hi SQL experts,<p class="MsoNormal"> <p class="MsoNormal">I’ve got a
questionhere, is that possible to implement a window function without aggregation? Any SQL could get below desired
result?<pclass="MsoNormal"> <p class="MsoNormal">For example:<p class="MsoNormal"> <p class="MsoNormal">Table input<p
class="MsoNormal">   date    | id<p class="MsoNormal">------------+--------<p class="MsoNormal">2014-04-26 | A<p
class="MsoNormal">2014-05-03| B<p class="MsoNormal">2014-05-10 | C<p class="MsoNormal">2014-05-17 | D<p
class="MsoNormal">2014-05-24| E<p class="MsoNormal">2014-05-31 | F<p class="MsoNormal"> <p class="MsoNormal">Expected
output,use 2 week roll up as an example:<p class="MsoNormal">    date    | id<p
class="MsoNormal">------------+--------<pclass="MsoNormal">2014-04-26 | A<p class="MsoNormal">2014-05-03 | A<p
class="MsoNormal">2014-05-03| B<p class="MsoNormal">2014-05-10 | B<p class="MsoNormal">2014-05-10 | C<p
class="MsoNormal">2014-05-17| C<p class="MsoNormal">2014-05-17 | D<p class="MsoNormal">2014-05-24 | D<p
class="MsoNormal">2014-05-24| E<p class="MsoNormal">2014-05-31 | E<p class="MsoNormal">2014-05-31 | F<p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">Thanks,<p
class="MsoNormal">Suya</div>

Huang, Suya wrote
> Hi SQL experts,
> 
> I've got a question here, is that possible to implement a window function
> without aggregation? Any SQL could get below desired result?
> 
> For example:
> 
> Table input
>     date    | id
> ------------+--------
> 2014-04-26 | A
> 2014-05-03 | B
> 2014-05-10 | C
> 2014-05-17 | D
> 2014-05-24 | E
> 2014-05-31 | F
> 
> Expected output, use 2 week roll up as an example:
>     date    | id
> ------------+--------
> 2014-04-26 | A
> 2014-05-03 | A
> 2014-05-03 | B
> 2014-05-10 | B
> 2014-05-10 | C
> 2014-05-17 | C
> 2014-05-17 | D
> 2014-05-24 | D
> 2014-05-24 | E
> 2014-05-31 | E
> 2014-05-31 | F
> 
> 
> 
> Thanks,
> Suya

Use the lead() function to create a second column.  Then write a UNION ALL
query to covert the two columns into one.

David J.




--
View this message in context: http://postgresql.nabble.com/rolling-window-without-aggregation-tp5829344p5829345.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.