SQL / Presto SQL: sum by group in a same column

user_16703007 0
Viewed 849

I'm trying to solve a problem like following :

There is a table like this:

logtime name seconds flag
1629302433 a 30 1-1
1629302463 a 30 1-1
1629302483 a 20 0-1
1629302513 a 30 1-1
1629302533 a 20 0-1
1629302553 a 30 1-1

as the flag = 0-1, the data going to split by 3 parts, and sum seconds column value by each part like following: (logtime is timestamp)

name seconds
a 60
a 30
a 30
presto hive sql
2 Answers

Calculate group number to which each row belongs to as running sum of flag '0-1' occurences. Then aggregate group by name and group number.

Demo:

with mytable as (
SELECT * FROM (
    VALUES
(1629302433, 'a', 30, '1-1'),
(1629302463, 'a', 30, '1-1'),
(1629302483, 'a', 20, '0-1'),
(1629302513, 'a', 30, '1-1'),
(1629302533, 'a', 20, '0-1'),
(1629302553, 'a', 30, '1-1')
) AS t (logtime, name, seconds, flag)
)

select name, 
       sum(seconds) seconds
from
(--calculate group number as running sum of 0-1 occurances
select logtime, name, seconds, flag,
       sum(case when flag='0-1' then 1 else 0 end) over(partition by name order by logtime) as group_nbr
  from mytable
)s
where flag='1-1' --do not sum '0-1' records
group by name, group_nbr 
order by name, group_nbr --remove ordering if not necessary

Result:

name    seconds 
a       60
a       30
a       30
user_27003440

You can use lag() function to find where the value changes and then do a cumulative sum to assign groups and then sum over the group:

WITH dataset AS (
  SELECT * 
  FROM 
    (
      VALUES 
        (1629302433,    'a',    30, '1-1'),
        (1629302463,    'a',    30, '1-1'),
        (1629302483,    'a',    20, '0-1'),
        (1629302513,    'a',    30, '1-1'),
        (1629302533,    'a',    20, '0-1'),
        (1629302553,    'a',    30, '1-1')
    ) AS t (logtime,    name,   seconds,    flag)
) 

select name, sum(seconds) seconds
from (
         select *,
                sum(case when flag = prev_flag then 0 else 1 end) over (partition by name order by logtime) as grp
         from (
                  select logtime,
                         name,
                         seconds,
                         flag,
                         lag(flag) over (partition by name order by logtime) as prev_flag
                  from dataset
              )
     )
where flag = '1-1'
group by name, grp

Output:

name seconds
a 60
a 30
a 30
user_25012790
Related