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

Откуда: Прага
Сообщений: 776
Добрый день.

Новый год к нам мчится, а с ним и планы.

Есть таблица
 create table  #t (AE float, ClientID int null, Area varchar(50) not null, Division Varchar(50) not null)


В ней лежат планы на top 10 клиентов и планы на "остаток по району".

Планы по клиентам розделены по дивизиям, остаток лежит в фиктивной дивизии (2 BU, 999 и 9999).

Нужно - разделить "остаток по району" на дивизии (с учётом BU) так, чтобы общаяя сумма не изменилась. 8/

Пробовал в эксцеле через пивот по формуле

AE=AEorig+AEorig/(sum(area)-sum(Остаток))*sum(Остаток BU)

Однако получается коррекция контрольной суммы. Она на порядок ниже, чем остаток, порядка 1%. А хотелось бы, чтобы сумма сходилась. Сижу, третий час ломаю голову, как разделить "остаток без остатка"?
30 ноя 17, 12:17    [20994911]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Добрый Э - Эх
Guest
Шыфл,

вообще не понятно - что есть, что нужно получить....
приведи репрезентативный юзабильный набор тестовых данных и желаемый вид результата на них....
30 ноя 17, 12:50    [20995037]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
В терминах SQL я пробовал что-то типа

;with a as 
(
	select 
		SUM(cast(AE as float)) ae, 
		Area, 
		division 
	from #t
	where division not in ('9999','999')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aDF as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area,
		division 
	from #t
	where division not in ('9999','999','P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aLP as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area,
		division 
	from #t
	where division in ('P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,b as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area 
	from #t
	where division in ('9999','999')
	group by Area
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)	
, tmp as 
(
	select 
		a.Area,
		a.division, 
		a.ae,
		a.ae + a.ae/(k.ae-isnull(k1.ae,0)-isnull(k2.ae,0))*ISNULL(k1.ae,k2.ae) ae0
	from a
	outer apply (select ae from b where Area=a.Area) k
	outer apply (select ae from aDF where Area=a.Area and division=a.division) k1
	outer apply (select ae from aLP where Area=a.Area and division=a.division) k2
)
select * from tmp
30 ноя 17, 12:50    [20995038]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Мужчина, хватит утомлять людей лапшекодом.
Сделайте нормальный воспроизводимый пример, и внятно опишите чего хотите.
30 ноя 17, 12:55    [20995057]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
похоже, он делит 1 на троих.
каждому выходит по 0,(3)
ну и когда он обратно складывает эти 0,3333333333 + 0,3333333333 + 0,3333333333, то не получает 1
30 ноя 17, 13:00    [20995073]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
вообще флоаты складывать вещь неблагодарная.
особенно если план параллельный.
2 разных запуска дадут 2 разные суммы.
30 ноя 17, 13:03    [20995088]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Да, забыл добаватить
--должно сходиться 

select sum(AE) from #t
--и 
select sum(ae) from tmp
30 ноя 17, 13:04    [20995091]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Yasha123,

Не, не дадут округления ошибку в 1% на 2500К суммы.
30 ноя 17, 13:06    [20995100]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

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


Я тебе говорю есть план, есть остаток. Нужно разделить остаток в структуре плана. Нужна формула, код я и сам писать умею.
30 ноя 17, 13:08    [20995105]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл,

а какая формула бывает, как уже писали 1 бутылка на 3х?
30 ноя 17, 13:12    [20995120]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Шыфл
код я и сам писать умею.

Судя по стилю и неймингам лапши сверху, это вопрос более чем обсуждаемый...
30 ноя 17, 13:22    [20995162]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK
Шыфл,
а какая формула бывает, как уже писали 1 бутылка на 3х?

Это ж водка! Нужно очень аккуратно!
автор
Судя по стилю и неймингам лапши сверху, это вопрос более чем обсуждаемый...

А что не так с неймингом? Там всё написанно на коленке за 3 минуты:
+ Это суммы без остатков

a as 
(
	select 
		SUM(cast(AE as float)) ae, 
		Area, 
		division 
	from #t
	where division not in ('9999','999')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)


+ Это суммы BU 1 и 2 без остатка

aDF as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area,
		division 
	from #t
	where division not in ('9999','999','P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
,aLP as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area,
		division 
	from #t
	where division in ('P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
)


+ Это остаток

,b as 
(
	select 
		SUM(cast(AE as float)) ae,
		Area 
	from #t
	where division in ('9999','999')
	group by Area
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)


+ а это, собственно, формула

 tmp as 
(
	select 
		a.Area,
		a.division, 
		a.ae,
		a.ae + a.ae/(k.ae-isnull(k1.ae,0)-isnull(k2.ae,0))*ISNULL(k1.ae,k2.ae) ae0
	from a
	outer apply (select ae from b where Area=a.Area) k
	outer apply (select ae from aDF where Area=a.Area and division=a.division) k1
	outer apply (select ae from aLP where Area=a.Area and division=a.division) k2
)

30 ноя 17, 13:59    [20995331]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
aleks222
Guest
Все элементарно, Ватсон!

Для одного (последнего например) поправка вычисляется как остаток от остатка.
Т.е. вычитанием из остатка, всего, что нараспределяли.
30 ноя 17, 14:50    [20995607]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
aleks222
Все элементарно, Ватсон!

Для одного (последнего например) поправка вычисляется как остаток от остатка.
Т.е. вычитанием из остатка, всего, что нараспределяли.

или и иногда, погрешность или повторно распределяется, или тому у кого самый большой остаток
30 ноя 17, 14:53    [20995619]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
aleks222,

Холмс, я решил проблему модным метдом лоботомии. Модифицировал формулу и подобрал коэфициент, при котором нераспределённый остаток меньше 1.

Коэфициент оказался подозрительно близок к 2, что наводит на определённые мысли

+ пока лучшего варианта нет, будем планировать так

declare @coef float=2.085012042

;with a as 
(
	select SUM(cast(AE as float)) ae, Area, division from #t
	where division not in ('9999','999')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aDF as 
(
	select SUM(cast(AE as float)) ae, Area,division from #t
	where division not in ('9999','999','P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aLP as 
(
	select SUM(cast(AE as float)) ae, Area, division from #t
	where division in ('P310','P360','U292')
	group by Area,division
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,b as 
(
	select SUM(cast(AE as float)) ae, Area from #t
	where division in ('9999','999')
	group by Area
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,c as 
(
	select SUM(cast(AE as float)) ae, Area from #t
	where division not in ('9999','999')
	group by Area
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)	
, tmp as 
(
	select a.Area,a.division, a.ae, isnull(a.ae + a.ae/(k0.ae-@coef*k.ae)*ISNULL(k1.ae,k2.ae),a.ae) ae0
	from a
	outer apply (select ae from c where Area=a.Area) k0
	outer apply (select ae from b where Area=a.Area) k
	outer apply (select ae from aDF where Area=a.Area and division=a.division ) k1
	outer apply (select ae from aLP where Area=a.Area and division=a.division ) k2
)
select *,a.ae-tmp.ae0 dif from a
left join tmp on a.Area=tmp.Area and a.division=tmp.division

30 ноя 17, 15:05    [20995673]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Обычная бытовая магия
IF OBJECT_ID( 'tempdb..#s' ) IS NOT NULL DROP TABLE #s
IF OBJECT_ID( 'tempdb..#d' ) IS NOT NULL DROP TABLE #d
GO
CREATE TABLE #s ( [area] VARCHAR(1), [sum] FLOAT )
INSERT INTO #s VALUES ( 'A', 1000.10 )
INSERT INTO #s VALUES ( 'B', 700.50 )
INSERT INTO #s VALUES ( 'C', 1 )
CREATE TABLE #d ( [area] VARCHAR(1), [division] VARCHAR(2) )
INSERT INTO #d VALUES 
  ( 'A', 'A1' ),
  ( 'A', 'A2' ),
  ( 'A', 'A3' ),
  ( 'A', 'A4' ),
  ( 'B', 'B1' ),
  ( 'B', 'B2' ),
  ( 'B', 'B3' ),
  ( 'C', 'C1' ),
  ( 'C', 'C2' )
;
WITH
m AS (
  SELECT
    d.[area],
    d.[division],
    s.[sum],
    [count] = COUNT(*) OVER ( PARTITION BY d.[area] ),
    [rn] = ROW_NUMBER() OVER ( PARTITION BY d.[area] ORDER BY 1/ 0 )
  FROM
    #s s
    INNER JOIN #d d ON (
          d.[area] = s.[area] )
)
SELECT
  [area],
  [division],
  [sum],
  [rn],
  [count],
  [plan] = FLOOR( [sum] / [count] ) + CASE WHEN [rn] = 1 THEN [sum] - FLOOR( [sum] / [count] ) * [count] ELSE 0 END
FROM
  m
30 ноя 17, 15:07    [20995685]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Руслан Дамирович
Обычная бытовая магия

Что-то не понял идеи. Что есть остаток, который мы делим?


Ватсон
Холмс, я решил проблему модным метдом лоботомии. Модифицировал формулу и подобрал коэфициент, при котором нераспределённый остаток меньше 1.


Это не верный подход, ибо планы сильно диформируются и часто уходят в глубокий минус :( Можно, конечно, клиентам доплачивать, но не сотни миллионов :/

Пока остаток снизился с 400М до 20М, это неплохо, это можно уже просто прибавить к самому большому... Копаем дальше
30 ноя 17, 15:58    [20995875]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Холмс
Ватсон
Холмс, я решил проблему модным метдом лоботомии. Модифицировал формулу и подобрал коэфициент, при котором нераспределённый остаток меньше 1.


Это не верный подход, ибо планы сильно диформируются и часто уходят в глубокий минус :( Можно, конечно, клиентам доплачивать, но не сотни миллионов :/


Коллега, вы правы - для начала нужно исправить ошибки в коде - такая деформация явно противоречит логике. Если исправить ошибки, всё становится на свои места :)
+ так победим

declare @coef1 float=1.0011
declare @coef2 float=1.242897361
;with a as 
(
	select SUM(cast(AE as float)) ae, bezirk, ID from #t
	where id not in ('9999','1003')
	group by Bezirk,ID
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aDF as 
(
	select SUM(cast(AE as float)) ae, bezirk,id from #t
	where id not in ('9999','1003','P310','P360','U292')
	group by Bezirk,id
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,aLP as 
(
	select SUM(cast(AE as float)) ae, bezirk, id from #t
	where id in ('P310','P360','U292')
	group by Bezirk,ID
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,b as 
(
	select SUM(cast(AE as float)) ae, Bezirk from #t
	where id in ('9999','1003')
	group by Bezirk
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)
,c as 
(
	select SUM(cast(AE as float)) ae, bezirk from #t
	where id not in ('9999','1003')
	group by Bezirk
	having ABS(ISNULL(SUM(cast(AE as float)),0))>1
)	
, tmp as 
(
	select a.bezirk,a.id, a.ae, 
	case when a.ae<0 then a.ae else isnull(a.ae + a.ae/(k0.ae*@coef2)*ISNULL(k1.ae,k2.ae),a.ae)*@coef1 end ae0
	, k0.ae k0, k.ae k, k1.ae k1, k2.ae k2
	
	from a
	outer apply (select ae from c where bezirk=a.bezirk) k0
	outer apply (select ae from b where bezirk=a.bezirk) k
	outer apply (select ae from aDF where bezirk=a.bezirk and ID=a.id) k1
	outer apply (select ae from aLP where bezirk=a.bezirk and ID=a.id) k2
)


select 
(select SUM(ae) from #t)-(select SUM(ae0) from tmp),

*,a.ae-tmp.ae0 dif 
from a
left join tmp on 
a.bezirk=tmp.bezirk and a.id=tmp.id

30 ноя 17, 17:22    [20996182]     Ответить | Цитировать Сообщить модератору
 Re: План Б - разделение остатка без коррекций  [new]
лолл
Member

Откуда:
Сообщений: 450
Шыфл,

была в свое время похожая задача, с которой, наверное, в бухгалтерии сталкиваются многие: заданы разные суммы, полученные в результате распределения некой общей суммы на множество записей с последующим округлением этих сумм до копеек. После этого нужно определить разницу, между распределенной суммы и величиной, полученной суммированием отдельных распределений, а далее еще понять, в каких записях провести корректировки. Где-то корректируют любую запись, где-то наибольшую (по модулю), а где-то методом "честного округления" - ту запись, сумма которой наиболее близка до округления в большую или меньшую сторону, в зависимости от знака корректировки.
30 ноя 17, 18:23    [20996317]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить