Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sum raw with the same continuous flags

Sum raw with the same continuous flags

From
Shenli Zhu
Date:
Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
Table is like
| flag | num |  
|------+-----|
|    1 |   2 | \ 5
|    1 |   3 | /
|    0 |   1 | \ 7
|    0 |   6 | /
|    1 |   4 | \ 9
|    1 |   5 | /
|  ... | ... |  
I want to sum up the raw with the same continuous flags. E.g. flag in 1st and
2nd row are both 1, 3rd and 4th are both 0, 5th and 6th are both 1. So
the table becomes
| flag | num |
|------+-----|
|    1 |   5 |
|    0 |   7 |
|    1 |   9 |

Can I do this in SQL or PL/pgSQL? Any suggestions are welcome.

Re: Sum raw with the same continuous flags

From
Andreas Kretschmer
Date:
Shenli Zhu <zhushenli@gmail.com> wrote:

> Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
> Table is like
> | flag | num |  
> |------+-----|
> |    1 |   2 | \ 5
> |    1 |   3 | /
> |    0 |   1 | \ 7
> |    0 |   6 | /
> |    1 |   4 | \ 9
> |    1 |   5 | /
> |  ... | ... |  
> I want to sum up the raw with the same continuous flags. E.g. flag in 1st and
> 2nd row are both 1, 3rd and 4th are both 0, 5th and 6th are both 1. So
> the table becomes
> | flag | num |
> |------+-----|
> |    1 |   5 |
> |    0 |   7 |
> |    1 |   9 |
>
> Can I do this in SQL or PL/pgSQL? Any suggestions are welcome.

You can do that in plain SQL (window-function, sum() over (...)), but i
think you needs an order-criteria to do that.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Sum raw with the same continuous flags

From
Victor Yegorov
Date:
2013/6/1 Shenli Zhu <zhushenli@gmail.com>
Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
Table is like
| flag | num |  
|------+-----|
|    1 |   2 | \ 5
|    1 |   3 | /
|    0 |   1 | \ 7
|    0 |   6 | /
|    1 |   4 | \ 9
|    1 |   5 | /
|  ... | ... |  
I want to sum up the raw with the same continuous flags. E.g. flag in 1st and
2nd row are both 1, 3rd and 4th are both 0, 5th and 6th are both 1. So
the table becomes
| flag | num |
|------+-----|
|    1 |   5 |
|    0 |   7 |
|    1 |   9 |

Can I do this in SQL or PL/pgSQL? Any suggestions are welcome.

WITH data(flag,num) AS (VALUES
    (true,2),(true, 3),
    (false,1),(false,6),
    (true,4),(true,5))
SELECT flag,
       sum(num) AS sum_num
  FROM (
    SELECT flag,num,
           sum(grp_flag) OVER (ORDER BY rn) AS grp
      FROM (
        SELECT flag,num,
               row_number() OVER() AS rn,
               CASE WHEN lag(flag) OVER () = flag THEN NULL ELSE 1 END AS grp_flag
          FROM data
        ) s1
    ) s2
 GROUP BY grp,flag
 ORDER BY grp;

You should introduce some explicit ordering column into your table though, as results will
change otherwise based on your DB activity.


--
Victor Y. Yegorov

Re: Sum raw with the same continuous flags

From
David Johnston
Date:
Виктор Егоров wrote
> 2013/6/1 Shenli Zhu <

> zhushenli@

> >
>
>> Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
>> Table is like
>> | flag | num |
>> |------+-----|
>> |    1 |   2 | \ 5
>> |    1 |   3 | /
>> |    0 |   1 | \ 7
>> |    0 |   6 | /
>> |    1 |   4 | \ 9
>> |    1 |   5 | /
>> |  ... | ... |
>> I want to sum up the raw with the same continuous flags.

What you want to do is possible and you have been provided some resources to
do so.  However, with the table you have provided it is not possible to
implement such a solution.  Tables do not have "order" to them inherently.
You must decide on a mechanism to distinguish between the "1"s in the first
group and the "1"s in the second group and code your query to ORDER BY using
that mechanism before assigning group identifiers and performing the group
sub-total.

This is not an optional requirement.  If you query works without doing that
you are simply lucky and at some point in the future it can possibly stop
working which means that you query is broken/buggy.

Since your example looks made-up you are on your own for the "mechanism" but
if time can be used you can possibly get away with either an "serial" column
or a "current timestamp" column to enforce the order.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Sum-raw-with-the-same-continuous-flags-tp5757658p5757671.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.