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

Откуда:
Сообщений: 323
Критик
Средний остаток также считается в кубе методом ввода фиктивной sum-меры и присвоением ей в scope расчитанного остатка.

Можно тут поподробней?
Вот что есть:
--Остатки на конец по движениям
([Дата].[День].[День], [Measures].[Last Q]) = SUM(NULL:[Дата].[День].CurrentMember,[Measures].[Qty]);  
--SUM(YTD(),[Measures].[Qty]); --можно использовать если есть годовые срезы

--на начало
([Дата].[День].[День], [Measures].[First Q]) = [Measures].[Last Q]-[Measures].[Qty];

Last Q и First Q - фиктивные меры в группе мер Движений с АФ LastChild и FirstChild соответственно. Добавил еще одну фиктивную меру Daily Avg Q (АФ = sum), как в нее подставить формулу
(сумма остатков на начало дней + остаток на конец дня) / (кол-во дней в периоде +1)

чтобы работал мультиселект и любая иерархия времени? Кол-во дней в периоде я посчитаю в отдельной группе мер, как вы и сказали. А вот суммы остатков не могу осилить.
15 май 12, 14:31    [12556802]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
треугольным запросом.
Memory Data Corrupted.
Был уверен на 200% что нахватался это выражение именно здесь на форуме.
Так я его не могу нигде найти.

Пипл, не дайте сойти с ума, как называется данная конструкция ?:
SELECT	 R.Product
	,R.Date
	,R.Amount
	,Sum(L.Amount)	AS Total
FROM	     dbo.Movements	R
	JOIN dbo.Movements	L ON L.Product  = R.Product
				 AND L.Date    <= R.Date -- L.ID <= L.ID
GROUP BY R.Product
	,R.Date
	,R.Amount
Было же где в FAQ описана. Где?
15 май 12, 16:38    [12558283]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Критик
Member

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

Точно так же, как вы считаете [Last Q] и [First Q]:
фиктивная мера [Daily Avg Q] = [Last Q] + [First Q]
CM [Daily Avg] = [Daily Avg Q]/[количество дней]
15 май 12, 16:53    [12558409]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Invoice]
	,[DATE]
) ON [PRIMARY]
Ну дык, базовые знания по индексам.
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Date]
	,[Invoice]
) ON [PRIMARY]
Или надо и сдесь разжевать?
PassedBI
Только какая польза, если индекс заполнен за какой-то период, ведь для вычисления потребуется все прошлые данные.
Так заполняйте все данные.
Блин, ну не тармозите пожайлуста.
Если у вас данные добавляются, то ничего делать ненадо вааще, индексированное представление само всё подсчитает.
Но если надо добавить колонку тотала, то естественно её же надо подсчитать с самого начала. Поэтому предстваление заполняется с нуля полностью.
PassedBI
И кстати, разве у скуля математика "нестрогая"?
Естсественно. Там же императивщина позволяется (порядок инстуркций, их ветвления, мутагенность и состояния).
Думаю это должно быть очевидно что даёт декларативное программирование. И в чём суть и сила формализма.
PassedBI
Всерьез считаете SSAS маркетинговым продуктом?
Ну думаю ани пользуются в основном. Могу ошибаться.
PassedBI
Можно пример как "настраивать нужные вью" ... или все это останется на уровне "сорока на хвосте принесла"?
С моих слов да, принесла. Спрашивайте у специалистов по OLAP-у.

PS: Ссори, нет времени на поный разбор полётов.
15 май 12, 17:00    [12558488]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Mnior
треугольным запросом.
Memory Data Corrupted.
Был уверен на 200% что нахватался это выражение именно здесь на форуме.
Так я его не могу нигде найти.

Пипл, не дайте сойти с ума, как называется данная конструкция ?:
SELECT	 R.Product
	,R.Date
	,R.Amount
	,Sum(L.Amount)	AS Total
FROM	     dbo.Movements	R
	JOIN dbo.Movements	L ON L.Product  = R.Product
				 AND L.Date    <= R.Date -- L.ID <= L.ID
GROUP BY R.Product
	,R.Date
	,R.Amount
Было же где в FAQ описана. Где?
Актуально.
15 май 12, 17:06    [12558532]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
Mnior,
накопительный итог
15 май 12, 17:19    [12558633]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Критик
Mnior,
накопительный итог
Агромное спасибо.
Memory Link was restored

Ну надо же было так проффтыкать. Не могу объяснить почему треугольный. По виду подсчёта так и выглядит.
А т.к. чем больше ассоциаций тем лучше помнишь. Может просто мысли тогда приходили всякие ... глупые.
Но осадок всётаки остался.

Ссори за беспокойство.
15 май 12, 17:53    [12558911]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

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

Точно так же, как вы считаете [Last Q] и [First Q]:
фиктивная мера [Daily Avg Q] = [Last Q] + [First Q]
CM [Daily Avg] = [Daily Avg Q]/[количество дней]


Написал так
--сумма остатков на начало всех дней в периоде
([Дата].[День].[День], [Measures].[Daily Avg Q]) = [First Q]; 

--СТЗ = (сумма остатков на начало дней + остаток на конец дня) / (кол-во дней в периоде +1)
CREATE [Measures].[Средний товарный запас] = 
IIF([Measures].[Daily Avg Q]+[Measures].[Last Q] = 0
 , NULL
 , ([Measures].[Daily Avg Q]+[Measures].[Last Q])
/
(1+[Measures].[Number of Days]));

Дает то что нужно, спасибо!
16 май 12, 10:21    [12560798]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior
Ну дык, базовые знания по индексам.
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Date]
	,[Invoice]
) ON [PRIMARY]

Это понятно, пробовал переставлять поля и до этого. Тут странность в чем - для дней, где простая группировка по [Date] вьюха запрашивается, и ей даже пофиг в какой последовательности поля в индекс забиты.
А для месяцев, когда группировать надо по последнему дню месяца
[Date] = DateAdd(Day,-1,Dateadd(Month,1,DateAdd(Day,1-Day([Date]),[Date])))

запрос
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < '20120530'
уже проваливается в скан движений. Почему?
16 май 12, 10:32    [12560876]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < '20120530'
уже проваливается в скан движений. Почему?
А вы на эту вторую вью индекс то повесили?

Рад что наконец по человечески через OLAP.
16 май 12, 13:44    [12562890]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
PassedBI
запрос
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < '20120530'
уже проваливается в скан движений. Почему?


Вероятно, что у вас основой массив данных лежит до 2012-05-30, а значит выгоднее сделать просканировать всю таблицу. Вобщем-то это нормально и документировано.
16 май 12, 13:51    [12562969]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
простой пример для тестирования:
use tempdb;

--движения
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	
FROM	dbo.Movement	
GROUP BY Invoice
	,[Date]
GO	
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay] ON [dbo].[vwMovementInvoiceDay] (
	 [Date], [Invoice]	--если поменять порядок, все равно вью используется
) on [primary]
GO
--запрос остатка
SELECT sum(Amount) FROM dbo.vwMovementInvoiceDay WHERE [Date] < '20120229'
GO

--вьюха по месяцу
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	
FROM	dbo.Movement	
GROUP BY Invoice
	,DateAdd(Day,-1,Dateadd(Month,1,DateAdd(Day,1-Day([Date]),[Date])))	
GO	
--drop INDEX [PK_vwMovementInvoiceMonth] ON [dbo].[vwMovementInvoiceMonth]
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceMonth] ON [dbo].[vwMovementInvoiceMonth] (
	   [Date], [Invoice]
) on [primary]
GO
--запрос остатка
SELECT Sum(Amount) FROM dbo.vwMovementInvoiceMonth WHERE [Date] < '20120229'

/*
drop VIEW [dbo].[vwMovementInvoiceDay]
drop VIEW [dbo].[vwMovementInvoiceMonth]
drop TABLE [dbo].[Movement]
*/


Планы запросов
Картинка с другого сайта.
16 май 12, 14:58    [12563556]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Критик
Вероятно, что у вас основой массив данных лежит до 2012-05-30, а значит выгоднее сделать просканировать всю таблицу.
Вы издеваетесь? Просканировать 10 строк из view хуже, чем просканировать лимон из таблы?

PassedBI, не вижу команды обновления статистики.
Будет время буду проверять.

PassedBI
DateAdd(Day,-1,Dateadd(Month,1,DateAdd(Day,1-Day([Date]),[Date])))
Этого я не понял савсем.
Оно даёт начала последнего дня месяца. WTF?
У вас что-то с предаставлением о времени не то. Время непрерывно. Как только вы будете его нормально воспринимать, никаких танцев с будном в коде (+/- 1 день и т.п.) больше не будет.
Чую пахнет недобитым бухгалтером/маркетологом с гуманитарным складом ума (ссори за тавтологию).
16 май 12, 16:51    [12564516]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior,
Вы странный такой, то вроде умные вещи подкидываете, то чушь какую-то несете на ровном месте. С таким же успехом могу объявить вас пациентом известных заведений, с ясностью рассудка, зависящей от фазы луны.
Mnior
не вижу команды обновления статистики.

Какая статистика вам нужна? на индекс она есть.

Mnior
DateAdd(Day,-1,Dateadd(Month,1,DateAdd(Day,1-Day([Date]),[Date])))
Этого я не понял савсем.
Оно даёт начала последнего дня месяца. WTF?

Из фака
set nocount on
declare @d datetime
set @d=convert(char(8),getdate(),112)
select 'Дата ',@d
select  'последний день месяца',
dateadd(month,1,dateadd(day,1-day(@d),@d))-1

В моем примере (построенном на вашем), используется тип Date, к которому нельзя дописать -1, поэтому обернул в еще один DateAdd(Day,-1, @d).
Для чего? Это агрегирование движений по месяцам, завязанное на последний день месяца. Для чего? А иначе я не понимаю, что за объект vwMovementInvoiceMonth был в вашей функции использован и что она вообще делает тогда.
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
16 май 12, 17:25    [12564845]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
Mnior, Вы странный такой, ... , с ясностью рассудка, зависящей от фазы луны.
Не могу спорить. Но это долго описывать ... очень тяжело когда много всего в короткий промежуток времени
PassedBI
Какая статистика вам нужна? на индекс она есть.
Ну когдась выкладывают скрипты их выполняют скопом. Естественно что после команды CREATE INDEX у него нет статистики (как я помню). В добавок это как контрольный в голову.

Mnior
FAQ ...
В моем примере (построенном на вашем), используется тип Date, к которому нельзя дописать -1, поэтому обернул в еще один DateAdd(Day,-1, @d).
Для чего? Это агрегирование движений по месяцам, завязанное на последний день месяца. Для чего? А иначе я не понимаю, что за объект vwMovementInvoiceMonth был в вашей функции использован и что она вообще делает тогда.
MultipleMistakes
Явное недопонимание и разрыв шаблонов.
+ Обисняю как трёхлетнему.
1. Время (как и пространство) непрерывно, оно не меряется в штуках.
2. Поэтому есть только диапазоны.
3. При обозначении одной величины (2012-05-16 21:10:34) понятно что она является границей двух диапазонов
4. Поэтому для исключение перекрытия (дублирования) принято что эта точка принадлежит одному диапазону (не принадлежит другому)
5. Чтобы не было путаницы все диапазоны одинаковы по принципу описания: к примеру с левой стороны точка принадлежит диапазону, а с правой стороны нет ("выколотая" точка). Обычно так
6. Есть точность описания/указания/измерения (ибо описание конечно)
7. DateTime (точность 1/3 секунды), SmallDateTime(точность 1/3 минуты), Date (точность день)
8. Момент времени для данных величин подразумевает начало к этой точности (рассуждения про диапазоны)
9. При указании диапазона указывается с одной стороны точка, которая включена в него, а с другой выколота
10. Патамушто невозможно описать бесконечное написание окрестности выколотой точки.
11. Иначе (.999) будут разрывы с потерей данных.

Совершенно не важно, в каких точных типах вы описываете время, всё равно вы описываете момент времени. Вы не можете указать полдень или вечер в типе Date. Только полночь, начало новых суток.

Когда говорят на человеческом, т.е. неформальном языке выражение "остатки на конец месяца", формально это означает остатки на полночь между последним днём месяца и началом первого дня последующего, т.е. логически это означает тоже самое что "остатки на начало следующего месяца".

Когда говорят диапазон, то чаще подразумевают опять таки включение, иногда это оговаривают. С 1-го по 5-ое (включительно). Но формально это с "2012-05-01 00:00" по "2012-06-01 00:00" (не включительно).

Поэтому всегда пишут так:
    @From <= @Date
AND @To   >  @Date
И только так, и других правил нет.

У вас в данных показывается "28.04.2012". Это означает не на "конец месяца", а на конец дня "27.04.2012" или начало "28.04.2012".
Вот как работает:
DECLARE  @Date	Date	= '20120428'
	,@DateF	DateTime= '20120427 21:00'
	,@DateT	DateTime= '20120428 04:00'
IF (@DateF <= @Date) AND (@Date < @DateT) PRINT 'WTF?' -- Разрыв шаблона
Бывает два случая. Когда показывается момент и когда подразумевается диапазон.
К примеру, остатки это состояние и оно бывает только на момент. А движения это процесс, он определён только на диапазоне.
Иногда нет смысла указывать оба конца диапазон, например когда длины всех диапазонов одинакова. К примеру день.
Тогда указывают только начало.

Когда говорится, остатки на момент X это означает сумма всех движений ДО данного момента, не включительно.
Если будут для периодов использовать другие правила чем для моментов, то будет ужасно неудобно все это писать и каждый раз учитывать.

Когда пишут <= '2012-02-29', видно что выражение сильно зависит от выбранной системы мер. Если будет день с концом, то одно, если время, то другое.
Но когда пишут < '2012-03-01' совершенно неважна точность и система мер. Можно смело заменить Date на DateTime в такой системе и всё всё равно будет работать как надо.

Поэтому, во вью с dbo.vwMovementInvoiceDay мы тупо отсекаем время DateTime -> Date (начинается с 0).
Для dbo.vwMovementInvoiceMonth мы должны тупо отсекать дату (со временем конечно) (начинается с 1).
Для dbo.vwMovementInvoiceYear мы должны тупо отсекать месяц (с датой и со временем конечно) (начинается с 1).

Не путайте в dbo.vwMovementInvoiceDay указаны обороты за этот день
в dbo.vwMovementInvoiceMonth обороты за этот месяц
в dbo.vwMovementInvoiceYear обороты за этот год
Это не остатки.

PassedBI
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.vwMovementInvoiceDay   WHERE [Date] < @Date AND [Date] >= DateAdd(Day,1-Day(@Date),@Date)
)	SELECT	 Invoice
		,Sum(Amount)	AS Amount
	FROM	MonthsAndDays
	GROUP BY Invoice
GO
Очепятка C&P.
Надеюсь все полноценно понимают зачем я отделил месячные обороты?
------------------------------------------------
Ссори, по вопросу скана таблы (статистики) пока не могу ответить (нет времени ). Но это важно.
Заметка: есть управляющее слово: NOEXPAND. Т.е. можно банально вписать, если разбираться вам будет лень. Для всех не Enterprise редакций это слово обязательно указывать (не знаю как на новых версиях).
16 май 12, 23:46    [12566581]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

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

Про статистику - у меня автоматом создается при создании индекса. Попробуйте, может и у вас получится. Как вариант проверить опцию базы AUTO_CREATE_STATISTICS - по дефолту она включена.
Про время - вы объясняете очевидные вещи, даже не знаю зачем вы при вашей занятости столько текста набили, лучше бы скрипт запустили. Еще и про точность типов DateTime и SmallDateTime наврали.

DECLARE  @Date	Date	= '20120428'
	,@DateF	DateTime= '20120427 21:00'
	,@DateT	DateTime= '20120428 04:00'
IF (@DateF <= @Date) AND (@Date < @DateT) PRINT 'WTF?' -- Разрыв шаблона
Да у меня прям шаблон разорвало от такого примера, а если бы я узнал, что
'20120427 21:00' <= '20120428' AND '20120428' < '20120428 00:00:00.003'
то, видимо, убежал бы в закат, дико хохоча
Mnior
Не путайте в dbo.vwMovementInvoiceDay указаны обороты за этот день
в dbo.vwMovementInvoiceMonth обороты за этот месяц
в dbo.vwMovementInvoiceYear обороты за этот год
Это не остатки.

Значит, я все правильно понял. Для запроса остатка за конкретный день можно использовать 3 вьюхи: из годовой вытащить обороты на начало года запрашиваемой даты, из месячной - с начала года до начала месяца, из дневной уже с начала месяца до самой даты (если на начало, то < @date, если на конец, то <= @date).
Теперь простой вопрос: у вас в vwMovementInvoiceMonth в поле [Date] что лежит? Скажем для апреля 2012 года.
Я использовал последний день месяца в качестве ключа для этой вьюхи, т.е. '20120430' - по-моему, для формы запроса в вашей функции это подходит идеально. Можно использовать и первый день месяца, и INT 201204 и еще много вариантов, но все это по сути не имеет никакого значения, просто для входящей даты, надо будет делать такие же преобразования ключа, а в случае с последним днем этого не требуется.
17 май 12, 09:41    [12567348]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
Mnior
Естественно что после команды CREATE INDEX у него нет статистики (как я помню). В добавок это как контрольный в голову.


Обманываешь.
Сиквел создает автоматически статистику для каждого создаваемого индекса:

USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL
	DROP TABLE dbo.test
CREATE TABLE dbo.test (id INT)
GO
INSERT INTO dbo.Test VALUES (1),(2),(3),(4)
GO
CREATE INDEX IX_test ON Test (id ASC)
GO

DBCC SHOW_STATISTICS ( test , IX_test )
17 май 12, 12:26    [12568872]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
при вашей занятости столько текста набили, лучше бы скрипт запустили.
Дома я за бубунтой не запущу. Не думаю что скуль можно под WINE запустить. А виртуалку не потяну. ДА и вообще не хочу с этим г..., с этой вяндой возиться.

PassedBI
Теперь простой вопрос: у вас в vwMovementInvoiceMonth в поле [Date] что лежит?
Начало периода, незвисимо от его длины (день/месяц/год). Всегда.

PassedBI
Скажем для апреля 2012 года.
2004-04-01
Т.е. 01 это нуль - начало периода.

PassedBI
Я использовал последний день месяца в качестве ключа для этой вьюхи, т.е. '20120430' - по-моему, для формы запроса в вашей функции это подходит идеально. ... но все это по сути не имеет никакого значения,
Вы написали "вы объясняете очевидные вещи" но вы не поняли и продолжаете делать неправильно.

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

Покажите обороты:
спо
2010-02-23 12:302012-02-28 12:30
2010-02-29 12:302012-02-29 12:30
2012-02-28 12:302013-02-04 12:30
2012-02-29 12:302013-02-04 12:30
Месячный оборот с датой "2010-02-29" мешает для обоих концов. Поэто либо будет неправильно работать запрос, или надо обе даты преобразовавать. Т.к. "2010-02-29" это не конец и не начало месяца, это где-то между. Между "2010-02-29 00:00:00.000" и "2010-02-29 23:59:59.999(9)".

А "2010-02-01" это строго начало (или конец предыдущего, вот именно здесь неважно!) Поэтому хватает написать строгое или не строгое неравенство.
Теперь понятно?

Не вижу реакцию на NOEXPAND.
17 май 12, 12:31    [12568917]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Andrey Sribnyak
Mnior
Естественно что после команды CREATE INDEX у него нет статистики (как я помню). В добавок это как контрольный в голову.
Обманываешь.
Сиквел создает автоматически статистику для каждого создаваемого индекса
Ну и не был я уверен.
Ссори.
17 май 12, 12:34    [12568955]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Неа, всётака я был прав. Статистика не нарабатывается полноценно.
Не та которая должна быть. Нужно или запросы часто запускать или создавать её самому или ставить правильные индексы.
+ Скрипты
USE tempdb
GO
CREATE TABLE [dbo].[Movement] (
	 [ID]		Int		IDENTITY
	 CONSTRAINT [PK_Movement]	PRIMARY KEY
	,[Invoice]	Int		NOT NULL
	,[Date]		DateTime	NOT NULL
	,[Amount]	Money		NOT NULL
)
GO
INSERT dbo.Movement 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 100
GO
CREATE VIEW [dbo].[vwMovementInvoiceDay] WITH SCHEMABINDING AS
SELECT	 DateAdd(Day,DateDiff(Day,0,[Date]),0)		AS [Date]
	,Invoice
	,Sum(Amount)					AS Amount
	,Count_Big(*)					AS [Count]
FROM	dbo.Movement	
GROUP BY DateAdd(Day,DateDiff(Day,0,[Date]),0)
	,Invoice
GO	
CREATE VIEW [dbo].[vwMovementInvoiceMonth] WITH SCHEMABINDING AS
SELECT	 DateAdd(Month,DateDiff(Month,0,[Date]),0)	AS [Date]
	,Invoice
	,Sum(Amount)					AS Amount
	,Count_Big(*)					AS [Count]
FROM	dbo.Movement
GROUP BY DateAdd(Month,DateDiff(Month,0,[Date]),0)
	,Invoice
GO	
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceDay]   ON [dbo].[vwMovementInvoiceDay]   ([Date], [Invoice])
CREATE UNIQUE CLUSTERED INDEX [PK_vwMovementInvoiceMonth] ON [dbo].[vwMovementInvoiceMonth] ([Date], [Invoice])
GO
SELECT Sum(Amount) FROM dbo.vwMovementInvoiceDay WHERE [Date] < '20110129'
GO
-- UPDATE STATISTICS [dbo].[Movement]
-- CREATE INDEX [IX_Movement] ON [dbo].[Movement] ([Date], [Invoice])
/*
DROP VIEW  [dbo].[vwMovementInvoiceMonth]
DROP VIEW  [dbo].[vwMovementInvoiceDay]
DROP TABLE [dbo].[Movement]
*/


Потыкал потыкал и вот результат:

К сообщению приложен файл. Размер - 64Kb
17 май 12, 14:01    [12569800]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

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

Начал было писать вам ответ, но лучше сначала уточню, может все таки удастся синхронизировать потоки сознания.
У вас в vwMovementInvoiceMonth для 2012-04-01 содержатся обороты за весь апрель (т.е. сумма движений за период с '2012-04-01 00:00:00.000' по '2012-04-30 23:59:59.998' включительно, если тип dateTime)? Или таки за март (определение периода аналогичное)?

Noexpand дал эффект, наконец-то вьюха запрашивается, теперь надо тестировать функцию на рабочих данных. На тестовых функция значительно проигрывает прямому суммированию движений, ну да данных мало. Поэтому то скуль ее автоматом и не использовал, и все таки это странно. Похоже, если группировать по полю, которое получается в результате каких-либо преобразований, то вьюха не используется. Блин но индекс же физически создан, выделены страницы под его данные, там же не нужно снова вычисления делать..
17 май 12, 14:20    [12570115]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
У вас в vwMovementInvoiceMonth для 2012-04-01 содержатся обороты за весь апрель Или таки за март
У меня за апрель (2012-04), (хотя я не буду спорить если будет за март (2012-03)). Кстати в XML есть такой тип xs:gYearMonth.

Про индекс писал выше.
17 май 12, 17:59    [12572413]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior
У меня за апрель (2012-04), (хотя я не буду спорить если будет за март (2012-03)). Кстати в XML есть такой тип xs:gYearMonth.

Про индекс писал выше.

В общем, если за апрель, то функция вернет некорректный результат, если за март, то все нормально. Про xs:gYearMonth, идея у типа такая же как если бы мы агрегировали по int ключу 201204, об этом тоже упоминал. Со статистикой надо будет поковыряться. Спасибо =)
17 май 12, 20:52    [12573120]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PassedBI
В общем, если за апрель, то функция вернет некорректный результат.
Что-то вы обленились.
Неужто я так "запресовал" что аргументы слетели в "установку на место"?

Да, согласен, проффтыкал. Опять таки, на коленке на скорую руку мог и ошибиться. Главное смысл показать.
CREATE FUNCTION [dbo].[fnInvoiceRest] (
	@Date	Date
) RETURNS TABLE AS RETURN
WITH MonthsAndDays AS (
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] <  DateAdd(Day,1-Day(@Date),@Date)
UNION ALL
	SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceDay   WHERE [Date] >= DateAdd(Day,1-Day(@Date),@Date) AND [Date] < @Date
)	SELECT	 Invoice
		,Sum(Amount)	AS Amount
	FROM	MonthsAndDays
	GROUP BY Invoice
GO
Да, теперь чувствуется очевидность.

PassedBI
Про xs:gYearMonth, идея у типа такая же как если бы мы агрегировали по int ключу 201204
Не очень понял причём тут агрегация. Я упомянул что просто существуют типы (в другом языке) с разной точностью. Чисто оффтопная заметка.
PassedBI
об этом тоже упоминал.
Про инт? Не очень понимаю почему на этот инт вообще так надо обратить внимание? В принципе совершенно неважно в каком типе хранится, там-то точное значение палюбэ.
В приведённом мной скрипте я вообще забыл привести к типу Date.
PassedBI
Со статистикой надо будет поковыряться.
Как я понимаю дело не в существовании, а в актуальности. Здесь я изначально неправильно выразился.

В добавок там куча тонких моментов с
AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC
И этого:
http://technet.microsoft.com/en-us/library/dd535534.aspx
Normally, statistics are not required on indexed views. This is because the substitution of the indexed views into the query plan is considered only after all the statistics for the underlying tables and indexes are attached to the query plan.


Но мне интересно как там в OLAP-е работают эти остатки. Шустро отрабатываются?
18 май 12, 02:10    [12573892]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по дням когда они были  [new]
PassedBI
Member

Откуда:
Сообщений: 323
Mnior
Что-то вы обленились.Неужто я так "запресовал" что аргументы слетели в "установку на место"?
Да уже замусолили эту тему с выбором точки времени в качестве ключа )) можно было и на примерах показать ошибку, но понадеялся, что и сами поймете.
Mnior
PassedBI
Про xs:gYearMonth, идея у типа такая же как если бы мы агрегировали по int ключу 201204
Не очень понял причём тут агрегация. Я упомянул что просто существуют типы (в другом языке) с разной точностью. Чисто оффтопная заметка.
PassedBI
об этом тоже упоминал.
Про инт? Не очень понимаю почему на этот инт вообще так надо обратить внимание?
Агрегация в том смысле, что вьюха содержит агрегированные данные за месяц и то, на какой ключ вешать эти агрегаты ('2012-04-01' или '2012-04-30' или 201204 или 'едрён-батон, седня ж апрель 12-го года' или тот же xs:gYearMonth если бы он тут был)..
Mnior
В принципе совершенно неважно в каком типе хранится..
вот именно. Упоминание:
PassedBI
Я использовал последний день месяца в качестве ключа для этой вьюхи, т.е. '20120430' - по-моему, для формы запроса в вашей функции это подходит идеально. Можно использовать и первый день месяца, и INT 201204 и еще много вариантов, но все это по сути не имеет никакого значения, просто для входящей даты, надо будет делать такие же преобразования ключа, а в случае с последним днем этого не требуется.
И как только вы признали ошибку, вы совершенно правильно добавили преобразование входящего параметра к ключу агрегации месячной вьюхи (в вашем случае - к первому дню месяца)
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] <  DateAdd(Day,1-Day(@Date),@Date)

Было бы другое значение ключа, надо было бы входящий параметр к нему приводить. При моем выборе (последний день месяца в качестве ключа) такое преобразование не требуется, будет работать и так
SELECT	 Invoice, Amount FROM dbo.vwMovementInvoiceMonth WHERE [Date] < @Date

Minor
Как я понимаю дело не в существовании, а в актуальности. Здесь я изначально неправильно выразился.

В добавок там куча тонких моментов с
AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS
AUTO_UPDATE_STATISTICS_ASYNC
И этого:
http://technet.microsoft.com/en-us/library/dd535534.aspx
Normally, statistics are not required on indexed views. This is because the substitution of the indexed views into the query plan is considered only after all the statistics for the underlying tables and indexes are attached to the query plan.
Спасибо за дополнения, буду проверять, но позже. Эта темка была весьма полезной, но сожрала немало рабочего времени =))
Minor
Но мне интересно как там в OLAP-е работают эти остатки. Шустро отрабатываются?
У меня пока тестовая площадка с усеченным набором данных и фиговым железом. Работает нормально, отклик конечно не мгновенный, но вполне приемлемый. Жду недостающих комплектующих для железки помощнее, вот там уже начнется настоящее тестирование на полных объемах, там и поглядим. Но должно быть нормально. Может интереса ради заполню свою таблицу "с дырками" и сравню скорость отклика, если время будет. Но это, факультативно, так сказать, не будем начинать все сначала
18 май 12, 10:22    [12574833]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить