ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вопрос
16 сообщений из 16, страница 1 из 1
sql вопрос
    #39937566
dbmsoutput
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbmsoutput
Гость
Добрый день,

Помогите пожалуйста с элигантным решением в 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, 14:06
| Ответить | Цитировать | Написать  
sql вопрос
    #39937607
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
dbmsoutput,

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

group by global_week - rownum


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

если можно покажите select
...
Рейтинг: 0 / 0
15.03.2020, 19:20
| Ответить | Цитировать | Написать  
sql вопрос
    #39937648
PuM256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PuM256
Гость
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, 20:21
| Ответить | Цитировать | Написать  
sql вопрос
    #39937675
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
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, 22:51
| Ответить | Цитировать | Написать  
sql вопрос
    #39937678
dbmsoutput
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbmsoutput
Гость
SY,

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

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

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

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

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

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

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

Согласен, очень верное замечание. Я на данные внимания не обратил, подразумевал сквозную порядковую нумерацию недель.
...
Рейтинг: 0 / 0
16.03.2020, 11:39
| Ответить | Цитировать | Написать  
sql вопрос
    #39937802
dbmsoutput
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbmsoutput
Гость
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:30
| Ответить | Цитировать | Написать  
sql вопрос
    #39937809
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
dbmsoutput
PuM256,

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

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

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

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

В датаварехаузе есть DIM_DAY там все дни с неделями в iso week
Все траснакции привязаны к DIM_DAY
...
Рейтинг: 0 / 0
16.03.2020, 14:15
| Ответить | Цитировать | Написать  
sql вопрос
    #39937848
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
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, 14:58
| Ответить | Цитировать | Написать  
sql вопрос
    #39937881
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
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:02
| Ответить | Цитировать | Написать  
sql вопрос
    #39937911
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
SY,

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


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