1

I am trying to group data using Postgres.

Input Data:

enter image description here

My expectation result IF the output is 0, it will grouping with others, and IF output more than 0, it will grouping with other results, so we will know, time period for 0 output.

enter image description here

D-S
  • 897
  • 6
  • 12
Alfian Wira Satya Sakti
  • 23
  • 3
  • What you're trying to do is aggregate by consecutive values where the value is zero or non-zero. There should be quite a few solutions for that on StackOverflow. This may not be much help, but it is similar. https://stackoverflow.com/questions/55654156/group-consecutive-rows-based-on-one-column – D-S Mar 28 '22 at 04:32

1 Answers1

2

This is a gaps and islands problem. One approach uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Seq ORDER BY Datetime) rn1,
              ROW_NUMBER() OVER (PARTITION BY Seq, Output > 0
                                 ORDER BY Datetime) rn2
    FROM yourTable
)

SELECT
    Seq,
    MIN(Datetime) AS MinDatetime,
    MAX(Datetime) AS MaxDatetime,
    SUM(Output) AS sum_output
FROM cte
GROUP BY
    Seq,
    Output > 0,
    rn1 - rn2
ORDER BY
    Seq,
    MIN(Datetime);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360