Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вопрос / 16 сообщений из 16, страница 1 из 1
15.03.2020, 14:06
    #39937566
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
Добрый день,

Помогите пожалуйста с элигантным решением в SQL, наверно с аналитическими функциями.
У меня получается многожтажный аналитический огород и не до конца то что хочу. Кажется что вот вот, но нет :(

Задача довольно длинная, но я сдесь сокращу формулирофку и пример:

Есть таблица с продажами по дням. Надо выдать непрерывные недельные интервалы (чтобы не было 'пустых недель' без продаж) с общим количеством продаж. Датасет достаточно большой, лет 10 даты, +- 10млн строчек. Но анализируем только 2 последних года.
в примере полосочки -- показывают дырку, т.е начало новой серии
week - неделя
total - количество продаж за неделю

with basis as (
select 201846 week, 60 total from dual union
--
select 201850 week, 100 total from dual union
--
select 201852 week, 70 total from dual union
select 201853 week, 80 total from dual union
select 201901 week, 100 total from dual union
--
select 201903 week, 20 total from dual union
select 201904 week, 60 total from dual union
--
select 201906 week, 200 total from dual union
select 201907 week, 50 total from dual union
select 201908 week, 30 total from dual union
select 201909 week, 80 total from dual union
select 201910 week, 70 total from dual union
select 201911 week, 100 total from dual union
--
select 201913 week, 80 total from dual union
select 201914 week, 20 total from dual union
--
select 201916 week, 100 total from dual)
select bas.*
from basis bas
order by bas.week;


Желаемый резултат: начало непрерывной серии (начало-конец, 201852-201901, еще лучше), длина серии в неделях, общее количество в серии

week_start weeks total
201846 1 60
201850 1 100
201852 3 250
201903 2 80
201906 6 530
201913 2 100
201916 1 100

большое спасибо и хорошего здорового дня,

Роман
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.03.2020, 16:26
    #39937607
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput,

group by global_week - rownum
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.03.2020, 19:20
    #39937641
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
-2-
dbmsoutput,

group by global_week - rownum


Спасибо, но я не понял что имеется ввиду.

если можно покажите select
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.03.2020, 20:21
    #39937648
PuM256
PuM256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput,

В жалаемом результате ошибка.

Код: 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.
 24  Select Min(week) week_start, Count(groupno) weeks, Sum(total) total From (
 25  	Select week, total, Sum(StartOfGroup) Over(Order By week) groupno From (
 26  		select bas.week, bas.total,
 27  	    Case When
 28          (week - Lag(week) Over(Order By week) = 1) Then 0
 29          Else 1
 30  			End StartOfGroup
 31  			from basis bas
 32  	)
 33  )
 34  Group By groupno
 35  Order By 1;

WEEK_START      WEEKS      TOTAL
---------- ---------- ----------
    201846          1         60
    201850          1        100
    201852          2        150
    201901          1        100
    201903          2         80
    201906          6        530
    201913          2        100
    201916          1        100

8 rows selected
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.03.2020, 22:51
    #39937675
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput,

Ты не указал самого главного - что для тебя есть неделя. Вначале я предположил ISO но ISO год 2018 начался в Понедельник 1 Января 2018 и состоит из 52ух недель. Так-что

select 201853 week, 80 total from dual union

не ISO. Предположу что все-таки ISO и это описка и исключу эту строку:

Код: 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
with basis as (
               select 201846 week, 60 total from dual union
               --
               select 201850 week, 100 total from dual union
               --
               select 201852 week, 70 total from dual union
--               select 201853 week, 80 total from dual union
               select 201901 week, 100 total from dual union
               --
               select 201903 week, 20 total from dual union
               select 201904 week, 60 total from dual union
               --
               select 201906 week, 200 total from dual union
               select 201907 week, 50 total from dual union
               select 201908 week, 30 total from dual union
               select 201909 week, 80 total from dual union
               select 201910 week, 70 total from dual union
               select 201911 week, 100 total from dual union
               --
               select 201913 week, 80 total from dual union
               select 201914 week, 20 total from dual union
               --
               select 201916 week, 100 total from dual
              ),
         t as (
               select  bas.*,
                       trunc(to_date(substr(week,1,4) || '0130','YYYYMMDD'),'IYYY') + 7 * (mod(week,100) - 1) -
                       7 * row_number() over(order by week) grp
                 from  basis bas
              )
select  min(week) week_start,
        count(*) weeks,
        sum(total) total
  from  t
  group by grp
  order by grp
/

WEEK_START      WEEKS      TOTAL
---------- ---------- ----------
    201846          1         60
    201850          1        100
    201852          2        170
    201903          2         80
    201906          6        530
    201913          2        100
    201916          1        100

7 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.03.2020, 23:04
    #39937678
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
SY,

БОЛЬШОЕ СПАСИБО!

Завтра доберусь до настоящей таблицы и попробую переписать.

... а я огород городил ... искал дырки и аналитически в 6 этажей sql ... и без результата.

Спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 11:00
    #39937754
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
PuM256,

Большое спасибо! Я как то так и пытался, но не до пытался.
Сейчас буду на реальную таблицу пытаться переписать.

Хорошего дня
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 11:14
    #39937759
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput
PuM256,

Большое спасибо!
lag также неприменим, как и row_number, без перевода погодной нумерации недели к дате или глобальной нумерации
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 11:39
    #39937772
PuM256
PuM256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
-2-,

Согласен, очень верное замечание. Я на данные внимания не обратил, подразумевал сквозную порядковую нумерацию недель.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 13:30
    #39937802
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
PuM256,

Я посмотрел.... если ошибка имеется ввиду неделя 201853 то да действительно такой не было в 2018 :)
Тогда результат должен быть как SY посчитал

201852 2 150

Ваше решение очень красивое, но не видит что 201852 и 201901 идут подряд и дырки там нет. Наверно надо мне использовать логику перевода SY чисел в календарь/'неделю'

select 201852 week, 70 total from dual union
select 201853 week, 80 total from dual union
select 201901 week, 100 total from dual union
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 13:38
    #39937809
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput
PuM256,

Я посмотрел.... если ошибка имеется ввиду неделя 201853 то да действительно такой не было в 2018 :)

Как у Вас реально считаются недели

31/12/yyyy может быть первой (не последней в yyyy) неделей yyyy+1 года?
01/01/yyyy всегда первая неделя yyyy года?

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 14:15
    #39937838
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
Stax,

В датаварехаузе есть DIM_DAY там все дни с неделями в iso week
Все траснакции привязаны к DIM_DAY
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 14:58
    #39937848
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
dbmsoutput,

 
чуть подправленное от SY
Код: 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
with basis as (
               select 201846 week, 60 total from dual union
               --
               select 201850 week, 100 total from dual union
               --
               select 201852 week, 70 total from dual union
--               select 201853 week, 80 total from dual union
               select 201901 week, 100 total from dual union
               --
               select 201903 week, 20 total from dual union
               select 201904 week, 60 total from dual union
               --
               select 201906 week, 200 total from dual union
               select 201907 week, 50 total from dual union
               select 201908 week, 30 total from dual union
               select 201909 week, 80 total from dual union
               select 201910 week, 70 total from dual union
               select 201911 week, 100 total from dual union
               --
               select 201913 week, 80 total from dual union
               select 201914 week, 20 total from dual union
               --
               select 201916 week, 100 total from dual
              ),
         pre as (
               select  bas.*,
                       to_char(
                           trunc(
                               to_date(substr(week,1,4)||'0101','YYYYMMDD')  -- первый день года
                               + 7 * (mod(week,100)) -- добавляем 7*номер_недели, 
                           ,'IW') -- trunc(..,'IW') - обрезаем до начала недели
                           ,'J'   -- to_char(...,'J') - получаем номер дня (Julian day; the number of days since January 1, 4712 BC.)
                       )/7        -- делим на 7 - т.е. номер недели с January 1, 4712 BC.
                       as week_n
                 from  basis bas
         ),
         t as (
               select  pre.*,
                       week_n - row_number()over(order by week) as grp
                 from  pre
              )
select  min(week) week_start,
        count(*) weeks,
        sum(total) total
  from  t
  group by grp
  order by grp
/

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 16:02
    #39937881
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
xtender


чуть подправленное от SY


Начало ISO года + 7 * недель != trunc(Начало календарного года + 7 * недель,'IW')
Попробуй например последняя ISO неделя 2023 и первая ISO неделя 2024:

 
Код: 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
SQL> with basis as (
  2                 select 202352 week, 60 total from dual union
  3                 select 202401 week, 100 total from dual
  4                ),
  5           pre as (
  6                 select  bas.*,
  7                         to_char(
  8                             trunc(
  9                                 to_date(substr(week,1,4)||'0101','YYYYMMDD')
 10                                 + 7 * (mod(week,100))
 11                             ,'IW')
 12                             ,'J'
 13                         )/7
 14                         as week_n
 15                   from  basis bas
 16           ),
 17           t as (
 18                 select  pre.*,
 19                         week_n - row_number()over(order by week) as grp
 20                   from  pre
 21                )
 22  select  min(week) week_start,
 23          count(*) weeks,
 24          sum(total) total
 25    from  t
 26    group by grp
 27*   order by grp
SQL> /

WEEK_START      WEEKS      TOTAL
---------- ---------- ----------
    202352          1         60
    202401          1        100

SQL> with basis as (
  2                 select 202352 week, 60 total from dual union
  3                 select 202401 week, 100 total from dual
  4                ),
  5           t as (
  6                 select  bas.*,
  7                         trunc(to_date(substr(week,1,4) || '0130','YYYYMMDD'),'IYYY') + 7 * (mod(week,100) - 1) -
  8                         7 * row_number() over(order by week) grp
  9                   from  basis bas
 10                )
 11  select  min(week) week_start,
 12          count(*) weeks,
 13          sum(total) total
 14    from  t
 15    group by grp
 16    order by grp
 17  /

WEEK_START      WEEKS      TOTAL
---------- ---------- ----------
    202352          2        160

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 16:44
    #39937911
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
SY,

Да, опечатка, там 0104 должно быть у меня
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.03.2020, 17:08
    #39937920
dbmsoutput
dbmsoutput
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вопрос
ребята, как то даже страшно с вами :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вопрос / 16 сообщений из 16, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 159ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1881460]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 40ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 15ms
total:  159ms
x
x
Закрыть


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