IKSiRT #199o PCF ESilMATF VALUES (Сошга .KG. 5358592;

INSERT #1996 Р0Р Е5Т1МАГЕ VALUES CEasf, MO, 5071604)

INSERT ifl996 ?0P ESTIMATE VALUES (СотгаГ ,OK. 3300902)

SELECT Region, MIN(Pcpulation) AS Min-.rrjm, MAX(Population AS Ma/irruqi.

AVG;?opulat;on) AS Average. VAR;Populatcn) AS Va-iance. VAR3(Population) AS

VarianceP, STDEV(Pcpulatior) AS StandardDev-aton, STDEVP.opulaton) AS

StandardDev-.atonP

FRCM ri996 P0P ESIiM.AIE

GROUP BY Region

ORDER BY MaxiituTi DESC

(результаты сокращены)

Медианы

Задачи позиционирования занисей - то есть нахождения записе!! на основании их физического расположения в выборке - исторически были ка.чиге.м преткновения в SQL. Найти запись по значению в языках, ориентированных на работу с \И10жества.\и1, довольно просто; найти же запись по позиции - совсем другое дело. Задача нахождения .медиан представляет собой задачу позиционирования записей. Если в выборке нечетное количество значений, значение медианы равно значению в середшге, выще и ниже этого значения существует одинаковое количество элементов. Если в выборке нечетное количество значений, значение медианы - либо среднее двух центра-п>ных значений (в случае финансовых .медиан), либо .меньшее из них (в случае статистических .медиан).

Подход с использованием icientity-столбцов

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

SET NOCOUNT ON USE GG TS

IF (OBJECT IO( fmancialjnecian DROP TABLE financial median

DECLARE (Jstanttime catetime SET estanttime-GETOATEO CREATE TABLE financial median

IS N01 NULL;

с1 float DEFAULT (

(CASE (CAST(RAND()+.5 AS 1nt)*-l) WHEN 0 THEN 1 ELSE -1

END)*(CAST(RAND() *

100000 AS int) % 10000)*RAND()). c2 int DEFAULT 0 )

-- Инициализуруек таблицу десятью записями INSERT financial iTiedian DEFAULT VALUES INSERT financial iTiedlan DEFAULT VALUES INSEKI financial iTiedian DEFAULT VALUES INSERT financial iTiedian DEFAULT VALUES INSERT financial iTiedian DEFAULT VALUES INSERT f1nancial iTiedian DEFAULT VALUES INSERT financial iTiedian DEFAULT VALUES INSERT financial iTiedian DEFAULT VALUES INSERT f1nancial iTiedian DEFAULT VALUES INSERT financ1al iTiedian DEFAULT VALUES -- Создаем выборку с миллионом значений

WHILE (SELECT TOP I rows FROM sysindexes WHERE id-OBJECT ID(financial iTiedian) ORDER BY indid)< 1000000 BEGIN

INSERT financial iTiedian (c2) SELECT TOP 344640 c2 FROM f1nancial iTiedian

SELECT Создание и заполнение таблицы зaнялo+CAST(DATEDIFF(ss.(Зstaгttime.GETDATE()) AS

varchar)+ секунд SET gstarttime=GETDATE() -- Сортируем выборку

CREATE CLUSTERED INDEX cl ON financiaI iTiedian (cl) ALTER TABLE flnancial iTiedian ADD kl int identity DROP INDEX financial iTiedian.cl CREATE CLUSTERED INDEX.kl ON financiaI iTiedian (kl)

SELECT Сортировка таблицы заняла +CAST(DATEDIFF(ss.?starttiiTie.GETOATE()) AS varchar) + секунд

-- Вычисляем финансовую медиану

DECLARE @starttiiTie datetime. ?rows int t

SET @starttime=GETDATE()

SET STATISTICS TIME ON

SELECT TOP 1 @rows=rows FROM sysindexes WHERE id=OBJECT ID(financ1al median) ORDER BY indid

SELECT B таблице 4-CA5T((3rows AS varchar)+ записей'

SELECT AVG(cl) AS Финансовая медиана равна FROM financial median

WHERE kl BETWEEN (apows / 2 AND (?rows / 2)+SIGN(?rows+l t 2)

SET STATISTICS TIME OFF

SELECT Вычиспение финансовой медианы заняло tCAST(DATEDIFF(ms.(astartt1me.GETDATE()) AS varchar)* миллисекунд'

Создание и заполнение таблицы заняло 73 секунды The clustered index has been dropped.

Сортировка таблицы заняла 148 секунд В таблице 1000000 записей

-1Ь9бЛ257544255732

SQL Server fxecuLor T-i;ies:

CPU tine = 0 ms. elapsed time = 23/ ms.

Еычистен/е фи.aнcoвo медианы зая):о 290 iis

В это.м запросе есть несколько н^тepecиы.\ мо.ментов. Спач1ыа мы создаем таб-.пщу и заполняем ее 1\н1ллиопо.м записей. Каждая итерация цикла заполняет столбец с1 новым случайным значением (все 3ainicn, вставляемые одной операцией, будут и.меть одинаковое случайное значите). Раз.мер табл1П1ы фактически удваивается с кажды.м проходо.м цикла. В каждой итерации выбираются первые 344 640 записей, чтобы гарантировать, что количество элементов в .множестве не превысит лпгмлиои. Ограничение в 344640 записей ие и.меет значения до последнего прохода цикла - до этого мо.мента запрос выбирает все заниси из таблицы financial median и вставляет их назад в таблицу (после предпоследней итерации таблица содержит 655360 .записей; 344 640 = 1000000 - 655360). Хотя мы и не получаем случайное значение в каждой записи, вре.мя, затраченное иа создание выборки, .\нии1мально, так что мы можем приступать к решению основной задачи - к поиску медианы.

Зате.м запрос создает кластерный индекс но столбцу с1, чтобы отсортировать значения в выборке (это необходи.мый шаг для вычиетеиия ее .медианы). После этого в таблицу добавляется identiLy-столбец, он же становится кластерным ипдексо.м. Так как датшш уже находятся в необходи.мо!! иа.м г!Оследователы10сти, к01-да добавляется ideиLity-cтoлбeц, с помошыо пего они просто последовательно ну.меруются. Па последнем шаге вычисляется медиана. Запрос находит об-шее количество записеГ! (чтоб!)! .можно бь!ло определить срединное значение) и возвра!цает среднее двух централь!1ых 3!!ачеи1!Й, есл1! в выборке четное количество 3!1ачений, и просто центральное 31!аче!11!е, есл!1 элементов !!ечетное количество.

В реаль!н>!Х сценариях для вычисле![ия мед!1а!1!)1 скорее вси'о потребуется только последн!!!! u!ar. Выборка уже существовача б!)1 i! была бы отсортирована с использова1!ие\! кластерного индекса. Так как количество з!!ачсний в выборке .может быть 3apai!ee неизвестно, я вк.т10Ч1!л ша!-, которь!Й наход1!т количество записей в таблице с 110,мощь!о небольшо1ю запроса к sysindexes. Я привел его только для полноты карти!1Ы - !ia,\! уже извест1!0 количество зап1!сей, потому что мы создаем выборку !i находи.м .\1ед!!а[!у в од!10м я том же запросе. Вы можете также просто !!спользовать MAX(kl) для В1)14!!сле!!ия количества з!!аче!1ий, так как вы .можете с!10койно предполагать, что identity-столбец kl начи!1ается с едР!!1!!цы и увеличивается последователь!!о:

DECLARE ?starttime datetime. @rows int

SET (astarttime-GETDATEO

SET STATISTICS TIME ON

SELECT iarows=MAX(kl) FROM financial median

SELECT B табли14е +CAST((3rows AS varchar)+ запксей'

SELECT AVG(cI) AS Финансовая медиана равна FROM financial median

WHERE kl BETWEEN larows / 2 AND (larOws / 2)+SIGN((arows+l % 2)

SET STAIISTICS TIME OFF

SELECT Вычисление финансовой медианы заняло +CAST(DATEDIFF(ms.(astartt1me.GETDATE()) AS varchar)+ миллисекунд'

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

Теперь подробнее о том, как это работает: выражение с SIGN() добавляет единицу к количеству значений в выборке, чтобы изменить его с нечетного на четное, или наоборот, затем вычисляет остаток от деления этого числа на 2 (чтобы определить, имеем ли мы дело с четным или нечетным числом) и возвращает 1 или О, в зависимости от знака. Так что для 1000000 записей мы добавляем 1, получаем 1000001, затем находим остаток от деления на два, который равен 1. Затем, мы берем значение функции SIGN() от этого числа, оно равно 1, и добавляем его к количеству записей (поделенному на 2), чтобы вычислить значение kl второй центральной записи. Теперь мы можем вычислить значение AVG() для этих двух значений, чтобы получить финансовую медиану. Для нечетного количества значений остаток был бы равен О, в результате значение SIGN() также было бы равно О, так что оба условия в BETWEEN ссыла^тись бы на одно и то же значение - центральное значение .множества. В конечном счете .медиана вычисляется практически мгновенно. Если таблица создана правильно, вычисление медианы занимает меньше секунды, даже на таком относительно маломощно.м портативном компьютере с процессоро.м с тактовой частотой 166 МГц, на котором я пишу эту книгу. Если учесть, что мы имеем дело с выборкой из миллиона записей, это немалый подвиг.

Это классический пример, в которо.м SQL Server превосходит традиционные языки программирования, потому что он спроектирован для работы с даьшыми. Чтобы вычислить значеш-ie медианы на традиционном языке программирования, нам, скорее всего, потребовалось бы поместить все элеме}ггы с диска в .массив. После того как список отсортирован можно найти центральное(ые) зна-чение(я). Этот гюследний шаг - обычно довольно быстрый благодаря индексации массива. Дольше всего происходит загрузка данных в массив, SQL Server не надо заботиться об этом шаге, так как он .может получать доступ к данным напрямую. Более того, если в подходе с испо.пьзова1шем традициошюго языка будет загружено больше элементов, че.м может поместиться в памяти, некоторые нз них будут выгружены на диск (в виртуальную память), очевид1Ю, что это замедлит процесс заполнения и вычисления медианы.

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

значения ио (итдексу или нросканироваБ массив. Довольно глупо работать с базой данных, как с обычны.м (jjaiLio.vi. Она игнорирует тот факт, что можно попросить SQL Server отсортировать элементы, прежде че.м их вернуть. Она также игнорирует тот факт, что .можно запросить у сервера количество записей перед получение.чг их всех и те.м са.мым избавиться от необходимости загружать всю выборку в память, просто чтобы посчитать количество элементов в пей и вычислить .медиану. За счет этих двух omn.NniBannii - сортировки данных серверо.м и получения количества записе!! заранее - можно у.меньшить необходи.мое количество па.мяти и вре.мя, пеобходи.\юс для заполнения .массива или списка, по крайней мере вдвое

Но са.м SQL Server может сделать это даже enie лучше. Поско;1ьку выборка хранится в 6aie данных, с которой сервер может работать иапря.мую, е.му ие надо ничего загружать в .массив или подобную структуру. Одно только это означает, что работа будет выполнена па порядок быстрее, чем при подходе с использованием трад1пи10И1[Ого языка. Так как данные уже загружены , SQL Server остается только найти .медиану, и, как я уже говорил, если у нас есть последовательный пденттгфикатор sannceii, эта задача становится довольно простои.

Чтобы попять, почему подход на Transact-SQL луч[ие и быстрее, рассматривайте .механиз.мы хранения SQL Server (В-деревья, страницы, экстенты н так далее) как связные списки - очень умные связные списки. Эти списки \югут автоматически отслеживать количество своих эле\гентов, распределение значений, а также непрерьшно поддерживают несколько высокоскоростных способов доступа к своим значеиия.\г Эти списки перемещают себя в физическую память и извлекают из нее с по.мощью сложного .механизма кэширования, которое постоянно уравновешивает распределение значений, а также всегда синхронизируются с постоянной версией на диске, так что никогда нет причины зафужать или сохранять их явно. Данный список может одновременно использоваться иескольки.чт гюль-зователяхги, дост>т1 к нему упрощается авто.матически встроенпы.\г оптимизатором запросов. Это список, к которо.му люгут одновременно прозрачно обращаться несколько потоков и процессоров - за счет этого roжиo задеГютвовать преи.му-щества лгпогопоточности операционных систем Win32 и нескольких процессоров.

С концеит>ачьиой точки зрения механизмы хранения/получения данных SQL Server и больите .массивы в виртуатьной памяти в традиционных языках не так уж и различны; просто .механиз.мы SQL Server на несколько порядков сложнее и совершеннее, чем конструкции обычных языков. Не все .механиз.чня храиеиия-получеиия были созданы сразу идентичными. SQL Server настраивался и перенастраивался, разрабатывался и перерабатывался более десяти лет. Понатобилось много времени, чтобы достичь зрелости. Он вырос в суровььх условиях войны на несколько фронтов, продолжавшеГюя на всем протяжении его жизненного цикла. Несколько ведущих nporpaivi.MHcTOB .мира круглый год работа/ти, чтобы улучшить и ускорить его. Таки.м образо.м, ои обеспечивает более оптимальные механиз.мы хранения и получения данных, че.м в традиционных языках. Не и.меет смысла изобретать велосипед, если вы \южете бесплатно получить эти же воз-.можности от SQL Server, но в.место этого стойко и методично продолжаете использовать скудные возможности своего продукта.

Следует рассмотреть ситуацию, когда выборка изменяется очень-часто. Что случится, если новые записи будут добавляться, например, ежечасно? Столбец kl перестанет быть последовательным идентификатором значений выборки, как тогда мы сможем вычислить .медиану, использзя подход с identity-столбцом? Решение заключается в удалешиг (сластерного индекса по kl вместе с самим столбцом и повторении части, реализуюше!! сортировку:

DROP INDEX financial median.kl

ALTER TABLE financial median DROP COLUMN kl

CREATE CLUSTERED INDEX Cl ON financial median (cl)

ALTER TABLE financial median ADD kl int identity

DROP INDEX financ1al median,cl

CREATE CLUSTERED INDEX kl ON financ1al median (kl)

Очевидно, данная техника неприменима для больших выборок, которые непостоянны по своей природе. Каждый раз, когда выборка изменяется, ее необходимо пересортировать. Обработка с номопило :-)того подхода больших выборок, измеияюн1ихся чаше, чем, скажем, раз в день, представляется проблематичной. Вместо этого необходимо использовать од1Н1 нз методов, описанных далее.

Заметьте, что быстрее было бы вообще опустить два последних шага в фазе сортировки. Если оставить кластерный индекс но с1, вычисление медианы займет значительно больше времени (1-2 с на упомянутом выше портативном компьютере), но весь процесс заполнения, сортировки и гюлучения множества займет на 15% меньше времени. Я включил эти таги, так как в большинстве реальных сценариев данные загружаются и сортируются не очень часто - скажем, раз в день или реже - тогда как .медиана .может вычисляться тысячи раз в день.

Подход с использованием CASE

Также довольно просто можно пычнслнть медиану с использованием CASE. Предположим, для начала, эту таблицу и лаши,1е:

CREATE TABLE #dist (cl int) .

INSERT #d1st VALUES (2)

INSERT #dist VALUES (3)

INSERT #dist VALUES (1)

INSERT #dist VALUES (4)

INSERT #dist VALUES (8)

Этот запрос вернет значеьп-ге .медианы:

SELECT Median=d,cl

FROM #dist d CROSS JOIN #d-;st i

GROUP BY d.cl

HAVING COUNKCASF WHEN .-:] <- d.cl r-lEN 1 ELSE ULL END)(C0UNT(*)+l)/2 Median

В этом примере мы создаем перекрестное обт.единение таблицы #dist с самой собой, затем прнменн.м HAVING, чтобы отфильтровать все, кро.ме значения медианы. Функция САБЕ позволяет вычислить количество зиачегпн i, которые меньше или равны каждому 31шчению d, а затем HAVING ограничивает возвращаемые за-

пней теми значеиия.\и1 d, дия которых ко.мичесгво зиачений i равно точно половине ко.тичества значений в хиюжестве.

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

CREAlt FABLE #clist (Ci mt)

INSERT Idist VALUES (2)

INSERT #disi VALUES (3)

INSERT #01st VALUES (1)

INSERT #dist VALUES (4J

INSERT #dist VALUES (8)

INSERT #dist VALUES (9) -- Четное ксл/1чесгво Згачений

SELECT Median-d.cl

EROM #dist d CROSS JOIN #c>st i

GROUP BY d.cl

HAVING COUNT(CASE UHEN i.cl <= d.cl THEN 1 ELSE NULL END;--CCUNT()/2-l Median

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

CREATE TABLE #dist (cl int) INSERT INTO #dist VALUES (2) INSERT INTO #dist VALUES (3) INSERT INTO #dist VALUES (1) INSERT INTO #dist VALUES (4) INSERT INTO #dist VALUES (8) INSERT INTO #dist VALUES (9)

Вот запрос на Transact-SQL, которы)! вычисляет финансовую медиану:

SELECT Median=CASE C0UNT(*)X2

WHEN О THEN -- Четное количество значений

(d.cKMIN(CASE WHEN i.cl>d.cl THEN i.cl ELSE NULL END))/2.0

ELSE d.cl END -- Нечетное количество FROM ifdist d CROSS JOIN #dist i GROUP BY d.cl

HAVING COUNT(CASE WHEN i.cl <= d.cl THEN 1 ELSE NULL END)=(C0UNT(*)+l)/2 Median

3.500000

Центральные значения выборки - 3 ]i 4, так что запрос, приведенный выше, возвращает 3,5 в качестве финансовой .медианы выборки.

Векторные медианы

Так как Transact-SQL не имеет агрегатной функции MEDIAN(), вычисление векторных или частичных медиан необходимо производить не с помощью обычного подхода с GROUP BY, а как-то по-другому. Рассмотрим следующую таблицу и данные:

CREATE TABLE #dist (kl int. cl int) INSERT #dist VALUES (1.2) INSERT #dist VALUES (2.3) INSERT #dist VALUES (2.1) INSERT #dist VALUES (2.5) INSERT #dist VALUES (5.4) INSERT #dist VALUES (7.8) INSERT #dist VALUES (7.9)

вот модификация первого примера для получения векторной медианы:

SELECT d.kl. d.cl

FROM #dist d CROSS JOIN #dist i

WHERE d.kl=i.kl

GROUP BY d.kl. d.cl

HAVING COUNT(CASE WHEN i.cl<=d.cl THEN 1 ELSE NULL END)=(C0UNT(*)+l)/2 ORDER BY d.kl

kl cl

2 3 5 4

8 этом примере kl - это столбец, определяющий вектор. Если бы в Transact-SQL была агрегатная функция MEDIAN(), kl был бы единственны.м столбцом в списке GROUP BY.

Дублирующиеся значения

Если в выборке присутствуют дублирующиеся значения, такую ситуацию не сможет нормально обработать ни один из приведенных выше запросов, вычисляющих медиану. Фактически все приведенные к настоящему времени запросы вернут NULL или пропустят соответствующую часть, если рядом с медианой будет существовать дублирующееся значение. Это происходит потому, что в этих запросах первый экзе.мпляр таблицы группируется по столбцу с1. Группировка автоматически объединяет повторяющиеся значения, так что запрос не может различить несколько экземпляров одного и того же значения. Для корректной обработки дублирующихся значений необходимо переработать предложение HAVING. Предположим, что у нас есть следующая таблица и данные:

CREATE TABLE #dist (cl int)

INSERT #dist VALUES (2)

INSERT #dist VALUES (3)

INSERT #dist VALUES (1)

INSERT #dist VALUES (3) -- Дублирующееся значение INSERT #dist VALUES (8) INSERT #dist VALUES (9)

вот модификация запроса, возвраи1ак)1цего статис1Т1ческую медиану, с коррект-Hoii обработкой дублирующихся значений:

select d.cl

FROM #dist d CROSS JOIM fdisL i GROOP BY d.cl

hAVlNG (COUNKCASE WEN i.cl <= d cl ;hEN i ELSc KULi. ENDi>=(C0U\ir(*) + l)/2) AND (COUM(C.ASl when i.cl >-d.cl THEN 1 ELSE NLLL END) CCCNr(*)/2+l)

Ана,топ1Чно, вот запрос, возвращающий финансовую медиану, корректно обрабатывающий дублирующиеся значения:

CREAFE TABLE #dist (cl int) INSERT fdist VALUES (2) INSERT #dist VALUES (2) INSERT #dist VALUES (1) INSERT #dist VALUES (5) INSERT #dist VALUES (5) INSERT #dist VALUES (9)

SELECT Median=ISNULL((CASE WHEN COUNKCASE WHEN i.cl<=d cl TliEN 1 ELSE NULL END) > (C0UNT(*)+l)/2 THEN 1.0*d.cl ELSE NULL END)-C0UNT(*)X2.

{d.cl+MiN((CASE WHEN i.cl>d.cl THEN i.cl ELSE NULL END)))/2.0) FRCM fdist d CROSS JOIN #di5t i GROUP BY d.cl

HAVING (COUNT(CASE WHEN i.cl <- d.c; THEN I ELSE NULL END)>=(CCUNT(*}-1)/2) AND (COUNT(CASE WHEN .cl >=d.cl THEN 1 ELSE NUIJ END) >= COUNT(*),/2+1)

Median 3.5

Как вы видите, все немного усложнилось, когда на сцену выпши дублирующиеся значения. Вот вариант запроса, возвращающего финансовую медиану, который использует ключевой столбец (kl) и также обрабатывает дублирующиеся значе1И1я:

CREATE TABLE #dist (kl mt. cl int)

INSERT #dist VALUES (1.2)

INSERT #dist VALUES (2.2)

INSERT #dist VALUES (3.1)

INSERT #dist VALUES (4,4j

INSERT #dist VALUES (5.5)

INSERl #dist VALUES (6.7)

INSERT #dist VALUES (7.8)

INSERl fdist VALUES (8.9)

SELECT Median=AVG(DISTINCT 1.0*cl)

FROM (SELECT dl.cl

FROM #dist dl CROSS JOIN ifdist d2

GROUP BY dl.kl. dl.cl

HAVING SUM(CASE WHEN d2.cl - Gl.cl THEN 1 ELSE 0 END) >= ABS(SUM(CASE WHEN d2.Ci < dl.cl THEN 1 WHEN d2.cl > dl.cl THEN -TElSE 0

END))) a

Median 4.500C00

Отсечение

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

CREATE TABLE #va1ueset (cl -nt)

INSERT #va1ueset VALUES (2)

INSERT #vaiueset VALUES (3)

INSERT #va1ueseL VALUES (IJ

INSERT #valueset VALUES (4)

INSERT #va1ueset VALUES (8)

INSERT #va1ueset VALUES (9)

SELECT y.cl

FROM #va1ueset v CROSS JOIN #vaTjeS9t a GROUP BY v.cl

HAVING v.cl > MIN(a.cI) AND v.cl < MAX(a.cI) Cl

2 3 4 8

Да1гный код использует перекрестное соединение и простое предложение HAVING для исключения минимальных и максимальных значений из множества, но что делать, если мы хотим исключить некоторое количество записей из начала или конца множества? Мы пе можем просто из.менить > MAX(a.cl) на > МАХ(с.с1)+1, потому что мы не знаем, последовательны ли значения (фактически в нашем случае это пе так). Чтобы можгю было приминтгь префиксы/суффиксы размером более одной записи, необходимо переработать предложение HAVING. Вот новый запрос, который .может отсекать префиксы и суффиксы произвольного размера:

SELECT v.cl

FROM #va1ueset v CROSS JOIN #vabeset a GROUP BY v.cl

HAVING COUNT(CASE WHEN a.ci <-v.cl THEN 1 ELSE NULI END) > 2 AND COUNT(CASE WHEN a.cl >- v.cl THEN I ELSE NULI END) >2

Обратите внимание, что этот код довольно гибкий - он позволяет отсекать префиксы и суффиксы разного ра,змера. Первый предикат в предложении HAVING отсекает префикс, а второй обрабатывает суффикс. Сравнение > 2 контролирует размер отсекаемого участка. Чтобы отсечь более двух записей, увеличьте это значение; чтобы отсечь меньше - его необходн.мо уменьшить.