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

Откуда:
Сообщений: 679
Всем привет !



Есть табличка со столбцами и значениями
Id, Quantity
1, 201
2, 251
3, 113
4, 50
5, 1001


Требуется написать выборку (без использования курсора), которая возрващает все строки и значения удовлятворяющие следующим условиям
1) значение quantity должно быть кратным N для примера N=51
2) Сумма quantity по последовательности строк не должно превышать M для примера M = 515 и считается в порядке возрастания id и с учетом первого пункта
3) Отображаются только те строки значения для них, которые удовлетворяют пунктам 1) и 2)

Результат
id, quantity, quantity_calc, sum_quantity
1, 201, 153, 153
2, 251, 204, 357
3, 113, 102, 459
4, 50, 0, 459 - не должно отобразиться, посколько 50 не кратно 51
5, 1001, 1, 510 quantity_calc = 1, а не 19 потому, что sum_quantity требуется добавить всего лишь одно значение 51 чтобы выполнилось условие пункта 2).
22 июл 13, 21:10    [14601073]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74928
Testor1
5, 1001, 1, 510 quantity_calc = 1, а не 19 потому, что sum_quantity требуется добавить всего лишь одно значение 51 чтобы выполнилось условие пункта 2).


Непонятно, по какому алгоритму "quantity_calc = 1, а не 19", если до этого quantity_calc считалось как Quantity / 51 * 51 AS Quantity_Calc?!
22 июл 13, 21:38    [14601173]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74928
И, кстати, что такое "кратно" - математика 6ой класс.
22 июл 13, 21:45    [14601196]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
постановщик 79lvl
Guest
Testor1,

ипическая сила...

если переводить на известные в более широких кругах наречия, то образно задача примерно такая (?):

* есть обрезки досок разной длины (201см, 251, 113, 50, 1001)
* нужно нарезать из них детали (...образно же) длиной 51см
* всего деталей нужно десять штук (почему тогда 515?)
* из каждого обрезка нужно нарезать максимально возможное количество деталей
* обрезки поступаю в образный станок для нарезания пятидесятиодносантиметровыхнаноштангенциркулей в режиме FIFO

в так называемом "решении" по последней строке quantity_calc = 51, а не один. в колонках такая же каша как в постановке.
есть три столбца с "кол-вом" в названии и ни в одном из них не выведено 3, 4, 2, 0, 1 (вот тут как раз единичка и пригодилась бы).
22 июл 13, 23:14    [14601492]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
SQL 2012
declare @N int=51, @M int=515;
declare @qTable table (id int identity, Quantity int)
insert @qTable(Quantity) values (201),(251),(113),(50),(1001),(190),(320)

declare @K int=@M/@N
select id, Quantity, 
   case when @K>=sNmax then Nmax when @K>(sNmax-Nmax) then @K-(sNmax-Nmax) else 0 end * @N qCalc,
   case when @K>=sNmax then sNmax else @K end * @N qSum
from (
   select 
      id, Quantity,
      Quantity/@N Nmax, 
      sum(Quantity/@N)over(order by id)sNmax 
   from @qTable
   )q
idQuantityqCalcqSum
1201153153
2251204357
3113102459
4500459
5100151510
61900510
73200510
23 июл 13, 00:24    [14601743]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Cygapb-007
SQL 2012
declare @N int=51, @M int=515;
declare @qTable table (id int identity, Quantity int)
insert @qTable(Quantity) values (201),(251),(113),(50),(1001),(190),(320)

declare @K int=@M/@N
select id, Quantity, 
   case when @K>=sNmax then Nmax when @K>(sNmax-Nmax) then @K-(sNmax-Nmax) else 0 end * @N qCalc,
   case when @K>=sNmax then sNmax else @K end * @N qSum
from (
   select 
      id, Quantity,
      Quantity/@N Nmax, 
      sum(Quantity/@N)over(order by id)sNmax 
   from @qTable
   )q
idQuantityqCalcqSum
1201153153
2251204357
3113102459
4500459
5100151510
61900510
73200510



То что нужно. Спасибо большое !

отфильтрую по qCalc >0
23 июл 13, 09:30    [14602257]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Testor1
отфильтрую по qCalc >0
from (...) q where @K>(sNmax-Nmax)
23 июл 13, 09:40    [14602299]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Cygapb-007
Testor1
отфильтрую по qCalc >0
from (...) q where @K>(sNmax-Nmax)


На 2008 R2 это все будет работать ?
23 июл 13, 09:53    [14602369]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1
На 2008 R2 это все будет работать ?

Подсчёт нарастающего итога при помощи SUM(...) OVER(ORDER BY...) появился только в MSSQL 2012.
23 июл 13, 10:05    [14602449]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Testor1
На 2008 R2 это все будет работать ?

Подсчёт нарастающего итога при помощи SUM(...) OVER(ORDER BY...) появился только в MSSQL 2012.


:(

Есть идеи ?

У меня пока есть мысли на счет cross apply, но пока не успел проверить на практике
23 июл 13, 10:14    [14602488]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2407
Testor1
У меня пока есть мысли на счет cross apply, но пока не успел проверить на практике


ну так проверьте. что ж вы все ждете блюдечка с голубой каемочкой?
23 июл 13, 10:19    [14602505]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1,

Есть идея использовать курсор. Для подсчёта нарастающего итога, в общем случае, это самый быстрый из документированных способов.
23 июл 13, 10:20    [14602511]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Testor1,

Есть идея использовать курсор. Для подсчёта нарастающего итога, в общем случае, это самый быстрый из документированных способов.


С курсором будет работать :) это 100% и знаю как все реализовать, но я стараюсь использовать его по минимуму.
Вот и думаю как хитро все сделать.
23 июл 13, 10:23    [14602522]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Добрый Э - Эх
Guest
Testor1
У меня пока есть мысли на счет cross apply, но пока не успел проверить на практике
Вариантов реализации накопительного итога на "допотопных" версиях сервера - масса. CRoss apply - один из них
23 июл 13, 10:26    [14602538]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1
я стараюсь использовать его по минимуму

Можно поинтересоваться, почему?

Гость333
курсор. Для подсчёта нарастающего итога, в общем случае, это самый быстрый из документированных способов.

Это, понятно, для версий ниже 2012. Для 2012 самый быстрый — sum() over(order by).
23 июл 13, 10:27    [14602548]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Testor1
я стараюсь использовать его по минимуму

Можно поинтересоваться, почему?

Гость333
курсор. Для подсчёта нарастающего итога, в общем случае, это самый быстрый из документированных способов.

Это, понятно, для версий ниже 2012. Для 2012 самый быстрый — sum() over(order by).


Потому курсор медленный. Это рекомендация была от других участников форума и на другой ветке. С чем я полностью согласен. Убедился на собственном опыте.
23 июл 13, 12:43    [14603534]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Testor1
Потому курсор медленный.

Для задачи подсчёта нарастающего итога — сюрприз! — курсор быстрый.
http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver
23 июл 13, 12:47    [14603574]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Testor1
Member

Откуда:
Сообщений: 679
Добрый Э - Эх
Testor1
У меня пока есть мысли на счет cross apply, но пока не успел проверить на практике
Вариантов реализации накопительного итога на "допотопных" версиях сервера - масса. CRoss apply - один из них


Дойду ло сервака и опробую его.
Вижу один только минус. Он по несколько раз будет делать селект, чтобы подсчитать сумму бонусов для последовательности id.
23 июл 13, 12:51    [14603603]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Pavel_Buk
Member

Откуда:
Сообщений: 4
Не очень понятно, в чело сложность задачи. Почему не подходит простой вариант:

DECLARE @N int=51
DECLARE @M int=515

select t.id,t.Quantity,(t.Quantity/@N)*@N as Calc,
(select SUM((t2.Quantity/@N)*@N) from t1 t2 where t2.id<=t.id)
from t1 t
where (select SUM((t2.Quantity/@N)*@N) from t1 t2 where t2.id<=t.id)<=@M



?
23 июл 13, 15:19    [14604741]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
dfhjkj
Guest
Pavel_Buk,

сложность твоего запроса О(n^3), у курсора, у хитрого апдейта и window функций O(n)
23 июл 13, 15:30    [14604841]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Pavel_Buk,

к тому же запрос выдает неверный результат
23 июл 13, 15:49    [14605006]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Testor1
На 2008 R2 это все будет работать ?
declare @N int=51, @M int=515;
declare @qTable table (id int identity primary key, Quantity int, qCalc int, qSum int)
insert @qTable(Quantity) values (201),(251),(113),(50),(1001),(190),(320)

declare @t int, @s int=0, @K int=@M/@N
update q set
   @t=@s,
   @s+=q.Quantity/@N,
   qCalc=@N*case when @K>@s then @s-@t when @K>@t then @K-@t end,
   qSum =@N*case when @K>@s then @s else @K end
from @qTable q

select * from @qTable where qCalc is not null
23 июл 13, 15:57    [14605081]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Cygapb-007,

Только надо бы указать, что этот способ не документирован, следовательно, результат не гарантируется.
23 июл 13, 16:02    [14605121]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
хотя, вспоминая о параллельных вычислениях, гарантии правильности нет :(
23 июл 13, 16:02    [14605125]     Ответить | Цитировать Сообщить модератору
 Re: Задачку решить без использования курсора  [new]
fhfjgkldll
Guest
Cygapb-007,

где-то тут не так давно кидали сцыль на статью. там опсосано какие хинты добавить чтобы все было пучком. также рассматривали разные варианты индексов и еще кучу всяких случаев. где та статья хз.
23 июл 13, 16:45    [14605425]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить