Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Добрый день.
Есть складские проводки где фиксируется движение товара в разрезе даты, склада, ячейки и т.д.
Эти данные сгруппировал по дню и швырнул в новую таблицу. Там же посчитал остаток на дни движения (на начало и конец в штуках и в рублях). Выглядит так:
select top 10 * from FactStocks 

Картинка с другого сайта.

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

В общем каким путем я пошел: создал таблицу, куда вставлял проводки и интервалы дат, в течении которых остатки не менялись. Т.е. эти строки проводок нужно продублировать столько раз, сколько дней в интервале (границы не в счет). Вот такая таблица:
select * from DateGaps where productKey in (9, 20) order by productKey, FromDate

Картинка с другого сайта.

Дальше удаляю все индексы на FactStocks и пишу запрос на вставку, используя таблицу с датами dimDate с кластерным индексом по ключу:
INSERT INTO FactStocks WITH (TABLOCK) 
	(
	  ProductKey
	, StoreKey
	, CellKey
	, ReclamationKey
	, DateFinancialKey
	, BeginDayQty
	, EndDayQty
	, BeginDayCostAmount
	, EndDayCostAmount
	) 
SELECT gaps.ProductKey
	, gaps.StoreKey
	, gaps.CellKey
	, gaps.ReclamationKey
	, dd.DateKey
	, BeginDayQty = gaps.EndDayQty
	, gaps.EndDayQty
	, BeginDayCostAmount = gaps.EndDayCostAmount
	, gaps.EndDayCostAmount 
  FROM DateGaps gaps
    JOIN dimDate dd
      ON dd.DateKey > gaps.FromDate AND dd.DateKey < gaps.ToDate


Получаю следующий план:
Картинка с другого сайта.

и следующие статы:
Картинка с другого сайта.

Картинка с другого сайта.

Красным обвел то, что смущает. Интересует как можно все это убыстрить. На уровне запроса или на уровне схемы в целом.
10 май 12, 15:22    [12530658]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Попробуй обновить статистику на dimDate
10 май 12, 15:31    [12530734]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
сравни еще быстродействие с option (hash join)
10 май 12, 15:39    [12530819]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Мистер Хенки,

Статистика никак не повлияла на estimated rows count, я даже всю таблицу пересоздал с нуля. По гистограмме посмотрел - все четко, range_rows прям один в один сколько и должно быть.

C option (hash join) запрос вообще не запускается, пишет
Сообщение 8622, уровень 16, состояние 1, строка 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Я ж правильно эту констукцию в конец запроса всунул?
  FROM DateGaps gaps
    JOIN dimDate  dd
      ON dd.DateKey > gaps.FromDate AND dd.DateKey < gaps.ToDate
    option (hash join);
10 май 12, 16:15    [12531204]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
PassedBI
Мистер Хенки,

Статистика никак не повлияла на estimated rows count, я даже всю таблицу пересоздал с нуля. По гистограмме посмотрел - все четко, range_rows прям один в один сколько и должно быть.

C option (hash join) запрос вообще не запускается, пишет
Сообщение 8622, уровень 16, состояние 1, строка 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Я ж правильно эту констукцию в конец запроса всунул?
  FROM DateGaps gaps
    JOIN dimDate  dd
      ON dd.DateKey > gaps.FromDate AND dd.DateKey < gaps.ToDate
    option (hash join);

а попробуй inner hash join вместо join написать
10 май 12, 16:44    [12531485]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
тоже самое, ошибка. Нагуглил пару примеров с такой ошибкой, но примеры не подходят к моему запросу, ищу дальше.
10 май 12, 17:02    [12531686]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
ой извини, ты не получишь hash join в этом запросе. он требует оператора эквивалентности.
10 май 12, 17:18    [12531813]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
stop searching... =)
10 май 12, 17:23    [12531848]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Что-то много телодвижений.
-- Имеем проводки (точнее полупроводки, ну это как система построена)
CREATE TABLE [dbo].[Movement] (
	 [ID]		Int	IDENTITY
	 CONSTRAINT [PK_Movement]		PRIMARY KEY
	,[Invoice]	Int	NOT NULL
	 CONSTRAINT [FK_Movement_Invoice]	REFERENCE [dbo].[Invoice]([ID])	-- Товары
	,[Date]		Date	NOT NULL
	,[Amount]	Money	NOT NULL
--	 CONSTRAINT [CK_Movement_Amount]	CHECK ([Amount] > 0 AND [Amount] % 0.01 = 0)
--	,[Direction]	Bit	NOT NULL
	,...
)
GO	-- Группировка по дню
CREATE VIEW [dbo].[vwMovementInvoiceDay] WITH SCHEMABINDING AS
SELECT	 Invoice
	,[Date]
	,Sum(Amount)	AS Amount
--	,Sum(CASE Direction WHEN 0 THEN Amount WHEN 1 THEN -Amount END) AS Amount
FROM	dbo.Movement
WHERE	    [Date] >= '20120101'	-- Тестовый диапазон
	AND [Date] <  '20120201'	-- чтоб систему не завалить :)
GROUP BY Invoice
	,[Date]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Invoice]
	,[Date]
) -- Админсткие параметры создания индекса
-- Всё, группировку залили в виртуальную таблу
GO	-- Запрос остатков ------------------------------
SELECT	 Invoice
	,[Date]
	,Sum(Amount)OVER(PARTITION BY Invoice ORDER BY [Date])	AS Rest
FROM	dbo.vwMovementInvoiceDay
Можно топо в таблу загнать
Но можно ещё групировоки сделать (по месяцам) и просуммировать:
CREATE FUNCTION [dbo].[fnInvoiceRest] (
	@Date	Date
) RETURNS TABLE AS RETURN
WITH MonthsAndDays AS (
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < @Date
UNION ALL
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < @Date AND [Date] >= DateAdd(Day,1-Day(@Date),@Date)
)	SELECT	 Invoice
		,Sum(Amount)	AS Amount
	FROM	MonthsAndDays
	GROUP BY Invoice
GO
Но прикольнее было если можно было сделать индекс на вьюху с Sum()OVER(ORDER BY)

PS: А что за интерфейс на картинках? Denaly?
11 май 12, 00:24    [12533496]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
PS: А что за интерфейс на картинках? Denaly?
SQL Sentry Plan Explorer
11 май 12, 00:41    [12533536]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mind
SQL Sentry Plan Explorer
С виду ещё одна бесполезная хрень.
Неужели до сих пор под SSMS Addon-ы писать убийственно тяжело?
И в Denali ничего с этим не поменялось?
11 май 12, 03:32    [12533765]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior,

sum() over(partition by order by) - это же sql server 2012?
В 2008R2 order by не дает написать, а у меня как раз эта версия, надо было вначале упомянуть =)
Но в любом случае, насколько я понял ваш запрос возвращает накопительный итог на дату проводки в разрезе товаров. Если так, то этот результат у меня есть, он представлен первой картинкой в начальном посте. Т.е. накопительный итог на дату проводки в нужных разрезах у меня есть. Можно сказать, что это контрольная точка пройдена и дальше мне надо каким-то образом "растянуть" полученные остатки. Т.е. если 1-го мая остаток по товару1 = 50 штук, потом он 7-го мая уменьшается до 30 штук (всего 2 проводки с остатками 50 (на 01.05.2012) и 30 (на 07.05.2012)), то мне надо получить набор данных
01.05.2012 / 50
02.05.2012 / 50
03.05.2012 / 50
04.05.2012 / 50
05.05.2012 / 50
06.05.2012 / 50
07.05.2012 / 30
08.05.2012 / 30
09.05.2012 / 30
10.05.2012 / 30
11.05.2012 / 30 -- тянуть до сегодняшнего дня, если остаток есть

Собственно все, что написано после контрольной точки это попытка получить подобный список. В общем-то я его получил, но хочется больше скорости.
11 май 12, 09:24    [12534067]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
В общем-то я его получил, но хочется больше скорости.
В том то и дело, важно как вы это заархитектурили.
У вас логический SEEK, а нуна Stream.
11 май 12, 12:14    [12535312]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
так а что понадобится в логике приложения выдавать всегда запрос за тот диапазон дат которые есть в dimDate? Использование дополнительных фильтрующих условий значительно уменьшит количество обрабатываемых записей. Подумай, как можно ограничить используемый диапазон дат: таблица с уже посчитанными остатками на каждый день(дополнять эту таблицу по мере необходимости), ввод дополнительных фильтров по дате и т.д.
11 май 12, 12:45    [12535603]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Ну можно FactStocksPerDay создать как индексированное представление(задается тем самым запросом который смущает + можно наверное не создавать DateGaps, а прямо в обьявлении вьюшки просчитать DateGaps ) и данные там будут обновлятся автоматически, но операции изменения в FactStocks замедлятся.
11 май 12, 13:04    [12535731]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior,

вот архитектура:
вместо индексированной вьюхи использую таблицу.
--insert groupped movement into table dbo.FactStocks
SELECT
	  ProductKey
	, StoreKey
	, CellKey
	, ReclamationKey
	, DateFinancialKey
	
	, Qty		= SUM(Qty)
	, BeginDayQty	= CAST(NULL AS DECIMAL(16,4))
	, EndDayQty	= CAST(NULL AS DECIMAL(16,4))

	, CostAmount		  = SUM(CostAmount)	
	, BeginDayCostAmount      = CAST(NULL AS MONEY)
	, EndDayCostAmount	  = CAST(NULL AS MONEY)	

  INTO dbo.FactStocks
  FROM dbo.movement
  WHERE DateFinancialKey >= 20120101  --sample
  GROUP BY ProductKey, StoreKey, CellKey, ReclamationKey, DateFinancialKey

CREATE CLUSTERED INDEX [ClusterIdx] ON [dbo].[FactStocks] 
(
	[ProductKey] ASC,
	[StoreKey] ASC,
	[CellKey] ASC,
	[ReclamationKey] ASC,
	[DateFinancialKey] ASC
)
ON [PRIMARY]

Дальше прогоняется скрипт, который заполняет накопительный итог для каждой строки в разрезе ключей
ProductKey, StoreKey, CellKey, ReclamationKey

Получается картинка, которую я уже приводил в первом посте:
Картинка с другого сайта.

Имея на руках такую таблицу мне нужно растиражировать остатки на те даты, когда они были в наличии. Если какой-то остаток кончился скажем 18-го апреля, то дублировать строку с нулевыми остатками не нужно.
Как использовать вашу функцию я не понимаю -( Может тут секрет как раз в индексированной вьюхе, не приходилось с ними работать.
11 май 12, 14:18    [12536478]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Мистер Хенки
так а что понадобится в логике приложения выдавать всегда запрос за тот диапазон дат которые есть в dimDate? Использование дополнительных фильтрующих условий значительно уменьшит количество обрабатываемых записей. Подумай, как можно ограничить используемый диапазон дат: таблица с уже посчитанными остатками на каждый день(дополнять эту таблицу по мере необходимости), ввод дополнительных фильтров по дате и т.д.


Нет, весь диапазон dimDate не нужен в том плане, что в таком случае будет просто куча нулевых строк, которые не нужны, это просто взорвет таблицу. Не нужна инфа по всем сочетаниям товаров, складов, ячеек и других возможных ключей за Каждый день - там 90% нулей будет. Но те дни, когда остатки были в системе нужны в полном объеме.
Основное назначение такой портянки - таблица фактов для olap + хотелось бы иметь возможность получать остатки за периоды средствами sql, типа среднедневные остатки за произвольный период по произвольным срезам.
11 май 12, 14:30    [12536605]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
PassedBI
Мистер Хенки
так а что понадобится в логике приложения выдавать всегда запрос за тот диапазон дат которые есть в dimDate? Использование дополнительных фильтрующих условий значительно уменьшит количество обрабатываемых записей. Подумай, как можно ограничить используемый диапазон дат: таблица с уже посчитанными остатками на каждый день(дополнять эту таблицу по мере необходимости), ввод дополнительных фильтров по дате и т.д.


Нет, весь диапазон dimDate не нужен в том плане, что в таком случае будет просто куча нулевых строк, которые не нужны, это просто взорвет таблицу. Не нужна инфа по всем сочетаниям товаров, складов, ячеек и других возможных ключей за Каждый день - там 90% нулей будет. Но те дни, когда остатки были в системе нужны в полном объеме.
Основное назначение такой портянки - таблица фактов для olap + хотелось бы иметь возможность получать остатки за периоды средствами sql, типа среднедневные остатки за произвольный период по произвольным срезам.

ну попробуй индексированное представление на основе FactStocks и dimDate. За счет индекса получится "развиртуализированное" представление и данные там будут актуализироваться автоматически. Либо можно создать таблицу как ты делал и добавлять туда данные за период. Скажем раз в неделю или квартал или еще как то. Тогда и количество записей участвующих в запросе будет не таким угрожающим и операция будет выполнятся достаточно редко, чтоб не очень заморачиваться на ее производительности.
11 май 12, 15:36    [12537337]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI, правильно ли я понимаю что вам надо один раз заполнить таблу, а вот поддерживать актуальность вы её бедуте по другому (триггера)?

0. Если так, то какая разница как. Берёте по кускам, делаете сразу остатки без всяких промежуточных движений на день (или с).
Хоть треугольным запросом. Окном, месяц за месяцем.

Если нет, то стоит вопрос о механизме использовании остатков.
1. Если в системе не надо использовать остатки на любой момент, а только на "операционные дни", то банально индексированная вьюха на треугольный запрос OperationDaty * Movements [ * ControlDay].
2. Если надо оперативно юзать по чёрному остатки на гипотетически любой день, то не советую делать "дырки", SEEK вас погубит.
Т.е. подсчитать заново остатки будет быстрее, чем для каждого продукта получить:
(SELECT Top(1) ... WHERE Date <= @Date ORDER BY Date DESC)
-- Или
JOIN (SELECT Max(Date) ...
Проверено опытом.
Сделать 1000 SEEK чаще медленее чем Stream Aggregate для 100000 строк.

Поэтому я привёл фунцию к рассмотрению. Только надо написать для каждого вида запроса отдельный запрос.
Т.е. для получения движений за период и т.п. нужны свои запросы. Но они в основном будут быстрее, чем запросы на остатках.
11 май 12, 19:20    [12538752]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
В идеале конечно да - один раз заполнил, дальше каждую ночь догружаются измениния + новые движения, из них обновляются остатки. Честно сказать я пока не отрабатывал механизм инкрементного обновления, хотел сначала алгоритм заполнения реализовать. На практике же одним заполнением будет не обойтись, должна быть возможность пересоздания таблицы с нуля в разумные временные рамки. Могут просто новый ключ добавить в анализ и надо будет все заново считать.
Вот общая схема использования таблицы с предрассчитанными остатками:
1) Первая цель - скулевые запросы. К такой таблице можно направить запрос на получение остатков за любой день по любому разрезу (если записей нет, значит остатков не было). Ок, это можно легко получить прямо из таблицы движений - на конкретный день сработает довольно быстро простое суммирование до запрашиваемой даты. Сложнее уже со среднедневными остатками за произвольный период, ну скажем надо показать какой среднедневной остаток был в феврале, марте и апреле или в неделях 14, 15, 16 опять таки для произвольного набора ключей. Для этого нужно получить сумму остатоков за каждый день внутри заданного периода и разделить их на кол-во дней, когда эти остатки были. Тут наверно возможны варианты, не высчитывающие остатки на каждый день, а только на первый, дальше через движения получать последующие, но с такой таблицей на руках расчет был бы куда проще и быстрее, имхо.
2) Вторая цель - использование в качестве таблицы фактов при построении OLAP куба. Из этой таблицы можно было бы дергать остатки как физические меры с агрегацией FirstChild и LastChild, не используя mdx для их расчета. Еще одной физической мерой с агрегацией AverageOfChildren получаются те самые среднедневные остатки в любых разрезах и иерархиях даже при мультиселекте. Опять таки в SSAS есть подходы, вычисляющие остатки на дату, имея в основе только таблицу движений, но тут уже не все так гладко со среднедневными + мультиселект. Ну и очень интересно было бы сравнить производительность таких подходов.
В общем для olap нужны остатки на каждый день когда они были.

Но чувствую я, что слишком усложняю реализацию и похоже лучшим решением будет разделить цели и использовать для каждой более жизнеспособные решения =)
11 май 12, 23:24    [12539569]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
В идеале конечно да - один раз заполнил, дальше каждую ночь догружаются измениния + новые движения, из них обновляются остатки. Честно сказать я пока не отрабатывал механизм инкрементного обновления, хотел сначала алгоритм заполнения реализовать.
В том то и дело что декларативные индексированные представления (далее ИП) решают и эту задачу. Вам не надо думать что после заливки данных, надо что-то там ещё делать. Оно уже есть.

PassedBI
На практике же одним заполнением будет не обойтись, должна быть возможность пересоздания таблицы с нуля в разумные временные рамки.
И здесь ИП работают неплохо, и я уже упомянул как:
Mnior
OperationDaty * Movements [ * ControlDay].
Т.е. вы прямо в ней указываете условие диапазона индексирования:
JOIN dbo.OperationDay D ON D.Date = X.Date AND D.ControlField = 1
После создания ИП вы в цикле небольшими пачками устанавливаете это поле, и все ваши ИП будут заполнятся:
SELECT @To = 0
WHILE (@@RowCount > 0) BEGIN
	SELECT	 @From	= @To
		,@To	= DateAdd(Month,1,@To)

	UPDATE	dbo.OperationDay
	SET	ControlField = 1
	WHERE	    Date >= @From
		AND Date <  @To
END


PassedBI, для заполнения OLAP-а вам не нужны таблицы. Т.е. это не тупое чтение из таблиц. Заполнение (за какой-то период) это одноразовая работа, а таблица, это хранение, для многократного (очень частого) использования. Для заполнения OLAP-а используют запросы.

Не знаток я в парадигме OLAP, но мне казалось, что для него нужны только первичные данные, а всё остальное он может и сам посчитать.
Остаток - это вторичные данные, это тупо инкремент операций. Посему для меня вопрос, почему в него вы тупо не записываете движения?
Или OLAP может только статистику и разрезы показывать, а следствия не могёт (не эффективно)?

PassedBI
Сложнее уже со среднедневными остатками за произвольный период, ну скажем надо показать какой среднедневной остаток был в феврале, марте и апреле или в неделях 14, 15, 16
1. Ваши остатки c "дырками" убийственное решение для этой задачи. Seek Bomb
2. Нужно понимать всё досконально как оно работает
3. К математике это относится так же
4. Разве OLAP этим не занимается?
5. Операции совершаются в момент времени. Какая формула для "остатка дня"?
6. Думать надо всегда и для всего.

PassedBI
Тут наверно возможны варианты, не высчитывающие остатки на каждый день, а только на первый, дальше через движения получать последующие
Как я безумно рад читать это.

Средний-показатель = показатель на начало + сумма изменений * на оставшийся диапазон.
Если опустить вопрос с "моментом времени" и дискретизация операций - день, то:
SELECT	  Sum(CASE WHEN Date <  @From THEN Amount END)	-- AS RestBegin
	+ Sum(CASE WHEN Date >= @From THEN Amount * DateDiff(Day,Date,@To) END)
	/ DateDiff(Day,@From,@To)			-- AS AvgPeriod
FROM	dbo.Movements
WHERE	Date < @To
RestBegin Можно подсчитать отдельно, по функции, приведённой ранее. Это будет работать в основном намного быстрее.

PassedBI
но с такой таблицей на руках расчет был бы куда проще и быстрее, имхо.
Не проще и не быстрее.
Проблема в том и состоит, чтобы сказать проще/быстрее/лучше надо их сравнить. А что делают в основном наши горе "програмисты". Правильно - болтологией, или подразумевают под "проще" - "не думать", т.е. лень. Согласен, homo sapiens стремится думать как можно меньше. Лень и глупость здесь синонимы.

В данном случае есть одна офигенная вещь - память. Один раз подумав заранее вы перестаёте думать потом, вы уже просто знаете. ;)
Думаю вы понимаете что это конечно обман, но думать заранее всё равно надо.

PassedBI
Из этой таблицы можно было бы дергать остатки как физические меры с агрегацией FirstChild и LastChild, не используя mdx для их расчета. Еще одной физической мерой с агрегацией AverageOfChildren получаются те самые среднедневные остатки в любых разрезах и иерархиях даже при мультиселекте. Опять таки в SSAS есть подходы, вычисляющие остатки на дату, имея в основе только таблицу движений, но тут уже не все так гладко со среднедневными + мультиселект. Ну и очень интересно было бы сравнить производительность таких подходов.
Это безумно интересно, и скорее всего у вас есть одна идея говорящая почему возможно так. Вы не хотели бы её сформулировать словами и озвучить ?

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

Другое дело можно и микроскопом гвозди забивать. А более поверю в это, т.к. OLAP обычно ближе к маркетологам.
Не надо быть 7ми пядей во лбу, чтобы ежедневно наблюдать картину, как гуманитарии возбуждаются от "мифических" с их точки зрения явлений и утверждений, с диким визгом передавая слухи друг другу теряя всякий здравый смысл при этом. Гуманитарий и больной извращенец здесь синонимы.
12 май 12, 17:33    [12543796]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
PassedBI,

Фигней занимаетесь. На ваших незначительных объемах данных для расчета остатков в ОЛАПе вполне можно использовать движения. Если хотите - посчитайте месячные срезы, будет работать чуть быстрее. Средний остаток также считается в кубе методом ввода фиктивной sum-меры и присвоением ей в scope расчитанного остатка. Затем эту меру делите на показатель "количество дней", который в свою очередь считается на таблице-календаре (отдельная группа мер).
12 май 12, 20:31    [12544309]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
Если sql-запросы к готовой таблице очень нужны, то делаете процедуру, которая считает остатки для складов(StoreKey) запросом или в цикле (собственно, не важно как, на ваших объемах будет быстро и то и то).

У процедуры входной параметр i = (0,1,2,3...N-1), где N - число процессоров. Одновременно запускаются N копий процедуры, каждая со своим параметром. Каждая процедура обрабатывается свои склады, например так "склад%параметр = i", то есть на основе остатка от деления.

Плюс должна быть служебая табличка для пересчета: "StoreKey, ДатаНачалаРасчета, ДатаОкончанияРасчета". Последний столбец не обязателен, если его нет, то считать "до сегодня". По ней же можно будет отслеживать прогресс расчета и прогнозировать его время.
12 май 12, 20:47    [12544356]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Как мне тут нашептал один не специалист по олапу. Всё дело в волшебных пузырьках - если в кубике нормально заранее настроить нужные вью, то будут у вас нужные остатки сразу же после наполнения нативно. Не думаю что олапик заполняет их не эффективно.
Так что не мучайте бедный скуль и нормально разберитесь с олапом.
Как грится, не стоит незнание в одной области компенсировать незнаниями в другой.
Шутка.
14 май 12, 22:37    [12553530]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Итак, по порядку. Попробовал индексированные представления, но пока неудачно. Начну с тестовых примеров:
USE tempdb;
GO;
--Тестовые проводки
CREATE TABLE [dbo].[Movement] (
	 [ID]		INT	IDENTITY CONSTRAINT [PK_Movement]		PRIMARY KEY
	,[Invoice]	INT	NOT NULL
	,[DATE]		DATE	NOT NULL
	,[Amount]	MONEY	NOT NULL
)
GO;
--заполнение тестовыми данными, от руки чтоб точно проверить расчеты
INSERT INTO Movement (Invoice, DATE, Amount)
VALUES (1, '20120401', 100), (1, '20120402', -5), (1, '20120405', -15), (1, '20120420', 10), (1, '20120425', -40) 
	,(2, '20120402', 20), (2, '20120406', 5), (2, '20120417', -10), (2, '20120428', 20), (2, '20120505', 10)
	,(3, '20120223', 800), (3, '20120406', -150), (3, '20120517', -100), (3, '20120628', 200), (3, '20120705', -500)
	,(4, '20120102', 150), (4, '20120106', 50), (4, '20120217', -100), (4, '20120320', 200), (4, '20120404', 100)
	,(5, '20120308', 98), (5, '20120316', 102), (5, '20120317', -17), (5, '20120402', 20), (5, '20120405', -10)
GO
-- Группировка по дню	
CREATE VIEW [dbo].[vwMovementInvoiceDay] WITH SCHEMABINDING AS
SELECT	 Invoice
	,[DATE]
	,SUM(Amount)	AS Amount
	,COUNT_BIG(*) AS cnt	--allow clustered index to be created
FROM	dbo.Movement
GROUP BY Invoice	,[DATE]
GO	
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Invoice]
	,[DATE]
) ON [PRIMARY]

Про остатки вопросы.
--Запрос остатков
--sql 2012 only, doesn't work on sql 2008 r2
SELECT	 Invoice
	,[Date]
	,Sum(Amount)OVER(PARTITION BY Invoice ORDER BY [Date])	AS Rest		
FROM	dbo.vwMovementInvoiceDay

--sql 2008
--как для 2008 вернуть остакти в каждой строке? старые подходы с джойнами и сабселектами?

--для конкретной даты так:
DECLARE @restDate DATETIME	--дата на которую надо достать остатки
SET @restDate = '20120501'
SELECT	 Invoice
	,restAtDayBegin = SUM(CASE WHEN [DATE] < @restDate THEN amount END)
	,restAtDayEnd = SUM(CASE WHEN [DATE] <= @restDate THEN amount END)
FROM	dbo.vwMovementInvoiceDay
WHERE [DATE] <= @restDate
GROUP BY Invoice

--так же можно достать из проводок. indexеd view для такого запроса нет смысла держать
GO
DECLARE @restDate DATETIME	--дата на которую надо достать остатки
SET @restDate = '20120530'
SELECT	 Invoice
	,restAtDayBegin = SUM(CASE WHEN [DATE] < @restDate THEN amount END)
	,restAtDayEnd = SUM(CASE WHEN [DATE] <= @restDate THEN amount END)
FROM	dbo.Movement
WHERE [DATE] <= @restDate
GROUP BY Invoice

