ReSQL.ru
     
powered by simpleCommunicator - 2.0.18     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
12 сообщений из 12, страница 1 из 1
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036296
Страдалецъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Страдалецъ
Участник
Имеется таблица Test
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create table Test (Number int, State int);
insert into Test (Number, State) values(1,1);
insert into Test (Number, State) values(2,0);
insert into Test (Number, State) values(3,0);
insert into Test (Number, State) values(4,1);
insert into Test (Number, State) values(5,1);
insert into Test (Number, State) values(6,0);
insert into Test (Number, State) values(7,1);
insert into Test (Number, State) values(8,1);
insert into Test (Number, State) values(9,1);
insert into Test (Number, State) values(10,0);


с такими данными:
NumberState112030415160718191100
И необходимо получить минимальный Number с которого начинается непрерывная числовая последовательность длиной 3. Последовательность считается непрерывной, если для всех значений в Number поле State=1.
В данном примере это Number=7. Количество данных может быть существенно больше 10 и длина последовательности тоже может быть любой.
И вот как это сделать через SQL не пойму.
...
Рейтинг: 0 / 0
15.01.2021, 23:38
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036318
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Этого никто не может понять. Не решают такие задачи в базах данных.

Ну или решают на уровне триггеров и вторичных таблиц.
Когда в основную таблицу приходит запись - триггер проверяет "сейчас мы набираем последовательность нулей/единиц" и либо создает новую запись в таблице регистрации последовательностей, либо обновляет счетчик в последней записи этой таблицы.


А на существующей таблице... Ну проще всего это сделать в цикле снаружи:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
rs = conn.execute("select Number, State from Test order by Number")
while rs.fetch
   if current_state = 0 and rs[State] = 1 then
      new_seq = rs[Number]
      new_len = 1
   else if current_state = 1 and rs[State] = 0
       if new_len > max_len then
           max_len = new_len
           max_seq = new_seq
       end if
   else if current_state = 1 and rs[State] = 1 then
      new_len = new_len +1
   end if
   current_state = rs[State]
end while

if new_len > max_len then
  max_len = new_len
  max_seq = new_seq
end if

print max_seq, max_len
...
Рейтинг: 0 / 0
16.01.2021, 06:02
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036331
Страдалецъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Страдалецъ
Участник
Кодом я и так это решил, но мне необходимо решение через SQL.
Попробую перефразировать вопрос на примере. Допустим у нас есть железнодорожный состав из 10 вагонов и есть груз разного типа, который необходимо максимально компактно распределить. Вот я и хочу получить из состава свободную секцию из 3-х вагонов, под груз водки, а потом мне потребуется секция из двух вагонов под груз селедки и т.д.
...
Рейтинг: 0 / 0
16.01.2021, 13:07
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036332
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
SQL-ем это не решается, т.к. с точки зрения теории реляционных БД нет такого понятия как порядок получения записей, нет ни первой, ни последней, все записи равнозначны и выбираться могут в любом порядке. Отсортировать можно только результат выборки, а во время выборки записи могут браться в любом порядке. Вывод: нельзя сравнивать соседние записи.

В твоем случает надо решать задачу алгоритмически или кардинально менять структуру БД, чтобы в одной записи хранился диапазон номеров.

Но если очень хочется решить задачу "выбрать 3 номера подряд", то есть изврат:
Код: sql
1.
2.
3.
select T1.Number 
   from Test T1 join Test T2 on T1.Number + 1 = T2.Number
                 join Test T3 on T2.Number + 1 = T3.Number


Повторюсь, это изврат. так писать не советую.
...
Рейтинг: 0 / 0
16.01.2021, 13:45
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036333
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Страдалецъ
Кодом я и так это решил, но мне необходимо решение через SQL.
Попробую перефразировать вопрос на примере. Допустим у нас есть железнодорожный состав из 10 вагонов и есть груз разного типа, который необходимо максимально компактно распределить. Вот я и хочу получить из состава свободную секцию из 3-х вагонов, под груз водки, а потом мне потребуется секция из двух вагонов под груз селедки и т.д.

Это задача о рюкзаке , ее средствами SQL не решить.
...
Рейтинг: 0 / 0
16.01.2021, 13:48
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036389
JeyCi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JeyCi
Гость
Страдалецъ
Вот я и хочу получить из состава свободную секцию из 3-х вагонов.

так пронумеруйте вашу последовательность 3 раза с интервалом +1... а потом сделайте self-join этих 3х таблиц по номеру - получите 3 столбца последовательных вагонов... и отберите по Where пустой вагон во всех 3х столбцах
...
Рейтинг: 0 / 0
16.01.2021, 16:32
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036391
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
SQLite умеет в аналитические функции как и Oracle, а там задача поиска start of group труда не составляет

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
with t as (select numb
                , grp_num
                , count(numb) over (partition by grp_num) grp_len 
             from (select numb
                        , sum(sog) over (order by number) grp_num 
                     from (select number
                                , state
                                , case when state = 1 then number end numb
                                , case when state - lag(state,1,0) over(order by number) >0 then 1 end sog from test
                          )
                  )
          )
select min(numb) from t group by grp_num having count(numb) = (select max(grp_len) from t)



если не нужно динамически определять максимальную последовательность можно убрать один уровень вложенности
...
Рейтинг: 0 / 0
16.01.2021, 16:39
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036398
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Оптимизируем
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with t as (select numb
                , grp_num
                , count(numb) over (partition by grp_num) grp_len 
             from (select numb
                        , sum(sog) over (order by number) grp_num 
                     from (select number
                                , state
                                , case when state = 1 then number end numb
                                , case when state - lag(state,1,0) over(order by number) >0 then 1 end sog 
                             from test
                          )
                  )
          )
select min(numb) from t where grp_len = (select max(grp_len) from t)
...
Рейтинг: 0 / 0
16.01.2021, 16:54
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036407
Страдалецъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Страдалецъ
Участник
Вот использование оконных функций я и предполагал, но опыта их использования нет вообще. Спасибо, сейчас попробуем.
...
Рейтинг: 0 / 0
16.01.2021, 17:08
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036410
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Dima T
Страдалецъ
Кодом я и так это решил, но мне необходимо решение через SQL.
Попробую перефразировать вопрос на примере. Допустим у нас есть железнодорожный состав из 10 вагонов и есть груз разного типа, который необходимо максимально компактно распределить. Вот я и хочу получить из состава свободную секцию из 3-х вагонов, под груз водки, а потом мне потребуется секция из двух вагонов под груз селедки и т.д.

Это задача о рюкзаке , ее средствами SQL не решить.
Как ни странно, но вообще-то это возможно.
Правда, только с использованием процедурных расширений.
Делал я как-то жадный вариант решения на WatcomSQL, исходник хранимой процедуры получился в 80 килобайт... И да, за неимением процедур, на SQLite это впрямую повторить не получится.
...
Рейтинг: 0 / 0
16.01.2021, 17:13
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036416
Страдалецъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Страдалецъ
Участник
MazoHist почти то что надо! Но не находит первую группу если выставить в примере для второй записи значение State=1. Выдает первый элемент из второй группы.
...
Рейтинг: 0 / 0
16.01.2021, 17:22
| Ответить | Цитировать | Написать  
Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
    #40036442
Страдалецъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Страдалецъ
Участник
Стоп, был не прав. Все работает. Это подключился через DBeaver, а там оказывается надо отдельно подтверждать сохранение данных. Пока не стал по шагам проверять результаты, не видел этого косяка.
...
Рейтинг: 0 / 0
16.01.2021, 19:14
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Выбрать из последовательности ближайший, непрерывный интервал размером не меньше чем N
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (1): Анонимы (1)
Читали форум (1): Анонимы (1)
Пользователи онлайн (9): Анонимы (6), Bing Bot, RePredeclared, Yandex Bot
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
Start [/forum/topic.php?fid=54&tid=2008340]:
 0ms
get settings:
 1ms
get forum list:
 7ms
check forum access:
 0ms
check topic access:
 0ms
track hit:
 30ms
get topic data:
 3ms
get forum data:
 1ms
get page messages:
 15ms
update_topic_read_status (2008340): 16.01.2021 19:14:40:
 0ms
get tp. blocked users:
 0ms
get online users:
 31ms
check new:
 1ms
others: 93ms
total:  182ms
созд. / загр.: 182ms (нв.: 1ms)
x
x
Закрыть


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