LW, MTD, STD, YTD Sales ... все в очереди и выглядит довольно

Это Последняя неделя , Месяц-время , Сезон-время и Год от даты продажи.

У меня есть табличная функция для каждого из них, которая принимает дату и возвращает все значения CalendarDate для интересующего периода времени:

    dbo.CalendarDatesLW литий> dbo.CalendarDatesMTD литий> dbo.CalendarDatesSTD литий> dbo.CalendarDatesYTD литий>

Итак, у меня есть CTE для выбора продаж YTD (где «год» работает с декабря по ноябрь):

with 
    cteYTD (SalesRep, Category, Units, Amount) as (
        select 
            sales.RepCode,
            sales.Category,
            sum(sales.TotalUnits),
            sum(sales.TotalNetAmount)
        from dbo.Sales sales
            inner join dbo.CalendarDatesYTD(getdate()) calendar on sales.InvoiceDate = calendar.CalendarDate
        group by
            sales.RepCode,
            sales.Category),

.. затем другой для продажи LW:

    cteLW (SalesRep, Category, Units, Amount) as (
        select 
            sales.RepCode,
            sales.Category,
            sum(sales.TotalUnits),
            sum(sales.TotalNetAmount)
        from dbo.Sales sales
            inner join dbo.CalendarDatesLW(getdate()) lw on sales.InvoiceDate = lw.CalendarDate
        group by
            sales.RepCode,
            sales.Category),

... другой для продаж MTD:

    cteMTD (SalesRep, Category, Units, Amount) as (
        select 
            sales.RepCode,
            sales.Category,
            sum(sales.TotalUnits),
            sum(sales.TotalNetAmount)
        from dbo.Sales sales
            inner join dbo.CalendarDatesMTD(getdate()) mtd on sales.InvoiceDate = mtd.CalendarDate
        group by
            sales.RepCode,
            sales.Category),

... и еще один для продажи STD:

    cteSTD (SalesRep, Category, Units, Amount) as (
        select 
            sales.RepCode,
            sales.Category,
            sum(sales.TotalUnits),
            sum(sales.TotalNetAmount)
        from dbo.Sales sales
            inner join dbo.CalendarDatesSTD(getdate()) std on sales.InvoiceDate = std.CalendarDate
        group by
            sales.RepCode,
            sales.Category)

.. и все они индивидуально запускаются в значительной степени мгновенно (0-1 секунды каждый, план выполнения не жалуется на какой-либо потенциальный недостающий индекс). Затем я присоединяюсь к ним:

select
    ytd.SalesRep,
    ytd.Category,
    isnull(lw.Units,0) UnitsLW,
    isnull(lw.Amount,0) AmountLW,
    isnull(mtd.Units,0) UnitsMTD,
    isnull(mtd.Amount,0) AmountMTD,
    isnull(std.Units,0) UnitsSTD,
    isnull(std.Amount,0) AmountSTD,
    ytd.Units UnitsYTD,
    ytd.Amount AmountYTD
from cteYTD ytd
    left join cteSTD std on ytd.SalesRep = std.SalesRep
                        and ytd.Category = std.Category
    left join cteMTD mtd on ytd.SalesRep = mtd.SalesRep
                        and ytd.Category = mtd.Category
    left join cteLW lw on ytd.SalesRep = lw.SalesRep
                        and ytd.Category = lw.Category

Это 17 секунд, для 35 строк - и он становится экспоненциально хуже, если я начинаю добавлять больше столбцов в group by ... и план выполнения выглядит очень, очень странно (хотя не удивительно, учитывая сценарий):

dbo.Sales запрашивается 4 раза, план выполнения выглядит как сталагмиты, горизонтально уложенные

Я запрашиваю dbo.Sales 4 раза, каждый раз захватывая другой временной интервал, а затем снова и снова объединяю те же поля. Должен быть лучший способ сделать это ... правильно?


dbo.Sales - это представление , объединение нескольких FK и реализация некоторых бизнес-правил (например, таких или таких клиентов продажи под категорией X должны быть представлены под категорией Y).

Я чувствую, что я злоупотребляю CTE и должен использовать что-то еще для этого. Но что?

12 голосов | спросил Mathieu Guindon 17 +04002014-10-17T01:50:08+04:00312014bEurope/MoscowFri, 17 Oct 2014 01:50:08 +0400 2014, 01:50:08

4 ответа


5

В течение первой недели года в вашей логике есть недостаток. Если продажа произошла для repcode /category на прошлой неделе предыдущего года, но продажа за тот же самый номер /категория пока еще не произошла в текущем году, тогда значение LW не будет представлено в конечном результате, поскольку оно не будет успешно присоединяться к left outer join. Вы можете разрешить это с помощью полного внешнего соединения, но это становится очень грязным.

Более простое и, вероятно, более быстрое решение состоит в том, чтобы создать только один CTE факторов даты, для всех дат, например:

with DateParts as (

    select CalendarDate, 1 as LWFact, 0 as MTDFact, 0 as STDFact, 0 as YTDFact
    from dbo.CalendarDatesLW(GetDate())
  UNION
    select CalendarDate, 0 as LWFact, 1 as MTDFact, 0 as STDFact, 0 as YTDFact
    from dbo.CalendarDatesMTD(GetDate())
  UNION
    select CalendarDate, 0 as LWFact, 0 as MTDFact, 1 as STDFact, 0 as YTDFact
    from dbo.CalendarDatesSTD(GetDate())
  UNION
    select CalendarDate, 0 as LWFact, 0 as MTDFact, 0 as STDFact, 1 as YTDFact
    from dbo.CalendarDatesYTD(GetDate())
), DateFactors as (
    select CalendarDate,
           max(LWFact) as LWFact,
           max(MTDFact) as MTDFact,
           max(STDFact) as STDFact,
           max(YTDFact) as YTDFact
    from DateParts
    group by CalendarDate
)

ОК, теперь с приведенными выше данными, если вы считаете дату, которая произошла недавно, но не на прошлой неделе, назовите ее x, и еще одна дата y, которая произошла на прошлой неделе, но до этого месяца и в этом сезоне, но в этом году, затем DateFactors будет выглядеть так:

CalndarDate LWFact MTDFact STDFact YTDFact
----------- ------ ------- ------- -------
x                0       1       1       1
y                1       0       0       1

Итак, что-то, что произошло в дату x, будет способствовать показателям MTD, STD и YTD, но не LW Figures.

Теперь вы заполните запрос:

    select 
        sales.RepCode,
        sales.Category,
        sum(sales.TotalUnits * LWFact) as LWUnits,
        sum(sales.TotalNetAmount * LWFact) as LWAmount,
        sum(sales.TotalUnits * MTDFact) as MTDUnits,
        sum(sales.TotalNetAmount * MTDFact) as MTDAmount,
        sum(sales.TotalUnits * STDFact) as STDUnits,
        sum(sales.TotalNetAmount * STDFact) as STDAmount,
        sum(sales.TotalUnits * YTDFact) as YTDUnits,
        sum(sales.TotalNetAmount * YTDFact) as YTDAmount
    from dbo.Sales sales
        inner join DateFactors on sales.InvoiceDate = DateFactors.CalendarDate
    group by
        sales.RepCode,
        sales.Category
ответил rolfl 17 +04002014-10-17T04:25:26+04:00312014bEurope/MoscowFri, 17 Oct 2014 04:25:26 +0400 2014, 04:25:26
7

Я думаю, ваша проблема связана с количеством декартовых продуктов, которые вы формируете. Я бы подошел к чему-то подобному, как я упоминал в чате:

with salesYTD as (
    SELECT SalesPerson, InvoiceDate, Sum(Amount) As AmountOnDay
    FROM sales 
    WHERE InvoiceDate > DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
    GROUP By SalesPerson, InvoiceDate
),
calculatedYTD as (
    SELECT SalesPerson, SUM(AmountOnDay)
    FROM salesYTD
    GROUP BY SalesPerson
),
calculatedMTD as (
    SELECT SalesPerson, SUM(AmountOnDay)
    FROM salesYTD
    WHERE InvoiceDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY SalesPerson
)
-- etc
SELECT *
FROM calculatedYTD ytd
LEFT JOIN calculatedMTD mtd ON ytd.SalesPerson = mtd.SalesPerson

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

Но основной принцип:

  • Выполните дорогостоящую операцию один раз, в частности, с самым большим диапазоном дат (YTD)
  • Выполните оставшуюся часть вычисления из результата первого (уже отфильтрованного)

Но есть несколько вещей, которые вы можете сделать, на которые я сделал предположения выше, предполагая:

  • YTD означает начало года
  • MTD означает начало месяца ... и т. д.

вы можете:

  • Уберите функции вычисления (они дороги по сравнению с ...)
  • Рассчитайте свой YTD как DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
  • Рассчитайте MTD как DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Я не запускал его, но я подозреваю, что он даст вам намного лучшую производительность /производительность. Дайте мне знать, если это поможет ...

ответил Stuart Blackler 17 +04002014-10-17T02:40:56+04:00312014bEurope/MoscowFri, 17 Oct 2014 02:40:56 +0400 2014, 02:40:56
5

Вы выполняете четыре объединения, которые идентичны, за исключением диапазона дат. Это говорит о том, что вы должны работать с диапазонами дат как своего рода данные, а не как код или таблицы или столбцы. Давайте сделаем это, используя некоторые временные таблицы:

create table #dateRanges
( Period NVARCHAR(3)
, StartDateIncl DATE
, EndDateExcl DATE
);

insert into #dateRanges values
( 'LW',  '2016-10-05', '2016-10-12' ),
( 'MTD', '2016-10-01', '2016-11-01' ),
( 'STD', '2016-09-01', '2017-01-01' ),
( 'YTD', '2016-01-01', '2017-01-01' );

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

Таким образом, вы сможете запустить этот простой запрос, который дает все необходимые вам результаты, просто не агрегируется так, как вы хотите:

select range.Period,
       sales.RepCode as SalesRep,
       sales.Category,
       sales.TotalUnits as Units,
       sales.TotalNetAmount as Amount
    from dbo.sales as sales
        inner join #dateRanges as range
            on sales.InvoiceDate >= StartDateIncl
            and sales.InvoiceDate < EndDateExcl;

Итак, как мы получаем оттуда до желаемого результата? Вы хотите сделать два столбца для каждого периода времени. Создание столбцов из строк может быть выполнено с помощью PIVOT .

with salesDateRanges (Period, SalesRep, Category, Units, Amount) as (
    select range.Period,
           sales.RepCode as SalesRep,
           sales.Category,
           sales.TotalUnits as Units,
           sales.TotalNetAmount as Amount
        from dbo.sales as sales
            inner join #dateRanges as range
                on sales.InvoiceDate >= StartDateIncl
                and sales.InvoiceDate < EndDateExcl
), pivotedByUnits (SalesRep, Category, LW, MTD, STD, YTD) as (
    select SalesRep,
           Category,
           isnull(sum([LW]),  0),
           isnull(sum([MTD]), 0),
           isnull(sum([STD]), 0),
           isnull(sum([YTD]), 0)
    from salesDateRanges pivot (
        sum(Units)
        for Period IN ([LW], [MTD], [STD], [YTD])
    ) as pivotTable
    group by SalesRep, Category
), pivotedByAmount (SalesRep, Category, LW, MTD, STD, YTD) as (
    select SalesRep,
           Category,
           isnull(sum([LW]),  0),
           isnull(sum([MTD]), 0),
           isnull(sum([STD]), 0),
           isnull(sum([YTD]), 0)
    from salesDateRanges pivot (
        sum(Amount)
        for Period IN ([LW], [MTD], [STD], [YTD])
    ) as pivotTable
    group by SalesRep, Category
)
select u.SalesRep,
       u.Category,
       u.LW  as UnitsLW,  a.LW  as AmountLW,
       u.MTD as UnitsMTD, a.MTD as AmountMTD,
       u.STD as UnitsSTD, a.STD as AmountSTD,
       u.YTD as UnitsYTD, a.YTD as AmountYTD
    from pivotedByUnits as u
        inner join pivotedByAmount as a
            on u.SalesRep = a.SalesRep
            and u.Category = a.Category;
ответил 200_success 17 +04002014-10-17T03:27:24+04:00312014bEurope/MoscowFri, 17 Oct 2014 03:27:24 +0400 2014, 03:27:24
3

Если вам нужны только Sales on Dates, которые действительно находятся в функции Date-List, вы можете попробовать следующее:

Мы используем MIN /MAX Date of all Ranges в качестве предварительного фильтра (особенно важно ускорить процесс, если данные разбиты на разделы по дате), а затем только один столбец-сканирование по всем соответствующим записям и подсчет всех соответствующих сумм ...

Вы также можете сохранить MIN /MAX диапазона во внешней таблице или просто вычислить их заранее и использовать их в качестве прямых переменных ...

WITH alldates AS (
  SELECT CalendarDate FROM dbo.CalendarDatesLW( getdate() )
  UNION
  SELECT CalendarDate FROM dbo.CalendarDatesMTD( getdate() )
  UNION
  SELECT CalendarDate FROM dbo.CalendarDatesSTD( getdate() )
  UNION
  SELECT CalendarDate FROM dbo.CalendarDatesYTD( getdate() )
),
maxdaterange AS (
  SELECT  MIN( CalendarDate ) minStartDate
         ,MAX( CalendarDate ) maxEndDate
    FROM  alldates
)
-- Everything above is only so we don't have to scan the whole table, but can profit from partitions or an Index-Range Scan on the table afterwards...
SELECT  sales.RepCode
       ,sales.Category
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesLW( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsLW
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesLW( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountLW
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesMTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsMTD
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesMTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountMTD
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesSTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsSTD
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesSTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountSTD
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesYTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsYTD
       ,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesYTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountYTD
  FROM  dbo.Sales
 WHERE  sales.InvoiceDate >= ( SELECT minStartDate FROM maxdaterange )
   AND  sales.InvoiceDate <= ( SELECT maxEndDate   FROM maxdaterange )
 GROUP  BY sales.RepCode, sales.Category;
ответил Falco 17 +04002014-10-17T12:37:24+04:00312014bEurope/MoscowFri, 17 Oct 2014 12:37:24 +0400 2014, 12:37:24

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132