Про функцию неясно.
--для начала создать вьюху vwMovementInvoiceMonth - группировка по месяцам (по последнему дню месяца)
CREATE VIEW [dbo].vwMovementInvoiceMonth WITH SCHEMABINDING AS
SELECT	 Invoice
	,[DATE] = DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY([DATE]),[DATE])))
	,SUM(Amount)	AS Amount
	,COUNT_BIG(*) AS cnt	--allow clustered index to be created
FROM	dbo.Movement	
GROUP BY Invoice
	,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY([DATE]),[DATE])))
GO	
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceMonth] ON [dbo].[vwMovementInvoiceMonth] (
	 [Invoice]
	,[DATE]
) ON [PRIMARY]

Дальше либо я не понимаю скрытого смысла функции, либо вы там ошиблись и вместо month union day написали month union month.
Все же наверно там заложена логика использовать агрегаты, расчитанные по месяцам + добавить дни из последнего месяца. Т.е. так
CREATE FUNCTION [dbo].[fnInvoiceRest] (
	@restDate	DATE
) RETURNS TABLE AS RETURN
WITH MonthsAndDays AS (
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [DATE] < @restDate
UNION ALL
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceDay WHERE [DATE] < @restDate AND [DATE] >= DATEADD(DAY,1-DAY(@restDate),@restDate)
)	SELECT	 Invoice
		,SUM(Amount)	AS Amount
	FROM	MonthsAndDays
	GROUP BY Invoice
GO

Поробуем запросить остаток:
select * from fnInvoiceRest('20120530')

Результаты выданы, но вот план..
Картинка с другого сайта.
Картинка с другого сайта.
WTF? почему не используется месячная вьюха?
Даже прямой запрос
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < '20120530'

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

Mnior
1. Если в системе не надо использовать остатки на любой момент, а только на "операционные дни", то банально индексированная вьюха на треугольный запрос OperationDaty * Movements [ * ControlDay].
Т.е. вы прямо в ней указываете условие диапазона индексирования:
JOIN dbo.OperationDay D ON D.Date = X.Date AND D.ControlField = 1
После создания ИП вы в цикле небольшими пачками устанавливаете это поле, и все ваши ИП будут заполнятся:
SELECT @To = 0
WHILE (@@RowCount > 0) BEGIN
	SELECT	 @From	= @To
		,@To	= DateAdd(Month,1,@To)

	UPDATE	dbo.OperationDay
	SET	ControlField = 1
	WHERE	    Date >= @From
		AND Date <  @To
END


Это тоже попробовал - тут все работает, классно, индекс заполняется порциями. Только какая польза, если индекс заполнен за какой-то период, ведь для вычисления потребуется все прошлые данные. Разве что держать остатки по месяцам, а по дням только за последнее время, если есть уверенность, что не понадобятся остатки по дням за более старый срок. А если индекс таки должен быть заполнен полностью, то что дает заполнение окнами или их можно запускать паралельно?

Mnior
PassedBI, для заполнения OLAP-а вам не нужны таблицы. Т.е. это не тупое чтение из таблиц. Заполнение (за какой-то период) это одноразовая работа, а таблица, это хранение, для многократного (очень частого) использования. Для заполнения OLAP-а используют запросы.

Разумеется, только запросы куда?
Mnior
Не знаток я в парадигме OLAP, но мне казалось, что для него нужны только первичные данные, а всё остальное он может и сам посчитать.
Остаток - это вторичные данные, это тупо инкремент операций. Посему для меня вопрос, почему в него вы тупо не записываете движения?
Или OLAP может только статистику и разрезы показывать, а следствия не могёт (не эффективно)?

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

Mnior
1. Ваши остатки c "дырками" убийственное решение для этой задачи. Seek Bomb
2. Нужно понимать всё досконально как оно работает
3. К математике это относится так же
4. Разве OLAP этим не занимается?
5. Операции совершаются в момент времени. Какая формула для "остатка дня"?
6. Думать надо всегда и для всего.

1. я покажу, что это не так.
4. олап олапом, у него своя область применения. остатки же бывают нужны и через sql
5. верно, есть 2 понятия - остаток на начало и остаток на конец. Для среднедневных остатков используется формула (сумма остатков на начало каждого дня + остаток на конец последнего дня) / (кол-во дней в периоде +1). Для одного дня это будет (остаток на начало + остаток на конец)/2

Mnior
Средний-показатель = показатель на начало + сумма изменений * на оставшийся диапазон.
Если опустить вопрос с "моментом времени" и дискретизация операций - день, то:
SELECT	  Sum(CASE WHEN Date <  @From THEN Amount END)	-- AS RestBegin
	+ Sum(CASE WHEN Date >= @From THEN Amount * DateDiff(Day,Date,@To) END)
	/ DateDiff(Day,@From,@To)			-- AS AvgPeriod
FROM	dbo.Movements
WHERE	Date < @To
RestBegin Можно подсчитать отдельно, по функции, приведённой ранее. Это будет работать в основном намного быстрее.

Интересная формула, спасибо. Если поднять вопрос с моментом времени, то
SELECT	  Sum(CASE WHEN Date <  @From THEN Amount END)	-- AS RestBegin
	+ Sum(CASE WHEN Date >= @From THEN Amount * DateDiff(Day,Date,@To) END)
	/ DateDiff(Day,@From,@To+1)			-- AS AvgPeriod
FROM	dbo.Movements
WHERE	Date < @To

Пока с остатком через вьюхи не сложилось, натравил на проводки, что-то около минуты на запрос. Вполне себе вариант.

Mnior
PassedBI
но с такой таблицей на руках расчет был бы куда проще и быстрее, имхо.
Не проще и не быстрее.
Проблема в том и состоит, чтобы сказать проще/быстрее/лучше надо их сравнить. А что делают в основном наши горе "програмисты". Правильно - болтологией, или подразумевают под "проще" - "не думать", т.е. лень. Согласен, homo sapiens стремится думать как можно меньше. Лень и глупость здесь синонимы.

Если бы мне было лень, этого поста бы тут не было.
Посмотрим на расчет с такой таблицей на руках. Не со всей, а с ее частью, заполненной с начала 2012г. В ней порядка 16млн записей, после заполнения дырок + кластерный индекс по дате.
SELECT (SUM(BeginDayCostAmount)+
		SUM(CASE WHEN DateFinancialKey = 20120229 THEN EndDayCostAmount ELSE 0 END))
	/(20120229+2-20120201) 
  FROM FactStocks
  WHERE DateFinancialKey BETWEEN 20120201 AND 20120229

около 3 секунд на выполнение
Картинка с другого сайта.
Картинка с другого сайта.
Конечно, некорректно сравнивать таблицу, заполненную меньше чем на 5%, хотя если учесть, что было бы секционирование по годам.. Заполнять только ее небыстро относительно. К примеру таблица из примера выше создается с нуля минуты за 4 на однопроцессорном железе. Поддерживать ее конечно не так легко как indexed views, но вот использование уже готовой таблицы для меня более прозрачно. По скорости отклика тоже пока все ок. Однако делаю эти опыты скорее из любопытства и получения опыта, нежели для непременного практического их применения в промышленной среде.
Mnior
PassedBI
Из этой таблицы можно было бы дергать остатки как физические меры с агрегацией FirstChild и LastChild, не используя mdx для их расчета

Это безумно интересно, и скорее всего у вас есть одна идея говорящая почему возможно так. Вы не хотели бы её сформулировать словами и озвучить
Мне лично казалось, что OLAP внутрях так и работает, как показано выше. А всему виной должна быть его строгая математика. Так что я жду обоснований.

Еще раз - нет какой-то зацикленности почему надо сделать именно так, но я хотел попробовать так сделать, посмотреть как ведет себя подход с физическими остатками в большой таблице против вычислений по движениям - кто кого? Насчет внутренней работы олап - не думаю, что у нас хватит знаний вот так сходу обсуждать эту тему не на уровне абстракций. Единственное, что скажу - все таки олап многомерен, а скуль - нет. Как они будут работать по одной логике? И кстати, разве у скуля математика "нестрогая"?
Mnior
Другое дело можно и микроскопом гвозди забивать. А более поверю в это, т.к. OLAP обычно ближе к маркетологам.
Всерьез считаете SSAS маркетинговым продуктом?
Mnior
Как мне тут нашептал один не специалист по олапу. Всё дело в волшебных пузырьках - если в кубике нормально заранее настроить нужные вью, то будут у вас нужные остатки сразу же после наполнения нативно. Не думаю что олапик заполняет их не эффективно.
Можно пример как "настраивать нужные вью", чтобы сразу остатки получались, очень любопытно было бы взглянуть, возможно даже, что вы осчастливите целую ветку данного форума, где обсуждают олапы, или все это останется на уровне "сорока на хвосте принесла"?
15 май 12, 14:05    [12556522]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить