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

Откуда: столичный город кудаблин
Сообщений: 619
Надо было назвать топик "Помогите написать простенький запрос", но я решил не расслабляться :)

Anyways. Есть худая но очень длинная талбичка, в которой лежат данные от измерительных устройств. Устройств много, чтения каждые пять мнут, данных поэтому тоже много. SQL Server 2008. Схема такая:

CREATE TABLE [dbo].[vData]
(
	[TRV_ID] [decimal](6, 0) NOT NULL, -- deviceID
	[DEF_ID] [varchar](20) NOT NULL,   -- readingID
	[DATE] [datetime] NOT NULL,
	[VALUE] [float] NULL
); 

За схему не пинать, в реальности она выглядит еще хуже, за миксирование оракловского стиля и sql стиля я бы сам руки отрывал, но вопрос не об этом.

Бизнес хочет различные отчеты по различным интервалам дат: hour, day, week, month, year. Пока я эти отчеты пишу, я подумываю о том, что бы их не пришлось через 3 месяца переписывать потому что они тормозят. Поэтому у меня есть пара вопросов:

1. Насколько эффективна конструкция вида на больших объемах данных?
	SELECT D.TRV_ID
		, CAST( D.[DATE] as date )
		, AVG( Value ) 
	FROM vData 
	WHERE D.[DATE] between '2009-01-21' and '2009-05-21'
	GROUP BY CAST( D.FullDate as date ), D.TRV_ID
Насколько дорогая операция кастинга? CAST( ... as date ) и стоит ли ей пользоватся в группинге больших объемов данных?

1.1. Задачу обрезания времени от даты я решил так cast( cast( D.FullDate as date ) as datetime ) - за это сразу руки отрывать или же пускай пока живет?

2.
написал такую функцию
CREATE FUNCTION [dbo].[fn_ProcessInterval] ( @aFullDate datetime, 	@aDate date = NULL, 	@aInterval varchar(20) )
RETURNS datetime
AS BEGIN
	IF @aDate is null 
	BEGIN 
		set @aDate = cast( @aFullDate as date ); 
	END; 

	IF @aInterval = 'hour' 
	BEGIN 
		RETURN DATEADD( hh, DATEPART( hh, @aFullDate), cast( @aDate as datetime ) ); 
	END 
	ELSE IF @aInterval = 'day' 
	BEGIN 
		RETURN cast( @aDate as datetime ); 
	END 
	IF @aInterval = 'week' 
	BEGIN 
		-- beginning of the week is monday around here!
		RETURN dateadd( dd, 2 - datepart( dw, @aDate), @aDate); 
	END
	IF @aInterval = 'month' 
	BEGIN 
		-- beginning of the month
		RETURN dateadd( dd, 1 - datepart( dd, @aDate), @aDate); 
	END 
	
	-- beginning of the year 
	RETURN dateadd( dd, 1 - datepart( dy, @aDate), @aDate); 
	
END

для использования следующим образом

select D.TRV_ID
	, D.DEF_ID 
	, dbo.fn_ProcessInterval( D.[Date], null, @interval )
	, min( D.Value ) as minValue
	, avg( D.Value ) as avgValue
	, max( D.Value ) as maxValue
from vData D 
where D.TRV_ID in ( select TrvID from vDevices where TrvID = 1001 or ParentID = 1001 ) 
and D.[Date] between @aDateFrom and @aDateTo
and D.Def_ID = '204a'
group by D.TRV_ID, D.DEF_ID, dbo.fn_ProcessInterval( D.[Date], null, @interval )

Вопрос: будет ли это эффективно на больших объемах данных? И как можно сделать лучше? В частности избежать кастинга туда сюда для каждой выбранной строки?

Cheers
Pete
3 июн 09, 13:41    [7260469]     Ответить | Цитировать Сообщить модератору
 Re: группировка по интервалу дат  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
B0rG
Бизнес хочет различные отчеты по различным интервалам дат: hour, day, week, month, year.
1) Почитайте про индексированные представления
Возможно, это как раз то, что Вы ищете...

2) Поговорите с DBA, которому предстоит эту БД администрировать
Расскажите про объемы данных и про природу этих данных
Возможно, будет принято решение секционировать таблицу, и задача Ваша несколько изменится
3 июн 09, 13:55    [7260526]     Ответить | Цитировать Сообщить модератору
 Re: группировка по интервалу дат  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Подумайте о таблице-календаре, бо вот это
group by D.TRV_ID, D.DEF_ID, dbo.fn_ProcessInterval( D.[Date], null, @interval )
не может не тормозить на больших объемах.
3 июн 09, 14:00    [7260549]     Ответить | Цитировать Сообщить модератору
 Re: группировка по интервалу дат  [new]
B0rG
Member

Откуда: столичный город кудаблин
Сообщений: 619
Паганель

2) Поговорите с DBA, которому предстоит эту БД администрировать
Расскажите про объемы данных и про природу этих данных
Возможно, будет принято решение секционировать таблицу, и задача Ваша несколько изменится


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

С момента начала операции в январе, база - 2.5 гига чистых данных и индексов, но файл уже подрос до 7.5 гигов.

Проект стартаповского размера, дба к нему не предусмотрен, весь штат - 2 эникейщика. В родительской конторе толкового дба тоже нет. Жизнь непроста, короче :)
3 июн 09, 14:59    [7260908]     Ответить | Цитировать Сообщить модератору
 Re: группировка по интервалу дат  [new]
B0rG
Member

Откуда: столичный город кудаблин
Сообщений: 619
Senya_L
Подумайте о таблице-календаре, бо вот это
group by D.TRV_ID, D.DEF_ID, dbo.fn_ProcessInterval( D.[Date], null, @interval )
не может не тормозить на больших объемах.


Народ в факе говорит, что функцию лучше инлайнить, но мне пока что этого делать не хочется.

С таблицей дат есть одна маленькая проблема - считывание данных от девайсов по спецификации каждые 5 минут, но в реальная ситуация совсем иная.

Хмм... Хотя если подумать, что нибудь вроде

tblDateList( DateListID int, Date datetime, Year int, Month int, week int, Day int, hour int)

получим 8760 записей за год...

Интересная идея, мне нравится, спасибо. :)
3 июн 09, 15:04    [7260934]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить