Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
Возможно ли с помощью sql?
Есть таблица
val_num val_str
1 a
2 b
3 c
5 d
6 e
8 f
10 g

Надо выделить группы в столбце val_num и пометить каждую стоку номером группы, к которой строка относится. Группу задают значения отличающиеся на 1 от предыдущего значения в этом столбце.
То есть в итоге надо получить
group_num val_num val_str
1 1 a
1 2 b
1 3 c
2 5 d
2 6 e
3 8 f
4 10 g

Я могу определить разность с предыдущим и последующим значением, используя LAG и LEAD, но как создавать значения столбца номер группы?..Буду благодарен любым идеям.
P.s.Понятно, что количество возможных групп ограничено количеством строк в исходной таблице.
15 фев 12, 11:57    [12094183]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Elic
Member

Откуда:
Сообщений: 29979
STFF start_of_group
15 фев 12, 12:00    [12094222]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
AmKad
Member

Откуда:
Сообщений: 5222
александер_85,

with s as (
select 1 f1,	 'a' f2 from dual union all
select 2,	 'b' f2 from dual union all
select 3,	 'c' f2 from dual union all
select 5,	 'd' f2 from dual union all
select 6,	 'e' f2 from dual union all
select 8,	 'f' f2 from dual union all
select 10, 'g' f2 from dual)
select g, f1, f2
from s
model
dimension by (row_number() over (order by f1) d)
measures (1 g, f1, f2)
rules 
(g[d>1] order by d = g[cv()-1] + sign(f1[cv()] - f1[cv()-1] - 1)
);

G F1 F2
- -- --
1  1 a  
1  2 b  
1  3 c  
2  5 d  
2  6 e  
3  8 f  
4 10 g  

 7 rows selected 
15 фев 12, 12:17    [12094432]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
AmKad,
зачем так сложно?)
+
with s as (
select 1 f1,   'a' f2 from dual union all
select 2,   'b' f2 from dual union all
select 3,   'c' f2 from dual union all
select 5,   'd' f2 from dual union all
select 6,   'e' f2 from dual union all
select 8,   'f' f2 from dual union all
select 10, 'g' f2 from dual)
select f1-row_number() over (order by f1) from s
15 фев 12, 12:51    [12094891]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Vint,

Если нет необходимости поддерживать последовательные номера групп, то твой метод подойдет как нельзя лучше.
15 фев 12, 13:00    [12094989]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
AmKad,
а если есть, можно схитрить)))
+ rownum= row_number()
with s as (
  SELECT 1 f1, 'a' f2
    FROM dual
  UNION ALL
  SELECT 2 f1, 'b' f2
    FROM dual
  UNION ALL
  SELECT 3 f1, 'c' f2
    FROM dual
  UNION ALL
  SELECT 5 f1, 'd' f2
    FROM dual
  UNION ALL
  SELECT 6 f1, 'e' f2
    FROM dual
  UNION ALL
  SELECT 8 f1, 'f' f2
    FROM dual
  UNION ALL
  SELECT 12 f1, 'g' f2 FROM dual
 ORDER BY f1)
select dense_rank() over (order by f1-rownum) from s
15 фев 12, 13:11    [12095099]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
Спасибо большое за ответы. Это как раз то, что надо:)
Разбираюсь сейчас с model, читаю хабр)
Еще вопросик, по вычислению следующего значения группы (в приведенном выше примере это строка:
d = g[cv()-1] + sign(f1[cv()] - f1[cv()-1] - 1) )
Не подскажите, есть ли в sql тернарные условные операции, как в обычном языке программирования, то есть result = a>b ? 1 : -1.
как реализовать такое присвоение в sql?
15 фев 12, 15:23    [12096356]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
александер_85,
case.
для вашей задаи model это извращение.
Elic дал вполне конкретный ответ. учитесь пользоваться поиском.
15 фев 12, 15:26    [12096398]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Elic
Member

Откуда:
Сообщений: 29979
александер_85
a>b ? 1 : -1.
RTFM CASE Expressions (FAQ)
15 фев 12, 15:26    [12096400]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
понятно, я думал, можно записать менее громоздко. спасибо.
15 фев 12, 15:42    [12096566]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
Vint
александер_85,
case.
для вашей задаи model это извращение.
Elic дал вполне конкретный ответ. учитесь пользоваться поиском.


Совсем не понял, просто, о чем говорил Elic, с примером AmKad я полностью разобрался и эта штуковина работает.

Можно ли пояснить что имел в виду Elic?
15 фев 12, 15:46    [12096601]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Добрый Э - Эх
Guest
александер_85
Можно ли пояснить что имел в виду Elic?

Elic имел ввиду: "ищи по форуму, ключевое слово для поиска - START_OF_GROUP"
15 фев 12, 15:50    [12096639]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
:) да уж, я вижу лентяем тут ходу нет:) Уже ищу)
15 фев 12, 15:54    [12096680]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
AmKad
Member

Откуда:
Сообщений: 5222
with s as (
select 1 f1,	 'a' f2 from dual union all
select 2,	 'b' f2 from dual union all
select 3,	 'c' f2 from dual union all
select 5,	 'd' f2 from dual union all
select 6,	 'e' f2 from dual union all
select 8,	 'f' f2 from dual union all
select 10, 'g' f2 from dual)
select f1, f2, count(decode(lg, 1, null, 1)) over (order by f1) grp_id
from
 (select f1, f2,
  f1 - lag(f1, 1, f1) over (order by f1) lg
  from s
 );

F1 F2 GRP_ID
-- -- ------
 1 a       1 
 2 b       1 
 3 c       1 
 5 d       2 
 6 e       2 
 8 f       3 
10 g       4 

 7 rows selected 
15 фев 12, 15:54    [12096681]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
александер_85,
действительно, лентяю, который не может нажать на плюсик в двух моих постах выше, на форуме делать нечего... лучше сразу сменить профессию)
15 фев 12, 15:57    [12096720]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
С start_of_group все оказалось проще.
Оказывается start_of_group это ни какая то оракловая переменная или еще что-то, это понятие специфичное для этого форума, и на подобные моему запросу создают поле для меток разделения на группы, которое обычно называют start_of_group. поэтому в гугле искать start_of_group бесполезно, это специфично для данного форума.(это я написал для примерно таких же как я).

Используя start_of_group можно написать такой запрос:
+ Запрос

with s as (
select 1 f1, 'a' f2 from dual union all
select 2, 'b' f2 from dual union all
select 3, 'c' f2 from dual union all
select 5, 'd' f2 from dual union all
select 6, 'e' f2 from dual union all
select 8, 'f' f2 from dual union all
select 10, 'g' f2 from dual)
select sum(start_of_group) OVER(ORDER BY f1) group_num,
f1, f2
from
(
SELECT case
when (f1 - (LAG(f1) OVER(ORDER BY f1))) < 2 then 0
ELSE 1
END start_of_group,
f1,
f2
FROM s
)


Только не совсем понятно как работает эта строчка sum(start_of_group) OVER(ORDER BY f1) group_num.
Sum вычисляется для каждой строки как сумма своего и всех предыдущих значений, и непонятно следующее:
если partition by не указан, то считается весь результат одной группой, и сумма должна вычисляться по всему столбцу в группе и для каждой строки должна быть равной количеству единиц - или неправильная логика рассуждений.
16 фев 12, 00:01    [12099941]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
AmKad
Member

Откуда:
Сообщений: 5222
александер_85,

Почитай про нарастающий итог.
16 фев 12, 00:17    [12099982]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
Напишу для таких как я немного разъяснений. Знатоки поправят ошибки, если допущу, или неточности.
Функция sum понимается анализатором как аналитическая, а не как агрегатная из-за ключевого слова over. Также "при отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну". И конструкция
select sum(start_of_group) OVER(ORDER BY f1)
на самом деле равносильна
select sum(start_of_group) OVER(ORDER BY f1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
то есть order by задает стандартное окно.
Подробнее можно прочитать в Кайте книга 2 гл 12 - Аналитические функции.
16 фев 12, 11:22    [12101360]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
александер_85
Напишу для таких как я немного разъяснений. Знатоки поправят ошибки, если допущу, или неточности.
Функция sum понимается анализатором как аналитическая, а не как агрегатная из-за ключевого слова over. Также "при отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну". И конструкция
select sum(start_of_group) OVER(ORDER BY f1)
на самом деле равносильна
select sum(start_of_group) OVER(ORDER BY f1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
то есть order by задает стандартное окно.
Подробнее можно прочитать в Кайте книга 2 гл 12 - Аналитические функции.

че то я не уловил что ли?!!!, причем тут функция sum??
16 фев 12, 11:28    [12101437]     Ответить | Цитировать Сообщить модератору
 Re: создание групп в group by  [new]
александер_85
Member

Откуда:
Сообщений: 29
Ramin,

sum как пример аналитической функции, для которой работает order by со стандартным окном.
16 фев 12, 11:30    [12101475]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить