Блог

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

Для контекста мы переносим около 3000 элементов по 30 категориям продуктов и 50 подкатегориям (отношения родитель-потомок). Мы продаем их в тысячах магазинов, и наша база данных фиксирует еженедельные продажи в каждом магазине по каждому продукту. Мы храним данные за несколько лет.

В настоящее время мой запрос возвращает все записи, в то время как я хотел бы ограничить его 10 наиболее продаваемыми товарами на основе суммы их единичных продаж за последние 52 недели (мое предложение where указывает 52 недели, но мне нужны еженедельные сведения в моем извлечении).

 SELECT
store.store_id,
store.sales_rep,
store.sales_rep_manager,
prod.category,
prod.sub_category,
prod.item,
sales.week_id,
sum(sales.units) as "UNITS SOLD",
sum(sale.dollars) as "DOLLARS SOLD"
...
GROUP BY
store.store_id,
store.sales_rep,
store.sales_rep_manager,
prod.category,
prod.sub_category,
prod.item,
sales.week_id,
ORDER BY
7 desc
  

Я думаю, что мне следует использовать оператор TOP, но все, что мне удалось сделать, это ограничить весь вывод 10 лучшими записями в целом.

Я хотел бы видеть 10 лучших элементов на основе скорости единиц измерения для выбранного диапазона дат, но для каждого магазина и подкатегории

Магазин 1 Категория 1 Подкатегория 1 САМЫЙ продаваемый товар # 1 САМЫЙ продаваемый товар # 2 САМЫЙ продаваемый товар # 3… САМЫЙ ПРОДАВАЕМЫЙ товар # 10

Прямо сейчас я подключил свой запрос в Excel и прошу свою сводную таблицу отфильтровать только элементы top10.

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

Вы можете довольно легко ограничить результаты общим объемом продаж в результирующем наборе:

 with q as (<your query here>)
select q.*
from (select q.*, dense_rank() over (order by TotalUs) as rnk
      from (select q.*,
                  sum("Units Sold") over (partition by prod.item) as TotalUS
            from q
           ) q
      ) q
where rnk <= 10;
  

Получить его за последний год немного сложнее:

 with q as (<your query here>)
select q.*
from (select q.*, dense_rank() over (order by TotalUs) as rnk
      from (select q.*,
                  sum(last_52weeks) over (partition by prod.item) as TotalUS
            from (select q.*,
                         (case when dense_rank() over (partition by item_id order by week_id desc) <= 52
                               then "Units Sold" else 0
                          end) as last_52weeks
                  from q
                 ) q
           ) q
      ) q
where rnk <= 10;
  

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

Вот пример, который я собрал для этого случая. Сами данные несколько сомнительны, поскольку store_id 5 на самом деле являются единственными, у которых продано более 10 элементов, но на самом деле это просто демонстрация, так что, надеюсь, вы все еще можете получить общую картину. Очевидно, что ваша структура данных сильно отличается, но вы должны иметь возможность вносить необходимые коррективы, чтобы заставить ее работать с вашими реальными настройками:

 --===================================================================
-- Create and populate a table for demonstration purposes only:
--===================================================================

IF OBJECT_ID('tempdb..#Sales') IS NOT NULL DROP TABLE #Sales;
CREATE TABLE #Sales (
    item_id INT,
    category VARCHAR(10),
    sub_category VARCHAR(10),
    store_id INT,
    week_id INT,
    units INT,
    dollars MONEY
);

INSERT INTO #Sales
VALUES  (1, 'A', 'A1', 1, 1, 10, 50),
        (1, 'A', 'A1', 2, 1, 10, 50),
        (1, 'A', 'A1', 3, 1, 10, 50),
        (1, 'A', 'A1', 4, 1, 10, 50),
        (1, 'A', 'A1', 5, 1, 20, 50),
        (2, 'B', 'B1', 1, 1, 20, 50),
        (2, 'B', 'B1', 2, 1, 20, 50),
        (2, 'B', 'B1', 3, 1, 20, 50),
        (2, 'B', 'B1', 4, 1, 20, 50),
        (2, 'B', 'B1', 5, 1, 20, 50),
        (3, 'A', 'A1', 5, 1, 40, 50),
        (4, 'A', 'A1', 5, 1, 10, 50),
        (5, 'A', 'A1', 5, 1, 5, 50),
        (6, 'A', 'A1', 5, 1, 100, 50),
        (7, 'A', 'A1', 5, 1, 95, 50),
        (8, 'A', 'A1', 5, 1, 35, 50),
        (9, 'A', 'A1', 5, 1, 15, 50),
        (10, 'A', 'A1', 5, 1, 11, 50),
        (11, 'A', 'A1', 5, 1, 12, 50),
        (12, 'A', 'A1', 5, 1, 49, 50),
        (12, 'A', 'A1', 5, 1, 150, 50);


--===================================================================
-- The actual query starts here:
-- (note that the following is a single statement)
--===================================================================

WITH AggregatedSales AS (
    -- This CTE will give you the totals for each store, for each item and category / sub-category:
    SELECT
        s.store_id,
        s.category,
        s.sub_category,
        s.item_id,
        --s.week_id, -- If you want to see the combined data for the entire date range, don't include week here
        SUM(s.units) [total_units_sold],
        SUM(s.dollars) [total_dollars_sold]
    FROM #Sales s
    WHERE s.week_id BETWEEN 1 AND 52 -- Adjust these to match your actual range
    GROUP BY
        s.store_id,
        s.category,
        s.sub_category,
        s.item_id
        --s.week_id
),
RankedSales AS (
    -- This will assign a ranking to each of the records from the previous CTE.
    -- The ranking is reset for each store, and ranks higher number of units sold toward the top.
    SELECT
        a.*,
        DENSE_RANK() OVER (
            PARTITION BY a.store_id
            ORDER BY a.total_units_sold DESC
        ) [ranking]
    FROM AggregatedSales a
)
-- Now we just select all of the "TOP 10" ranked items here:
-- (the WHERE clause is doing all the work in this case, so we don't need an actual TOP)
SELECT
    rs.*
FROM RankedSales rs
WHERE rs.ranking <= 10
ORDER BY
    rs.store_id,
    rs.ranking;