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

Откуда: Санкт-Петербург
Сообщений: 5943
Изучаю функции ранжирования. Запрос и результат:

Картинка с другого сайта.

не понимаю, почему во втором столбце ("Группа") у меня получаются одни единицы - наименования ведь ведомостей разные, а следовательно для каждого нового наименования номер группы должен увеличиваться на 1. что не так?
28 июл 09, 10:29    [7467352]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
попробуйте убрать partition by и посмотреть на результат
28 июл 09, 10:35    [7467388]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Для каждого значения поля в PARTITION BY ранжирующая функция отрабатывает независимо от других значений этого поля.
Если значения этого поля уникальны, то для каждого значения DENSE_RANK() будет начинаться и заканчиваться единицей.
28 июл 09, 10:42    [7467440]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
Паганель
попробуйте убрать partition by и посмотреть на результат

спасибо, получил нужный результат, но не понял почему. ведь через partition by я указываю, какие столбцы анализировать, дабы давать нумерацию. а так - только сортировка... ???
28 июл 09, 10:47    [7467471]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
iap
Для каждого значения поля в PARTITION BY ранжирующая функция отрабатывает независимо от других значений этого поля.
Если значения этого поля уникальны, то для каждого значения DENSE_RANK() будет начинаться и заканчиваться единицей.

так я же на это и расчитывал.
Видите в колонке "ведомость" разные наименования ведомостей? так вот для Инженерия везде в Группе должна стоять 1, для ПИР -2, для Реставрация - 3 и т.д.
поэтому в PARTITION BY и указал by est.EstimateName, мол только по этому полю анализировать...
28 июл 09, 10:53    [7467516]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Compositum
через partition by я указываю, какие столбцы анализировать, дабы давать нумерацию. а так - только сортировка... ???
Такие заявления надо обосновывать цитатами из документации
Например, про Предложение OVER (Transact-SQL)
28 июл 09, 10:54    [7467520]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Compositum
iap
Для каждого значения поля в PARTITION BY ранжирующая функция отрабатывает независимо от других значений этого поля.
Если значения этого поля уникальны, то для каждого значения DENSE_RANK() будет начинаться и заканчиваться единицей.

так я же на это и расчитывал.
Видите в колонке "ведомость" разные наименования ведомостей? так вот для Инженерия везде в Группе должна стоять 1, для ПИР -2, для Реставрация - 3 и т.д.
поэтому в PARTITION BY и указал by est.EstimateName, мол только по этому полю анализировать...
1. Берётся одно значение наименования ведомостей (потому что это поле в PARTITION BY)
2. Берутся все строки с этим значением и сортируются по полю, заданному в ORDER BY
3. Задаётся ранг в зависимости от номера строки в порядке ORDER BY,
при этом строкам с одинаковым значением поля сортировки присваивается равный ранг.
Так работает RANK()
4. DENSE_RANK() перенумеровывает результат RANK() в том же порядке, но чтобы не было пропусков в результирующем значении.

У Вас все значения est.EstimateName разные? Ну так значит ранжируется по одной строке на каждое значение. Потому что других нет.
28 июл 09, 11:24    [7467754]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
iap

У Вас все значения est.EstimateName разные? Ну так значит ранжируется по одной строке на каждое значение. Потому что других нет.

Нет, ну где же они все разные? Смотрите: с 1-й по 8-ю одно значение, с 9-й по 11-ю др. и т.д.
28 июл 09, 12:41    [7468316]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Compositum
Смотрите: с 1-й по 8-ю одно значение
Это и будет секция
Про которую написано в документации
Ссылку на которую я дал

А внутри этой секции
iap
строкам с одинаковым значением поля сортировки присваивается равный ранг.
28 июл 09, 12:47    [7468344]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
уточнение: на скрине est.EstimateName - это поле "Ведомость", по нему и выполняю DENSE_RANK().
28 июл 09, 12:47    [7468346]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Compositum
iap

У Вас все значения est.EstimateName разные? Ну так значит ранжируется по одной строке на каждое значение. Потому что других нет.

Нет, ну где же они все разные? Смотрите: с 1-й по 8-ю одно значение, с 9-й по 11-ю др. и т.д.
Но у Вас написано ORDER BY est.EstimateName. А в пределах одного и того же est.EstimateName (из-за PARTITION BY) получается один и тот же RANK() для всех строк в этом случае.
28 июл 09, 12:47    [7468347]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
Паганель,

а вон оно как.... сиба, тогда понятно.
28 июл 09, 12:49    [7468357]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
iap,

я понял, спасибо =)
28 июл 09, 12:49    [7468358]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Compositum
iap

У Вас все значения est.EstimateName разные? Ну так значит ранжируется по одной строке на каждое значение. Потому что других нет.

Нет, ну где же они все разные? Смотрите: с 1-й по 8-ю одно значение, с 9-й по 11-ю др. и т.д.
Но у Вас написано ORDER BY est.EstimateName. А в пределах одного и того же est.EstimateName (из-за PARTITION BY) получается один и тот же RANK() для всех строк в этом случае.
Фактически, у Вас нет никакой сортировки в OVER(), поскольку и PARTITION BY, и ORDER BY по одному и тому же полю.

P.S. Если не затруднит, в дальнейшем обходитесь, пожалуйста, без картинок в стиле "Мурзилки".
Желательно всё выкладывать в тексте.
28 июл 09, 12:50    [7468363]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
iap
Если не затруднит, в дальнейшем обходитесь, пожалуйста, без картинок в стиле "Мурзилки".
Желательно всё выкладывать в тексте.

хорошо =)
28 июл 09, 12:53    [7468380]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
Запрос:

select 
--Создаю постраничную выборку, указав, что данные необходимо разместить на 3-х страницах
NTILE(3) over ( order by EstimateName, PositionName) [Страница],
--Общая последовательная нумерация строк выборки
ROW_NUMBER() over (order by EstimateName, PositionName) [№ поз.],
--Нумерация имеющихся ведомостей
DENSE_RANK() over (order by EstimateName) [№ вед.],
[EstimateName] as [Ведомость], 
--Нумерация разделов, имеющихся в составе ведомости
DENSE_RANK() over (partition by EstimateName order by EstimateName, PositionName) [№ разд.],
[PositionName] as [Разделы в составе ведомости]
from dbo.tabEstimates as e 
left join 
dbo.tabPositions as p 
on e.EstimateID = p.EstimateID
where IsPart = 1
order by EstimateName, PositionName

Результат:

Страница№ поз.№ вед.Ведомость№ разд.Разделы в составе ведомости
111Инженерия1Внеплощадочные инженерные сети
121Инженерия2Внутренний водопровод и канализация
131Инженерия3Крышная газовая котельная
141Инженерия4Освещение экспозиционных помещений
151Инженерия5Отопление, вентиляция, кондиционирование, холодоснабжение.
161Инженерия6Системы безопасности и связи. Диспетчеризация инженерных систем.
171Инженерия7Технологические решения
181Инженерия8Электроснабжение. Силовое электрооборудование. Электроосвещение.
192ПИР1Изыскания и исследования.
1102ПИР2Исполнительная документация.
1112ПИР3Разработка рабочей документации.
1123Реставрация1Дверные и воротные заполнения
1133Реставрация2Лестницы
1143Реставрация3Напольные покрытия
1153Реставрация4Оконные заполнения
1163Реставрация5Осветительные приборы
1173Реставрация6Отопительные приборы
1183Реставрация7Помещения 2, 3 и 4 этажей
1193Реставрация8Фасады
1203Реставрация9Фурнитура дверных заполнений
1213Реставрация10Фурнитура оконных заполнений
1223Реставрация11Художественная отделка помещений и карнизы
2234СМР11Бетонные и железобетонные конструкции. 4 корпус
2244СМР12Бетонные и железобетонные конструкции. Двор 4
2254СМР13Бетонные и железобетонные конструкции. 3 корпус.
2264СМР14Бетонные и железобетонные конструкции. Двор 5. Лестница Л-1
2274СМР15Благоустройство. Булыжное мощение тротуара, 390 м2
2284СМР16Вертикальный транспорт
2294СМР17Гидроизоляция конструкций
2304СМР18Гидроизоляция подвала
2314СМР19Защита металлических конструкций от коррозии
2324СМР110Земляные работы
2334СМР111Конструкции полов
2344СМР112Корпуса 3, 4. Дворы 4, 5. Лестница 1.
2354СМР113Лестницы
2364СМР114Лифтовые шахты
2374СМР115Мероприятия по ПОС.
2384СМР116Металлические конструкции. Корпус 3
2394СМР117Металлические конструкции. Корпус 4
2404СМР118Металлические конструкции. Двор 4
2414СМР119Металлические конструкции. Двор 5. Лестница-1
2424СМР120Металлические конструкции. Устройство нового покрытия
2434СМР121Облицовочные работы
2444СМР122Огнезащита металлических конструкций
3454СМР123Отделочные работы
3464СМР124Подготовительные работы. Разборка конструкций
3474СМР125Реконструкция зала в осях 4-8/А-Г
3484СМР126Световые фонари
3494СМР127Светоограждающие конструкции. Оконное заполнение
3504СМР128Светоограждающие конструкции. Витражное заполнение
3514СМР129Светопрозрачное покрытие дворов
3524СМР130Стеклянное ограждение платформ, лестниц, мостиков
3534СМР131Углубление подвала
3544СМР132Усиление и замена конструкций. Кирпичные стены.
3554СМР133Усиление и замена конструкций. Перекрытия
3564СМР134Усиление фундаментов
3574СМР135Установка дверей
3584СМР136Устройство кровель различных типов
3594СМР137Устройство несущего каркаса настила и площадок в зале на 5 этаже над Аркой
3604СМР138Устройство ограждений маршей и площадок в новых проектируемых лестницах
3614СМР139Устройство перегородок
3624СМР140Устройство свайных фундаментов
3634СМР141Устройство фонарей
3644СМР142Штукатурные работы
3655Страхование1Гарантии


Получаю почти то, что хотел. Но всё же есть один момент, который бы мне хотелось решить иначе:
в приведенном выше запросе я использую NTILE(3), которая разбивает мою выборку на 3 страницы, рассчитывая, исходя из этого то, сколько строк на какой странице должно располагаться.
Я же хочу, чтобы расчет происходил наоборот - количество страниц рассчитывалось в зависимости от того, какое количество строк я назначу для страницы.

Т.е. хочу установить условие, мол "на странице должно располагаться не более 1 000 записей" - исходя из этого в поле "Страница" должны ставиться соответствующие значения страниц.

Далее, имея подобную выборку уже можно получать данные постранично, указывая в условии на выборку например это:
where Страница = 2
и затем уже буду отображать результат в таблицу GUI. Щелкая по кнопке, расположенной в окошке под таблицей, юзер будет "перелистывать" эти листы туда/сюда.
28 июл 09, 13:17    [7468485]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Compositum
Далее, имея подобную выборку уже можно получать данные постранично
Статью Постраничная выборка уже читали?
28 июл 09, 13:26    [7468540]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А что,
(ROW_NUMBER()OVER(ORDER BY EstimateName, PositionName)-1)/[КоличествоСтрокНаСтранице] AS [№ страницы]
не годится для этих целей?
28 июл 09, 13:34    [7468579]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
Паганель
Compositum
Далее, имея подобную выборку уже можно получать данные постранично
Статью Постраничная выборка уже читали?


да постраничная выборка не проблема. я ведь пишу об ином способе разделения на страницы. выборка имеет тот же вид, только данные первой колонки формируются по другому алгоритму.
28 июл 09, 14:11    [7468813]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
iap
А что,
(ROW_NUMBER()OVER(ORDER BY EstimateName, PositionName)-1)/[КоличествоСтрокНаСтранице] AS [№ страницы]
не годится для этих целей?

оно самое!!!! спасибо

ВСЕМ спасибо!
28 июл 09, 14:14    [7468836]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Вам надо:
Compositum
where Страница = 2
Если пройдете по данной мною ссылке, то прочтете
edwardsky
WHERE RowNumber between 10 and 20
Разве это не то, что Вам нужно?
28 июл 09, 14:16    [7468851]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Compositum
Member

Откуда: Санкт-Петербург
Сообщений: 5943
Паганель
Вам надо:
Compositum
where Страница = 2
Если пройдете по данной мною ссылке, то прочтете
edwardsky
WHERE RowNumber between 10 and 20
Разве это не то, что Вам нужно?

честно говоря - не то (или я вас не до конца понимаю).

преведенный iap код делает именно то, что мне было нужно.

похоже, что я все же до сих пор не правильно понимаю работу over...

Смотрите, применительно к коду:
(ROW_NUMBER()OVER(ORDER BY EstimateName, PositionName)-1)/10 AS [№ страницы]
я мысленно прокручиваю события так:

1. Первая строка, Смотрю наименование ведомости и наименование позиции, назначаю им номер 1, от которого отнимаю 1 и делю на 10, получаю 0.
2. Вторая строка, Смотрю наименование ведомости (оно совпадает с наименованием ведомости предидущей записи) и смотрю наименование позиции (а вот здесь уже не совпадает). Раз совпадений нет - присваиваю номер 2, от которой отнимаю 1 и делю на 10 получаю 0,1. и т.д.
3. Третья строка, Смотрю наименование ведомости (оно совпадает с наименованием ведомости предидущей записи) и смотрю наименование позиции (а вот здесь снова не совпадает). Раз совпадений нет - присваиваю номер 3, от которой отнимаю 1 и делю на 10 получаю 0,2. и т.д.
т.е согласно логике моего вычисления в колонке "Страница" должен был бы получиться такой набор:
0
0.1
0.2
0.3
0.4
и т.д.

Где я рассуждаю неверно?
28 июл 09, 14:26    [7468927]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2Compositum
а вы запускали данный скрипт? или логику включили?

для спящего время бодрствования равносильно сну
28 июл 09, 14:31    [7468973]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ну или на крайний случай расскажете на словах как хотите пронумеровать "страницу"?

для спящего время бодрствования равносильно сну
28 июл 09, 14:32    [7468982]     Ответить | Цитировать Сообщить модератору
 Re: Dense_rank()  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Compositum
присваиваю номер 2, от которой отнимаю 1 и делю на 10 получаю 0,1.
Такие рассуждения не помешало бы обосновывать цитатами из документации
Например, про / (деление) (Transact-SQL) (см. "Типы результата")
28 июл 09, 14:32    [7468985]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить