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

Откуда: из-за границы
Сообщений: 2113
SQL 2008

классическая иерархия Product_ID => Parent_ID

для подсчёта всех потомков идёт СТЕ

;
WITH ChildrenCTE AS (
  SELECT top 100 percent RootID = Product_ID, Product_ID,Parent_ID
  FROM    Product_Tree
  WHERE Parent_ID=0
  order by logicOrder          
  UNION ALL
  SELECT top 100 percent cte.RootID, d.Product_ID,d.Parent_ID
  FROM    ChildrenCTE cte
          INNER JOIN Product_Tree d ON d.Parent_ID = cte.Product_ID
 order by d.logicOrder          
)
SELECT  d.RowId, d.Product_ID, d.Parent_ID, ISNULL(cnt.Children,0)
FROM Product_Tree d
        LEFT OUTER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.Product_ID
    order by d.Product_ID



собственно вопрос
как посчитать потомков для каждого элемента только на самом нижнем уровне ?
тех у кого нет детей?
спасибо
5 июл 15, 13:41    [17853757]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
michael R
SQL 2008

классическая иерархия Product_ID => Parent_ID

для подсчёта всех потомков идёт СТЕ

;
WITH ChildrenCTE AS (
  SELECT top 100 percent RootID = Product_ID, Product_ID,Parent_ID
  FROM    Product_Tree
  WHERE Parent_ID=0
  order by logicOrder          
  UNION ALL
  SELECT top 100 percent cte.RootID, d.Product_ID,d.Parent_ID
  FROM    ChildrenCTE cte
          INNER JOIN Product_Tree d ON d.Parent_ID = cte.Product_ID
 order by d.logicOrder          
)
SELECT  d.RowId, d.Product_ID, d.Parent_ID, ISNULL(cnt.Children,0)
FROM Product_Tree d
        LEFT OUTER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.Product_ID
    order by d.Product_ID



собственно вопрос
как посчитать потомков для каждого элемента только на самом нижнем уровне ?
тех у кого нет детей?
спасибо


Это два вопроса ?
На самом нижнем уровне - это где? по идее на нем потомков нет по определению,это же самый нижний.
5 июл 15, 13:47    [17853768]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
нее вопрос один
просто уточнение

примерное дерево
1
  2
    3
  4
  5
    6
    7
      8


Результат
ID  COUNT
1   4
2   1
3   0
4   0
5   2
6   0
7   1
8   0
5 июл 15, 14:25    [17853850]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
michael R
нее вопрос один
просто уточнение

примерное дерево
1
  2
    3
  4
  5
    6
    7
      8


Результат
ID  COUNT
1   4
2   1
3   0
4   0
5   2
6   0
7   1
8   0


как у 1 получается 4?
5 июл 15, 14:36    [17853867]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
1 - 3,4,6,8
5 июл 15, 14:42    [17853878]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
michael R
1 - 3,4,6,8


ага, задача звучит так: как посчитать количество листьев для указанного узла
5 июл 15, 15:26    [17853973]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
это все листья дерева традиционным способом

select * from tree where id not in (select distinct parent_id from tree where parent_id is not null)


Фильтрони только для указанного парента
5 июл 15, 15:30    [17853978]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
нет не получается
declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values(1,  0)
insert into @TT(Product_ID,Parent_ID) values(2,  1)
insert into @TT(Product_ID,Parent_ID) values(3,  2)
insert into @TT(Product_ID,Parent_ID) values(4,  1)
insert into @TT(Product_ID,Parent_ID) values(5,  1)
insert into @TT(Product_ID,Parent_ID) values(6,  5)
insert into @TT(Product_ID,Parent_ID) values(7,  5)
insert into @TT(Product_ID,Parent_ID) values(8,  7);
;with cteRecurs(AncestorId,Product_ID,Parent_Id)
as
(   Select AncestorId=Product_ID,Product_ID,Parent_Id
    from @TT T
    union all
    Select cteRecurs.AncestorId,T.Product_ID,T.Parent_Id
      from cteRecurs,           @TT T
     where T.Parent_Id = cteRecurs.Product_ID 
 )
 select cc.AncestorId, count(*)-1
	from cteRecurs cc
	group by cc.AncestorId
OPTION  (MAXRECURSION 0) ;


результат
1	7
2	1
3	0
4	0
5	3
6	0
7	1
8	0


добавляю ограничение из примера выше
показываются последние листы (3,4,6,8)
а нужно что бы были все с их количествами конечных листов
5 июл 15, 17:56    [17854250]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
michael R,

Здесь предполагается, что число уровней не более 8. НО если нужно произвольное, то вместо колонок l0-l8 можно формировать строку /1/2/3, ... А в выделенном красным цветом месте вызывать костыль, парсящий /1/2/3 в виде одноколоночной таблицы целых чисел. Можешь также доработать таблицу добавив вычисляемые триггером колонки l0-lN или одну колонку __path...

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
r as
(
	select
		0 as lvl,
		id as l0,
		null as l1,
		null as l2,
		null as l3,
		null as l4,
		null as l5,
		null as l6,
		null as l7,
		null as l8,
		id,
		id_p,
		iif(id_p = 0, 1, 0) as is_root
	from 
		data d1
	where
		not exists(select * from data d2 where d1.id = d2.id_p)

	union all

	select
		r.lvl + 1 as lvl,
		r.l0 as l0,
		iif(lvl + 1 = 1, d.id, r.l1) as l1,
		iif(lvl + 1 = 2, d.id, r.l2) as l2,
		iif(lvl + 1 = 3, d.id, r.l3) as l3,
		iif(lvl + 1 = 4, d.id, r.l4) as l4,
		iif(lvl + 1 = 5, d.id, r.l5) as l5,
		iif(lvl + 1 = 6, d.id, r.l6) as l6,
		iif(lvl + 1 = 7, d.id, r.l7) as l7,
		iif(lvl + 1 = 8, d.id, r.l8) as l8,
		d.id,
		d.id_p,
		iif(d.id_p = 0, 1, 0) as is_root
	from
		data d
			inner join 
		r on d.id = r.id_p
)
select
	t.id, sum(iif(r.l0 <> t.id, 1, 0))cnt
from 
	r
		cross apply
	(values (l0), (l1), (l2), (l3), (l4), (l5), (l6), (l7), (l8)) as t(id)
where
	t.id is not null
	and r.is_root = 1
group by
	t.id


id          cnt
----------- -----------
1 4
2 1
3 0
4 0
5 2
6 0
7 1
8 0

Алгоритм такой - строим пути от листьев к корням. Потом пути сливаем в кучу. Сколько раз id встретился в полученном, столько у него и листьев.
5 июл 15, 20:11    [17854600]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Тоже самое, для случая, когда максимально возможный уровень вложенности не известен.

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as (select row_number() over(order by (select 0)) from l4 t1, l4 t2),
r as
(
	select
		0 as lvl,
		cast(id as varbinary(max)) as p,
		id,
		id_p,
		iif(id_p = 0, 1, 0) as is_root
	from 
		data d1
	where
		not exists(select * from data d2 where d1.id = d2.id_p)

	union all

	select
		r.lvl + 1 as lvl,
		p + cast(d.id as varbinary(max)) as p,
		d.id,
		d.id_p,
		iif(d.id_p = 0, 1, 0) as is_root
	from
		data d
			inner join 
		r on d.id = r.id_p
)
select
	t.id, sum(iif(t.id_c <> t.id, 1, 0)) as cnt
from 
	r
		cross apply
	(
		select top(datalength(r.p) / 4)
			convert(int, cast(substring(r.p, 4 * (n - 1) + 1, 4) as varbinary(4))) as id,
			convert(int, cast(left(r.p, 4) as varbinary(4))) as id_c
		from
			rt
	) as t(id, id_c)
where
	t.id is not null
	and r.is_root = 1
group by
	t.id
5 июл 15, 20:55    [17854739]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
churupaha, а зачем так сложно?

Вот же:

with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
), cte as
(
	select d1.id, d1.id as id_p 
	from data d1
		left  join data d2
			on d1.id = d2.id_p
	where d2.id is null
	union all
	select cte.id, d1.id_p
	from	cte 
		inner join data d1
			on cte.id_p = d1.id
 )
 select id_p, count(case when id = id_p then null else id end) from cte
 where id_p != 0
 group by id_p
 order by id_p
6 июл 15, 10:08    [17855994]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
with data(id, id_p) as
(
	select 1, 0 union all
	select 2, 1 union all
	select 3, 2 union all
	select 4, 1 union all
	select 5, 1 union all
	select 6, 5 union all
	select 7, 5 union all
	select 8, 7
),
s as
(
 select
  d.id, d.id_p, d.id as id_l, 1 as is_leaf
 from
  data d
 where
  not exists(select 1 from data where id_p = d.id)

 union all

 select
  d.id, d.id_p, s.id_l, 0
 from
  s join
  data d on d.id = s.id_p
)
select
 id, count(distinct case when is_leaf = 0 then id_l end)
from
 s
group by
 id;
6 июл 15, 10:10    [17856004]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
хм вроде работает последние

а если у меня возникает ситуация что связка ребёнок-родитель не уникальна ?
то есть она может появляться как потомок другой ветки?
6 июл 15, 10:25    [17856064]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
michael R
хм вроде работает последние

а если у меня возникает ситуация что связка ребёнок-родитель не уникальна ?
то есть она может появляться как потомок другой ветки?
В таком случае это никакая не иерархия.
Это не дерево, а граф. А узлы имеют не одну ссылку на родителя, а много?
Или связи хранятся в отдельно таблице? Иначе непонятно, как это возможно.
Кроме всего прочего появятся циклы.
Значит, надо будет следить за прохождением узлов не более одного раза по одному и тому же пути.
6 июл 15, 10:36    [17856110]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
эээ
это пока вариант
такое тоже возможно с множественным вхождением
там скорее всего связь должна будет представлена уже через номер строки

ну в любом случае спасибо
пока вроде всё с этим.....
6 июл 15, 10:42    [17856137]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Minamoto
churupaha, а зачем так сложно?

Вот же:


чета протупил
6 июл 15, 13:24    [17857078]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
churupaha
michael R,

Здесь предполагается, что число уровней не более 8. НО если нужно произвольное, то вместо колонок l0-l8 можно формировать строку /1/2/3, ...


а смысл? можно тогда заюзить hierarchyid и не морочить себе голову со строками.
6 июл 15, 13:46    [17857204]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Winnipuh
churupaha
michael R,

Здесь предполагается, что число уровней не более 8. НО если нужно произвольное, то вместо колонок l0-l8 можно формировать строку /1/2/3, ...


а смысл? можно тогда заюзить hierarchyid и не морочить себе голову со строками.


да и в этом тоже смысла нету. ибо вон 17856004
6 июл 15, 14:22    [17857450]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
и мои 5 копеек:)
declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values
  (1,  0),(2,  1),(3,  2),(4,  1),(5,  1),(6,  5),(7,  5),(8,  7);

;with rec as (
	select t.product_id, null middle, t.product_id nxt
    from @TT t
  union all
  select r.product_id, r.nxt, t.product_id
    from rec r
    join @TT t on t.Parent_id=r.nxt
  )
select product_id, count(distinct middle)qty
from rec 
group by product_id
6 июл 15, 15:06    [17857710]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
michael R
хм вроде работает последние

а если у меня возникает ситуация что связка ребёнок-родитель не уникальна ?
то есть она может появляться как потомок другой ветки?
Поправка:
declare @TT table (product_id int,Parent_id int)
insert into @TT(Product_ID,Parent_ID) values
  (1,  0),(2,  1),(3,  2),(4,  1),(5,  1),(6,  5),(7,  5),(8,  7), (7, 4);

;with rec as (
	select t.product_id, null middle, t.product_id nxt
    from @TT t
  union all
  select r.product_id, isnull(nullif(r.nxt,r.product_id),t.product_id), t.product_id
    from rec r
    join @TT t on t.Parent_id=r.nxt
  )
select product_id, count(distinct middle)qty from rec group by product_id
6 июл 15, 15:16    [17857769]     Ответить | Цитировать Сообщить модератору
 Re: классическая иерархия! отец-ребёнок подсчёт кол-ва детей только на последнем уровне  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
не, извиняюсь, соврамши(
6 июл 15, 15:22    [17857815]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить