В общем, раз автор не торопится доделывать то вот решение без секционирования.
Но доделанное с left join и union all.
Добавлена индексированная вьюха TurnoverMonth.
Если кому особо надо, берите и сравнивайте что вам будет быстрее.
Может abkurenkov стоит это скопировать в статью.
немного переделанное заполнение бд, меньше данных, что бы побыстрее заполнялось
if object_id('dbo.Turnover','U') is not null drop table dbo.Turnover;
go
with times as
(
select 1 id
union all
select id+1
from times
where id < 1*155*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет
)
, storehouse as
(
select 1 id
union all
select id+1
from storehouse
where id < 3 -- количество складов
)
select
identity(int,1,1) id,
dateadd(minute, t.id, convert(datetime,'20161101',120)) dt,
1+abs(convert(int,convert(binary(4),newid()))%3) ProductID, -- 1000 - количество разных продуктов
s.id StorehouseID,
case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход
1+abs(convert(int,convert(binary(4),newid()))%100) Quantity
into dbo.Turnover
from times t cross join storehouse s
option(maxrecursion 0);
go
--- 15 min
alter table dbo.Turnover alter column id int not null
go
alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page)
go
-- 6 min
create view dbo.TurnoverHour
with schemabinding as
select
convert(datetime,convert(varchar(13),dt,120)+':00',120) as dt, -- округляем до часа
ProductID,
StorehouseID,
sum(isnull(Operation*Quantity,0)) as Quantity,
count_big(*) qty
from dbo.Turnover
group by
ProductID,
StorehouseID,
convert(datetime,convert(varchar(13),dt,120)+':00',120)
go
create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (ProductID, StorehouseID, dt)
go
create view dbo.TurnoverMonth
with schemabinding as
select
CAST(dateadd(day, 1-day(dt), dt) as date) as monthT, -- округляем до месяца
ProductID,
StorehouseID,
sum(isnull(Operation*Quantity,0)) as Quantity,
count_big(*) qty
from dbo.Turnover
group by
ProductID,
StorehouseID,
CAST(dateadd(day, 1-day(dt), dt) as date)
go
create unique clustered index uix_TurnoverMonth on dbo.TurnoverMonth (ProductID, StorehouseID, monthT)
go
join left
set statistics io on;
set statistics time on;
set dateformat ymd;
declare
@start datetime = '2016-12-01',
@finish datetime = '2017-02-28'
declare @start_month datetime = dateadd(day, 1-day(@start), @start)
select *
from
(
select
TurnoverHour.dt,
TurnoverHour.StorehouseID,
TurnoverHour.ProductId,
TurnoverHour.Quantity as deltaQuantity,
sum(TurnoverHour.Quantity) over
(
partition by TurnoverHour.ProductID, TurnoverHour.StorehouseID
order by dt
) as Accumulate,
isnull(totalsBefore.TotalBeforeStartMonth, 0) TotalBeforeStartMonth,
sum(TurnoverHour.Quantity) over
(
partition by TurnoverHour.ProductID, TurnoverHour.StorehouseID
order by dt
) + isnull(TotalBeforeStartMonth, 0) as BalanceTotal
from dbo.TurnoverHour with(noexpand)
left join (
select
--@start_month-1 as dt,
ProductID,
StorehouseID,
sum(Quantity) as TotalBeforeStartMonth
from dbo.TurnoverMonth with(noexpand)
where monthT < @start_month
--and ProductID = 2 and StorehouseID = 2
group by
ProductID,
StorehouseID
) as totalsBefore on totalsBefore.ProductID = TurnoverHour.ProductID
and totalsBefore.StorehouseID = TurnoverHour.StorehouseID
where dt between @start_month and @finish
--and TurnoverHour.ProductID = 2 and TurnoverHour.StorehouseID = 2
) as tmp
where dt >= @start
--and ProductID = 2 and StorehouseID = 2
order by ProductID, StorehouseID, dt
option(recompile);
union all
set statistics io on;
set statistics time on;
set dateformat ymd;
declare
@start datetime = '2016-12-01',
@finish datetime = '2017-02-28'
declare @start_month datetime = dateadd(day, 1-day(@start), @start)
select *
from
(
select
dt,
ProductId,
StorehouseID,
Quantity as deltaQuantity,
sum(Quantity) over
(
partition by ProductID, StorehouseID
order by dt
) as BalanceTotal
from (
select
@start_month-1 as dt,
ProductID,
StorehouseID,
sum(Quantity) as Quantity
from dbo.TurnoverMonth with(noexpand)
where monthT < @start_month
--and ProductID = 2 and StorehouseID = 2
group by
ProductID,
StorehouseID
union all
select
TurnoverHour.dt,
TurnoverHour.ProductId,
TurnoverHour.StorehouseID,
TurnoverHour.Quantity
from dbo.TurnoverHour with(noexpand)
where dt between @start_month and @finish
--and ProductID = 2 and StorehouseID = 2
) as u
) as tmp
where dt >= @start
--and ProductID = 2 and StorehouseID = 2
order by ProductID, StorehouseID, dt
option(recompile);
Что бы мы друг друга правильно поняли. Вы считаете что итоговый запрос правильный?
Баланс это остаток на какой то момент времени.
У автора балансы есть только в запросах, в которых не используется between.
Во вьюхе TurnoverHour нет остатков, а только обороты за месяц.
Автор забыл в итоговом запросе посчитать все обороты с начала времен.
Вот это условие, всё поломало, превратив Остатки в обороты за период:
where dt between @start_month and @finish
Сумма оборотов за период НЕ РАВНА сумме оборотов с начала времен.
Только сумма оборотов с начала времен является остатокм.
Я утверждаю что тут нет итогового правильного запроса.
Автор разбил задачи, но НЕ объединил их. (или сделал это неправильно)
Нужно просуммировать всё в TurnoverHour, с начала времен до начала месяца (не включая начало месяца), а затем, накопительно проссумировать всё, от начала месяца, до даты меньшей чем finish+1 (включая начало месяца)
finish datetime = '2015-01-03'
и
where dt between>@start_month and finish
да и вообще, если нужно данные на конец 2015-01-03 дня, то данное условие невыведет данные на конец 03 числа, а только 3 число и 00 секунд.
Лучше так: «dt < „2015-01-04“», то есть, строго меньше следующего дня
в общем abkurenkov, для завершения «Остатки на складах» нужно к текущему балансу (накопительному итогу) добавить «остатки на начало месяца» который получается так «select dateadd(day, 1-day(@start), start)».
union all или left outer join в помощь
а, ну да, я там про индекс не упомянул. Порядок полей в конструкции partition by тоже думаю надо поменять, но и в индексе, одновременно, что бы одинаковые были с partition by.
При наличии where productID in эта манипуляция должна ускорить выборку
Я говорю про порядок полей в индексе, что бы было меньше чтений, нужно выносить более селективное поле в начало списка полей составного/кластерного индекса.
В данном примере уместен порядок полей в индексе productId, StorehouseID, Dt.
Возможно я под вечер подустал, но:
dbo.TurnoverHour у нас не содержит остатков на начало месяца, оно содержит дельту за определенный час
select
dt,
StorehouseID,
ProductId,
Quantity,
sum(Quantity) over
(
partition by StorehouseID, ProductID
order by dt
) as Balance
from dbo.TurnoverHour with(noexpand)
where dt between start_month and finish
выдаст накопительную сумму изменений, от нулевого остатка на начало месяца до момента finish
мне кажется где то не хватает UNION ALL текущего запроса с остатками на начало месяца
уточню, я имею ввиду поиск по кластерному/составному индексу, допустим нам нужно узнать сколько на остатках разных чипсов, но не остальных товаров, пишу:
where productId in (1,8,9,101,647) and StorehouseID in (1,5, 7)
если productId в начале, то это ускорит поиск по индексу, для набора товаров, а не для всех товаров в таблице.
Если же первым будет StorehouseID у которого плохая селективность, то конечно подходящего индекса для особого ускорения не будет.
Поскольку селективность по ProductID лучше (продуктов больше чем складов), может лучше ProductID на первое место поставить, не знаю ускорит ли это работу для группировок или замедлит, но обычно торговые/складские системы делают поиск по условию по товару или набору товаров, а значит ProductID на первом месте должен ускорить поиск/фильтр где в условии есть товар
Но доделанное с left join и union all.
Добавлена индексированная вьюха TurnoverMonth.
Если кому особо надо, берите и сравнивайте что вам будет быстрее.
Может abkurenkov стоит это скопировать в статью.
Баланс это остаток на какой то момент времени.
У автора балансы есть только в запросах, в которых не используется between.
Во вьюхе TurnoverHour нет остатков, а только обороты за месяц.
Автор забыл в итоговом запросе посчитать все обороты с начала времен.
Вот это условие, всё поломало, превратив Остатки в обороты за период:
Сумма оборотов за период НЕ РАВНА сумме оборотов с начала времен.
Только сумма оборотов с начала времен является остатокм.
Я утверждаю что тут нет итогового правильного запроса.
Автор разбил задачи, но НЕ объединил их. (или сделал это неправильно)
Нужно просуммировать всё в TurnoverHour, с начала времен до начала месяца (не включая начало месяца), а затем, накопительно проссумировать всё, от начала месяца, до даты меньшей чем finish+1 (включая начало месяца)
Лучше так: «dt < „2015-01-04“», то есть, строго меньше следующего дня
У меня индекс такой:
появилась сортировка
а значит имеет значение порядок в partition by
union all или left outer join в помощь
2015-02-01 00:00:00.000
что наоборот то?
При наличии where productID in эта манипуляция должна ускорить выборку
В данном примере уместен порядок полей в индексе productId, StorehouseID, Dt.
По моему у автора итоговый запрос абсолютно неправильный, так как не выдает остатков.
dbo.TurnoverHour у нас не содержит остатков на начало месяца, оно содержит дельту за определенный час
select
dt,
StorehouseID,
ProductId,
Quantity,
sum(Quantity) over
(
partition by StorehouseID, ProductID
order by dt
) as Balance
from dbo.TurnoverHour with(noexpand)
where dt between start_month and finish
выдаст накопительную сумму изменений, от нулевого остатка на начало месяца до момента finish
мне кажется где то не хватает UNION ALL текущего запроса с остатками на начало месяца
получаю select @start_month; = 2015-02-21 00:00:00.000
это нормально?
если productId в начале, то это ускорит поиск по индексу, для набора товаров, а не для всех товаров в таблице.
Если же первым будет StorehouseID у которого плохая селективность, то конечно подходящего индекса для особого ускорения не будет.