Объединение нескольких CTE
Я работаю над базой данных крупного розничного магазина. Мне нужно запрашивать данные из нескольких таблиц, чтобы получить такие числа, как доход, необработанные доходы и сравнить разные периоды времени. Большая часть этого довольно проста, но я изо всех сил пытался найти способ объединения нескольких CTE. Я сделал скрипку, чтобы вы знали, о чем я говорю. Я сильно упростил структуру и пропустил несколько столбцов в подзапросах, потому что в данном случае они не имеют значения.
Как видите, в каждой строке каждой таблицы указана страна и бренд. Окончательный запрос должен быть сгруппирован по тем. Сначала я попытался выполнить ПОЛНОЕ СОЕДИНЕНИЕ со всеми таблицами, но в некоторых случаях это не сработало, как вы можете видеть здесь: SQLfiddle #1. Обратите внимание на две последние строки, которые неправильно сгруппированы.
Select Coalesce(incoming.country, revenue.country, revcompare.country,
openord.country) As country,
Coalesce(incoming.brand, revenue.brand, revcompare.brand,
openord.brand) As brand,
incoming.OrdersNet,
openord.OpenOrdersNet,
revenue.Revenue,
revenue.RawProceeds,
revcompare.RevenueCompare,
revcompare.RawProceedsCompare
From incoming
Full Join openord On openord.country = incoming.country And
openord.brand = incoming.brand
Full Join revenue On revenue.country = incoming.country And
revenue.brand = incoming.brand
Full Join revcompare On revcompare.country = incoming.country And
revcompare.brand = incoming.brand
Group By incoming.OrdersNet,
openord.OpenOrdersNet,
revenue.Revenue,
revenue.RawProceeds,
revcompare.RevenueCompare,
revcompare.RawProceedsCompare,
incoming.country,
revenue.country,
openord.country,
revcompare.country,
incoming.brand,
revenue.brand,
revcompare.brand,
openord.brand
Order By country,
brand
Затем я переписал запрос, сохранив все CTE. Я добавил еще один CTE (основу), который объединяет все возможные комбинации стран и брендов, и оставил объединенным на этом. Теперь все работает нормально (посмотрите здесь -> SQLfiddle #2), но это кажется таким сложным. Нет ли более простого способа добиться этого? Единственное, что я, вероятно, не смогу изменить, это CTE, так как в реальной жизни они намного сложнее.
WITH basis AS (
SELECT Country, Brand FROM incoming
UNION
SELECT Country, Brand FROM openord
UNION
SELECT Country, Brand FROM revenue
UNION
SELECT Country, Brand FROM revcompare
)
SELECT
basis.Country,
basis.Brand,
incoming.OrdersNet,
openord.OpenOrdersNet,
revenue.Revenue,
revenue.RawProceeds,
revcompare.RevenueCompare,
revcompare.RawProceedsCompare
FROM basis
LEFT JOIN incoming On incoming.Country = basis.Country AND incoming.Brand = basis.Brand
LEFT JOIN openord On openord.Country = basis.Country AND openord.Brand = basis.Brand
LEFT JOIN revenue On revenue.Country = basis.Country AND revenue.Brand = basis.Brand
LEFT JOIN revcompare On revcompare.Country = basis.Country AND revcompare.Brand = basis.Brand
Спасибо за вашу помощь!
Кроме того, есть ли причина, по которой это нужно делать в одном запросе? Для ясности, во многих случаях это делает код более читаемым, если вы используете табличные переменные или временные таблицы.
если вы изо всех сил пытаетесь использовать CTE, вернитесь к временным таблицам
Обратите внимание на две последние строки, которые не сгруппированы правильно. Да, они сгруппированы правильно, потому что именно так вы написали запрос. У вас слишком много столбцов в последнем предложении group by. Но это спорный вопрос, поскольку ваши CTE будут создавать только 1 строку для каждой страны/бренда. Окончательный запрос вообще не нужно группировать.
Примечание. Входящие CTE и openord можно объединить в 1 с помощью условного суммирования, чтобы, возможно, сделать его более эффективным. То же самое для доходов и revcompare.
@ Чад, да, это правильно. спасибо всем за подсказки с условным суммированием. Я попробую это.
Ответы 1
Поскольку вы работаете только с двумя таблицами, orders и rev, рассмотрите возможность условной агрегации, переместив WHERE условия в логику CASE для одного агрегированного запроса. Кроме того, рассмотрите только один CTE для всех возможных пар страна/бренд для LEFT JOIN в двух таблицах.
WITH cb AS (
SELECT Country, Brand FROM orders
UNION
SELECT Country, Brand FROM rev
)
SELECT cb.Country
, cb.Brand
, SUM(o.netprice) AS OrdersNet
, SUM(CASE
WHEN o.isopen = 1
THEN o.netprice
END) AS OpenOrdersNet
, SUM(CASE
WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
THEN r.netprice
END) AS Revenue
, SUM(CASE
WHEN r.bdate BETWEEN '2020-12-01' AND '2020-12-31'
THEN r.rpro
END) AS RawProceeds
, SUM(CASE
WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
THEN r.netprice
END) AS RevenueCompare
, SUM(CASE
WHEN r.bdate BETWEEN '2020-11-01' AND '2020-11-30'
THEN r.rpro
END) AS RawProceedsCompare
FROM cb
LEFT JOIN orders o
ON cb.Country = o.Country
AND cb.Brand = o.Brand
LEFT JOIN rev r
ON cb.Country = r.Country
AND cb.Brand = r.Brand
GROUP BY cb.Country
, cb.Brand
Так что просто, чтобы получить краткое изложение того, что вы хотите. Вам нужен уникальный список всех комбинаций
Country+Brand, а затем вы хотите объединить статистику изincoming,openord,revenue,recompare?