Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Группировка идущих подряд повторяющихся строк  [new]
Guest_group
Guest
Добрый день!
Подскажите как сгруппировать идущие подряд повторяющиеся строки и посчитать сумму и количество строк по ним.

Т.е. есть результат запроса:
IDVALSUMMA
10110
15110
2022100
2422100
2822100
33110
442210
52333100
53333100
57333100
59333100

Надо сгруппировать идущие подряд повторяющиеся строки по VAL с сортировкой по ID
и вывести min(ID), max(ID), sum(SUMMA), count(*)

т.е. получить такую таблицу:
VAL min(ID) max(ID) sum(SUMMA) count(*)
11015202
2220283003
13333101
224444101
33352594004


min(ID) и max(ID) найти получилось:
select t.val, t.min_id, t.max_id
  from (
        select t.*,
               t.id min_id,
               case when row_last = 1 then id else lead(t.id) over(order by t.id) end max_id
          from (
                select t.*,
                       decode(t.val, lag(t.val) over(order by t.id), 0, 1) row_first,
                       decode(t.val, lead(t.val) over(order by t.id), 0, 1) row_last
                  from (
                        select 10 id, 1   val, 10  summa from dual union all
                        select 15 id, 1   val, 10  summa from dual union all
                        select 20 id, 22  val, 100 summa from dual union all
                        select 24 id, 22  val, 100 summa from dual union all
                        select 28 id, 22  val, 100 summa from dual union all
                        select 33 id, 1   val, 10  summa from dual union all
                        select 44 id, 22  val, 10  summa from dual union all
                        select 52 id, 333 val, 100 summa from dual union all
                        select 53 id, 333 val, 100 summa from dual union all
                        select 57 id, 333 val, 100 summa from dual union all
                        select 59 id, 333 val, 100 summa from dual
                       )t
               )t
         where not (row_first = 0 and row_last = 0)
       )t
 where t.row_first = 1


а как получить сумму и количество?
27 сен 17, 08:16    [20825655]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Guest_group
Guest
Сумму и кол-во можно найти с помощью вложенных select-ов:
with tab as (
  select 10 id, 1   val, 10  summa from dual union all
  select 15 id, 1   val, 10  summa from dual union all
  select 20 id, 22  val, 100 summa from dual union all
  select 24 id, 22  val, 100 summa from dual union all
  select 28 id, 22  val, 100 summa from dual union all
  select 33 id, 1   val, 10  summa from dual union all
  select 44 id, 22  val, 10  summa from dual union all
  select 52 id, 333 val, 100 summa from dual union all
  select 53 id, 333 val, 100 summa from dual union all
  select 57 id, 333 val, 100 summa from dual union all
  select 59 id, 333 val, 100 summa from dual
)
select t.val, t.min_id, t.max_id,
       (select sum(summa) from tab t1 where t1.val = t.val and t1.id between t.min_id and t.max_id) sum_summa,
       (select count(*) from tab t1 where t1.val = t.val and t1.id between t.min_id and t.max_id) count_row
  from (
        select t.*,
               t.id min_id,
               case when row_last = 1 then id else lead(t.id) over(order by t.id) end max_id
          from (
                select t.*,
                       decode(t.val, lag(t.val) over(order by t.id), 0, 1) row_first,
                       decode(t.val, lead(t.val) over(order by t.id), 0, 1) row_last
                  from tab t
               )t
         where not (row_first = 0 and row_last = 0)
       )t
 where t.row_first = 1


но такое решение не подходит, т.к. группируется большое кол-во строк, и с вложенными select-ами работает очень долго.
Нужно решение без них.
27 сен 17, 08:28    [20825665]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Elic
Member

Откуда:
Сообщений: 29976
STFF start_of_group
27 сен 17, 08:45    [20825679]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Guest_group
Guest
Elic, Спасибо

with tab as (
  select 10 id, 1   val, 10  summa from dual union all
  select 15 id, 1   val, 10  summa from dual union all
  select 20 id, 22  val, 100 summa from dual union all
  select 24 id, 22  val, 100 summa from dual union all
  select 28 id, 22  val, 100 summa from dual union all
  select 33 id, 1   val, 10  summa from dual union all
  select 44 id, 22  val, 10  summa from dual union all
  select 52 id, 333 val, 100 summa from dual union all
  select 53 id, 333 val, 100 summa from dual union all
  select 57 id, 333 val, 100 summa from dual union all
  select 59 id, 333 val, 100 summa from dual
)
select val,
       min(id) min_id,
       max(id) max_id,
       sum(summa) sum_summa,
       count(*) count_row
  from (
select t.*,
       sum(start_of_group) over(order by t.id) gr
  from (
        select t.*,
               decode(t.val, lag(t.val) over(order by t.id), 0, 1) start_of_group
          from tab t
       )t
)
group by val, gr
order by gr
27 сен 17, 09:34    [20825758]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
mcwhite
Member

Откуда: Москва
Сообщений: 423
Elic, присоединяюсь к благодарностям.
27 сен 17, 11:04    [20826107]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Добрый Э - Эх
Guest
Guest_group,

чего-то ты сильно окольными путями пошел в решении своей простой задачки.
можно же было вот так:
with tab as (
  select 10 id, 1   val, 10  summa from dual union all
  select 15 id, 1   val, 10  summa from dual union all
  select 20 id, 22  val, 100 summa from dual union all
  select 24 id, 22  val, 100 summa from dual union all
  select 28 id, 22  val, 100 summa from dual union all
  select 33 id, 1   val, 10  summa from dual union all
  select 44 id, 22  val, 10  summa from dual union all
  select 52 id, 333 val, 100 summa from dual union all
  select 53 id, 333 val, 100 summa from dual union all
  select 57 id, 333 val, 100 summa from dual union all
  select 59 id, 333 val, 100 summa from dual
)

select val, min(id) as min_id, max(id) as max_id, sum(summa) as sum_summs, count(1) as count_row
  from (
         select tab.*
              , row_number() over(partition by val order by id) - 
                row_number() over(order by id) as grp_id
           from tab
       ) v
 group by grp_id, val
 order by min(id);
27 сен 17, 11:15    [20826149]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Guest_group
Guest
Добрый Э - Эх, спасибо, но в твоём примере есть один нюанс,
у тебя один grp_id может быть одинаковый для разных val,
в моём примере он уникальный для всех групп

with tab as (
  select 10 id, 1   val, 10  summa from dual union all
  select 15 id, 1   val, 10  summa from dual union all
  select 20 id, 22  val, 100 summa from dual union all
  select 24 id, 22  val, 100 summa from dual union all
  select 28 id, 22  val, 100 summa from dual union all
  select 33 id, 1   val, 10  summa from dual union all
  select 44 id, 22  val, 10  summa from dual union all
  select 52 id, 333 val, 100 summa from dual union all
  select 53 id, 333 val, 100 summa from dual union all
  select 57 id, 333 val, 100 summa from dual union all
  select 59 id, 333 val, 100 summa from dual
)
select t.id, t.val,
       grp_id_val,
       sum(start_of_group) over(order by t.id) grp_id_global
  from (
        select t.*,
               decode(t.val, lag(t.val) over(order by t.id), 0, 1) start_of_group,
               row_number() over(order by id) - row_number() over(partition by val order by id) grp_id_val
          from tab t
       )t

IDVALGRP_ID_VALGRP_ID_GLOBAL
10101
15101
202222
242222
282222
33133
442234
5233375
5333375
5733375
5933375


В данном примере это не критично, но в некоторых задачах на это стоит обратить внимание.
27 сен 17, 12:01    [20826348]     Ответить | Цитировать Сообщить модератору
 Re: Группировка идущих подряд повторяющихся строк  [new]
Добрый Э - Эх
Guest
Guest_group,

сам по себе grp_id интереса не представляет, смотреть нужно только в паре с VAL, ибо GRP_ID - это номер группы внутри одного VAL. Именно поэтому GROUP BY идет по их паре.
27 сен 17, 12:11    [20826370]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить