ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выборка граничных значений из числового ряда по условию
18 сообщений из 18, страница 1 из 1
Выборка граничных значений из числового ряда по условию
    #39816032
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Здравствуйте уважаемые товарищи.
Помогите пожалуйста составить запрос для выборки периодов из ряда номеров для заданного условия.
Есть таблица
Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE `number` (
  `series` varchar(16) NOT NULL,
  `number` mediumint(9) unsigned NOT NULL,
  `status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`series`,`number`),
  KEY `series` (`series`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Заполненная для примера следующими значениями:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
series  number status
aa      1          0
aa      2          0
aa      3          1
aa      4          1
aa      5          0
aa      6          1
aa      7          1
ab      1          0
ab      2          1
ab      3          1
ab      4          1
...

Делаем выборку периодов ряда номеров со статусом равным 1

В результате выполнения составляемого запроса, должна появится выборка
Код: plaintext
1.
2.
3.
series  number_min number_max
aa      3                4
aa      6                7
ab      2                4

Ребята, подскажите пожалуйста, в каком направлении искать решение этой задачи, а в идеале хотел бы увидеть рабочий запрос.
P.S. задачу нужно решить только средствами SQL без использования языков программирования.
...
Рейтинг: 0 / 0
21.05.2019, 20:55
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816033
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
1) Укажите версию MySQL.
2) При наличии индекса (`series`,`number`) индекс (`series`) не имеет смысла.
...
Рейтинг: 0 / 0
21.05.2019, 21:01
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816060
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Akina, В качестве испытательного стенда использую MariaDB (Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8) но имею желание получить наиболее универсальное решение для MySQL.


автор2) При наличии индекса (`series`,`number`) индекс (`series`) не имеет смысла.
Да вы правы, в контексте этой задачи индекс по столбцу series не нужен.
...
Рейтинг: 0 / 0
21.05.2019, 22:53
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816121
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
АндрейNEW,

"монотонность и непрерывность" значений поля number в пределах одного и того же значения поля series - гарантируется?
или гарантируется только монотонность, но не гарантируется непрерывность?

В целом, напрашивается попытка реализовать метод "инварианта групп": искать заницу между сквозным нумератором записей в размере series и series+status...
...
Рейтинг: 0 / 0
22.05.2019, 06:36
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816122
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Щукина АннаАндрейNEW,

"монотонность и непрерывность" значений поля number в пределах одного и того же значения поля series - гарантируется?
или гарантируется только монотонность, но не гарантируется непрерывность?

В целом, напрашивается попытка реализовать метод "инварианта групп": искать заницу разницу между сквозным нумератором записей в размере series и series+status...
...
Рейтинг: 0 / 0
22.05.2019, 06:37
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816143
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
АндрейNEWв контексте этой задачи индекс по столбцу series не нуженОн ВООБЩЕ не нужен. Индекс по выражению может использоваться как полностью, так и префиксно.

АндрейNEWимею желание получить наиболее универсальное решение для MySQL.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH cte1 AS (
SELECT *, 
       COALESCE(LEAD(status) OVER (PARTITION BY series ORDER BY number DESC), 0) prev,
       COALESCE(LAG(status) OVER (PARTITION BY series ORDER BY number DESC), 0) next
FROM number
),
cte2 AS (
SELECT series, number, prev, LAG(number) OVER (PARTITION BY series ORDER BY number DESC) next
FROM cte1
WHERE prev + next = 1
  AND status = 1
)
SELECT series, number start, next end
FROM cte2
WHERE prev=0
ORDER BY 1,2


fiddle
Для 5 версии наиболее разумным будет решение с использованием переменных. Но лень.
...
Рейтинг: 0 / 0
22.05.2019, 07:51
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816224
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Щукина АннаАндрейNEW,

"монотонность и непрерывность" значений поля number в пределах одного и того же значения поля series - гарантируется?
или гарантируется только монотонность, но не гарантируется непрерывность?

В целом, напрашивается попытка реализовать метод "инварианта групп": искать разницу между сквозным нумератором записей в размере series и series+status...

Здравствуйте Анна.
Непрерывность не гарантируется, но исходя из составного индекса по серии и номеру гарантируется уникальность каждого номера в своей серии.
...
Рейтинг: 0 / 0
22.05.2019, 10:22
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816233
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Akina,
Благодарю за помощь, Ваш запрос хорош, но есть один момент.
в случае если запись со статусом 1 единична, то она не попадает в выборку, а должна попадать.
При исходных данных в таблице
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
insert into number values
('aa',1,0),
('aa',2,0),
('aa',3,1),
('aa',4,1),
('aa',5,0),
('aa',6,1),
('aa',7,1),
('ab',1,0),
('ab',2,1),
('ab',3,1),
('ab',5,1),
('ab',6,0),
('ab',7,0),
('ab',9,1),
('ab',10,0)


Получаем результат:

Код: plaintext
1.
2.
3.
series start	end
aa	 3	4
aa	 6	7
ab	 2	5

а должен быть:

Код: plaintext
1.
2.
3.
4.
series	start	end
aa	3	4
aa	6	7
ab	2	5
ab     9      9
...
Рейтинг: 0 / 0
22.05.2019, 10:30
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816283
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
АндрейNEWв случае если запись со статусом 1 единична, то она не попадает в выборку, а должна попадать.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
WITH cte1 AS (
SELECT *, 
       COALESCE(LEAD(status) OVER (PARTITION BY series ORDER BY number DESC), 0) prev,
       COALESCE(LAG(status) OVER (PARTITION BY series ORDER BY number DESC), 0) next
FROM number
),
cte2 AS (
SELECT *
FROM cte1
WHERE prev + next <= 1
  AND status = 1
),
cte3 AS (
SELECT *, 
       CASE WHEN next 
            THEN LAG(number) OVER (PARTITION BY series ORDER BY number DESC)
            ELSE number END last
FROM cte2
)
SELECT series, number first, last
FROM cte3
WHERE prev = 0
ORDER BY 1,2


fiddle

Правда, мне кажется, что если рисовать заново, получится лучше и оптимальнее - но ле-е-ень, проще оказалось доработать.
...
Рейтинг: 0 / 0
22.05.2019, 11:26
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816754
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
Akina,

спасибо за тестовые данные.... :) ;)


АндрейNEW,

Пример на на "инварианте групп":
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select series, min(number) as first, max(number) as last
  from (
         select series, number, status 
              , - row_number() over(partition by series, status order by number)
                + row_number() over(partition by series order by number) as grp_id
           from number
       ) v
 where status = 1
 group by series, grp_id
 order by series, min(number);

fiddle
...
Рейтинг: 0 / 0
22.05.2019, 19:54
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816760
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
АндрейNEW,

ещё как вариант - метод начала групп:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
/************************************************/
/* Start-OF-Group Метод схлопавания диапазонов: */
/************************************************/

select series, min(number) as first, max(number) as last
  from (
         select series, number
              , sum(Start_Of_Group) over(partition by series 
                                             order by number) as grp_id
           from (
                  select series, number, status
                       , case
                          when lag(status) over(partition by series 
                                                    order by number) = status 
                            then 0 
                          else 1 
                         end as Start_Of_Group
                    from number
                ) v0
          where status = 1
       ) v1
  group by series, grp_id
 order by series, min(number);

fiddle
...
Рейтинг: 0 / 0
22.05.2019, 20:03
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816763
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
АндрейNEW,

можно без всяких чудес и новомодных фич реализовать решение, но, думаю, оно вас не порадует своей гирлядностью и ресурсоемкостью...
...
Рейтинг: 0 / 0
22.05.2019, 20:19
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39816825
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Щукина Анна, Akina Благодарю за помощь. Увидел как велик пробел в моих знаниях sql. Буду разбираться как работают написанные вами запросы, а параллельно придется изучить новые для меня области знаний в SQL.
...
Рейтинг: 0 / 0
22.05.2019, 23:43
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39819540
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Еще раз благодарю за помощь в решении задачи. Я изучил предложенные Вами варианты решения данной задачи.
К сожалению в MariaDB версии 10.1 отсутствую оконные функции, табличные выражения и рекурсии.
На свое машине проапгрейдить базу данных проблем нет, а вот на целевой машине, это сделать не возможно.
Возможно ли без использования этих функций решить данную задачу?
...
Рейтинг: 0 / 0
28.05.2019, 23:27
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39819572
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
АндрейNEWВозможно ли без использования этих функций решить данную задачу? Чуть выше
...
Рейтинг: 0 / 0
29.05.2019, 04:45
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39819575
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
АндрейNEW,

как подсказка...

Возьмите за основу способ на " инварианте групп ", но сделайте в нем нумерацию с использованием переменных , а не через row_number. Должно будет получиться вполне себе приемлемое решение...
...
Рейтинг: 0 / 0
29.05.2019, 05:15
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39821888
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Щукина Анна,
Благодарю Вас за желание помочь.
По вашему совету доработал запрос на инварианте груп с использованием переменных, но видно я не до конца понял алгоритм решения этой задачи. В итоге застпорился на таком варианте запроса но он к сожалению не работает. Прошу подправить меня, в нужном направлении еще раз.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select series, min(`number`) as first, max(`number`) as last
  from (
         select series, `number`, status 
              , - (SELECT @i := @i + 1 AS row_number FROM `number` AS sn, (select @i:=0)  AS z GROUP BY series, status ORDER BY `number`)
                + (SELECT @j := @j + 1 AS row_number FROM `number` AS sn2, (select @j:=0)  AS z2 GROUP BY series ORDER BY `number`) as grp_id
           from `number`
       ) v
 where status = 1
 group by series, grp_id
 order by series, min(`number`);


в результате запроса получаю ошибку: "Subquery returns more than 1 row".
...
Рейтинг: 0 / 0
03.06.2019, 18:30
| Ответить | Цитировать | Написать  
Выборка граничных значений из числового ряда по условию
    #39823971
АндрейNEW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АндрейNEW
Гость
Ребята помогите пожалуйста оформить этот запрос с использованием переменных. Сам никак не могу осилить.
...
Рейтинг: 0 / 0
07.06.2019, 12:21
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выборка граничных значений из числового ряда по условию
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=35&tid=1829111]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 2ms
others: 14ms
total:  152ms
созд. / загр.: 152ms
x
x
Закрыть


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