|
Подскажите как избавиться от FULL JOIN в коде
#40107210
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Всем привет. Чем заменить full outer join для экономии ресурсов
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
SELECT ISNULL(t1.AccountingDate, t2.AccountingDate) AS AccountingDate,
ISNULL(t1.RezonItemID,t2.RezonItemID) as RezonItemID,
ISNULL(t1.ClientOrderID, t2.ClientOrderID) AS ClientOrderID,
ISNULL(t1.PostingID, t2.PostingID) AS PostingID,
ISNULL(t1.Qty, 0) - ISNULL(t2.Qty, 0) AS Qty,
ISNULL(t1.BrandID, t2.BrandID) as BrandID,
ISNULL(t1.parent_supplier_id, t2.parent_supplier_id) parent_supplier_id,
ISNULL(t1.MarketplaceSellerID, t2.MarketplaceSellerID) as MarketplaceSellerID,
ISNULL(t1.parent_supplier_name,t2.parent_supplier_name) as parent_supplier_name
FROM
(
select t1.AccountingDate,
t1.ClientOrderID,
t1.PostingID,
t1.RezonItemID,
isnull(t2.BrandID,-1) BrandID,
t2.MarketplaceSellerID,
coalesce(ps.ParentID ,s.SupplierID) parent_supplier_id,
ps.Seller_Name as parent_supplier_name,
t1.Qty
FROM
(
SELECT AccountingDate,
RezonItemID,
ClientOrderID,
PostingID,
sum(Qty) as Qty
FROM metazonbeeeye.action
WHERE 1 = 1
AND ActionTypeID IN (401, 1003)
AND AccountingGroupID IN (31241000, 11180244043670, 31247000)
AND AccountingDate >= LAST_DAY(ADD_MONTHS(now(), -16)) +1
group by AccountingDate,
RezonItemID,
ClientOrderID,
PostingID
-- ORDER BY RezonItemID
) AS t1
inner JOIN DP_team.RezonItem AS t2
ON t1.RezonItemID = t2.RezonItemID
inner join DP_team.Seller_ID_distinct s
on s.MarketPlaceSellerID=t2.MarketplaceSellerID and s.Category_1=t2.CommercialCategoryLevel1Name
and s.YYYYMM=to_char(t1.AccountingDate,'YYYYMM')
left join DP_team.Parent_Supplierid ps on ps.SupplierID = s.SupplierID and ps.Category1 =t2.CommercialCategoryLevel1Name
WHERE t2.MacroBU <> 'Супермаркет'
) AS t1
FULL OUTER JOIN
(
select t1.AccountingDate,
t1.ClientOrderID,
t1.PostingID,
t1.RezonItemID,
isnull(t2.BrandID,-1) BrandID,
t2.MarketplaceSellerID,
coalesce(ps.ParentID ,s.SupplierID) parent_supplier_id,
ps.Seller_Name as parent_supplier_name,
t1.Qty
FROM
(
SELECT AccountingDate,
RezonItemID,
ClientOrderID,
PostingID,
sum(Qty) as Qty
FROM metazonbeeeye.action
WHERE 1 = 1
AND ActionTypeID IN (402, 403)
AND AccountingGroupID IN (31241000, 11180244043670, 31247000)
AND AccountingDate >= LAST_DAY(ADD_MONTHS(now(), -16)) +1
group by AccountingDate,
RezonItemID,
ClientOrderID,
PostingID
-- ORDER BY RezonItemID
) AS t1
inner JOIN DP_team.RezonItem AS t2
ON t1.RezonItemID = t2.RezonItemID
inner join DP_team.Seller_ID_distinct s --данные о селлере
on s.MarketPlaceSellerID=t2.MarketplaceSellerID and s.Category_1=t2.CommercialCategoryLevel1Name
and s.YYYYMM=to_char(t1.AccountingDate,'YYYYMM')
left join DP_team.Parent_Supplierid ps on ps.SupplierID = s.SupplierID and ps.Category1 =t2.CommercialCategoryLevel1Name
WHERE t2.MacroBU <> 'Супермаркет'
) AS t2
ON t1.AccountingDate = t2.AccountingDate AND t1.ClientOrderID = t2.ClientOrderID and t1.PostingID=t2.PostingID
and t1.BrandID=t2.BrandID and t1.parent_supplier_id=t2.parent_supplier_id
|
|
|
