Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Расставить sog, непонятка с sum() over() / 16 сообщений из 16, страница 1 из 1
03.11.2016, 17:50
    #39341062
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Есть запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
 SELECT dt date_from, nm,  
        CASE WHEN nm <> LAG(nm) OVER(ORDER BY dt) THEN 1 ELSE 0 END sog
   FROM t
/
 
DATE_FROM   NM             SOG
----------- ------- ----------
01.03.2016  ИВАНОВ           0
01.03.2016  СЕМЕНОВ          1
01.04.2016  СЕМЕНОВ          0
01.04.2016  ПЕТРОВ           1
01.07.2016  ИВАНОВ           1


Казалось бы все нормально, но проставляем теперь группы:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
SELECT date_from, nm, sog, 
       SUM(sog) OVER(ORDER BY date_from) grp
  FROM (SELECT dt date_from, nm,  
               CASE WHEN nm <> LAG(nm) OVER(ORDER BY dt) THEN 1 ELSE 0 END sog
          FROM t)
/
 
DATE_FROM   NM             SOG        GRP
----------- ------- ---------- ----------
01.03.2016  ИВАНОВ           0          1
01.03.2016  СЕМЕНОВ          1          1
01.04.2016  СЕМЕНОВ          0          2
01.04.2016  ПЕТРОВ           1          2
01.07.2016  ИВАНОВ           1          3


Семенов и Петров попадают в одну группу.
Понятно, что т.к. они идут одной датой и другой сортировки нет, то берется произвольно.
Т.е. такой косяк может вылезти по идее и при проставлении sog.

Но надо все-таки их отделить и на примере Семенова-Петрова должно быть так:
если в одной дате есть две фамилии, то за первую фамилию берем ту из них, которая есть в ближайшей более ранней дате.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 17:57
    #39341068
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
На выходе надо получить периоды:
Код: plsql
1.
2.
3.
4.
01.03.2016 - 01.03.2016 Иванов   
01.03.2016 - 01.04.2016 Семенов
01.04.2016 - 01.07.2016 Петров
01.07.2016 -            Иванов


Можно было бы обойтись min max с группировкой по фамилии, но фамилии могут повторяться в хронологии (Иванов).
Плюс запрос упрощен, в реальном еще потребует патишн бай.
Еще вариант, разбирать в коллекции, но интересно посмотреть запросом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:21
    #39341084
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Опиши постановку задачи, что именно требуется получить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:27
    #39341093
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Исх. набор:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') date_from, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
 SELECT date_from, nm
   FROM t


Получить надо:
Код: plsql
1.
2.
3.
4.
01.03.2016 - 01.03.2016 Иванов   
01.03.2016 - 01.04.2016 Семенов
01.04.2016 - 01.07.2016 Петров
01.07.2016 -            Иванов


То есть периоды с и по для nm.

Попутно вопрос, можно ли в операциях с окном (over partition by) при обозначении диапазона rows rows between обращаться к текущему значению для сравнения с предыдущими? Поищу, если найду, то решу. Можно еще было бы попробовать собрать в listagg предыдущие фамилии, но это не вариант, т.к. реально полей больше конечно для сравнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:35
    #39341101
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Avotge,

С некорректными данными либо надо их сначала привести к порядку, либо заниматься статистической оптимизацией иногда_работает vs иногда_не_работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 18:40
    #39341110
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
-2-С некорректными данными либо надо их сначала привести к порядку, либо заниматься статистической оптимизацией иногда_работает vs иногда_не_работает.
Можно сказать, что это и есть попытка приведения к порядку )
Пока правда из реального склоняюсь к тому, чтобы засовывать в коллекцию и там делать что надо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 20:13
    #39341150
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
AvotgeИсх. набор:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') date_from, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
 SELECT date_from, nm
   FROM t


Получить надо:
Код: plsql
1.
2.
3.
4.
01.03.2016 - 01.03.2016 Иванов   
01.03.2016 - 01.04.2016 Семенов
01.04.2016 - 01.07.2016 Петров
01.07.2016 -            Иванов


То есть периоды с и по для nm.

Попутно вопрос, можно ли в операциях с окном (over partition by) при обозначении диапазона rows rows between обращаться к текущему значению для сравнения с предыдущими? Поищу, если найду, то решу. Можно еще было бы попробовать собрать в listagg предыдущие фамилии, но это не вариант, т.к. реально полей больше конечно для сравнения.

сильно не тестировал, так не подойдет
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
  1  WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
  2             UNION ALL
  3             SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
  4             UNION ALL
  5             SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
  6             UNION ALL
  7             SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual
  8             UNION ALL
  9             SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
 10  SELECT date_from, nm, sog,
 11         SUM(sog) OVER(ORDER BY date_from,sog) grp
 12    FROM (SELECT dt date_from, nm,  LAG(nm) OVER(ORDER BY dt) lg,
 13                 CASE WHEN nm <> LAG(nm) OVER(ORDER BY dt) THEN 1 ELSE 0 END sog
 14*           FROM t)
SQL> /

DATE_FRO NM             SOG        GRP
-------- ------- ---------- ----------
01.03.16 ИВАНОВ           0          0
01.03.16 СЕМЕНОВ          1          1
01.04.16 СЕМЕНОВ          0          1
01.04.16 ПЕТРОВ           1          2
01.07.16 ИВАНОВ           1          3



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 21:01
    #39341186
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
AvotgeПолучить надо:Кто на ком стоял? :
Код: plsql
1.
2.
3.
4.
5.
6.
7.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 21:21
    #39341200
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Elic,

задачка вроде бы как понятна (дереминирована), но надо акуратно протверезветь

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 21:38
    #39341207
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
stax..задачка вроде бы как понятна (дереминирована),Тебе только кажется. Каждая строчка должна быть досортирована с одной стороны предысторией, с другой - послеисторией. Парадокс.

А твоё "решение" - неправильное. Сортировка только по dt недетерминирована. Достаточно поставить второго Семёнова после Петрова.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 22:29
    #39341233
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Elic,
сегодня не готов ответіть по существу

ps
ваше решение?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 22:50
    #39341240
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
Добрался до машины.
Пока получилось такая боль...
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
  WITH t0 AS (SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual
              UNION ALL
              SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
              UNION ALL
              SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual
              UNION ALL
              SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual              
              UNION ALL
              SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual),
       t1 AS (SELECT dt, nm, DENSE_RANK() OVER(ORDER BY dt) rank_dt
                FROM t0),
       t2 AS (SELECT dt, nm, rank_dt,
                     (SELECT SIGN(COUNT(*)) FROM t1 
                       WHERE t1.rank_dt = t3.rank_dt-1
                         AND t1.nm = t3.nm) AS is_exists_nm_in_lag_dt,                                               
                     (SELECT SIGN(COUNT(*)) FROM t1 
                       WHERE t1.rank_dt = t3.rank_dt+1
                         AND t1.nm = t3.nm) AS is_exists_nm_in_lead_dt
                FROM t1 t3),
              -- проставляем sog  
       t4 AS (SELECT dt, nm, 
                     rank_dt * 1000 + is_exists_nm_in_lead_dt - is_exists_nm_in_lag_dt AS ord_num,
                     LEAD(dt) OVER(ORDER BY rank_dt * 1000 + is_exists_nm_in_lead_dt - is_exists_nm_in_lag_dt) lead_dt,
                     CASE WHEN LAG(nm) OVER(ORDER BY rank_dt * 1000 + is_exists_nm_in_lead_dt - is_exists_nm_in_lag_dt) <> nm THEN  1 ELSE 0 END AS sog
                FROM t2),
              -- потом группу
       t5 AS (SELECT dt, lead_dt, nm, SUM(sog) OVER(ORDER BY ord_num) grp
                FROM t4)
              -- и тока теперь... :'(
      SELECT MIN(dt) date_from, MAX(lead_dt) date_to, nm
        FROM t5
       GROUP BY nm, grp
       ORDER BY date_from


Сильно не проверял, но что-то похожее.
Если учесть, что полей для группировки и определения окна больше, плюс еще нюансы,
то в реальности запрос получится ну совсем... А если переписать на коллекции, то вообще без стакана лучше не подходить похоже ( Нет. Можно конечно аккуратно все написать, разложить, но столько усилий и объем кода, ради того, чтобы сделать казалось бы простую группировку, это не нормально имхо )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.11.2016, 23:47
    #39341275
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
AvotgeИсх. набор:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') date_from, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') dt, 'ИВАНОВ' nm FROM dual)
 SELECT date_from, nm
   FROM t


Получить надо:
Код: plsql
1.
2.
3.
4.
01.03.2016 - 01.03.2016 Иванов   
01.03.2016 - 01.04.2016 Семенов
01.04.2016 - 01.07.2016 Петров
01.07.2016 -            Иванов


То есть периоды с и по для nm.Видимо ты что-то для биллинга ваяешь, но алгоритм до конца не ясен.
Приведи данные на которых решение ниже не работает
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
select nm, x1, lead(x1) over(order by x1, xx) x2
  from (select nm, min(dt) x1, max(dt) xx
          from (select dt, nm, sum(x) over(partition by nm order by dt) grp
                  from (select *
                          from (select dt,
                                       nm,
                                       decode(
                                              -- if previous dt for current nm
                                              lag(dt)
                                              over(partition by nm order by dt),
                                              -- equals to max dt less than current dt
                                              last_value(dt)
                                              over(order by dt
                                                   range between unbounded
                                                   preceding and 1 preceding),
                                              -- then keep current group
                                              0,
                                              1) x
                                  from t) t0) t1) t2
         group by nm, grp) t3;

NM      X1       X2
------- -------- --------
ИВАНОВ  01.03.16 01.03.16
СЕМЕНОВ 01.03.16 01.04.16
ПЕТРОВ  01.04.16 01.07.16
ИВАНОВ  01.07.16


AvotgeМожно было бы обойтись min max с группировкой по фамилии, но фамилии могут повторяться в хронологии (Иванов).Секционируем по фамилии, "вклинивания" других фамилий определяем с помощью decode.
AvotgeПопутно вопрос, можно ли в операциях с окном (over partition by) при обозначении диапазона rows rows between обращаться к текущему значению для сравнения с предыдущими? Поищу, если найду, то решу. Можно еще было бы попробовать собрать в listagg предыдущие фамилии, но это не вариант, т.к. реально полей больше конечно для сравнения.Обращаться к текущему значению в аналитике нельзя.
Только косвенно используя range и то, если сортировка не более чем по одному полю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2016, 00:42
    #39341294
Avotge
Avotge
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
dbms_photoshopВидимо ты что-то для биллинга ваяешь, но алгоритм до конца не ясен. Приведи данные на которых решение ниже не работает.
Не. Надеюсь, в биллинге более стройные данные ) Пока дырки не нашел, вроде все логично, хороший вариант, спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2016, 13:11
    #39341420
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
AvotgeПонятно, что т.к. они идут одной датой и другой сортировки нет, то берется произвольно.
Т.е. такой косяк может вылезти по идее и при проставлении sog.


"Косяк" потому что у тебя даты одинаковые, соотв аналитика sum over() считает до date_from, а не до current row как тебе нужно для корректного определения начала группы.

Добавляешь over(order by date_from, rownum ) как один из вариантов. (Кстати stax не угадал с ",sog" т.к. оно работает только на магии) )

Но вообще, если у тебя поле в таблице date c time то проблем не будет (опять же если не будет exact time).

i.e.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
WITH T AS (SELECT TO_DATE('01032016', 'ddmmyyyy') + dbms_random.value dt, 'ИВАНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01032016', 'ddmmyyyy') + dbms_random.value dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') + dbms_random.value dt, 'СЕМЕНОВ' nm FROM dual
           UNION ALL
           SELECT TO_DATE('01042016', 'ddmmyyyy') + dbms_random.value dt, 'ПЕТРОВ' nm FROM dual           
           UNION ALL           
           SELECT TO_DATE('01072016', 'ddmmyyyy') + dbms_random.value dt, 'ИВАНОВ' nm FROM dual)
 SELECT min(date_from), max(date_from), nm FROM (          
SELECT date_from, nm, sog, 
       SUM(sog) OVER(ORDER BY date_from) grp
  FROM (SELECT dt date_from, nm,  
               CASE WHEN nm <> LAG(nm) OVER(ORDER BY dt) THEN 1 ELSE 0 END sog
          FROM t)
          )
          GROUP BY nm, grp
          ORDER BY 1,2

MIN(DATE_ MAX(DATE_ NM
--------- --------- ------------------
01-MAR-16 01-MAR-16 ИВАНОВ
01-MAR-16 01-APR-16 СЕМЕНОВ
01-APR-16 01-APR-16 ПЕТРОВ
01-JUL-16 01-JUL-16 ИВАНОВ

SQL>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.11.2016, 20:48
    #39341622
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расставить sog, непонятка с sum() over()
ora601
Код: plsql
1.
 + dbms_random.value

Ага. Ударим по недетерминизму случайностью. Цирк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Расставить sog, непонятка с sum() over() / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 414ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887082]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 146ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 29ms
get tp. blocked users:
 1ms
others: 202ms
total:  414ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]