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

Откуда:
Сообщений: 23
Уважаемые прошу помощи в написании запроса.
Нужно решить туже самую задачу как здесь, только на tsql.
Подсчёт кол-ва элементов в текущей группе и всех подгруппах. Корневых элементов может быть несколько, для них parent_id=NULL.

Мой нерабочий запрос :(
;WITH result_table as (
	   select gg.group_id, g.parent_id, count(*) as cnt, count(*) as summ from group2good gg
	   join good_groups g on g.group_id=gg.group_id
	   WHERE g.parent_id is null
	   GROUP BY gg.group_id, g.parent_id
			UNION ALL
	   select gg.group_id, g.parent_id, count(*) as cnt, 
				(select sum(cnt) from 
				 (select count(*) as cnt from group2good gg1
				 join good_groups g1 on g1.group_id=gg1.group_id
				 WHERE g.parent_id = gg1.group_id) s) + count(*) as summ 
	   from group2good gg
	   join good_groups g on g.group_id=gg.group_id, result_table
	   WHERE result_table.parent_id = gg.group_id
	   GROUP BY gg.group_id, g.parent_id
)
select group_id, parent_id, summ from result_table rt1
6 авг 15, 12:10    [17983092]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
MS_t_SQL, а пример данных?
6 авг 15, 12:14    [17983120]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
Maxx_UA
Guest
MS_t_SQL,

ну вопервых - читаем хелп...
во вторых смотри мна то,что написали и смотрим на разницу с хелпом
в третьих исчем по форуму слово рекурсивный сте
6 авг 15, 12:17    [17983139]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
MS_t_SQL
Member

Откуда:
Сообщений: 23
CREATE TABLE [dbo].[group_sum](
	[group_id] [int] NOT NULL,
	[parent_id] [int] NULL, --=group_id родителя, для корня NULL
	[cnt] [int] NULL -- кол-во элементов в группе для простоты примем что в каждой группе 10 эл.
)
GO
/*
Лес подгрупп:
1 - 2 - 3
  - 7
  - 6
4 - 5
  - 8  
*/  
insert into group_sum  (group_id, parent_id, cnt)
select 1, NULL, 10
union all
select 4, NULL, 10
union all
select 2, 1, 10
union all
select 6, 1, 10
union all
select 7, 1, 10
union all
select 5, 4, 10
union all
select 8, 4, 10
union all
select 3, 2, 10
GO

--drop table group_sum
/*
В итоге, нужно получить сумму элементов в группах + подгруппах с учётом вложенности для каждой группы (влож-ть любая):
id cnt
1 50
2 20
3 10
4 30
5 10
6 10
7 10
8 10
*/
7 авг 15, 15:43    [17989826]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
with s as
(
 select
  group_id, cnt, group_id as group_id_root
 from
  dbo.group_sum

 union all

 select
  t.group_id, t.cnt, s.group_id_root
 from
  s join
  dbo.group_sum t on t.parent_id = s.group_id
)
select
 group_id_root, sum(cnt)
from
 s
group by
 group_id_root;
7 авг 15, 16:05    [17989993]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
MS_t_SQL
Member

Откуда:
Сообщений: 23
invm, низкий Вам поклон. Как просто оказалось!
7 авг 15, 21:49    [17991521]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
mini.weblab
Member

Откуда:
Сообщений: 1109
MS_t_SQL,
мне понравилось решение с использованием UDF
BEGINNING Microsoft SQL Server 2012 Programming, Paul Atkinson, Robert Vieira
стр. 501
Задача о поиске всех подчиненных Карлы Хантингтон
11 авг 15, 22:50    [18005731]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
mini.weblab
мне понравилось решение с использованием UDF
BEGINNING Microsoft SQL Server 2012 Programming, Paul Atkinson, Robert Vieira
стр. 501
И выглядит сиё чудо вот так:
CREATE FUNCTION dbo.fnGetReports
       (@EmployeeID AS int)
       RETURNS @Reports TABLE
       (
       EmployeeID    int         NOT NULL,
       ManagerID   int         NULL
       )
AS
BEGIN

/* Since we'll need to call this function recursively - that is once for each 
** reporting employee (to make sure that they don't have reports of their
** own), we need a holding variable to keep track of which employee we're
** currently working on. */
DECLARE @Employee AS int;

/* This inserts the current employee into our working table. The significance
** here is that we need the first record as something of a primer due to the
** recursive nature of the function - this is how we get it. */
INSERT INTO @Reports
   SELECT EmployeeID, ManagerID 
   FROM HumanResources.Employee2 
   WHERE EmployeeID = @EmployeeID;

/* Now we also need a primer for the recursive calls we're getting ready to
** start making to this function. This would probably be better done with a
** cursor, but we haven't gotten to that chapter yet, so.... */
SELECT @Employee = MIN(EmployeeID) 
FROM HumanResources.Employee2
WHERE ManagerID = @EmployeeID;

/* This next part would probably be better done with a cursor but we haven't
** gotten to that chapter yet, so we'll fake it. Notice the recursive call
** to our function! */
WHILE @Employee IS NOT NULL
   BEGIN
      INSERT INTO @Reports
           SELECT * 
           FROM fnGetReports(@Employee);

           SELECT @Employee = MIN(EmployeeID) 
           FROM HumanResources.Employee2 
           WHERE EmployeeID > @Employee
              AND ManagerID = @EmployeeID;
   END

RETURN;

END
GO

Замечательный пример как не нужно писать на T-SQL.
12 авг 15, 00:24    [18006039]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
mini.weblab
Member

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

ну это учебный пример, чтобы человек мог все попробовать :)

а можете прокомментировать основные слабые места?
(спасибо)
12 авг 15, 23:22    [18010798]     Ответить | Цитировать Сообщить модератору
 Re: Запрос = Рекурсия + parent + подсчёт подгрупп  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
mini.weblab
ну это учебный пример
Кому нужен пример, который не учит ничему хорошему?

mini.weblab
можете прокомментировать основные слабые места?
Первое и основное - неверный выбор способа решения задачи. Результат достигается одним запросом без циклов и табличных переменных:
CREATE FUNCTION dbo.fnGetReports
       (@EmployeeID AS int)
RETURNS TABLE
AS
RETURN (
 WITH t AS
 (
   SELECT EmployeeID, ManagerID 
   FROM HumanResources.Employee2
   WHERE EmployeeID = @EmployeeID

   UNION ALL

   SELECT EmployeeID, ManagerID 
   FROM t JOIN
    HumanResources.Employee2 e ON ManagerID = t.EmployeeID
 )
 SELECT EmployeeID, ManagerID FROM t
);

Далее: многооператорных табличных функций следует избегать. Хотя бы потому, что манипуляции с результирующей табличной переменной журналируются в журнале транзакций tempdb.
Вот вам пример с рекурсивной многооператорной функцией, - можете сами посмотреть, что происходит:
+
use tempdb;
go

create function dbo.fn1(@v int)
returns @r table (v int)
as
begin
 insert into @r values (@v);
 
 if @v > 0
  insert into @r select v from dbo.fn1(@v - 1);

 return;
end;
go

begin tran;

declare @tid nvarchar(30), @lsn nvarchar(30) = (select max([Current LSN]) from tempdb.sys.fn_dblog(null, null));

select * from dbo.fn1(3);

create table #t (tid nvarchar(30));

insert into #t
select [Transaction ID] from sys.fn_dblog(null, null) where [Current LSN] > @lsn and SPID = @@spid and [Transaction Name] = N'TVQuery';
select l.* from #t t join sys.fn_dblog(null, null) l on l.[Transaction ID] = t.tid;

rollback;
go

drop function dbo.fn1;
go
13 авг 15, 00:58    [18010975]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить