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

Откуда:
Сообщений: 40
Люди, поможите, третий день бьюсь.

declare @tab table(id char(20),parentid char(20),qty int,isfolder bit,idx char(20))

insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа1',20,0,'001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа1',10,0,'001002' union all
select 'Товар15','Группа1',20,0,'001002' union all
select 'Группа131','Группа13',40,1,'001002001' union all
select 'Товар132','Группа13',50,0,'001002001' union all
select 'Товар133','Группа13',60,0,'001002001'

select 2-grouping(idx)-grouping(id) grp,id,sum(qty) from @tab
group by idx,id with rollup
order by idx,grp


В каждой ветке дерева есть группа. Причем у группы тоже может быть количество
в запросе может не быть некоторых групп, если у элемента группы (не у ее потомках) qty=0

Результат:

0,NULL 230
1,NULL 0
2,Группа1 0
1,NULL 20
-- 2,Группа11 0

2,Товар12 20
1,NULL 60
2,Группа13 30
2,Товар14 10
2,Товар15 20
1,NULL 150
2,Группа131 40
2,Товар132 50
2,Товар133 60

А требуется:
Группа1 410 (20+240+150)
Группа11 20 (20)
Товар12 20
Группа13 240 (150+60+10+20)
Товар14 10
Товар15 20
Группа131 150 (40+50+60)
Товар132 50
Товар133 60
26 ноя 03, 20:28    [434908]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Тфу-ты. Не сортировка, а итог по веткам. Все, пора звонить 03 :-)
26 ноя 03, 20:31    [434909]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Dimitri KOH
Member

Откуда: London UK
Сообщений: 157
Poprobui CUBE i ROLLUP funkcii:

Summarizing Data Using CUBE
The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set containing a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, along with the aggregate values from the underlying rows that match that combination of dimension values.

For example, a simple table Inventory contains:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210

This query returns a result set that contains the Quantity subtotal for all possible combinations of Item and Color:

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Here is the result set:

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00

The following rows from the result set are of special interest:

Chair (null) 311.00

This row reports a subtotal for all rows having the value Chair in the Item dimension. The value NULL is returned for the Color dimension to show that aggregate reported by the row includes rows with any value of the Color dimension.

Table (null) 347.00

This row is similar, but reports the subtotal for all rows having Table in the Item dimension.

(null) (null) 658.00

This row reports the grand total for the cube. Both the Item and Color dimensions have the value NULL showing that all values of both dimensions are summarized in the row.

(null) Blue 225.00
(null) Red 433.00

These two rows report the subtotals for the Color dimension. Both have NULL in the Item dimension to show that the aggregate data came from rows having any value for the Item dimension.

Using GROUPING to Distinguish Null Values
The null values generated by the CUBE operation present a problem: How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This is achieved using the GROUPING function. The GROUPING function returns 0, if the column value came from the fact data, and 1 if the column value is a NULL generated by the CUBE operation. In a CUBE operation, a generated NULL represents all values. The SELECT statement can be written to use the GROUPING function to substitute the string ALL in place of any generated NULL. Because a NULL from the fact data indicates the data value is unknown, the SELECT can also be coded to return the string UNKNOWN in place of any NULL from the fact data. For example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Multidimensional Cubes
The CUBE operator can be used to generate n-dimensional cubes, or cubes with any number of dimensions. A single dimension cube can be used to generate a total, for example:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO

This SELECT statement returns a result set showing both the subtotals for each value of Item and the grand total for all values of Item:

Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00

SELECT statements that contain a CUBE with many dimensions can generate large result sets, because these statements generate rows for all combinations of the values in all the dimensions. These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement into a view:

CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

The view can then be used to query only the dimension values of interest:

SELECT *
FROM InvCube
WHERE Item = 'Chair'
AND Color = 'ALL'

Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00

(1 row(s) affected)


See Also

SELECT

Summarizing Data Using ROLLUP
26 ноя 03, 20:42    [434918]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Не, такое не прокатит.
Уточню данные:

declare @tab table(id char(20),parentid char(20),qty int,isfolder bit,idx char(20))
insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002' union all
select 'Товар15','Группа13',20,0,'001002' union all
select 'Группа131','Группа13',40,1,'001002001' union all
select 'Товар132','Группа131',50,0,'001002001' union all
select 'Товар133','Группа131',60,0,'001002001'

select 2-grouping(idx)-grouping(id) grp,id,sum(qty) from @tab
group by idx,id with rollup
order by idx,grp

Т.е. это классическое рекурсивное дерево. По нему уже построен индекс idx.
Нужно, чтобы в суммирующих строках стояло не NULL, а группа данной ветки. Причем в выборке группа может отсутствовать, если у нее количество=0. Т.е. будут присутствовать только элементы, входящие в эту группу, с qty>0.
Оговорюсь, что приведенный запрос сильно упрощен.

ROLLUP и CUBE не подходят, потому что они не обрабатывают деревья, и сумма вышестоящего узла не будет включать в себя сумму потомков. Причем CUBE совсем не подходит.
26 ноя 03, 21:03    [434937]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
>Причем в выборке группа может отсутствовать, если у нее количество=0.

Но в конечном результате такая группа все равно должна присутствовать.
26 ноя 03, 21:06    [434940]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Логика совершенно не ясна. Вот как я понял то, что Вам надо (результат отличается от Вашего и я ниже поясню почему)

declare @tab table(id char(20),
parentid char(20),
qty int,
isfolder bit,
idx varchar(20) -- замена на varchar!

)
insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002' union all
select 'Товар15','Группа13',20,0,'001002' union all
select 'Группа131','Группа13',40,1,'001002001' union all
select 'Товар132','Группа131',50,0,'001002001' union all
select 'Товар133','Группа131',60,0,'001002001'

select t1.id, (select sum(t2.qty)
from @tab t2
where t2.idx like t1.idx + '%'
and isfolder=0) as qty
from @tab t1
order by idx

Суммируется только количество товара. В Вашем результате на самом верхнем уровне Группа 131 (кол-во 10) считается 2 раза.
27 ноя 03, 07:35    [435177]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Подумав..
declare @tab table(id char(20),parentid char(20),qty int,isfolder bit,idx varchar(20))

insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002' union all
select 'Товар15','Группа13',20,0,'001002' union all
select 'Группа131','Группа13',40,1,'001002001' union all
select 'Товар132','Группа131',50,0,'001002001' union all
select 'Товар133','Группа131',60,0,'001002001'

select t1.id, case t1.isfolder when 1 then
isnull((select sum(t2.qty)
from @tab t2
where (
(
t2.idx like t1.idx + '%'
and DATALENGTH(t2.idx) > DATALENGTH(t1.idx)
) or t2.parentid = t1.id
)
and t2.isfolder = 0),0) when 0 then t1.qty end as qty
from @tab t1
order by idx
27 ноя 03, 09:04    [435257]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
2Павел Воронцов
Да, вы совершенно правильно поняли назначение индекса idx.
Но в предложенном вами запросе нет rollup.
Вобще-то запрос имеет фиксированную форму:
select 2-grouping(t.idx)-grouping(t.id) grp,t.id,sum(t.qty) from

(select id,parentid,qty,isfolder,idx
from @tab) t
group by idx,id with rollup
order by idx,grp

Причем, rollup во внешнем запросе обязателен (нужен для других целей).
Т.е. во внутреннем запросе идет выборка из таблиц, а во внешнием - вычисление функций. Это сделано для того, чтобы была воможность делать union во внутреннем запросе.

select id,sum(qty)
from (
select * from table1
union all
select * from table2) t
group by id with rollup

>Логика совершенно не ясна.
Надеюсь, я немного прояснил ситуацию.

Т.о. я ищу способ, чтобы вписать в эту конструкцию итоги по веткам дерева. По возможности, без вложенных запросов. Вот так вот хитро :-)
27 ноя 03, 09:26    [435296]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
З.Ы. Действительно, ошибся в арифметике:
А требуется:

Группа1 410 (20+240)
Группа11 20 (20)
Товар12 20
Группа13 240 (150+60+10+20)
Товар14 10
Товар15 20
Группа131 150 (40+50+60)
Товар132 50
Товар133 60
27 ноя 03, 09:36    [435321]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
Блин. Или я чего-то не понимаю или
А требуется:

Группа1 230 (20+210) -- было 410 ??!!

Группа11 20 (20)
Товар12 20
Группа13 210 (150+30+10+20) -- было 240

Товар14 10
Товар15 20
Группа131 150 (40+50+60)
Товар132 50
Товар133 60
27 ноя 03, 10:11    [435458]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Действительно, последний вариант счета правильный.
Главное, суть ясна. А арифметику пусть калькулятор считает.
Но мне уже стыдно :-) Каюсь. Похоже, еще не проснулся.

И все же, что по теме?
27 ноя 03, 10:26    [435494]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2386
Блог
по теме пока только одна мысль - idx у Вас неправильный. По хорошему надо бы
insert @tab

select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002001' union all
select 'Товар15','Группа13',20,0,'001002001' union all
select 'Группа131','Группа13',40,1,'001002001001' union all
select 'Товар132','Группа131',50,0,'001002001001001' union all
select 'Товар133','Группа131',60,0,'001002001001001'


Но как сей факт должен помочь в решении - не понимаю пока. Без подзапросов похоже никак, но я пока думаю
27 ноя 03, 10:49    [435566]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Вот и я уже 4 дня думаю. :-(
Но надежда на спасение меня не оставляет.
27 ноя 03, 10:51    [435572]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Ну что, неужели проблема не решается в принципе?

Хотя бы что-то типа
select something

from (select something
from @tab left outer join
@tab on ...
) t
27 ноя 03, 16:31    [436712]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
А индекс у меня такой, потому что он строится динамически, по циклу на уровень иерархии. Предложенный Павлом Воронцовым способ мне представляется проблематичным для реализации. Могу привести ХП.
27 ноя 03, 17:22    [436803]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Valentine
Member

Откуда:
Сообщений: 38
Я так вижу , что это 1с. Может для начала стоит развернуть дерево??
27 ноя 03, 18:16    [437068]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Попробую по другому сормулировать:

Есть о-о-очень большой запрос, содержащий в себе вложенный, тоже не маленький.
В общем виде выглядит примерно так:
select id,sum(qty)

from (select * from @tab) t
group by id with rollup

Причем во внутренних запросах НЕ используется group by.
Нужно сделать итоги по веткам дерева @tab, которую я уже приводил.
Вариант, который привел Павел Воронцов действительно работает, за это ему спасибо. Но для этого надо создавать врем. таблицу, что очень не хотелось бы. Проясню ситуацию - @tab - это результат многочисленных left, inner join-ов. Так что ее пришлось бы создавать заранее. Пока все делается в одном запросе.

Могу привести полный запрос, но сомневаюсь, что это что-либо прояснит.

Вероятно, нужно что-то типа CASE WHEN THEN SUM() END
27 ноя 03, 18:30    [437087]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
2Valentine
Развернуть дерево? Что вы имеете ввиду?
27 ноя 03, 18:31    [437090]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
P/S/ Вообще-то дерево уже развернуто, см. поле idx. Или как-то иначе?
27 ноя 03, 18:49    [437121]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
Не проблема узнать уровень:
declare @tab table(level int,id char(20),parentid char(20),qty int,isfolder bit,idx varchar(20))

insert @tab
select 1,'Группа1','',0,1,'001' union all
select 2,'Группа11','Группа1',0,1,'001001' union all
select 2,'Товар12','Группа11',20,0,'001001' union all
select 2,'Группа13','Группа1',30,1,'001002' union all
select 2,'Товар14','Группа13',10,0,'001002' union all
select 2,'Товар15','Группа13',20,0,'001002' union all
select 3,'Группа131','Группа13',40,1,'001002001' union all
select 3,'Товар132','Группа131',50,0,'001002001' union all
select 3,'Товар133','Группа131',60,0,'001002001'

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

А запрос действительно для 1С :-)
27 ноя 03, 19:21    [437163]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
remark
Member

Откуда:
Сообщений: 25
Типа вот так можно:

select t1.id, SUM(t2.qty)

from
@tab t1 inner join @tab t2 on (t2.idx like t1.idx+'%')
group by t1.id, t1.parentid
order by t1.id
28 ноя 03, 11:04    [437662]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
Valentine
Member

Откуда:
Сообщений: 38
Павел прав. Индекс должен так выглядеть:

insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001001' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002001' union all
select 'Товар15','Группа13',20,0,'001002001' union all
select 'Группа131','Группа13',40,1,'001002001001' union all
select 'Товар132','Группа131',50,0,'001002001001001' union all
select 'Товар133','Группа131',60,0,'001002001001001'


Кстати, если интересует- могу выслвть решение этого вопроса в 1С (самому недавно пришлось решать). Но пришлось использовать доп. таблицу, по-другому никак
28 ноя 03, 12:48    [437962]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
2Valentine
!!! Да! Да! Интересует!

2remark
Это очень похоже. Пока наилучший вариант. Спасибо!
28 ноя 03, 20:32    [438971]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
2Valentine
Особенно, интересно сравнить процедуры построения индекса.
29 ноя 03, 09:28    [439178]     Ответить | Цитировать Сообщить модератору
 Re: Профи, разминка! Сортировка по веткам дерева  [new]
userfromhell
Member

Откуда:
Сообщений: 40
А предложенный Павлом индекс избыточен. Для того, чтобы не пришлось упорядочивать по parentid, достаточно в конце для каждого элемента, не являющегося родителем, добавить 000. Причем такой вариант проще в реализации :-)
declare @tab table(id char(20),parentid char(20),qty int,isfolder bit,idx char(20))

insert @tab
select 'Группа1','',0,1,'001' union all
select 'Группа11','Группа1',0,1,'001001' union all
select 'Товар12','Группа11',20,0,'001001000' union all
select 'Группа13','Группа1',30,1,'001002' union all
select 'Товар14','Группа13',10,0,'001002000' union all
select 'Товар15','Группа13',20,0,'001002000' union all
select 'Группа131','Группа13',40,1,'001002001' union all
select 'Товар132','Группа131',50,0,'001002001000' union all
select 'Товар133','Группа131',60,0,'001002001000'
29 ноя 03, 10:33    [439193]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить