экстремальные значения

CfirTE TA5LE fvdjeset iki int identity, c: ini

INSERT #v3lueset (cl) VALUES (20)

INSERT #valueset (cl) VALUES (30)

INSERT #vabeset (cl) VALUES (40)

INSERT #yalueset (cl) VALUES (21)

INSERT #valueset (cl) VAIUES (31)

INSERT #valLieset (cl) VALUES (41)

INSERT #vabeset (ci) VALUES (22)

INSERT fvalueset (cl) VALUES (32)

INSERT #valueset (cl) VALUES (42)

SELECT v.kl. S!idingAvenage=AVGI.O*a.cl)

FRCM f/aiueset v CROSS JOIN #va1aeset a

WHERE (a.kl BETwEEN v.kl-4 AND v.kl)

GROUP BY v.kl

SiidingAvenage

20 25 30 27 28 32 31 29 33

.000000 ,000000 .000000 ,750000 .400000 ,600000 .000000 .400000 .600000

Заметьте, что средние значения для первых четырех .элементов рассчитываются как нарастающие средние, так как для них нет необходимого количества предшествующих значе101т'г. Тем не менее начиная с пятого значения Sliding-Average равняется среднему значению текущего значения и четырех предыдущих. Как и в случае нарастающих итогов, вы можете заме}пгть функцию AVG() другой агрегатной функцией для вычисления других типов скользящих агрегатов.

Экстремальные значения

Экстремальное значение, как определено здесь, это 1шибольшее значение среди двух или более столбцов заданной таблицы. Вы можете считать это горизонтальным агрегатом. В Oracle существуют функции (GREATEST() и LEAST()) для получения горизонтальных экстремальных значений; в Transact-SQL таких функций нет. Hecu)тpя на это, получение горизонтального экстремального значения для двух столбцов также просто, как использование CASE для выбора между ни.ми, как здесь:

CREATE TABLE #Lenipsamp (SampDate dateti.ne, Тетрбат int, Тетрбрт int)

INSERT ftempsamp VALUES С19990101,44,32) INSERT #tempsamp VALUES (19990201,41,39) INSERT #tempsamp VALUES (19990301,48,56) INSERT #tempsamp VALUES (1999040Г,65,72) INSERT #tempsamp VALUES (19990501,59,82)

INSERT #tempsamp VALUES (ЧЭЭЭОбОГ .47.84) INSERT #tempsamp VALUES С19990701.61.92) INSERT #tempsamp VALUES (1999080Г.56.101) INSERT #tempsamp VALUES (1999090Г.59.78) INSERT #tempsamp VALUES (1999100Г.54.74) INSERT #tempsamp VALUES (1999110Г.47.67) INSERT #tempsamp VALUES (1999120Г.32.41)

SELECT HiTemp=CASE WHEN Temp6am > Temp6pm THEN Temp6am ELSE Temp6pm END FROM #tempsamp

Hi Temp

Можно вкладывать функции CASE друг в друга, если необходимо рассматривать более двух горизонтальных значений. Заметьте, что также можно упорядочивать результирующие мгюжества, используя экстремальные значещш. Для этого достаточно включить псевдоним столбца с функцией CASE в предложение ORDER BY, вот так:

SELECT HiTemp-CASE WHEN Тетрбат > Тетрбрт THEN Тетрбат ELSE Тетрбрт END FROM #tempsamp ORDER BY Hi Temp

HiTemp

41 41 44 56 67 72 74 78 82 84 92 101

Если вы хотите упорядочивать множество в соответствии с экстремальны.м значением, на самом деле не выбирая его, просто перенесите выражение CASE из списка SELECT в предложение ORDER BY. Вот запрос, который возвращает интервалы, отсортированные по наименыпей температуре на дату каждого интервала:

SELECT * FROM #tempsamp

Определение атрибутов экстремальных значений

Помимо получения горизонтальных Э1<стремал1>1!1)1х значений, в запросе может понадобиться определить, какие атрибут!,! иа са.\!0.\! деле содержат эти экстрс-!aль!Iыe 3!!аче1П!Я. Вот запрос, когорыГ! это делает; SELECI McRth=DATENAME(nim.SampDate),

HighestTeiip-CASE WHEN Теглрбет > Тетрбрт THEN Morning ELSE Evening END FROM #tem,psamp

После TO!o как вы вычислили !оризоигалы1ое экстремальное значение, вам может понадобиться найти все за!1иси в таблице с так!1м же экстремальным зна-че!!ие.\!. Это можно сделат!>, если использовать CASE вместе с подзапросом. Вот при.мер;

SELECI * FROM #tempsamp

WHERE (CASE WHEN Тетрбат < Тетрбрт THEN Тетрбат ELSE Тетрбрт END)= (SELECT MIN(CASE WHEN Тетрбат < Тетрбрт THEN Тетрбат ELSE Тетрбрт END)-FROM Itempsamp)

SampDate

1999-01-01 00:00:00.000 1999-12-01 00:00:00.000

44 32

Тетрбрт

32 41

Заключение

в этой главе мы рассмотрели вычисление статистических данных с номон1ью Transact-SQL. Вы узнали о встроенных статистических функциях, а также о том, как создавать собственные. Благодаря ориентированности SQL Server на работу с множествами и статистическим функциям, он представляет собой удобный инструмент для осуществления статистических вычислений, фактически лучший, чем многие традиционные языки програ.ммирования.

Серии

и последовательности

я люблю напоминать cuoeii команде, что ,\1ы Б коиечисм iiToie поставляем продукты, а не документы, в которы.х описана снецификацня н архитектура. Так что не надо забывать о конечной цели.

Рои Соукап

Серии, области, иоследовательиости и ряды - это конструкции, обычно состоящие как мии'имум из двух столбцов: ключевого столбца, который является более-менее последовательным, и столбца со значением, содержащего необходи--мую нам инфор.мацию. Юиочевой столбец носледовательпости (или ряда) является последовательны.м, между его зпачеиия.\И1 нет про.межутков. Примеры последовательностей - временные ряды, но.чгера счетов-фактур, номера банковских счетов и так далее. Ключевой столбец серии также носледователен, хотя про.межутки .между его значения.\и1 .могут существовать, а югyт и ие существовать. Примера.ми серий являются регулярные последовательности (с промежут-KaNHi, конечно), такие как но.мера до.мов, по.мера версий и тому подобное. Область - это подпоследовательность, все члены которой удовлетворяют одному и то.му же условию. Интервал - это результат равио.мерного разделения последовательности на несколько иодпоследовательностей или под.\пюжеств.

Запросы для работы с эти.\и1 конструкция.ми очень часто похожи друг па друга, и способы обработки одного типа упорядоченного списка Moi-ут пересекаться со способа.ми обработки других. Так что запрос для нахождения взаимосвязей .между члена.ми последовательности \южет также работать с данны.\И1 вре.менпььх рядов - все зависит только от того, для чего вы его применяете.

Последовательности

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

устойчиво росли в цене, когда цены находились в некотором процентном отношении друг к другу и так далее. Вот запрос, показывающий как это сделать на Transact-SQL:

CREATE TABtE #valueset (kl smalldatetime, cl int) INSERT #valueset (kl. cl) VAtUES (1999090Г .28) INSERT #valueset (kl, cl) VALUES (1999100Г,25) INSERT #valueset (kl, cl) VALUES CigggUOr ,13) INSERT Ivalueset (kl, cl) VALUES ( 1999120Г ,15) INSERT #valueset (kl. cl) VALUES (20000101.35) INSERT Ivalueset (kl, cl) VALUES (20000201,38) INSERT Ivalueset (kl, cl) VALUES (20000301,16) SELECT v.kl, v.cl FROM #valueset v JOIN Ivalueset a ON ((a.cl >- v.cl) AND (a.kl = DATEADD(mm,l,v.kl))) OR ((a.cl <- v.cl) AND (a.kl = DATEADD(mm,-l,v,kl)))

Этот запрос находит интервалы, где значения последовательно увеличивались. Он использует самосоединение для сравнения рабочей таблицы с самой собой, а затем удаляет дублирующиеся значения с помощью предложения GROUP BY. Обратите в}шмание па использова}Н1е функции DATEADD() для получения предыдущего и следующего месяца для каждой точки данных.

Колебания временных рядов

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

SELECT

StartT1me=CAST(v.kl AS char(12)). EndT1me=CAST(a.kl AS char(12)). StartVal=v.cl. EndVaba .cl,

Change=SUBSTRING(- +.SIGN(a.cl-v.cI)+2,l)+CAST(ABS(a.cl-v.cl) AS varchar) FROM

(SELECT kl, cl. ranklng=(SELECT COUNT(DISTlNCT kl) FROM fvalueset u

WHERE u.kl <= 1.kl)

FROM #valueset 1) v LEFT OUTER JOIN

(SELECT kl. cl. rank1ng=(SELECT COUNT(DISTlNCT kl) FROM #valueset u WHERE u.kl <= l.kl) FROM fvalueset Da ON (a.ranking-v.ranking-bl) WHERE a.kl IS NOT NULL

StartTime EndTime StartVal EndVal Change

Sep 1 1999 Oct 1 1999 28 25 -3

Oct 1 1999 Nov 1 1999 25 13 -12

Nov 1 1999 Dec 1 1999 13 15 +2

Последовательности 225

Dec i 1999 Jan 1 2C0G 15 Jo -2:,

Jan 1 2C0G Feo 1 2G00 35 ЗЙ -3

Feb 1 200C Man 1 2000 38 lb -2?

Здесь есть несколько н))чересиых моментов, заслуживающих виима)т . Во-первых, обратите виима)пте на npiTMeiieinie нронвиодно!! таб.нщы для определения ранга зиачениГ! в последовательиост и по oTHOuieinno др}т к другу. Хотя с точки зрения синтаксиса было бы ко.\и1актнее псреиестн .по в представлепне, да)и)ьн 1 подход демонстрирует возмож1Юсть )1сно.т1):к}вания единствепиого запроса для получения всех необходимых дан1и>1х. Во-вторых, (обратите внн.\ппн1е на при.менение подзапроса в каждой проттзводпо!) табли)1е для непосредственного вычисления ранга. Это ос,уи1ествляется с ио.монпло вычисления COUNT(DISTINCr) среди других значе)1ий в pa6o4eii таб.лице, 1и)торые меньше пли равны данному 31шченню. И наконец, обратите вни.ма)И1е иа иси<)льзова)н1е 4)у11кци11 SIGN() и SUBSTRINGO для иолучения знака для каждого изме?1ею1я. За счет этого отрн-цательные значения обозначаются с гю.мощыо стаилартиого префикса <<- , а положительные показываются без знака.

Выборка каждого п-го значения

Другая задача, связанная с последовательн1)Стя.\)и, - вьии)лпепие расчетов или вычисление статистики по каждо.му и-.му значению. Так как )федыдунпп1 запрос ранжирует каждый эле.\1е)1т времеп)1ото ряда. сде;лать это сравн)1тельно просто. Вот модификация предыдущего запроса, которая выбирает каждое третье значение: SELECT

StartT1me=CAST(v,kl AS char(12}), Fndl-,rie=CAST(a.kl AS char{l2)), StartVal=v.cl. EndVal-a.c],

Change=SUBSTRING.C- +,SIGN{a.cl-v.cl)2.1;+C.AST,ABSca.cl-v.cl) AS varcnar) FROM

(SELECT kl. cl, ranking=(SELECr CCUNi(DISlINCt kl) FROM #valueseL u WHERE a.kl <= 1,kl) FRCM #valueset 1) v LEFT OUTER JOIN

(SELECT kl, cl, rank1ng=(SELECT COUNr;DlSlNCi Kl) FROM #v3lue5et u WHERE u,k; <= 1,kl) FROM fvalueset 1) a ON (a,rank1ng=v,rank1ngfl) WHERE a,kl IS NOT NULL AND v,ranking:S3=C

StartTime EndTime StartVal EndVa Change

NOV 1 1999 Dec 1 1999 13 15 +2

Feb 1 2000 Man 1 2000 38 16 -22

Единственное из.менение, которое .\h>i внесли, - )1сиользованне остатка от деления на 3 для фильтрации записей, в<)звpaщaeнлx :janpoco.m. Так как только каждая третья запись удовлетворяет условтно v.ranking%3, мы получим приведенный результат.

Области

Из всех наиболее общих задач, связанных с областя.ми, на нервом месте croiri задача собственно идентифнкацни (нахождения) области. В отличие от после-

довательностей и серий, которые существуют сами по себе, области определяются своими значениями. Члены области последовательны, все они удовлетворяют некоторому критерию. Этот критерий .может требовать, чтобы все члены области имели одинаковое абсолютное значение, то есть каждое значение имело одинаковое отнощение с предыдущим, или ее члены могут определяться как-то еще. Вот метод определения областей в последовательности;

CREATE TABLE #valueset (kl int Identity, cl Int)

INSERT #valueset (cl) VALUES (20)

INSERT #valueset (cl) VALUES (30)

INSERT #valueset (cl) VALUES (0)

INSERT fvalueset (cl) VALUES (0)

INSERT #valueset (cl) VALUES (0)

INSERT #valueset (cl) VALUES (41)

INSERT #valueset (cl) VALUES (0)

INSERT #valueset (cl) VALUES (32)

INSERT #valueset (cl) VALUES (42)

SELECT v.kl

FROM fvalueset v JOIN #va1ueset a

ON (v.cl=0) AND (a.cl-0) AND (ABS(a.kl-v.kl)=l)

GROUP BY v.kl

3 4 5

Как показано вьшге, область состоит из элементов последовательности, чьи значения равны нулю. Этот запрос творит чудеса с помощью самосоединения, из которого затем отфильтровываются дублирующиеся значения с помощью предложения GROUP BY. Предложение ON ограничивает рассматриваемые значения теми, которые: а) равны нулю и б) являются смежными с нулевыми. Смежность определяется вычитанием значения ключа таблицы а из значения ключевого столбца таблицы v. Абсолютное значение, равное единице, означает, что значение в а находится до или после З1ачения в v.

Области с относительными условиями

в дополнение к абсолютным значениям относительные условия также являются популярным критерием для опредедения членства в области. Относительное условие определяет некоторое отнощение между значениями в последовательности. Примером нахождения области на основании относительного условия может служить нахождение области, значения в которой увеличиваются последовательно. Вот код Transact-SQL, который определяет область, значения в которой увеличиваются монотонно:

CREATE TABLE fvalueset (kl int. cl int)

INSERT #valueset (kl

INSERT #valueset (kl

INSERT #valueset (kl

INSERT #valueset (kl

INSERT #valueset (kl

INSERT #valueset (kl

INSERT #valueset (kl

cl) VALUES (300,15)

cl) VALUES (340,25)

cl) VALUES (344,13)

cl) VALUES (345,14)

cl) VALUES (346.15)

Cl) VALUES (347.38)

cl) VALUES (348,16)

SEi.ЕСТ v.ici. v.c!

FROM fvalueset v JOiN fvoijesei a

ON (Ca.cl = v.cl+1) AND (a.<l - v.kl+1))

OR ((a.cl = v.cl-!) AND (a.kl = v.kl-i))

GROUP BY v.kl, v.cl

kl cl

344 13

345 14

346 15

И снова мы исно.тьзуем са.мосоединеине для сравнеиня рабочей таблицы с самой собой. Два критерия соединения, установленные предложешге.м ON, состоят в следующем:

о каждое значение в области до.чжно быть иа едии1щу больше или меньше [)ас-сматртшаемого значения;

о каждое значиш должно быть иос.тедовательиым со смежными.

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

SELECT v,kl, v.cl

FROM #valueset v JOIN #valueset a

ON ((a.cl >= v.cl) AND (a,kl = v.kl+D)

OR ((a.cl <= v.cl) AND (a.kl = v,kl-l))

GROUP BY vxl. v.cl

kl cl

344 13

345 14

346 15

347 38

Ограничение размера областей

После того как мы нашли области, может потребоваться отфильтровать их на основании их размера. Нет необходи.мости в получении каждой области, члены которой имеют некоторое абсолютное значение или имеют некоторую взаимосвязь друг с другом, - мы .можем захотеть ограничить рассматриваемые области теми, размер которых больше некоторого значения или равен ему. Вот код Transact-SQL, иллюстрирующи!! ограничение облаете!! по их раз.мерам:

CREATE TABLE #vaiueset(kl irt identity, cl int)

INSERT #valueset(cl) VALUES (20)

INSERT #valueset(cl) VALUES (30)

INSERT #valueset(cl) VALUES (32)

INSERT #valueset(ci) VALUES (34)

INSERT #valueset(cl) VALUES (36)

INSERT #va;ueset(c]) VALUES (0)

INSERT #vali.eset(cl) VALUES (0)

INSERT #valueset(cl) VALUES (41)

INSERT #valueset(cl) VALUES (0)

INSERT #valueset(cl) VALUES (0)

INSERT #valueset(cl) VALUES (0) INSERT #valueset(cl) VALUES (42)

SELECT v.kl

FROM #valueset v JOIN #value5et a ON (v.cbO)

GROUP BY v.kl

HAVING

(ISNULL(MIN(CASE WHEN a.kl > v.kl AND a.cl !=0 THEN a.kl ELSE null END)

MAX(CASE WHEN a.kl > v.kl THEN a.kl ELSE v.kl END)) ISNULL(MAX(CASF WHEN a.kl < v.kl AND a.cl !-0 THEN a.kl ELSE null

END)+1.

MIN(CASE WHEN a.kl < v.kl THEN a.kl ELSE v.kl END)))+i 3 -- Необходимый размер области

10 11

Условие >=3 офаиичивает выводимые области теми, которые имеют три и более элемента, как говорится в комментарии. Обратите внимание, что первая область последовательности (состоящая из лв)х нулевых значений) игнорируется запросом, поскольку она слишком мала. Возвращается только вторая, содержащая требуемое количество элементов.

Помимо использования JOIN и GROUP BY для сравнения таблицы с самой собой, основная работа запроса выполняется предложением HAVING. Рассмотрим первое выражение ISNULL(). В нем используется CASE, чтобы найти: а) первый ключ в а, значение которого меньше значения текущего ключа в v и не равно пулю, или б) последний ключ в а, который больше текущего ключа в v. Если ключ, соответствующий первому условию, не найден, то всегда будет существовать последний ключ в таблице. Необходимо найти ближайшее ненулевое значение, следую-щее'за текущим значением ключа в v. Мы пытаемся определить значение ключа нижней границы области - ее конца. Второе выражение ISNULLO, по существу, зеркальное отражение первого. Его задача состоит в том, чтобы определить значение первого ключа в области. После того как определены граничные ключи, верхняя граница вычитается из нижней для определения размера области. Затем это значение сравнивается с условием >=3, чтобы отфильтровать обласгн, имеющие менее трех членов.

Хотя этот способ работает и сравнительно компактен с точки зрения кода, я считаю его несколько замысловатым. Например, CASE используется для передачи NULL назад в ISNULL(), из-за чего ISNULL() приходится вычислять ее второй аргумент - получается жутко вложенное выражение типа если-то-нначе . Первое выражение ISNULL() можно было бы сделать несколько прозрачнее, например вот так:

CASE

WHEN a.kl > v.kl AND a.cl !=0 THEN MIN(a.kl)-l

ELSE MAX(CASE WHEN a.kl > v.kl THEN a.kl ELSE v.kl END)