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

Откуда:
Сообщений: 1526
Есть ParentChild - иерархия которая представляет собой распределение товаров по разным областям/городам.
При этом показатель count_tovar может быть указан и для не листового элемента иерархии

DECLARE @T TABLE (id int, parentid int, name varchar(100), isleaf bit, count_tovar numeric(10,5))
INSERT INTO @T
SELECT 1,NULL,'Россия',0,10 UNION ALL
SELECT 2,1,'Московская область',0,0 UNION ALL
SELECT 3,1,'Читинская область',0,5 UNION ALL
SELECT 4,2,'Москва',0,100 UNION ALL
SELECT 5,2,'Химки',1,50 UNION ALL
SELECT 6,3,'Чита',1,20 UNION ALL
SELECT 9,1,'Приморский край',1,1 UNION ALL
SELECT 10,4, 'СЗАО',1,0 UNION ALL
SELECT 11,4, 'ЦАО',1,0


Цель - получить список всех листовых элементов иерархии с показателем X который получается как распределение показателя count_tovar по дочерним элементам.

Пример:
У России указано три области - значит показатель count_tovar на уровне России мы делим на 3, получаем около 3.3 на каждую область. Далее этот получившийся показатель 3.3 мы складываем с count_tovar для каждой области. Например для Читинской области этот показатель будет равен 3.3 + 5 = 8.3. У Читинской области один город, значит для города Читы итог будет равен 8.3 / 1 + показатель count_tovar для Читы в итоге = 8.3 + 20.
Надеюсь понятно обьяснил...

Я так понял что нужно делать накопительный итог по иерархии только вместо SUM использовать AVG, но пока пытаюсь сделать через CTE и ничего не получается...
Пишу так, но суммы явно не те...

	WITH Counts AS
	(
		SELECT ParentId, COUNT(*) as CNT
		FROM @T
		WHERE id <> @ParentID
		GROUP BY ParentId
	),
	Sums AS
	(

		SELECT * FROM
		(
		SELECT t.id,t.parentid, t.name, CAST(count_tovar / ISNULL(r.CNT,1) AS numeric(10,5)) as X
		FROM @T t 
		INNER JOIN
		Counts r ON t.id = r.ParentId
		) a

		UNION ALL
		SELECT t.id,t.name,t.isleaf, sums.X FROM @T AS t JOIN Sums ON t.parentid = Sums.id
	)
	
	select * from Sums
13 дек 16, 20:01    [19999360]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Santa89
Member

Откуда:
Сообщений: 1526
Переменная @ParentId это айдишник самого верхнего уровня иерархии не имеющего родителя, т.е. Россия, запрос был немного вырван из контекста..
13 дек 16, 20:26    [19999429]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Santa89
Member

Откуда:
Сообщений: 1526
Неужели никто с подобным не сталкивался?
14 дек 16, 11:21    [20001337]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
Santa89
Я так понял что нужно делать накопительный итог по иерархии только вместо SUM использовать AVG, но пока пытаюсь сделать через CTE и ничего не получается...
Нужно посчитать количество товаров, и количество областей, а потом поделить.

Типа того:
DECLARE @T TABLE (id int, parentid int, name nvarchar(100), isleaf bit, count_tovar numeric(10,5))
INSERT INTO @T
SELECT 1,NULL,N'Россия',0,10 UNION ALL
SELECT 2,1,N'Московская область',0,0 UNION ALL
SELECT 3,1,N'Читинская область',0,5 UNION ALL
SELECT 4,2,N'Москва',0,100 UNION ALL
SELECT 5,2,N'Химки',1,50 UNION ALL
SELECT 6,3,N'Чита',1,20 UNION ALL
SELECT 9,1,N'Приморский край',1,1 UNION ALL
SELECT 10,4, N'СЗАО',1,0 UNION ALL
SELECT 11,4, N'ЦАО',1,0

;WITH Counts(id, parentid, name, count_tovar, Path) AS
	(
		SELECT id, parentid, name, count_tovar, convert(varchar(8000), id) as Path
		FROM @T
		WHERE parentid is null
		UNION ALL
		SELECT t.id, t.parentid, t.name, t.count_tovar, convert(varchar(8000), c.Path + '\' + convert(varchar, t.id))
		FROM @T AS t 
			JOIN Counts c ON c.id = t.parentid
	)
select id, parentid, name, count_tovar, (select sum(s.count_tovar) / count(*) from Counts s where s.Path like c.Path + '%')
from Counts c
14 дек 16, 11:56    [20001565]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
alexeyvg
Типа того:
Можно так, что бы нагляднее:
DECLARE @T TABLE (id int, parentid int, name nvarchar(100), isleaf bit, count_tovar numeric(10,5))
INSERT INTO @T
SELECT 1,NULL,N'Россия',0,10 UNION ALL
SELECT 2,1,N'Московская область',0,0 UNION ALL
SELECT 3,1,N'Читинская область',0,5 UNION ALL
SELECT 4,2,N'Москва',0,100 UNION ALL
SELECT 5,2,N'Химки',1,50 UNION ALL
SELECT 6,3,N'Чита',1,20 UNION ALL
SELECT 9,1,N'Приморский край',1,1 UNION ALL
SELECT 10,4, N'СЗАО',1,0 UNION ALL
SELECT 11,4, N'ЦАО',1,0

;WITH Counts(id, parentid, name, count_tovar, Path) AS
	(
		SELECT id, parentid, name, count_tovar, convert(varchar(8000), id) as Path
		FROM @T
		WHERE parentid is null
		UNION ALL
		SELECT t.id, t.parentid, t.name, t.count_tovar, convert(varchar(8000), c.Path + '\' + convert(varchar, t.id))
		FROM @T AS t 
			JOIN Counts c ON c.id = t.parentid
	)
select c.id, c.parentid, c.name, c.count_tovar, sum(s.count_tovar) as count_tovar_all, count(*) as count_all, sum(s.count_tovar) / count(*) as count_tovar_avg
from Counts c
	join Counts s on s.Path like c.Path + '%'
group by c.id, c.parentid, c.name, c.count_tovar
14 дек 16, 12:01    [20001601]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
Так, если рассматривать алгоритм ТС, то у меня получился такой вот черновик:
DECLARE @T TABLE (id int, parentid int, name varchar(100), isleaf bit, count_tovar numeric(10,5))
INSERT INTO @T
SELECT 1,NULL,'Россия',0,10 UNION ALL
SELECT 2,1,'Московская область',0,0 UNION ALL
SELECT 3,1,'Читинская область',0,5 UNION ALL
SELECT 4,2,'Москва',0,100 UNION ALL
SELECT 5,2,'Химки',1,50 UNION ALL
SELECT 6,3,'Чита',1,20 UNION ALL
SELECT 9,1,'Приморский край',1,1 UNION ALL
SELECT 10,4, 'СЗАО',1,0 UNION ALL
SELECT 11,4, 'ЦАО',1,0

;WITH [CountsCTE]
AS
(
    SELECT ParentId AS id
          ,COUNT(*) AS [CNT]
      FROM @T
      GROUP BY ParentId
)
,[CTE]
AS
(
SELECT T.id
      ,T.ParentId
      ,T.name
      ,cast(T.count_tovar as float) as count_tovar
      ,[CountsCTE].[CNT]
  FROM @T T
       LEFT JOIN [CountsCTE] ON T.id = [CountsCTE].id
)
,[SumsCTE]
AS
(
    SELECT id
          ,ParentId
          ,Name
          ,CAST([CTE].count_tovar / ISNULL([CTE].CNT,1) AS float) as X
          ,[CTE].CNT
      FROM [CTE]
     WHERE [CTE].[ParentId] IS NULL
     UNION ALL
    SELECT [CTE].id
          ,[CTE].ParentId
          ,[CTE].Name
          ,CAST((CAST([CTE].count_tovar as float) + cast([SumsCTE].X as float)) / ISNULL([CTE].CNT,1) AS float) as X
          ,[CTE].CNT
       FROM [CTE]
            JOIN [SumsCTE] ON [CTE].[ParentId] = [SumsCTE].[Id]
)
SELECT id
      ,name
      ,CAST(X AS numeric(10,5)) AS X
  FROM [SumsCTE]
 WHERE CNT IS NULL


На оптимальность решения не претендую, вылизывать идею у меня нет времени. Наверняка это можно переписать так, чтобы работало шустрее. Но общую идею я думаю ТС уловить сможет.

P.S. Во float перегонял потому, что у меня скуль ругался при попытке сложения numeric в рекурсивном CTE.
14 дек 16, 14:11    [20002410]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Santa89
Member

Откуда:
Сообщений: 1526
Огромное спасибо, сейчас буду копать!
14 дек 16, 15:16    [20002909]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
Sybex
P.S. Во float перегонял потому, что у меня скуль ругался при попытке сложения numeric в рекурсивном CTE.
Нужно приводить к одному типу.
14 дек 16, 20:58    [20004418]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
alexeyvg
Sybex
P.S. Во float перегонял потому, что у меня скуль ругался при попытке сложения numeric в рекурсивном CTE.
Нужно приводить к одному типу.


Я вот только не могу понять, почему они разного типа. Вот пример двух рекурсивных CTE, различающихся только тем, что в первом используется столбец типа float, а во втором numeric.
+
declare @T table(id int, parentid int, val1 float, val2 numeric(10,3))

insert @T
values (1, null, 10, 10)
      ,(2, 1, 5, 5)
      ,(3, 1, 3, 3)
      ,(4, 2, 4, 4)
      ,(5, 2, 3, 3)
      ,(6, 2, 5, 5)
      ,(7, 3, 5, 5)
      ,(8, 5, 6, 6)
      ,(9, 5, 10, 10)

;with cte
as
(
    select id
          ,parentid
          ,val1
          ,val1 as sums
      from @T
     where id = 1
     union all
    select T.id
          ,T.parentid
          ,T.val1
          ,T.val1 + cte.sums
      from @T T
           join cte on t.parentid = cte.id
)
select *
  from cte

;with cte
as
(
    select id
          ,parentid
          ,val2
          ,val2 as sums
      from @T
     where id = 1
     union all
    select T.id
          ,T.parentid
          ,T.val2
          ,T.val2 + cte.sums
      from @T T
           join cte on t.parentid = cte.id
)
select *
  from cte

Первое выполняется без проблем, а второе вываливается в ошибку:
Msg 240, Level 16, State 1, Line 34
Types don't match between the anchor and the recursive part in column "sums" of recursive query "cte".
15 дек 16, 10:49    [20005845]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
TaPaK
Member

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

потому что в первой части получат numeric(10,3) а при сложении хочет уже numeric(11,3) вот и не влазит, ну как я это понимаю :)
15 дек 16, 11:07    [20005996]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Sybex
Member

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

потому что в первой части получат numeric(10,3) а при сложении хочет уже numeric(11,3) вот и не влазит, ну как я это понимаю :)


Хм, и правда, если заменить в объявлении таблицы numeric(10,3) на numeric(38,3), то второй запрос так же отрабатывает без проблем.
15 дек 16, 11:18    [20006082]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
как-то так:
DECLARE @T TABLE (id int, parentid int, name nvarchar(100), isleaf bit, count_tovar numeric(10,5))
INSERT INTO @T
SELECT 1,NULL,N'Россия',0,10 UNION ALL
SELECT 2,1,N'Московская область',0,0 UNION ALL
SELECT 3,1,N'Читинская область',0,5 UNION ALL
SELECT 4,2,N'Москва',0,100 UNION ALL
SELECT 5,2,N'Химки',1,50 UNION ALL
SELECT 6,3,N'Чита',1,20 UNION ALL
SELECT 9,1,N'Приморский край',1,1 UNION ALL
SELECT 10,4, N'СЗАО',1,0 UNION ALL
SELECT 11,4, N'ЦАО',1,0;
with p as(
  select id,name,isleaf,count_tovar parent_tovarov
  from @t
  where parentid is null
  union all
  select t.id,t.name,t.isleaf,
    cast(t.count_tovar+p.parent_tovarov/count(*)over(order by(select 1)) as numeric(10,5))
  from @t t join p on t.parentid=p.id
)
select *
from p
--where isleaf=1
;
15 дек 16, 13:17    [20006772]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Santa89
Member

Откуда:
Сообщений: 1526
andrey odegov, на order ругается
19 дек 16, 14:23    [20022002]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 119
Santa89
andrey odegov, на order ругается

А это потому, что Вы не указали в своём посте версию SQL Server! Будете выполнять на 2012 и выше, не будет ругаться.
19 дек 16, 14:39    [20022163]     Ответить | Цитировать Сообщить модератору
 Re: Накопительный итог по иерархии  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Santa89, попробуйте так
count(*)over()
https://msdn.microsoft.com/ru-ru/library/ms189461(v=sql.90).aspx
19 дек 16, 21:49    [20024719]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить