Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблема с запросом  [new]
Bag09
Member

Откуда: Москва
Сообщений: 68
Добрый день!
Помогите с запросом.

Проблема такая:
Есть таблица Table1 c полями K и M. Известна Сумма K (S).
Дано число N.
Необходимо заполнить поле M так, чтобы SUM(M) стало равно N. (распределить N пропорционально K).

Например, таблица

K M
8
7
5

Пусть N=16, S=20

Делаю Update Table1 set M = Round(N/S*K,0)
Получаю
K M
8 6
7 6
5 4

Но так не годится, т.к. при округлении можно потерять единицы
Как сделать update так, чтобы ничего не потерять? Ошибки округления можно занести к любой записи.
22 апр 16, 14:35    [19093860]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Bag09,

читайте про типы данных
22 апр 16, 14:44    [19093933]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE @t TABLE (
	K INT,
	M INT
)

INSERT INTO @t (K)
VALUES (8), (7), (5)

DECLARE
      @N INT = 16
    , @S INT = 20

UPDATE @t
SET M = ROUND(@N * 1. / @S * K, 0)

SELECT *
FROM @t
22 апр 16, 14:49    [19093982]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Bag09
Member

Откуда: Москва
Сообщений: 68
Добавлю: поле M - целое
22 апр 16, 14:50    [19093986]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

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

declare @t table (rn int identity primary key, K int)
insert @t values (8), (7), (5)
declare @N int = 16

select *, ps * @N / s - ((ps - K) * @N / s) M
from @t t cross join (select sum(K) s from @t) s
	cross apply (select SUM(K) ps from @t tt where tt.rn <= t.rn) a
22 апр 16, 14:53    [19094013]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Bag09
Member

Откуда: Москва
Сообщений: 68
AlanDenton,

Спасибо, за ответ.
Но при значениях

K M
3
3
4

N=8, S=10

Получим ответ

K M
3 2
3 2
4 3

Итого 7
22 апр 16, 15:01    [19094055]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21055
Bag09
так не годится, т.к. при округлении можно потерять единицы

Вы их в любом случае потеряете, работая в целых типах. Но потеряете гарантированно меньше единиц, чем количество элементов.
Задача решается в два этапа. Первый Вы уже выполнили. А на втором надо подсчитать получившуюся разницу между полученным и требуемым результатами, и скорректировать на единицу в нужном направлении требуемое количество значений. Корректировать нужно, вероятно, те элементы, дробные части точных значений которых (без округления) максимально близки к 0,5.
Или плюнуть на такие тонкости и корректировать, например, максимальные по значению элементы.
22 апр 16, 15:15    [19094152]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
Bag09
Member

Откуда: Москва
Сообщений: 68
iljy,

у меня MS SQL 2000, Ругается на cross apply, но в целом идея понятна
22 апр 16, 15:25    [19094249]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
задача, в любом случае огонь, давайте размажем 3 по 9 элементам не потеряя мммм 3 :)
22 апр 16, 15:25    [19094250]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
TaPaK
задача, в любом случае огонь, давайте размажем 3 по 9 элементам не потеряя мммм 3 :)


ну кстати задача довольно обычная, пропорционально пересчитать всякие затраты-убытки-комплектации с сохранением общего количества.
22 апр 16, 15:30    [19094288]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
Bag09
iljy,

у меня MS SQL 2000, Ругается на cross apply, но в целом идея понятна


Замените кореллированным подзапросом. Но вообще там можно поймать серьезные проблемы с производительностью. Погуглите по RunningTotal, эту тему обсасывали 100500 раз.
22 апр 16, 15:41    [19094382]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
OUTER APPLY
Guest
iljy
Bag09
iljy,

у меня MS SQL 2000, Ругается на cross apply, но в целом идея понятна


Замените кореллированным подзапросом. Но вообще там можно поймать серьезные проблемы с производительностью. Погуглите по RunningTotal, эту тему обсасывали 100500 раз.

iljy у меня вопросик к Вам Безусловно наростающий итог быстрее всего подсчитывать в версиях >=2012 Об этом неоднократно упоминается в технич литературе У меня вопрос по поводу OUTER APPLY и производительности - не могли бы подсказать где об этом говорится
Спасибо!
22 апр 16, 16:11    [19094628]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
OUTER APPLY
iljy у меня вопросик к Вам Безусловно наростающий итог быстрее всего подсчитывать в версиях >=2012 Об этом неоднократно упоминается в технич литературе У меня вопрос по поводу OUTER APPLY и производительности - не могли бы подсказать где об этом говорится
Спасибо!


В гугле ;) самая первая ссылка.
22 апр 16, 16:20    [19094697]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
OUTER APPLY
Guest
Спасибо
22 апр 16, 16:33    [19094786]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iljy
TaPaK
задача, в любом случае огонь, давайте размажем 3 по 9 элементам не потеряя мммм 3 :)


ну кстати задача довольно обычная, пропорционально пересчитать всякие затраты-убытки-комплектации с сохранением общего количества.

в целых числах
22 апр 16, 16:42    [19094847]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
TaPaK
в целых числах


Ну да :) всего каждый месяц заказываем 100 баранов, 20 у одного поставщика, 50 - у другого, еще 30 - у третьего. И надо в следующем месяце заказать 130, с сохранением соотношение.
22 апр 16, 17:14    [19095087]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iljy
TaPaK
в целых числах


Ну да :) всего каждый месяц заказываем 100 баранов, 20 у одного поставщика, 50 - у другого, еще 30 - у третьего. И надо в следующем месяце заказать 130, с сохранением соотношение.

и как всегда одного барана отдаёте топовому поставщику поди :)
22 апр 16, 17:17    [19095107]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

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

ну дальше возможны варианты :)
22 апр 16, 17:18    [19095116]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iljy,

кстати, а как заказать 3 барана у 9-ти поставщиков по справедливости :)
22 апр 16, 17:23    [19095150]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
TaPaK
iljy,

кстати, а как заказать 3 барана у 9-ти поставщиков по справедливости :)


Справедливость вещь абстрактная :) технически же запрос все считает.
22 апр 16, 18:09    [19095341]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iljy
TaPaK
iljy,

кстати, а как заказать 3 барана у 9-ти поставщиков по справедливости :)


Справедливость вещь абстрактная :) технически же запрос все считает.

подставьте нули в свой вариант :)
22 апр 16, 20:45    [19095853]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
TaPaK
подставьте нули в свой вариант :)


Не, ну так уж не надо придираться, это просто пример реализации. Понятно, что деление на 0 надо проверять на боевом. Но делается это легко

select *, ps * @N / s - ((ps - K) * @N / s) M
from (
	select K, nullif(s,0) s, (select SUM(K) ps from @t tt where tt.rn <= t.rn) ps
	from @t t cross join (select sum(K) s from @t) s
)t
22 апр 16, 21:37    [19096084]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить