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

Откуда: СФО
Сообщений: 1269
Помогите пожалуйста решить задачку. Имею таблицу:
iNodeElementID iElementID_Root iElementID_Parent iElementID decNodeElementCnt iElem_ix iNode_ix
77779 6026 7778 1227 12 122700 777700
77777 6026 7777 1227 10 122700 777700
61059 6026 6146 1227 5 122700 614600
61043 6026 6145 1227 15 122700 614600

Необходимо в каждой строке вывести сумму по столбцу decNodeElementCnt, но по хитрому алгоритму. Не каждая строка и не для каждого случая должна попасть в эту сумму.
Вообще суммирование производится с группировкой по полям iElementID_Root, iElementID. Дополнительно имеет значение поле iNode_ix. Суммированию подлежат строки в которых действительно выражение
iNode_ix / 100 = iElementID_Parent
.
В случае если это выражение не действительно, то конкретно для этой строки сумма должна быть равна значению в данной строке плюс сумма значений для тех строк где это выражение действительно, исключая значения с таким же iNode_ix
Т.е. на выходе должна получится следующая таблица:

К сообщению приложен файл. Размер - 24Kb
22 ноя 13, 06:44    [15171843]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Изерлонер
В случае если это выражение не действительно, то конкретно для этой строки сумма должна быть равна значению в данной строке плюс сумма значений для тех строк где это выражение действительно, исключая значения с таким же iNode_ix


Немного не корректно, в таблице могут быть и другие строки с повторяющимися значениями iElementID_Root, iElementID, в которых равенство iNode_ix /100 = iElementID_Parent не соблюдается, но iNode_ix такой же, поправлюсь:
Конкретно для этой строки сумма должна быть равна сумме значений с группировкой по iElementID_Root, iElementID, iNode_ix плюс сумма значений для тех строк где это выражение действительно, исключая значения с таким же iNode_ix
22 ноя 13, 06:52    [15171849]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
[quot Изерлонер] Как-то так:
iNodeElementID iElementID_Root iElementID_Parent iElementID decNodeElementCnt iElem_ix iNode_ix decRes
98499 6026 7779 1227 3 122700 777700 8
88885 6026 7778 1227 12 122700 777700 29
77779 6026 7778 1227 12 122700 777700 29
77777 6026 7777 1227 10 122700 777700 15
61059 6026 6146 1227 5 122700 614600 15
61043 6026 6145 1227 15 122700 614600 25
22 ноя 13, 06:58    [15171853]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Изерлонер,

прочтите про аналитические функции.
про группировку и скользящее окно в них.
мне это видится примерно так:


select
*

case when sum(...) over(partition by....) <>=! .....
from
22 ноя 13, 07:38    [15171892]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Jaffar
Member

Откуда:
Сообщений: 633
как-то так
select *,
case when col_3 / 100 = col_4 then sum(col_5) over(partition by col_1, col_2) 
else col_5 + sum(case when col_3 / 100 = col_4 then col_5 else 0 end) over(partition by col_1, col_2) end
from table_1
22 ноя 13, 07:47    [15171906]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
У меня какая-то ерунда на выходе получается. Пробую еще, но получается все условия в оконной функции должны быть как-то . На всякий случай сделал скрипт примера:
USE [tstDB]
GO

/****** Object:  Table [dbo].[tblNE]    Script Date: 11/22/2013 13:34:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblNE](
	[iNodeElementID] [int] NOT NULL,
	[iElementID_Root] [int] NULL,
	[iElementID_Parent] [int] NULL,
	[iElementID] [int] NULL,
	[decNodeElementCnt] [decimal](8, 3) NULL,
	[inEL] [int] NULL,
	[inND] [int] NULL,
 CONSTRAINT [PK_tblNE] PRIMARY KEY CLUSTERED 
(
	[iNodeElementID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into tblNE values (61043,6026,6145,1227,15.000,122700,614600)
insert into tblNE values (61059,6026,6146,1227,5.000,122700,614600)
insert into tblNE values (77777,6026,7777,1227,10.000,122700,777700)
insert into tblNE values (77779,6026,7778,1227,12.000,122700,777700)
insert into tblNE values (88885,6026,7778,1227,12.000,122700,777700)
insert into tblNE values (98499,6026,7779,1227,3.000,122700,777700)

Go
22 ноя 13, 09:57    [15172317]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Фу-ф, кажется получилось. Есть сомнения, но на данной выборке данных сработало:
Select iNodeElementID, iElementID_Root, iElementID_Parent, iElementID, decNodeElementCnt, inEl, inND,
         Case
         When inND / 100 = iElementID_Parent
         Then 
              (Select SUM(decNodeElementCnt) From tblNE
               Where inND/100 = iElementID_Parent
               Group by iElementID_Root, iElementID)
         When inND / 100 <> iElementID_Parent
         Then 
               (Select SUM (decNodeElementCnt) From tblNE
               Where inND/100 <> iElementID_Parent And iElementID_Parent = NE2.iElementID_Parent
               GROUP by iElementID_Root, iElementID, inND) +
               (Select SUM (decNodeelementCnt) From tblNE
               Where inND/100 = iElementID_Parent And inND <> NE2.inND  
          GROUP by iElementID_Root, iElementID, inND)         
         Else 0
         End 
From tblNE NE2
22 ноя 13, 11:44    [15172924]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
что-то в условии не то.
вот это -- iNode_ix /100 = iElementID_Parent -- выполняется только для двух строк:
77777 6026 7777 1227 10.000 122700 777700
61059 6026 6146 1227 5.000 122700 614600

как получить тот ответ, что на картинке?
откуда 8 в первой строке, если строки с decNodeElementCnt 3 и 5 имеют разные iNode_ix?
22 ноя 13, 13:34    [15173814]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
Начал писать ответ, написал целую простыню с подробным описаним таблицы и задачи. ... В конце концов не выдержав моего занудства комп ушел в перезагрузку...
И я подумал, может оно Вам и не нужно, такое подробное описание.
Если все же интересно, то начало попыток разработать таблицу нормативов с взаимозаменяемыми деталями и узлами здесь.
И данная задачка как раз для решения последних моих вопросов в той теме.
Две строки которые Вы указали это базовые сборочные единицы из которых по умолчанию состоит агрегат. Они могут быть заменены на взаимозаменяемые на которых установлено другое количество (номенклатура) деталей. Расчет количества деталей на все изделие зависит от комплектации и производится по разному для базовой комплектации и для узлов заменяющих базовые. Эту задачу и решает данный запрос.
По этой выборке данных все правильно.
22 ноя 13, 15:59    [15175285]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
o-o
откуда 8 в первой строке, если строки с decNodeElementCnt 3 и 5 имеют разные iNode_ix?

iNode_ix (inND) индекс взаимозаменяемости ... в данном случае сборочных единиц. Детали сборочных единиц с одинаковым индексом и не могут суммироваться (нельзя одновременно поставить на изделие и базовый узел и его заменяющий). А вот другой узел, не взаимозаменяемый с данным и с другим iNode_ix вообще может находится в другой части изделия, это совершенно отдельный узел и ставятся они на изделие вместе. Может содержать (в данном случае содержит ту же самую деталь iElementID = 1227). А у меня и стоит задача посчитать общее количество данных деталей (каждой номенклатуры) на всем изделии. Эту задачу и решаю этим запрос ом.
Тут тоже все правильно. Детали из взаимозаменяемого узла 3 шт суммировались с деталями другого узла (но в базовой комплектации, по умолчанию считается что комплектация базовая, и был заменен на взаимозаменяемый только один узел, все возможные конфигурации и комплектации считать не возможно, да и не нужно.) 5 шт.
На деле этих узлов могут быть десятки.

...Сложно все... но предметная область такая. Башку сломать можно. ... Либо рог по центру нарастить.
22 ноя 13, 16:10    [15175392]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
у меня что-то получилось,
но с Вашей картинкой расхождение:
declare @tblNE table (
	[iNodeElementID] [int] NOT NULL,
	[iElementID_Root] [int] NULL,
	[iElementID_Parent] [int] NULL,
	[iElementID] [int] NULL,
	[decNodeElementCnt] [decimal](8, 3) NULL,
	[inEL] [int] NULL,
	[inND] [int] NULL)
	
insert into @tblNE values (98499,6026,7779,1227,3.000,122700,777700)
insert into @tblNE values (88885,6026,7778,1227,12.000,122700,777700)
insert into @tblNE values (77779,6026,7778,1227,12.000,122700,777700)
insert into @tblNE values (77777,6026,7777,1227,10.000,122700,777700)
insert into @tblNE values (61059,6026,6146,1227,5.000,122700,614600)
insert into @tblNE values (61043,6026,6145,1227,15.000,122700,614600)

select *, decNodeElementCnt + a.sum_
from @tblNE t1 outer apply (select SUM(decNodeElementCnt) as sum_ 
                            from @tblNE t2 
                            where t1.inND <> t2.inND
                                  and inND/100 = iElementID_Parent)a
order by 1 desc
----------------------------------------------------
iNodeElementID	iElementID_Root	iElementID_Parent	iElementID	decNodeElementCnt	inEL	inND	sum_	(No column name)
98499	6026	7779	1227	3.000	122700	777700	5.000	8.000
88885	6026	7778	1227	12.000	122700	777700	5.000	17.000
77779	6026	7778	1227	12.000	122700	777700	5.000	17.000
77777	6026	7777	1227	10.000	122700	777700	5.000	15.000
61059	6026	6146	1227	5.000	122700	614600	10.000	15.000
61043	6026	6145	1227	15.000	122700	614600	10.000	25.000

почему Вы прибавляете строку с 12, хотя в ней не выполняется условие inND/100 = iElementID_Parent?
22 ноя 13, 17:46    [15176105]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
ну и для "группировки по iElementID_Root, iElementID"
надо 2 условия добавить.

select t1.*, decNodeElementCnt + isnull(a.sum_, 0) as sum_
from @tblNE t1 outer apply (select SUM(t2.decNodeElementCnt) as sum_ 
                            from @tblNE t2 
                            where t1.inND <> t2.inND
                                  and t2.inND/100 = t2.iElementID_Parent
                                  and t1.iElementID_Root = t2.iElementID_Root
                                  and t1.iElementID = t2.iElementID)a
order by 1 desc
22 ноя 13, 18:06    [15176215]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
o-o
почему Вы прибавляете строку с 12, хотя в ней не выполняется условие inND/100 = iElementID_Parent?

У вас код как-то сильно по проще получается. У меня же динозавр какой-то.
... Прибавляю эту строку потому что она в пределах одного узла находится.

В этой выборке данных фактически есть два узла считающихся базовыми и по умолчанию устанавливаемых на изделие, это узлы с iElementID_Parent 6146 и 7777.
Узел 6146 можно заменить на узел 6145 (что и показано одинаковым индексом взаимозаменяемости inND)
Узел 7777 можно заменить на 7778 либо 7779

В узле 7778 деталь 1227 присутствует в двух местах в количестве 12 шт и там и там. (узел в свою очередь может быть разбит на подузлы с деталями, а те могут биться и на свои сборочные единицы, но по большому счету уровней не более 5... хотя кто его знает).

Считаем количество деталей 1227 для базовой комплектации изделия. В базовой комплектации два узла 6146 и 7777. Суммарное количество деталей у них 5+10 = 15. Напротив каждой строки узлов базовой комплектации ставим 15.

Считаем количество деталей 1227 для комплектации в которой узел 6146 был заменен на 6145. Количество деталей 1227 для этого узла 15 шт, все остальные узлы беруться из базовой комплектации, кроме того узла который был заменен, в данном случае это узел 7777 с количеством деталей 10 шт. 15+10 = 25. Проставляем 25 в строке узла 6145.

Считаем количество детелей 1227 для комплектации в которой узел 7777 был заменен на 7778. У узла 7778 детали 1227 имеются в двух местах в количестве 12 шт. Все остальные узлы, кроме заменяемого, беруться из базовой комплектации, это узел 6146.
Суммарное количество деталей 12 + 12 + 5 = 29.

Ну и для узла 7779 по той же логике получается 3 + 5 = 8.
23 ноя 13, 07:17    [15178207]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
o-o,

Исходя из Вашего примера:

Select T1.*, Sum(decNodeElementCnt) over (partition by iElementID_Parent)  + ISNULL(a.sum_, 0) as sum_
From tblNE T1 outer apply (Select SUM(T2.decNodeElementCnt) as sum_
                           From tblNE T2
                           Where T1.inND  <> T2.inND 
                                 And T2.inND/100 = T2.iElementID_Parent
                                 And T1.iElementID_Root = T2.iElementID_Root 
                                 And T1.iElementID = T2.iElementID) a


Однако функция Outer Apply для меня новая, не совсем понимаю как она действует. Чтение msdn пока не слишком помогло (тяжело воспринимаю формализованные тексты). И я не понимаю зачем Вы добавили
 And T1.iElementID_Root = T2.iElementID_Root 
 And T1.iElementID = T2.iElementID

если и без этого результат тот же. Но это наверно опять исходя из не понимания Outer Applay.
23 ноя 13, 14:50    [15178799]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
А возможно как-то используя стандартные инструменты SQL Server проанализировать оптимальность выполнения запросов, в каких-то относительных единицах что-ли... Ну по типу как это сделано при решении задач на сайте sql-ex.ru. Или для этого может быть есть какой-то отдельный инструмент, подскажите пожалуйста.

Хочется проверить эффективность запросов:
Select iNodeElementID, iElementID_Root, iElementID_Parent, iElementID, decNodeElementCnt, inEl, inND,
         Case
         When inND / 100 = iElementID_Parent
         Then 
              (Select SUM(decNodeElementCnt) From tblNE
               Where inND/100 = iElementID_Parent
               Group by iElementID_Root, iElementID)
         When inND / 100 <> iElementID_Parent
         Then 
               (Select SUM (decNodeElementCnt) From tblNE
               Where inND/100 <> iElementID_Parent And iElementID_Parent = NE2.iElementID_Parent
               GROUP by iElementID_Root, iElementID, inND) +
               (Select SUM (decNodeelementCnt) From tblNE
               Where inND/100 = iElementID_Parent And inND <> NE2.inND  
          GROUP by iElementID_Root, iElementID, inND)         
         Else 0
         End 
From tblNE NE2


против

Select T1.*, Sum(decNodeElementCnt) over (partition by iElementID_Parent)  + ISNULL(a.sum_, 0) as sum_
From tblNE T1 outer apply (Select SUM(T2.decNodeElementCnt) as sum_
                           From tblNE T2
                           Where T1.inND  <> T2.inND 
                                 And T2.inND/100 = T2.iElementID_Parent
                                 And T1.iElementID_Root = T2.iElementID_Root 
                                 And T1.iElementID = T2.iElementID) a


Пытался анализировать план выполнения, но там просто операции и сколько занимает каждая из них относительно выполнения всего запроса в целом. (или не там смотрю)
23 ноя 13, 15:00    [15178810]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
короче.
у меня не сходилось потому, что кто-то в коде использует вот такое условие:
And iElementID_Parent = NE2.iElementID_Parent

а в словесном описании про iElementID_Parent-ов ни слова.
(именно по этому условию попала в сумму строка с 12, хоть и inND/100 <> iElementID_Parent)

я исхожу именно из словесного описания.
предлагаю вариант с учетом iElementID_Parent-ов,
но еще раз повторюсь: это не другая запись Вашего кода (в нем я запутываюсь и не могу гарантировать,
что мой делает ровно то же, что и ваш)

в общем, проверяйте.
select t1.*, a.sum_
from @tblNE t1 cross apply (select SUM(t2.decNodeElementCnt) as sum_ 
                            from @tblNE t2 
                            where ((t1.inND <> t2.inND and t2.inND/100 = t2.iElementID_Parent) or
                                  ( t1.iElementID_Parent = t2.iElementID_Parent))
                                  and t1.iElementID_Root = t2.iElementID_Root
                                  and t1.iElementID = t2.iElementID)a
order by 1 desc
-----------------------------------
98499	6026	7779	1227	3.000 	122700	777700	8.000
88885	6026	7778	1227	12.000	122700	777700	29.000
77779	6026	7778	1227	12.000	122700	777700	29.000
77777	6026	7777	1227	10.000	122700	777700	15.000
61059	6026	6146	1227	5.000 	122700	614600	15.000
61043	6026	6145	1227	15.000	122700	614600	25.000
23 ноя 13, 15:20    [15178846]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
про почитайте в книге
Microsoft® SQL Server® 2012 T-SQL Fundamentals
ну или с тем же названием, но 2008.
книга с торрентов скачивается, либо переведенный 2008-ой можно в книжном найти.

только в последнем варианте уже и CROSS APPLY подойдет, потому что в сумму исходная строка попадет всегда,
раз добавили условие равенства Parent-ов.
-----------------------------------------------------------------------
для сравнения скорости надо засекать время выполнения или смотреть число чтений:
set statistics io on
set statistics time on

сравнение 2-ух планов, даже актуальных, относительно batch, в % может привирать.
не помню пример, но он тут был, дал его Гость333.
ну и сравнивать надо не на таблице в 6 строк, разумеется
-----------------------------------------------------------------------
в плане видно (хотя и без тоже видно, в данном случае),
что у меня 2 скана таблицы, у вас 4.

К сообщению приложен файл. Размер - 53Kb
23 ноя 13, 15:48    [15178902]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
o-o
короче.
у меня не сходилось потому, что кто-то в коде использует вот такое условие:
And iElementID_Parent = NE2.iElementID_Parent


моя ошибка, изначально в словесное описание это не внес. Да и сам не сразу сообразил что должно быть такое условие.
23 ноя 13, 15:51    [15178912]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
o-o
Guest
пардон,
про почитайте в книге --> про APPLY почитайте в книге
23 ноя 13, 15:53    [15178916]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый запрос  [new]
Изерлонер
Member

Откуда: СФО
Сообщений: 1269
o-o,

Спасибо, Вы мне очень помогли.

Если я правильно понял то при наведении на первый оператор (Select) в всплывающем окошке в действительном плане выполнения как раз выдается "стоимость" всего запроса. Если это так, то разница между моим первым запросом, и Вашим вариантом с Cross Applay на порядок, что весьма не слабо и даст приличный прирост скорости выполнения.
23 ноя 13, 16:23    [15178971]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить