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

Откуда:
Сообщений: 141
Руслан Дамирович,
Во первых, спасибо за конструктивное предложение и за желание помочь.
Нет, тут никто не злой и все правы:) Только нервные немного. Ну да, никто не железный, и я тоже:)
Тяжело отвечать каждый раз новому гуру на те же упрёки :) Только одному всё объяснишь, следующий появляется.
Но это хорошо - мощный траффик гуру детектед.

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

3. Прекрасно понимаю что хочу. Думал достаточно.

4. Код готов ещё вчера и выдаёт именно тот результат что требовался.

5. В рамках проявления доброй воли и попытки выяснить для себя насчёт DISTINCT в OUTER APPLY чуть позже постараюсь запостить пример Input/Output + скриптик.
4 дек 17, 17:59    [21005178]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Cammomile
Ну ты же можешь упростить до вменяемого уровня!

Нафига нам проникать мозгом твои сложные структуры, когла концептуально вопрос о том, как юзать ссылки цте на цте и аплаи?


Прав совершенно. Учту на будущее.
Понял что на этом не стоит экономить время.
Спасибо.
4 дек 17, 18:01    [21005183]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
slovnet
4. Код готов ещё вчера и выдаёт именно тот результат что требовался.

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

А твой вопрос о том, что нужен нарастающий итог по заказу, а не по деталям заказа - вообще не имеет смысла с моей точки зрения. Потому и попросил исходные данные и результат, чтобы понять, что именно ты хотел этим сказать.
Так что ждем портянку
4 дек 17, 18:09    [21005200]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
По предложению уважаемых комрадов формулирую вопрос правильно:
Input:

OrdNo Date PartName OrdQty
Заказ1 01/02/18 Part1 5
Заказ2 01/01/18 Part1 8
Заказ3 01/05/18 Part1 3
Заказ4 01/01/17 Part2 10
Заказ5 01/01/19 Part2 6


OrdNo WorkOrder
Заказ1 WO1
Заказ1 WO2
Заказ2 WO3
Заказ2 WO4
Заказ3 WO5



PartName Balance
Part1 20
Part2 29


Надо выдать все заказы (OrdNo), Date, PartName, OrdQty, Balance с распределением остатков по заказам с сортировкой по дате + ЗаказНаПроизводство (WorkOrder) привязанный к заказу.
Соответсвенно заказы 4 и 5 выпадают, а заказы 1 и 2 выдают по две записи (поскольку на каждый есть по 2 WorkOrder)
Однако распределением остатков надо произвести на уровне заказа, без разбивки по WorkOrder.

Требуемый результат выборки:
OrdNo Date PartName OrdQty AllocatedOrdQty AllocatedBalance TotalBalance WorkOrder
Заказ2 01/01/18 Part1 8 8 20 20 WO3
Заказ2 01/01/18 Part1 8 8 20 20 WO3
Заказ1 01/02/18 Part1 5 13 12 20 WO1
Заказ1 01/02/18 Part1 5 13 12 20 WO2
Заказ3 01/05/18 Part1 3 16 7 20 WO5


Скрипт в следующем посте.
4 дек 17, 18:35    [21005246]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Руслан Дамирович,
Раздуплился как только попросили. Уже даю. Пару минут, надо выкинуть лишнее.
4 дек 17, 18:40    [21005257]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Ну почему не так то?!

DECLARE 
  @Orders TABLE (
  OrdNo varchar(20) 
, Date datetime
, PartName varchar(20) 
, OrdQty int) 

INSERT INTO @Orders VALUES 

('Заказ1',	'01/02/18',	'Part1',	5)
,('Заказ2',	'01/01/18',	'Part1',	8)
,('Заказ3',	'01/05/18',	'Part1',	3)
,('Заказ4',	'01/01/17',	'Part2',	10)
,('Заказ5',	'01/01/19',	'Part2',	6)
4 дек 17, 18:43    [21005268]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
[src][/SRC]
4 дек 17, 19:07    [21005321]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Либо я к концу дня деревянный, либо у тебя "желаемый результат" неправильно сделан, но насколько я понял текстовую постановку задачи, там вообще нет никаких СТЕ и аплаев.

DECLARE 
  @Orders TABLE (
  OrdNo varchar(20) 
, Date datetime
, PartName varchar(20) 
, OrdQty int) 

INSERT INTO @Orders VALUES 

('Заказ1',	'01/02/18',	'Part1',	5)
,('Заказ2',	'01/01/18',	'Part1',	8)
,('Заказ3',	'01/05/18',	'Part1',	3)
,('Заказ4',	'01/01/17',	'Part2',	10)
,('Заказ5',	'01/01/19',	'Part2',	6)

DECLARE @Balance TABLE (PartName varchar(20), Balance int ) 
INSERT INTO @Balance  VALUES 
('Part1',	20)
,('Part2', 29)

DECLARE @Work TABLE (OrdNo varchar(20), WorkOrder varchar(20) )
INSERT INTO @Work VALUES 

('Заказ1',	'WO1')
,('Заказ1',	'WO2')
,('Заказ2',	'WO3')
,('Заказ2',	'WO4')
,('Заказ3',	'WO5')



SELECT 
  O.OrdNo  
, O.Date  
, O.PartName 
, O.OrdQty  
, B.Balance
, PartsReserved =  SUM(O.OrdQty) OVER (PARTITION BY O.PartName ORDER BY O.Date ROWS   unbounded preceding )
, PartsRest = B.Balance - (SUM(O.OrdQty) OVER (PARTITION BY O.PartName ORDER BY O.Date ROWS   unbounded preceding ))
, W.WorkOrder
 FROM @Orders O 
INNER JOIN  @Balance B ON B.PartName = O.PartName
INNER JOIN @Work W ON W.OrdNo = O.OrdNo
 ORDER BY O.Date, O.OrdNo, W.WorkOrder 

 
4 дек 17, 19:08    [21005323]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Отдельно непонятно зачем тут вообще сортировка по WorkOrder когда за ним ничего не стоит.
4 дек 17, 19:10    [21005328]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Обещаный код.
Надеюсь что не накосячил, пришлось сильно упрощать структуру и названия таблиц/полей.
Не факт что исполнится как есть:)

WITH 
Orders
as
(
SELECT ORDERS.Ordname Ordname, PART.PartName PartName, ORDERS.OrdQuant OrdQuant, 
SERIAL.WorkOrder WorkOrder, ORDERS.Date Date
FROM ORDERS JOIN PART
ON ORDERS.PART = PART.PART
JOIN SERIAL  
ON (ORDERS.SERIAL = SERIAL.SERIAL AND ORDERS.PART = SERIAL.PART)
WHERE ORDERS.CLOSED <> 'C'
)
,
PartBalance
as
(
SELECT PART.PartName PartName, WARHSBAL.BALANCE Balance
FROM WARHSBAL JOIN PART
ON WARHSBAL.PART = PART.PART
WHERE WARHSBAL.BALANCE > 0
)
,
OrdsDistinctTmp as 
(
SELECT distinct Ordname, PartName, OrdQuant, Date FROM Orders
)
,
OrdsDistinct as 
(
SELECT distinct Ordname, PartName, OrdQuant, Date, 
(ROW_NUMBER() OVER (PARTITION BY PartName ORDER BY Date)) as rn
FROM OrdsDistinctTmp
)
SELECT 
	OrdsDistinct1.PartName, OrdsDistinct1.Date, OrdsDistinct1.Ordname, OrdsDistinct1.OrdQuant, AllocatedOrdQty,
	ISNULL(Balance, 0) as TotalBalance, (ISNULL(Balance, 0)-AllocatedOrdQty) as AllocatedBalance, Orders.WorkOrder
FROM
  OrdsDistinct OrdsDistinct1
  Full Outer Join Orders
	ON OrdsDistinct1.Ordname = Orders.Ordname
  Left Join PartBalance
  ON PartBalance.PartName = OrdsDistinct1.PartName
	OUTER APPLY (
     SELECT
       SUM(OrdQuant) as AllocatedOrdQty
     FROM
        OrdsDistinct OrdsDistinct2
     WHERE
       OrdsDistinct2.PartName = OrdsDistinct1.PartName
       AND OrdsDistinct2.rn <= OrdsDistinct1.rn         
       ) as alloc
       Order By OrdsDistinct1.PartName, OrdsDistinct1.Date   
4 дек 17, 19:16    [21005340]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Cammomile
Отдельно непонятно зачем тут вообще сортировка по WorkOrder когда за ним ничего не стоит.


Вроде про сортировку по WorkOrder нигде в условиях не упоминалось.
Но сам WorkOrder юзер хочет видеть.
4 дек 17, 19:19    [21005348]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Cammomile
Ну почему не так то?!

DECLARE 
  @Orders TABLE (
  OrdNo varchar(20) 
, Date datetime
, PartName varchar(20) 
, OrdQty int) 

INSERT INTO @Orders VALUES 

('Заказ1',	'01/02/18',	'Part1',	5)
,('Заказ2',	'01/01/18',	'Part1',	8)
,('Заказ3',	'01/05/18',	'Part1',	3)
,('Заказ4',	'01/01/17',	'Part2',	10)
,('Заказ5',	'01/01/19',	'Part2',	6)


Понял, ну, в следующий раз. Хотел как красивше.
4 дек 17, 19:21    [21005353]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

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

Вот именно если сделать так то на каждую запись по Ordname где 2 WorkOrder'a будет удвоен нарастающий итог.
Должно быть так:
OrdNo Date PartName OrdQty AllocatedOrdQty AllocatedBalance TotalBalance WorkOrder
Заказ2 01/01/18 Part1 8 8 20 20 WO3
Заказ2 01/01/18 Part1 8 8 20 20 WO3
Заказ1 01/02/18 Part1 5 13 12 20 WO1
Заказ1 01/02/18 Part1 5 13 12 20 WO2
Заказ3 01/05/18 Part1 3 16 7 20 WO5

А будет так:
OrdNo Date PartName OrdQty AllocatedOrdQty AllocatedBalance TotalBalance WorkOrder
Заказ2 01/01/18 Part1 8 8 20 20 WO3
Заказ2 01/01/18 Part1 8 16 12 20 WO3
Заказ1 01/02/18 Part1 5 21 4 20 WO1
Заказ1 01/02/18 Part1 5 26 -1 20 WO2
Заказ3 01/05/18 Part1 3 29 -6 20 WO5


С подбивкой баланса в это время суток мог ошибиться, но смысл ясен.
4 дек 17, 19:46    [21005393]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Ну убери тогда джойн на воркинг ордерс. Вместо него напиши вере ордернум ин...

Рассчитай набегающий итог без ордеров, а потом уже помнож на них.

Все ещё без цте и аплаев.
4 дек 17, 20:39    [21005507]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

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

А, у меня 2008.
unbounded preceding не сработает.
Забыл что пробовал уже.

"Рассчитай набегающий итог без ордеров, а потом уже помнож" это ж выходим обратно на CTE, или лыжи не едут?
Ну типа свести там все выборки.

Да и для APPLY надо в CTE подготовить ROW_NUMBER?
5 дек 17, 00:16    [21005858]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
DECLARE 
  @Orders TABLE (
  OrdNo varchar(20) 
, Date datetime
, PartName varchar(20) 
, OrdQty int) 

INSERT INTO @Orders VALUES 

('Заказ1',	'01/02/18',	'Part1',	5)
,('Заказ2',	'01/01/18',	'Part1',	8)
,('Заказ3',	'01/05/18',	'Part1',	3)
,('Заказ4',	'01/01/17',	'Part2',	10)
,('Заказ5',	'01/01/19',	'Part2',	6)

DECLARE @Balance TABLE (PartName varchar(20), Balance int ) 
INSERT INTO @Balance  VALUES 
('Part1',	20)
,('Part2', 29)

DECLARE @Work TABLE (OrdNo varchar(20), WorkOrder varchar(20) )
INSERT INTO @Work VALUES 

('Заказ1',	'WO1')
,('Заказ1',	'WO2')
,('Заказ2',	'WO3')
,('Заказ2',	'WO4')
,('Заказ3',	'WO5')



SELECT 
  Z.* 
, W.WorkOrder

FROM(
  SELECT 
    O.OrdNo  
  , O.Date  
  , O.PartName 
  , O.OrdQty  
  , B.Balance
  , PartsReserved =  SUM(O.OrdQty) OVER (PARTITION BY O.PartName ORDER BY O.Date ROWS   unbounded preceding )
  , PartsRest = B.Balance - (SUM(O.OrdQty) OVER (PARTITION BY O.PartName ORDER BY O.Date ROWS   unbounded preceding ))
 
   FROM @Orders O 
     INNER JOIN  @Balance B ON B.PartName = O.PartName
   WHERE EXISTS (SELECT 1 FROM @Work W WHERE W.OrdNo = o.OrdNo) 
 ) Z
 INNER JOIN @Work W ON W.OrdNo = Z.OrdNo
 ORDER BY Z.Date, Z.OrdNo , W.WorkOrder

 
5 дек 17, 00:17    [21005861]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
О, спасибо.
Правда засаду с unbounded preceding всё равно надо как-то обойти.
А такой код отработает быстрее чем CTE? У меня с CTE очень тормозит.
5 дек 17, 00:27    [21005878]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
Вообще, и правда, так увлёкся CTE что протормозил насчёт обычного вложенного селекта.
5 дек 17, 00:29    [21005880]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1670
slovnet
О, спасибо.
Правда засаду с unbounded preceding всё равно надо как-то обойти.
А такой код отработает быстрее чем CTE? У меня с CTE очень тормозит.

Что вы к этому CTE придрались... Это просто более удобная форма записи запроса.

SELECT * FROM (SELECT * FROM (SELECT * FROM TableA) as rez) as rez2
--тоже самое что
;WITH Rez AS
  (SELECT * FROM TableA),
Rez2 AS
  (SELECT * FROM Rez)
SELECT * FROM Rez2

SQL сервер разворачивает ваше CTE в одно большое выражение...
И если вам кажется что
;WITH CTE AS
  (SELECT * FROM TableA),
CTE2 AS
  (SELECT * FROM CTE WHERE ID < 5).
CTE3 AS
  (SELECT * FROM CTE WHERE ID > 10)
SELECT * FROM CTE2 AS c2
JOIN CTE3 AS c3
  ON c3.ID = c2.ID

Сервер 1 раз выполнит SELECT * FROM TableA то вы заблуждаетесь....
Потому что он развернет это в
SELECT * FROM (SELECT * FROM (SELECT * FROM TableA) WHERE ID < 5) AS c2
JOIN (SELECT * FROM (SELECT * FROM TableA) WHERE ID > 10) AS c3
  ON c3.ID = c2.ID

Как видите читаем TableA 2 раза...


P.S. Код с оконными функциями отработает быстрее чем вложенные корреляционные запросы, которым кстати тоже нарастающие итоги считаются...
5 дек 17, 00:40    [21005890]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1670
https://blogs.technet.microsoft.com/isv_team/2011/07/26/1080/

Вот вам 4 примера расчета нарастающих итогов... Выбирайте какой хотите..
5 дек 17, 00:42    [21005892]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

Откуда:
Сообщений: 141
X-Cite,

Спасибо.
5 дек 17, 01:03    [21005905]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
X-Cite
https://blogs.technet.microsoft.com/isv_team/2011/07/26/1080/
Вот вам 4 примера расчета нарастающих итогов... Выбирайте какой хотите..

Лучший из 4х примеров автор не может, у него лапки 2008й. 2 других - корявое тормозное бе.
Так, что выбор только один - сэлф джойн.


Кстати, это очень показательный топик. В очередной раз человек вместо того, чтоб задать вопрос по сути задачи, задает вопрос по сути того, что он принял за её решение.

Ведь "Как использовать ОТВ в ОТВ" и "Как посчитать набегающий итог без окон" - принципиально разные задачи.

Пожалуй, добавлю этот топик в закладку, как эталонный.
5 дек 17, 09:43    [21006181]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
slovnet
Member

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

Вообще-то всё ровно наоборот.
Изначальный была описана суть задачи и задан вопрос каков best practice.

Здесь (кроме десятков неконструктивных наездов) советовали варианты с CTE.
И я так до сих пор и не понял в чём преимущество предложенного Вами решения перед CTE.
А учитывая 2008 APPLY вроде как оптимальное решение.

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

Поэтому задаёт вопрос общий - как лучше сделать. Каков консепт?
А ему в ответ - чего синтаксис не гуглил??!!! Начинается длинная разборка насчёт синтаксиса. А через три простыни выясняется что решение совсем не там. Тогда наезд - а зачем про этот синтаксис спрашивал???!!!

В любом случае, мне лично из этой переписки один сплошной профит, получил хороший мастер-класс по T-SQL.
За что всем пинавшим-помогавшим огромное спасибо.
5 дек 17, 11:32    [21006639]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
slovnet
Вообще, это очень показательный топик, хорошо демонстрирует непонятки между новичком и экспертом.
...
Поэтому задаёт вопрос общий - как лучше сделать. Каков консепт?

Именно, грамотный новичок и задает вопрос - как лучше сделать.

А у вас получилось: "Я тут два гвоздодера нашел, как мне их соединить? Может сваркой? Или еще как?" И только через пару страниц выясняется, что надо шуруп закрутить, и лучше всего воспользоваться шуруповертом (или, учитывая ограниченность ваших инструментов из-за версии сервера - отверткой).
5 дек 17, 11:58    [21006743]     Ответить | Цитировать Сообщить модератору
 Re: Best practice по сведению двух CTE  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Что значит чем лучше мой вариант чем ЦТЕ.

Вопрос в том, для решения какой задачи лучше?

Для набегающего итога?
Окно >> Сэлфджоин >> ОТВ >> Курсор
Поскольку твой 2008 не может решить задачу окном, то тебе нужен самоджойн.
Почему лучше, потому, что быстрее. Просто быстрее во всех случаях.

Для инкапсуляции "промежуточных наборов данных"?
Это опять же бабушка на двое сказала. Лично я не люблю СТЕ из-за ограничений, которые они накладывают на написание кода. СТЕ ты обязан использовать СРАЗУ после объявления, и никак иначе. Понятно почему, потому, что это (кроме случая с рекурсией) простой синтаксический сахар, который развернется, в итоге в тоже что и
SELECT  FROM (SELECT FROM) Z
.

Работу СТЕ ты не можешь, например, легко проверить во время написания процедуры, в отличие от #таблицы.

Ласт, бат нот лист, весь синтаксис ОТВ довольно необычен для скуль кода, и прямо скажем, режет глаз, особенно когда один за другим идет ворох ОТВшек- сложно распутывать.

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


В моей картине мира есть ровно ДВА случая, когда нужно использовать именно ОТВ.

а) рекурсивные запросы
б) ты пишешь инлайн функцию, в которой у тебя физически нет возможности использовать временные таблицы для каких то предрасчетов.
5 дек 17, 12:01    [21006757]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить