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


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

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

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

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

В целом, напрашивается попытка реализовать метод "инварианта групп": искать заницу разницу между сквозным нумератором записей в размере series и series+status...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.05.2019, 07:51
    #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, 10:22
    #39816224
АндрейNEW
АндрейNEW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка граничных значений из числового ряда по условию
Щукина АннаАндрейNEW,

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

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

Здравствуйте Анна.
Непрерывность не гарантируется, но исходя из составного индекса по серии и номеру гарантируется уникальность каждого номера в своей серии.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.05.2019, 10:30
    #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, 11:26
    #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, 19:54
    #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, 20:03
    #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:19
    #39816763
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка граничных значений из числового ряда по условию
АндрейNEW,

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

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

Возьмите за основу способ на " инварианте групп ", но сделайте в нем нумерацию с использованием переменных , а не через row_number. Должно будет получиться вполне себе приемлемое решение...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.06.2019, 18:30
    #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
| Ответить | Цитировать | Написать  
07.06.2019, 12:21
    #39823971
АндрейNEW
АндрейNEW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Выборка граничных значений из числового ряда по условию
Ребята помогите пожалуйста оформить этот запрос с использованием переменных. Сам никак не могу осилить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выборка граничных значений из числового ряда по условию / 18 сообщений из 18, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 440ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1829111]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 2ms
others: 308ms
total:  440ms
x
x
Закрыть


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