CODEWITHSUNDEEP
postgresql
Deutro
Deutro

Reputation: 3313

Aggregate column by condition

I have to write an Uptime Report for a Homepage. In the table that is available for me I have 2 columns. The first one is the status of the Homepage (0 is offline and 1 is online) and the second one is the duration of this status in seconds. An example table might look like this:

-------------------------
|  Status  |  Duration  |  
-------------------------
|  0       |  50        |
-------------------------
|  1       |  10        |
-------------------------
|  1       |  20        |
-------------------------
|  1       |  50        |
-------------------------
|  0       |  50        |
-------------------------
|  0       |  50        |
-------------------------
|  1       |  20        |
-------------------------

This does not look that nice in my report because the same stati should be aggregated into one row and not be shown as multiple rows like this:

-------------------------
|  Status  |  Duration  |  
-------------------------
|  0       |  50        |
-------------------------
|  1       |  80        |
-------------------------
|  0       |  100       |
-------------------------
|  1       |  20        |
-------------------------

Is there a way to achieve this with PostgreSQL?

Upvotes: 0

Views: 48

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

As I said already, you would need an id/datetime column to track the progress. Only then would you be able to use LEAD/LAG function or TABIBITOSAN method for this scenario.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE t
    (id INT,Status int, Duration int)
;

INSERT INTO t
    (id,Status, Duration)
VALUES
    (1,0, 50),
    (2,1, 10),
    (3,1, 20),
    (4,1, 50),
    (5,0, 50),
    (6,0, 50),
    (7,1, 20)
;

Query 1:

SELECT STATUS
    ,Sum(duration)
FROM (
    SELECT t.*
        ,row_number() OVER (
            ORDER BY id
            ) - row_number() OVER (
            PARTITION BY STATUS ORDER BY id
            ) AS seq
    FROM t
    ) s
GROUP BY STATUS
    ,seq
ORDER BY max(id)

Results:

| status | sum |
|--------|-----|
|      0 |  50 |
|      1 |  80 |
|      0 | 100 |
|      1 |  20 |

Upvotes: 1

JustMe
JustMe

Reputation: 2373

This aggregation can be achieved by using window functions and grouping:

select max(status) status, sum(duration) duration from (
          select status, duration, sum(case when status <> par then 1 else 0 end) over (order by id) wf from ( 
            select id, status, duration, lag(status, 1) over () par from test
          ) a order by id
      ) a group by wf order by wf

You just need to properly set your ordering in window function.

Test data:

create table test (status int, duration int, id bigserial primary key);

insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (1, 10);
insert into test (status, duration) values (1, 20);
insert into test (status, duration) values (1, 50);
insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (1, 20);

Output like you wanted.

Upvotes: 0

Related Questions