Способ выбора первого значения в группе в SQL Server?

Я пытаюсь найти первое значение и последнее значение в группе. Что-то вроде First ([Open]), Max ([High]), Min ([Low]), Last ([Close])

Один из запросов ниже (в настоящее время отсутствует логика для столбцов open/close. Набор данных чрезвычайно велик (более 150 миллионов записей), поэтому производительность запроса может стать проблемой.

Select 'AUDCHF' AS CURRENCY,
 Datepart(year, Datekey) AS [YEAR], 
 Datepart(month, Datekey) AS [MONTH], 
 Datepart(day, Datekey) AS [DAY], 
 Case When Datepart(hour, Datekey) BETWEEN 0 AND 11 Then 'AM' Else 'PM' End AS [12 Hour], 
 Case 
 When Datepart(hour, Datekey) BETWEEN 0 AND 3 Then '1st 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 4 AND 7 Then '2nd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 8 AND 11 Then '3rd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 12 AND 15 Then '4th 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 16 AND 19 Then '5th 4 Hours'
 Else '6th 4 Hours'
 End AS [4 Hours], 
 Datepart(hour, Datekey) AS [HOUR], 
 max(High) AS HIGH, 
 min(Low) AS LOW
From AUDCHF
 Group by Datepart(year, Datekey), Datepart(month, Datekey), Datepart(day, Datekey), 
 Case When Datepart(hour, Datekey) BETWEEN 0 AND 11 Then 'AM' Else 'PM' End,
 Case 
 When Datepart(hour, Datekey) BETWEEN 0 AND 3 Then '1st 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 4 AND 7 Then '2nd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 8 AND 11 Then '3rd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 12 AND 15 Then '4th 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 16 AND 19 Then '5th 4 Hours'
 Else '6th 4 Hours'
 End, 
 Datepart(hour, Datekey)
 Order by Datepart(year, Datekey), Datepart(month, Datekey), Datepart(day, Datekey), 
 Case When Datepart(hour, Datekey) BETWEEN 0 AND 11 Then 'AM' Else 'PM' End,
 Case 
 When Datepart(hour, Datekey) BETWEEN 0 AND 3 Then '1st 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 4 AND 7 Then '2nd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 8 AND 11 Then '3rd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 12 AND 15 Then '4th 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 16 AND 19 Then '5th 4 Hours'
 Else '6th 4 Hours'
 End, 
 Datepart(hour, Datekey)
2 ответа

ORDER BY может использовать псевдонимы, определенные в списке выражений SELECT, потому что он оценивается после части SELECT (это не относится к части GROUP BY).

В вашем запросе предложение order by может быть:

Order by [YEAR], [MONTH], [DAY], [4 Hours],[HOUR]

Поскольку вы группируете по году/месяцу/дню/4 часа /4 часа, я думаю, вы можете удалить 4-ю часть.

Я бы использовал функции окна и сделал внешний выбор с помощью GROUP BY, чтобы удалить дубликаты.

select [YEAR], [MONTH], [DAY], [HOUR], [12 Hour], [4 Hours],
 max([HIGH]) as HIGH, min([LOW]) as LOW,
 max([Open]) as [Open], max([Close]) as [Close]
from (
 select 
 Datepart(year, Datekey) AS [YEAR], 
 Datepart(month, Datekey) AS [MONTH], 
 Datepart(day, Datekey) AS [DAY], 
 Datepart(hour, Datekey) AS [HOUR], 
 Case When Datepart(hour, Datekey) BETWEEN 0 AND 11 Then 'AM' Else 'PM' End AS [12 Hour], 
 Case 
 When Datepart(hour, Datekey) BETWEEN 0 AND 3 Then '1st 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 4 AND 7 Then '2nd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 8 AND 11 Then '3rd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 12 AND 15 Then '4th 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 16 AND 19 Then '5th 4 Hours'
 Else '6th 4 Hours'
 End AS [4 Hours], 
 max(High) over( 
 partition by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey) , 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 ) as [HIGH], 
 min(Low) over( 
 partition by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey), 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 ) as [LOW],
 first_value([Open]) over( 
 partition by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey), 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 order by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey), 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 ) as [Open],
 last_value([Close]) over( 
 partition by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey), 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 order by
 Datepart(year, Datekey) , 
 Datepart(month, Datekey), 
 Datepart(day, Datekey), 
 Datepart(hour, Datekey) 
 ) as [Close]


 from AUDCHF ) T
group by [YEAR], [MONTH], [DAY], [HOUR], [12 Hour], [4 Hours]

Внешние max (High), Min (Low) и т.д. Здесь просто для того, чтобы сделать GROUP BY счастливыми, они здесь не имеют смысла, поскольку они были обработаны во внутреннем выборе (я не знаю, что такое Open and Close, поэтому я просто ставьте первые и последние значения с тем же разбиением).

Если этот запрос должен выполняться в большой таблице, и поскольку для сокращения выбранных строк не существует предложения WHERE, я бы создал индекс в Datekey, включая столбцы High и Low (и другие столбцы не в запросе: Close и т.д.), Чтобы избежать полное сканирование таблицы. Это приведет к полному сканированию индекса, и это может быть намного быстрее:

create nonclustered index IxAudchf on AUDCHF(Datekey) include( [High], [Low], [Open], [Close]) ;

Для функций Sql Window вы можете найти презентации здесь и здесь

Примечание: FIRST_VALUE и LAST_VALUE - только Sql2012, а не 2008.

Если вы используете SQL 2005 или 2008, то следует сделать то же самое (возможно, менее эффективно). Я взял "Низкий" и "Закрыть" в последней строке, и я не уверен, что это то, что вы хотите, измените его, чтобы следовать вашей логике, если я неправильно понял.

; WITH 
WAUDCHF1 as
( select 
 row_number() over( 
 partition by
 Datepart(year, Datekey), Datepart(month, Datekey) , 
 Datepart(day, Datekey), Datepart(hour, Datekey) 
 order by Datepart(year, Datekey) , Datepart(month, Datekey) , 
 Datepart(day, Datekey), Datepart(hour, Datekey) 
 ) as [Rownum], 
 Datepart(year, Datekey) AS [YEAR], 
 Datepart(month, Datekey) AS [MONTH], 
 Datepart(day, Datekey) AS [DAY], 
 Datepart(hour, Datekey) AS [HOUR], 
 Case When Datepart(hour, Datekey) BETWEEN 0 AND 11 Then 'AM' Else 'PM' End AS [12 Hour], 
 Case 
 When Datepart(hour, Datekey) BETWEEN 0 AND 3 Then '1st 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 4 AND 7 Then '2nd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 8 AND 11 Then '3rd 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 12 AND 15 Then '4th 4 Hours'
 When Datepart(hour, Datekey) BETWEEN 16 AND 19 Then '5th 4 Hours'
 Else '6th 4 Hours'
 End AS [4 Hours], 
 max(High) over( 
 partition by
 Datepart(year, Datekey) , Datepart(month, Datekey) , 
 Datepart(day, Datekey), Datepart(hour, Datekey) 
 ) as [HIGH], 
 min(Low) over( 
 partition by
 Datepart(year, Datekey) , Datepart(month, Datekey), 
 Datepart(day, Datekey), Datepart(hour, Datekey) 
 ) as [LOW],
 [Open],
 [Close]
 from AUDCHF ),
LASTROWNUM as (
 select [YEAR], [MONTH], [DAY], [HOUR], max(rownum) as [Rownum]
 from WAUDCHF1
 group by [YEAR], [MONTH], [DAY], [HOUR], [12 Hour], [4 Hours]
 )
select W1.[YEAR], W1.[MONTH], W1.[DAY], W1.[HOUR], 
 max(W1.[High]) as [High], min(W2.[Low]) as [Low], 
 max(W1.[Open]) as [Open], max(w2.[Close]) as [Close]
from LASTROWNUM M
inner join WAUDCHF1 W1 on M.[YEAR] = W1.[YEAR]
 and M.[MONTH]= W1.[MONTH]
 and M.[DAY] = W1.[DAY] 
 and M.[HOUR] = W1.[HOUR] 
inner join WAUDCHF1 W2 on W2.[YEAR] = M.[YEAR]
 and W2.[MONTH]= M.[MONTH]
 and W2.[DAY] = M.[DAY] 
 and W2.[HOUR] = M.[HOUR] 

 and W2.Rownum = M.Rownum
Where W1.Rownum = 1 
group by W1.[YEAR], W1.[MONTH], W1.[DAY], W1.[HOUR], w1.[12 Hour], W1.[4 Hours]
order by W1.[YEAR], W1.[MONTH], W1.[DAY], W1.[HOUR], w1.[12 Hour], W1.[4 Hours]


Запрос:

SELECT 'AUDCHF' AS CURRENCY,
 Datepart(year, Datekey) AS [YEAR], Datepart(month, Datekey) AS [MONTH], 
 Datepart(day, Datekey) AS [DAY], [12 Hour], [4 Hours],
 Datepart(hour, Datekey) AS [HOUR], High AS HIGH, Low AS LOW,
 (SELECT High FROM Rate AS R WHERE R.Datekey = (SELECT MIN(Datekey) 
 FROM Rate WHERE DATEADD(hour, DATEDIFF(hour, 0, Rate.Datekey), 0) =
 AUDCHF.Datekey AND Rate.Base = 'AUD' AND Rate.Target = 'CHF') 
 AND R.Base = 'AUD' AND R.Target = 'CHF') AS [Open],
 (SELECT Low FROM Rate AS R WHERE R.Datekey = (SELECT MAX(Datekey) 
 FROM Rate WHERE DATEADD(hour, DATEDIFF(hour, 0, Rate.Datekey), 0) =
 AUDCHF.Datekey AND Rate.Base = 'AUD' AND Rate.Target = 'CHF') 
 AND R.Base = 'AUD' AND R.Target = 'CHF') AS [Close]
FROM AUDCHF, Segment
 WHERE Segment.Hour = Datekey
 ORDER BY Datepart(year, Datekey), Datepart(month, Datekey), 
 Datepart(day, Datekey), Datepart(hour, Datekey);

вернет ожидаемые результаты. Я также извлек аргументы case в таблицу поддержки, которую вы можете увидеть в SQLFiddle. Выделите также результаты вашего запроса по некоторым тестовым данным. Это использует ответ в формате T-SQL datetime, округленный до ближайшей минуты и ближайших часов, с использованием функций для усечения времени в часах.

В принципе, просмотр AUDCHF преобразует обрезку в Datekey и выполняет группировку. Затем запрос соединяется с таблицей Сегмента для извлечения константных строк и вычисления начальных и конечных значений. Они должны быть в подзапросах, поскольку они не связаны с агрегатами.

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

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

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

licensed under cc by-sa 3.0 with attribution.