Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
Всем привет!

Требуется разбить результат запроса на группы с одинаковым количеством строк и перенумеровать получившиеся группы. Количество строк в группе будет передаваться из вне, в виде параметра. Количество строк, возвращаемое запросом, заранее неизвестно.
Логика разбиения простая - сортируем результат, с первой строки начинаем формировать первую группу, как насобирали N-строк - начинаем формировать следующую группу. Последняя группа может иметь строк меньше, чем переданное N, если результат запрос не делится на целое кол-во групп.

Решил так:
select owner
     , object_name
     , ceil(row_number() over(order by created)/ 6 /*будет передаваться в виде параметра*/) goup_num
from   all_objects
where  rownum <= 20

Но подумал - может просмотрел какую-то аналитическую функцию, которая делает тоже самое?
Что-то навроде NTILE, но которая на вход получает не количество групп, а количество строк в группе?
20 ноя 17, 08:50    [20967872]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Elic
Member

Откуда:
Сообщений: 29979
Начинучка
where  rownum <= 20
Наивный юноша.
Начинучка
может просмотрел какую-то аналитическую функцию, которая делает тоже самое?
Куда уж проще?
20 ноя 17, 09:06    [20967891]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
[quot Elic]
Начинучка
where  rownum <= 20
Данное условие, как и обращение к all_objects - это исключительно для демонстрационных целей требуемого.... Думаю - на 20-ти строках вполне себе показана суть желаемого? Понятное дело, что в реальности запрос будет не из all_tables и условия по ROWNUM в нем не будет. А если бы и было - запрос не ставит цель выбрать N-первых(последних) записей (если вы про то, что сортировка сработает только после отработки этого условия). Всё что требуется - разбить результат запроса на "пакеты" одинаковой ширины в пределах заданного количества.... Сортировка строк в группе, ровно и как групп между собой - в принципе, не важна.
20 ноя 17, 09:17    [20967907]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Anatoly B
Member

Откуда:
Сообщений: 178
Начинучка,

Речь о другом.
Чем row_number() over(order by created) отличается от rownum в запросе?
20 ноя 17, 09:20    [20967911]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
Anatoly B
Начинучка,

Речь о другом.
Чем row_number() over(order by created) отличается от rownum в запросе?
тем, что запрос будет работать в СУБД, отличной от Oracle. И если поддержка оконно-аналитических функций в ней есть, то вот ROWNUM - отсутствует как класс. Спросил же в оракловой ветке потому, что считаю, что тут наиболее компетентные и авторитетные специалисты в области оконных функций. И мнение это основывается на том, что в оракле эти функции есть с незапамятных времен (с версии 8i). И только ленивый их не использует. В то время как в других СУБД - это сравнительно "недавно" появившееся новшество и многие ещё не совсем в курсе дел.....
20 ноя 17, 09:26    [20967919]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Начинучка,

Если укажите СУБД, можно ответить точнее

В некоторых например поддерживается: SELECT TOP n
20 ноя 17, 12:58    [20968847]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
MaximaXXL,

СУБД MS SQL 2012 и выше. TOP n не подходит. Решаемая задача - удаление большого числа записей небольшими порциями. TOP n плохо вписывается, так как удалять нужно по определенному условию, которое непокрыто индексами, что приводит к многократным неоптимальным выборкам со сканами этой большой таблицы.
Было решено один раз выбирать список удаляемых ID (первичный ключ с кластерным индексом) во временную таблицу, бить полученную выборку на "пакеты" и удалять данные попакетно. При этом в целевой таблице данные выбираются по ID, что допускает использование кластерного индекса (seek) и увеличивает скорость обработки данных.
20 ноя 17, 13:12    [20968904]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Начинучка,

Не очень понял почему TOP не вписывается


DELETE FROM S.T
WHERE ID IN  
   (SELECT TOP 10 ID   
    FROM S.TEMP_T
    ORDER BY ID ASC);  
GO  
20 ноя 17, 13:25    [20968969]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
MaximaXXL,

Потому что условия выборки не ложатся на существующую схему индексирования.
Соответственно, если делать select top(n) from t where <тут условия вне индексов таблицы>, то возникает fullscan таблицы в которой порядка 500 млн. записей. А так как это все делается в цикле, то получается печалька. И самое грустное - это блокировочный характер скульсервера. Который решает не утруждать себя строчными блокировками и эскалирует блокировку до уровня таблицы, что ставит на колени всё остальное, работающее с этой же таблицей....
20 ноя 17, 13:31    [20969004]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Начинучка,
если у тебя есть задача (в СУБД MS SQL 2012 и выше) удаление большого числа записей небольшими порциями, то иди в тему MS SQL и спрашивай как там люди это делают.
тупее чем задавать в теме оракла вопрос о конкретной реализации одного из выбранных решений для другой субд придумать сложно.
20 ноя 17, 13:32    [20969010]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
Vint,

вопрос был не о том, как это реализовать в другое СУБД. вопрос был о том - нет ли аналитической функции, которая бы заменила собой конструкцию - ceil(row_number() over(order by created)/ 6 )
почему вопрос был задан именно тут - я уже ответил Анатолию Б. Все остальные ответы были исключительно для удовлетворения любопытства MaximaXXL и к первоначальному вопросу отношения не имеют.

По сути вопроса ответ получен - готовой функции нет, используемое решение для деления множества на пакеты - вполне себе оптимальное.
20 ноя 17, 13:42    [20969043]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Начинучка,
руки исправь. и научись искать.
20 ноя 17, 13:45    [20969047]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Начинучка,

ну ок, не нравиться TOP возьмите связку
Offset n Row
Fetch First k Rows Only


тоже может мило побить на пакеты ... и должно работать на MS SQL
20 ноя 17, 13:45    [20969049]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
Vint,

голову включи и почитай чего делает предложенный тобой и упомянутый мною выше NTILE - он делит выборку на заданное число групп. А мне не нужно заданное число групп, мне нужно - заданное количество строк в группе. Чтобы прикрутить NTILE именно для своей задачи, мне нужно будет знать количество строк, возвращаемых выборкой, поделить его на число строк в группе, чтобы вычислить кол-во групп, формируемое NTILE. Точно думаешь, что это будет проще того, что есть сейчас?
Уж если пытаешься отвечать, то хотя бы вникай в суть дискуссии. Иначе получается как в рекламе про жвачку - что иногда лучше жевать, чем говорить.....
20 ноя 17, 13:52    [20969083]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Начинучка,
я думаю что твоя задача решается по другому и абсолютно не надо делить на равное количество строк. это уже лично твоя идеотичность решения.
вместо распаралеливания удаления ты пытаешься удалить какое то конкретное количество строк.
так что это ты тут "олень", который вместо поиска решения - бьется головой в стенку и кричит что ее надо пробить.
20 ноя 17, 13:58    [20969107]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
Vint,

Что ещё раз выдаёт в тебе нечитателя....
Задача была озвучена - с минимальными затратами по ресурсам сервера и с минимальными помехами другим сессиям, удалить некоторое количество данных с большой высоконагруженной таблицы. По сути, выполнять удаление в фоне.
А теперь расскажи мне, как параллельные вычисления позволяют снизить потребляемые ресурсы и ожидания доступа к таблице и журнальному логу в среде блокировочника. При условии, что удаляемые данные равномерно размазаны по страницам и сервер отказывается вешать блокировки уровня строк-страниц, а сразу эскалируется до уровня блокировки таблицы. Буду рад услышать варианты решений от неоленя.
20 ноя 17, 15:05    [20969359]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Начинучка,

20969049 Это тоже не подходит?
20 ноя 17, 16:13    [20969670]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Филолог Марь Иванна
Guest
Vint
..идеотичность решения...

Какое из этих слов является основой?
21 ноя 17, 07:52    [20970841]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Начинучка,

Тоже нечитатель, но чем не подошло банальное
declare @n int = 10;
select row_number() over (order by t.table_name ) % @n s 
from INFORMATION_SCHEMA.TABLES t
21 ноя 17, 10:49    [20971288]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2787
env,

автор
Но подумал - может просмотрел какую-то аналитическую функцию, которая делает тоже самое?


.....
stax
21 ноя 17, 11:48    [20971612]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
Начинучка
Guest
env,

Разбиение на "пакеты" идет по значению ID. ID - первичный ключ с кластерным индексом (для ораклистов - очень приближенный аналог IOT).
Тут суть в том, что остаток от деления будет раскидывать близкие по значению записи в совершенно разные группы. В результате чего, при удалении записей одного "пакета" будет затронуто бОльшее число страниц. При первоначально выбранном алгоритме разбиения вероятность попадания близких по значению ID в один пакет - сильно выше.

Тут только из этих соображений делался выбор. Возможно, то экономия на спичках.
21 ноя 17, 12:03    [20971691]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Начинучка
При первоначально выбранном алгоритме разбиения вероятность попадания близких по значению ID в один пакет - сильно выше.


Всё равно не понял в чём сложность. Если надо последовательно разбить на группы в рамках сортировки, то можно воспользоваться прямолинейностью скуль сервера.
declare @n int = 10;
select cast(row_number() over (order by t.table_name ) / @n as int) s 
from INFORMATION_SCHEMA.TABLES t
21 ноя 17, 12:50    [20971851]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
env,

Даже cast не нужен, с точки зрения скуль сервера здесь деление int на int, а значит результат тоже int.
21 ноя 17, 12:56    [20971873]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Начинучка
Спросил же в оракловой ветке потому, что считаю, что тут наиболее компетентные и авторитетные специалисты в области оконных функций

Дык, наиболее компетентные и авторитетные специалисты первыми и ответили.
21 ноя 17, 12:58    [20971879]     Ответить | Цитировать Сообщить модератору
 Re: Разделение результата выборки на группы с заданным числом строк  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Stax,

Стас, когда решал подобную задачу - точно также удалял пачками по row_number() / int. Там структура хранения отличается от оракловой, поэтому желательно попадать в страницы и задействовать кластерный индекс при массовом удалении.
21 ноя 17, 13:01    [20971895]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить