П|Зоблема актипт и том, что пря.мые eebi.iuii иа a,kl м а,с1 ие разреитены в иредложе1ИИ1 HAVING, так как они ие соле-ржагся ни в arperaTHoii (})уикции, ни в предложении GROUP BY. Это ограничение ANSI SQL, и, в обще.м-то, нормальная вещь - за исключением случаев, когда вы nijiracrecb осуществить с.тож-иые запросы с по.мощью едттистпеииого оператора SELECT, как здесь. Мы никак не .може.м повлиять на тот факт, что они не включены в пре,тлол<ение GROUP BY, - необходимо оставить их как есть, по(Ч<о.чьку они служат для объеди1гепия nanie-го са.мосоединения. Однако мы може.м в.южпгь оба этих зиачеиия в агрегатную функцию, чтобы обойти ограничение ANSI SQL, связанное с пред.тожение.м HAVING. Как раз это запрос и делает; ои прячсг лсзгику ({зуикцтп! CASE внутри агрегатов, чтобы обойти oipaini4eiine, паклагывас.мое HAVING, - и это оспоЕитая причина, по которой эта логика сначала кажегся очень запутаипоГг

Границы областей

Иногда бывает необходимо вернуть границы об.ластей, а ие сами обласлп. Предыдущий запрос использует границы для вычисления размеров облаете!!, чтобь! огран1!ЧИть возвращае\!ые област!!. Вот !japi!a!!r 1!р(?дыду!!!его за!!роса, которь!Й возвращает гра1И1ць! всех иайде!!!!Ь!Х !!,\! областей;

SELECT RegionStart=v,kl,Regior,Enc!=lSNULL;MifJ(CASL ,4HhN a.klv.kl AND a.cl

!--C

THEN a.kl ELSE null END)-],

MAX(CASE WHEN a.kl > v.kl iHEN a.<l E,.SF .1 lNU;) FROM #valueset v JOIN fvalueset a ON (v.cl=0) GROUP BY v.kl HAVING

ISNULL(MIN(CASE WHEN a,kl>v.ki AND a.cl -C ibtN a.к] ELSr null END) 1. MAX(CASE WHEN a.kl > v.kl IHEN a.kl ELSE v.ki END;) > v.kl

ISNULL(MAX(CASE WHEN a.kl<v.kl AND a.cl !=C THEN a.kl ELSE null ENu)<l. MIN(CASE WHEN a.kl < v.kl THEN д .U ELSE v.kl END)) = v.kl

RegionStart RegionErd

9 11

Серии

Подобно свои.м 110следовалелы!ы,м родстве!!!И!ка,м, cepi!!! состоят как ми!И!.му,\! из двух столбцов; ключевого сло.лбца !! столб!1а, содержащего значен1!я. Ключевой столбец всегда иоследовате.лС!!, а его 3!!ачен!!я .мо1-ут не быть непрерь1в-ными.

Как и в случае последователь!10сте!1, .мь! !!oдpaзy!eвae.!. что наши дан1!Ь!е содержат серии. Примеры серий в!<:люча!ОТ вре.\!е1!нь!е серии с нерегулярщлми точками и систе.мь! нумерации с про.\!ежут!<а,\!!! (!1апри.\!ер, номсра счетов, номера кредитных карт, номера домов).

Области

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

CREATE TABLE #valueset (kl int. cl int) INSERT #valueset VALUES (2.0) INSERT #valueset VALUES (3,30) INSERT #valueset VALUES (5.0) INSERT #valueset VALUES (9,0) INSERT #valueset VALUES (10,0) INSERT #valueset VALUES (11,40) INSERT #valueset VALUES (13.0) INSERT #valueset VALUES (14.0) INSERT #valueset VALUES (15.42) SELECT v.kl

FROM #valueset v JOIN #valueset a ON (v.cl=0) GROUP BY v.kl

HAVING (MIN(CASE WHEN a.kl > v.kl THEN (2*(a.kl-v,kl))+CASE WHEN a.cl<>0 THEN 1 ELSE 0 END ELSE null EN0) 2=0)

OR (MIN(CASE WHEN a.kl < v kl THEN (2*(v.kl-a.kl))+CASE WHEN a.cl<>0 THEN 1 ELSE 0 END ELSE null ENO)X2-0)

Как и в других запросах, в этом используется комбинация самосоединение/ GROUP BY для сравнения рабочей таблицы с самой собой. Обратите внимание на применение вложенных выражений CASE для создания не.много более сложной логики. Также обратите внимание на то, как логика завернута в агрегатные функции, чтобы можно было обойти ограничения ANSI SQL, связанные с предложением HAVING.

Грани1ы областей

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

CREATE TABLE #valueset (kl int, cl int) INSERT #valueset VALUES (2.20) INSERT #valueset VALUES (3,30) INSERT #valueset VALUES (5,0) INSERT #valueset VALUES (9.0) INSERT #valueset VALUES (10.0) INSERT #valuesGt VALUES (11,40) INSERT Ivalueset VALUES (13,0) INSERT #valueset VALUES (15,0) INSERT #valueset VALUES (16.42)

SELlJ SiartRjnv.k;. ErxRur-d.kl

FRCM #valbeset v JOIN fvalueset a ON (v.ki < аЛ:) CROSS JOIN Svaueset ;

GROUP 5Y v.kl, a,kl

HAVING

{SUM;ABs;i,ci;*(CASr пнск v,ki <-\ki and :.k; a.ki ;hfn i llSE о

ENDjJ-O)

AND (iSNULl (MINICASE WHEN .k\ > a.kl

THE\ ;2-P.kl-a.kl;)-{CASE WHEN 1.ci<>C iHEN 1 E SE 0 END)

ELSE null END),1)?2 != 0) AND (1SNULL{MIN;CASE WHEN l.kl < v.kl THEN {2*(v.kl-l .kl))+(CASF WHEN l.cl<->0 THEN 1 ELSt С END) ELSE null END),1) %2 != 0)

StartRun EnaRur

5 10

13 15

Как и в предыдущем запросе, большая часть работы заключена в агрегатных функциях в предложении HAVING, Некоторые из них сложны для понимания. Обратите, напри.мер, втгмание на это выраже1И1е в предложении HAVING:

(SUM(ABS(1,cl)*(CASE WHEN v.kl <=l,kl AND l,k: <= a,kl THEN I ELSE 0 END))=C)

Или если nainicarb более доходч1шо:

((CASE WHEN v,kl <-l,kl AND l,kl <= a,kl IHEN SUM(ABS(1,cl)) ELSE 0 END)=0)

Однако, как я упоминал ранее, v.kl и l.kl должны либо быть указаны в предложении GROUP BY, либо использоваться в агрегатной функции, чтобы нх .можно было применить в предложении HAVING, так что этот синтаксис работать не будет. В.место этого мы возвращае.м единицу или нуль из выражения CASE, а зате.м у.множае.м полученное значение па SUM(ABS(l.cl)), достигая того же результата.

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

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

Как я уно.минат в при.мерах с последовательностя.ми, часто бывает необходи.мо ограничить области по их размеру. Вот пример запроса на Transacl-SQL, в которо.м серия сканируется иа пред.мет на,тичня областей, состоящих из трех или более элементов, значения которых меньше 10:

CREATE TABLE #vaiueset (kl int. cl int) INSERT fvalueset VALUES (2,20) INSERT #valueset VALUES (3,30) INSERT #valueset VALUES (5.0) INSERT #valueset VALUES (9.4) INSERT #valueset VALUES (10.8) INSERT #valueset VALUES (11.40) INSERT #valueset VAIUES (13.0) INSERT #valueset VALUES (15.12) INSERT #valueset VALUES (16.42)

SELECT

StartRun=v.kl, StartRunV=v.cl, EndRun-a.kl, EndRun\/=a.cl,

RunSi2G=C0UNT(CASE WHEN v.kl <= l.kl AND l.kl <= a.kl THEN 1 ELSE null END).

RunAvg-AVG(CASE WHEN v.kl <= l.kl AND l.kl <- a.kl THEN l.cl ELSE null END)

FROM #valuGSet v JOIN #valueSGt a ON (v.kl < a.kl) CROSS JOIN #valuGSet 1 GROUP BY v.kl. v.cl. a.kl. a.cl

HAVING (COUNKCASF WHEN v.kl <= l.kl AND l.kl <= a.kl THEN 1 ELSE NULL END)>-3) -- 3 = Требуемый размер области

AND (COUNT((CASE WHEN l.cl >=10 THEN 1 ELSE NULL ENO)*(CASE WHEN v.kl <= l.kl AND l.kl <= a.kl THEN 1 ELSE NULL ENO))=0)

AND (ISNULL(M1N((CASE WHEN l.kl > a.kl THEN (2*(1,kl-a.kl))+(CASE WHEN l.cl>=10 THEN 1 ELSE 0 END) ELSE null END)),1)2 != 0)

AND (ISNULL(MIN((CASE WHEN l.kl < v.kl THEN (2*(v.kM .kl)) + (CASE WHEN l.cl>=10 THEN 1 ELSE 0 END) ELSE null END)).1)2 != 0)

StartRun StartRunV EndRun EndRunV RunSIze RunAvg

5 0 10 8 3 4

Этот запрос также использует три экземпляра рабочей таблицтя. Он применяет самосоединение двух первых, а затем вт51Полняет декартово произведение с третьей и удаляет результирующие дублирующиеся значения с помощью предложения GROUP BY. Кроме того, логика, контролирующая, какие записи составят результирующее множество, находится в предложении HAVING. Как и во многих предыдущих примерах, вся основная логика отбора заключена в агрегатные функции, чтобы можно было обойти ограничения HAVING.

Интервалы

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

CREATE TABLE #valuGSet (cl int)

INSERT fvaluGSet VALUES (20)

INSERT #valuGSet VALUES (30)

INSERT #valueset VALUES (40)

INSERT #valueset VALUES (21)

INSERT #valuGSet VALUES (31)

INSERT #valueset VALUES (41)

INSERT #valuGSet VALUES (22)

INSERT #valuGset VALUES (32)

INSERT #valuesGt VALUES (42)

ВОТ оператор SELECT, который разбивает последовательность на три интервала и возвращает конечные точки каждого из них: SELECT v.cl

FROM #vdueset v CROSS JOIN rfvaijesti a GROUP BV v.cl

HAVING COUNKCASE WHEN a ci <= v.c: THEN I ELSE null ENO}5;(COUN (*)/3)0 cl

22 32 42

И снова здесь мы используем комбинацию JOIN/GROUP BY для сравнения таб;1нцы с са.мой собой. И вновь логика отбора заключена в предложеппн HAVING. /3 в нред;10женип HAVING означает размер интервала, который .мы пще.м. Предложе1[ие HAVING вычисляет ко.тпчество з.че.ментов в а. которые меньше текупгего элемента в v либо равны ему, а затем [троверяет, делится ли нацело число элементов иа необходн.\нлй раз.мер пнтервапа. Если остаток от делен1ш равен нулю, значит, мы напьтп конечную точку интервала, котор^г>1 будет возвращена запросом. За.метьте, что можно также очень просто вернуть позицию каждой конечной точки. Вот код:

SELECT

IntenvalEndv.cl.

Intef-valPosCOUNUCASE WHEN a.cl <= v.cl IHEN 1 ELSE null END; FROM fvalueset v CROSS JOIN tfvalueset a GROUP BY v.cl

HAVING COUNTCCASE WHEN a.cl <= v.cl THEN 1 ELSE nuT END.CCOUNI (*)/3;=G IntenvalEnd IntenvalPos

22 3

32 6

42 9

Чтобы [Юлучить начальные, a пе конечные точки каждого интервала, нз.ме-ните проверку остатка на 1?., вот так:

SELECT v.cl

FROM #valueset v CROSS JOIN tfvaiueset a GROUP BV v.cl

HAVING COUNTCCASE WHEN d.cl <= v.cl IHEN 1 LLSE nul ! LNU,:jCC0UNTC*i/3)-l cl

20 30 40

Разделенные интервалы

Часто, вместо того чтобы вычислять интервалы во Bceii носчедовагельпости, пе-обход1ьмо вычислять их только в какой-то ее части. То есть, вместо того чтобы получать все интервапы во Bceii таблице, необходимо получить их сгрупппро-ваниы.ми по некоторо.му столбцу - по столбцу (пли столбца.м) в GROUP BY, если угодно. Так как в Transact-SQL нет агрегатных функций вроде INTERVAL BEGIN() или INTERVAL END(), осуществление таких векторных вычислений требует использования нетрадиционного подхода. Как и в большинстве решений в этой главе, для получения hckomi.ix результатов в :то.м способе применяется декартово.про-

изведение двух экземпляров рабочей таблицы, совместно GROUP BY и HAVING. Вот пример процедуры Transact-SQL, которая возвращает разделенный список информации об интервалах:

CREATE TABLE #valueset (kl int. cl int)

INSERT #valueset VALUES (1.20)

INSERT #valueset VALUES (1.21)

INSERT #valueset VALUES (1.22)

INSERT #valueset VALUES (1.24)

INSERT #valueset VALUES (1.28)

INSERT #vaiueset VALUES (2.31)

INSERT #valueset VALUES (2.32)

INSERT #valueset VALUES (2.40)

INSERT #valueset VALUES (2.41)

INSERT #valueset VALUES (3.52)

INSERT #valueset VALUES (3.53)

INSERT #valueset VALUES (3.56)

INSERT #vaiueset VALUES (3.58)

INSERT #valueset VALUES (3.59)

INSERT #valueset VALUES (4.60)

INSERT #vaiueset VALUES (4.61)

INSERT #valueset VALUES (4,62)

SELECT v.kl. v.cl

FROM #valueset v JOIN #valueset a ON (v.kba.ki)

GROUP BY v.kl. v.cl

HAVING

COUNT(CASE WHEN a.cl <= v.cl THEN 1 ELSE null END) BETWEEN (C0UNT(*)/4) AND (C0UNT(*)/4)*2

kl cl

1 20

1 21

2 31

2 32 ,

3 52 3 53

Этот код, используя столбец kL разделяет или группирует записи в таблице в интервалы по четыре. Затем он возвращает два первых значения из каждого интервала. Это может быть полезно, например, если вы захотите получить п первых продавцов в каждом регионе или п первых студентов в каждом классе. Однако вам необходимо ограничить этот список интервала.ми некоторой длины, чтобы отфильтровать продавцов, в регионах которых кроме них мало других продавцов, или студентов, в классах которых совсем .мало других студентов.

Заключение

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

Заключение 235

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

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

Массивы

Init, Use, Destroy. Три вызова процедур, шесть воз.можных исх0л01), пять мз которых неправильны.

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

Томас Л. Холэдэй

Поскольку в Transact-SQL нет встроенного типа данных для работы с массивами, нет и прямого способа для храпения или работы с ними. Существует несколько альтернативных способов представления массивов, по все они не реализуют массивы в прямом смысле традиииопных языков программирования и они далеки от идеала. Два наиболее очевидных способа эмуляции массивов в Transact-SQL - использование таблиц, которые ведут себя, как массивы (столбцы представляют собой 113\гереиия), и применение единственного столбца для хранения нескольких значений (со специальными механиз.ма.ми индексирования для получения элементов массива). Преимущество первого подхода в то.м, что он более реляционный и расширяемый. Добавить измерение столь же просто, как добавить столбец. Второй подход проще и нагляднее.

Столбец, хранящий несколько значений, не слишком отличается от столбца, который может хранить .массив, - это больше вопрос синтаксиса и семантики. Заметьте, что массивы по своей природе нарушают основные правила нормализации. Для того чтобы таблица находилась в первой нормальной форме, в ней не должно быть повторяющихся значений. Повторяюпщеся значения могут представлять собой несколько столбцов, иснользуе.мых для хранения экземпляров одного и того же типа значения, или один столбец, служащий для хранения нескольких значений. Чтобы таблица была нор.малнзована, эти повторяющиеся значения должны быть з'далены. Храр1ение массивов - даже виртуальных массивов, какие описаны в этой главе, - .это форма денормализации, которую вы должны применять только в особых случаях.

Представление массивов в виде больших строк

Идея хранить массивы как большие строки }ie попа. Фактически, в 1980 году СУБД Advanced Revelation имела поддержку многозначных столбцов - по существу, строковых полей, храняптх множество значений, - и специальные

механизмы матту.чяции и.\ит Дал<е сегодня лиюгие О'ЬД, поддерживающие столбцы-массивы, внутренне хранят их как простые буферы и предоставляют рас-ишрения SQL, изолирующие раз1)аботчика от этого внутреннего представления. Вот пример запроса, которьн ! иллюстрирует подход с испо.тьзованпем .чнтого-знаниых столбцов в Transact-SQL:

CRlAU TA.OLE #array (ki 1nt icentity, ofr-iycol varch-jr(SOnC)) INSERT #array (arraycoi) VALUES (EES PAUL *

BUCDY GUY +

JEFF EECK ) INSERT Urray (arraycol) VALUES (STEVE MILLER <-

EDDIE VAN HALENt

TCM SCHOEZ )

INSERT #аггбу (arraycol) VALUES (STEVE VAI +

ERIC CLAPTON + SLASH )

SELEC ! Elementl SUBS!RING(arrayco1, (0*15)+1,15).

E !emer,t2=SUBSTRING(arraycc) .(1*15)+I,15),

E1ement3=SUBSTRING(arrayco},(215)-1,i5) FROM #array a

Elementl Ele:rient2 EiementS

EES PAUL BUDDV GUY JEFF BECK

STEVE MILLER EDDIE VAN HALEN TOM SCHOLZ

STEVE VAI ERIC CLAPTON SLASH

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

Обратите вни.маиие на пснользоваппе varchar(8000) при определении столбца массива. С появлением в SQL Server больших строковых типов данных, с помощью этого подхода мы .може.м хранить довольно больииш массивы. В случае, когда элементы массива тьмеют длину 15 байт, .мы може.м хранить до 533 эле.мен-тов в каждом столбце ,\шссгша, в каждой записи. Для бо.тьшииства приложений этого вполне достаточно.

Также заметьте, что фактически в тако.м виртуально]м массиве можно хранить любые типы данных, а ие только строктг Конечно, все, что иеобходи1МО хранить в строково.м столбце, следует сначала преобразовать к строке, но это уже дело десятое. Единственное требоваьше к элементам - все они должны быть оди?1а-ковой длины, независимо от их первоначального типа данных.

Операторы INSERT, с по.мощью которых заполняется таблица, специально разбиты иа несколько строк для тьмитации занолпения массива. Хотя в этом нет необходи.мости, вам стоит так делать, если вы решите использовать этот подход. Такая запись понятнее и позволяет контролировать ?1еобходи.мый размер каждого элемента - основное требование для правильной работы метода.

Обратите внимание на при.менення выражения (n*s)-)-l для вычисления индекса каждого элемента .массива. Здесь п представляет собой но.мер элемента

(начинается с нуля), который вы хотите получить, as- раз.мер элемента. Хотя проще было бы написать

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

CREATE TABLE #array (kl int identity, arraycol varchar(8000)) INSERT #array (arraycol) VALUES С EES PAUL +

BUDDY GUY +

JEFE BECK +

JOE SATRIANI )

INSERT #array (arraycol) VALUES (STEVE MILLER +

EDDIE VAN HALEN+ TOM SCH0L2 )

INSERT #array (arraycol) VALUES (STEVE VAI +

ERIC CLAPTON + SLASH + JIMI HENDRIX + JASON BECKER + MICHAEL HARTMAN)

SELECT

Elementl=SUBSTRING(arraycol.(0*15)+1.15). Element2=SUBSTRING(arraycol.(1*15)+1.15). Element3-SUBSTRING(arraycol.(2*15)-!.]5). Element4=SUBSTRING(arraycol.(3*15)+1.15). Element5=SU8STRING(arraycol.(4*15)+1.15). Element6=SUBSTRING(arraycol. (5-*15)+l. 15) EROM #array a

Elementl Element2 ElementO Element4 Elements Elements

EES PAUL BUDDY GUY JEFF BECK JOE SATRIANI

STEVE MILLER EDDIE VAN HALEN TOM SCH0L2 *

STEVE VAI ERIC CLAPTON SLASH JIMI HENDRIX JASON BECKER MICHAEL HARTMAN

Здесь различаются только данные. Так как SUBSTRING() возвращает пустю строку, если ей передать неправильную стартовую позицию, нам не нужна специальная обработка массивов, в которых .меньше шести .элементов. Пример, приведенный выше, можно задействовать для массивов с шестью или .меньше элементами. А что делать, если нам необходим массив из шестидесяти элементов? Что, если нам нужны массивы с сотнями элементов? Следует ли включать отдельный столбец в результирующее множество? Этот способ был бы довольно огра-ниченны.м, если бы пришлось создавать отдельный столбец для каждого элемента. Все быстро стало бы очень громоздким. Вот код, показывающий, как обрабатывать массивы произвольной длины без необходимости писать код для создания столбцов для каждого элемента:

DECLARE (Sarrayvar varchar(8000) DECLARE (3i int. (31 int

DECLARE с CURSOR FOR SELECT arraycol FROM lar.ay OPEN с

FETCH с INTO (aarrayvar