Блог

Я пытаюсь построить таблицу фактов в этой схеме снежинки: !1

Я использую эту базу данных: введите описание ссылки здесь

Я использую SSMS, и когда я щелкаю правой кнопкой мыши на своем хранилище и выбираю импорт данных, а затем подход с запросами, у меня остается проблема с тем, чтобы поместить все эти запросы для моих показателей в 1 большой запрос, с написанием которого у меня были проблемы, но в итоге я получил это единственное решение, в котором не было ошибок:

 SELECT Orders.OrderID AS OrderID,
Events.EventID AS EventID,
Customers.CustomerID AS CustomerID,
Dishes.DishID AS DishID,
NormAvgTable.AverageNormalCustomerCount,
EveAvgTable.AverageEventCustomerCount,
FavDishTable.CustomerFavDishOrderCount,
EveRevTable.TotalEventsRevenue,
DishOrderCtTable.DishOrderCount,
CustomerRev.CustomerOrderRevenue,
BdayCtTable.CustomerBirthdayOrderCount
FROM Orders, Events, Customers, Dishes
CROSS JOIN (
SELECT AVG(count) AS AverageNormalCustomerCount
    FROM (
    SELECT Events.EventID, COUNT(Orders.CustomerID) AS count
    FROM Customers
    INNER JOIN Orders
    ON Orders.CustomerID = Customers.CustomerID
    INNER JOIN OrdersDishes
    ON Orders.OrderID = OrdersDishes.OrderID
    INNER JOIN Events
    ON CAST(Orders.OrderDate AS DATE) <> CAST(Events.Date AS DATE)
    GROUP BY Events.EventID
) AS CtTable
) AS NormAvgTable
CROSS JOIN (
SELECT AVG(count) AS AverageEventCustomerCount
    FROM (
    SELECT Events.EventID, COUNT(Orders.CustomerID) AS count
    FROM Customers
    INNER JOIN Orders
    ON Orders.CustomerID = Customers.CustomerID
    INNER JOIN OrdersDishes
    ON Orders.OrderID = OrdersDishes.OrderID
    INNER JOIN Events
    ON CAST(Orders.OrderDate AS DATE) = CAST(Events.Date AS DATE)
    GROUP BY Events.EventID
)AS EveCtTable
) EveAvgTable
CROSS JOIN(
SELECT Customers.CustomerID AS CustomerID, COUNT(Orders.OrderID)
AS CustomerFavDishOrderCount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
INNER JOIN OrdersDishes
ON Customers.FavoriteDish = OrdersDishes.DishID
GROUP BY Customers.CustomerID
) AS FavDishTable
CROSS JOIN (
SELECT SUM(Dishes.Price)
AS TotalEventsRevenue
FROM Events
INNER JOIN Orders
ON CAST(Orders.OrderDate AS DATE) = CAST(Events.Date AS DATE)
INNER JOIN OrdersDishes
ON Orders.OrderID = OrdersDishes.OrderID
INNER JOIN Dishes
ON OrdersDishes.DishID = Dishes.DishID
GROUP BY Events.EventID
) AS EveRevTable
CROSS JOIN(
SELECT  Dishes.DishID AS DishID, COUNT(OrdersDishes.OrdersDishesID) 
AS DishOrderCount
FROM OrdersDishes
INNER JOIN Dishes
ON Dishes.DishID = OrdersDishes.DishID
GROUP BY Dishes.DishID
) AS DishOrderCtTable
CROSS JOIN(
SELECT Orders.CustomerID AS CustomerID, SUM(Dishes.Price)
AS CustomerOrderRevenue
FROM OrdersDishes
INNER JOIN Orders
ON Orders.OrderID = OrdersDishes.OrderID
INNER JOIN Dishes
ON OrdersDishes.DishID = Dishes.DishID
GROUP BY Orders.CustomerID
) AS CustomerRev
CROSS JOIN(
SELECT Customers.CustomerID AS CustomerID, COUNT(Orders.OrderID) 
AS CustomerBirthdayOrderCount
FROM Orders, Customers
WHERE DAY(Orders.OrderDate) = DAY(Customers.Birthday)
AND MONTH(Orders.OrderDate) = MONTH(Customers.Birthday)
GROUP BY Customers.CustomerID
) AS BdayCtTable
 

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

Какой лучший запрос я могу использовать/как оптимизировать свой (какие в нем ошибки).

Две проблемы — высокое количество записей и SQL выглядит сложным/нечитаемым. Количество записей велико из-за перекрестного соединения. Вам нужно установить некоторое условие соединения между подзапросами и таблицами. Например, вы упомянули нижеприведенные таблицы, но никаких соединений FROM Orders, Events, Customers, Dishes . Я не уверен в требованиях, поэтому вот мои мысли/решения. —

1. Сделайте минимальный SQL только с таблицей заказов. Затем продолжайте добавлять соединения и проверяйте количество, затраченное время. если выше ожиданий, попробуйте настроить это соединение. продолжайте до последнего столика.
2. Перекрестное соединение всегда дорого — попробуйте использовать внутреннее или левое. Кроме того, в немногих таблицах нет условий соединения, которые приводят к декартову соединению и, таким образом, увеличивают количество результатов.
3. Использовать sqlformat.org/ чтобы сделать ваш sql красивым 🙂
4. Если пункт № 1 выше становится сложным, создайте два SQLS и создайте для этого промежуточную таблицу. Вам не нужно помещать всю логику в один SQL. Может и его сломать.
5. Проверьте индекс в каждой таблице и посмотрите, можете ли вы использовать их в своем SQL.