понедельник, 18 апреля 2011 г.

Трюки в SQL – используем CASE

После серии статей “Основы SQL” я решил попробовать вспомнить некоторые нестандартные варианты использования Transact-SQL и начать серию статей “Трюки в SQL”.

В этой статье расскажу прежде всего о полезном но неочевидном использовании выражения CASE. Правда, речь пойдет о достаточно простых приемах (когда их знаешь). Основное содержание:

База данных для примеров

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

Поэтому для запуска примера потребуется БД AdventureWorks. Я пользовался облегченной версией для Microsoft SQL Server 2005 (там, в зависимости от версии, можно выбрать AdventureWorksLT.msi или AdventureWorksLT_x64.msi). Скорее всего, подойдут и более поздние версии с CodePlex.

План выполнения

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

Если серьезно и не вдаваясь в подробности, движку базы данных необходимо знать, в какой последовательности и каким способом соединять таблицы, какие индексы использовать и т.п. Иными словами, перед выполнением запроса необходимо построить (или взять из кэша) план выполнения.

Актуальный план выполнения вы можете посмотреть в SSMS (SQL Server Management Studio), выбрав его в пункте меню “Query / Include Actual Execution Plan” или нажав “Ctrl+M”. В результате, после выполнения пакета запросов вы увидите вкладку “Execution Plan”, в которой для каждого запроса из пакета будет выведен план выполнения в графическом виде.

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

Синтаксис CASE

На всякий случай напомню синтаксис “CASE”, поскольку его используют сравнительно редко:

-- простой вариант: 
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
-- усложненный вариант:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Хочу обратить ваше внимание на то, что “ELSE” можно не указывать, тогда, в случае отсутствия соответствующего “WHEN” вернется NULL.

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

SELECT CASE WHEN 1=1 THEN '0' ELSE 1 END

А так уже нет:

SELECT CASE WHEN 1=1 THEN 'A' ELSE 1 END

Обратите внимание: то же правило, как нетрудно догадаться, работает не только для констант, но и для столбцов таблиц. Так что будьте бдительны – ваш запрос может работать на одних данных (скажем, когда код товара хранится в строке, но содержит только цифры) и не работать на других (добавили в код товара буквы).

SUM по CASE

Итак, на первом месте, по праву – суммирование с условием внутри (возвращающим 0 или 1). Этот прием позволяет вместо нескольких однотипных запросов написать один и, что часто бывает, неплохо улучшить производительность.

Пример несколько синтетический, зато простой – представьте, что нужно периодически узнавать общее количество товаров, а также количество красных и черных товаров. Так можно решить задачу “в лоб”:

SELECT COUNT(*) FROM SalesLT.Product
SELECT COUNT(*) FROM SalesLT.Product WHERE Color = 'Red'
SELECT COUNT(*) FROM SalesLT.Product WHERE Color = 'Black'

А так, используя “SUM по CASE”:

SELECT COUNT(*) AS TotalCount,
SUM(CASE WHEN Color = 'Red' THEN 1 ELSE 0 END) AS RedCount,
SUM(CASE WHEN Color = 'Black' THEN 1 ELSE 0 END) AS BlackCount
FROM SalesLT.Product

Если в SSMS (SQL Server Management Studio) включить вывод актуального плана выполнения, то можно увидеть, что стоимость последнего запроса в два раза меньше, чем суммарная стоимость первых трех.

В качестве лирического отступления – хорошее понимание работы Microsoft SQL Server мне чаще всего грело душу тогда, когда после “неплохого” улучшения производительности запроса, он начинал выполняться в десятки раз быстрее. И еще один интересный факт – мы с моим коллегой (администратором SQL) не сговариваясь заранее сошлись во мнении, что свободное оперирование “SUM по CASE” говорит о хорошем знании SQL. Так что я вам только что рассказал один из секретов, как сойти за знатока SQL :)

ORDER по CASE

Раз уж заговорили про группировку с “CASE”, то стоит упомянуть и сортировку, как правильно мне напомнили в одном из комментариев. Здесь все довольно просто – в “ORDER BY” (как и в “WHERE”, “GROUP BY” и т.п.) мы тоже можем использовать “CASE”, что дает возможность делать более интеллектуальную сортировку.

Хорошим примером будет таблица с категориями товаров (двух-уровневое дерево). Давайте подумаем, как нам вывести эту таблицу как дерево (сначала родительский элемент, потом все его дочерние, потом следующий родительский и т.д.). Если мы будем использовать запрос без сортировки или с сортировкой по родительской категории, то получим сначала все родительские элементы.

Теперь попробуем с “CASE”:

SELECT * FROM SalesLT.ProductCategory 
ORDER BY CASE WHEN ParentProductCategoryID IS NULL
THEN ProductCategoryID
ELSE ParentProductCategoryID END, ProductCategoryID

Уже лучше. Однако нам, если разобраться, просто повезло с порядком идентификаторов. Если мы отсортируем дочерние элементы по названию, то сразу увидим в чем проблема.

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

SELECT * FROM SalesLT.ProductCategory 
ORDER BY CASE WHEN ParentProductCategoryID IS NULL
THEN ProductCategoryID
ELSE ParentProductCategoryID END,
CASE WHEN ParentProductCategoryID IS NOT NULL
THEN Name END

Резюме

Надеюсь, вам было интересно узнать эти приемы или освежить память о них.

В предыдущей серии статей я старался объяснить связанные базовые понятия, что сильно увеличивало объем статей. Теперь, по идее, должны получаться сравнительно короткие (и при этом вполне законченные) статьи, которые, по моему мнению, значительно удобнее читать.

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях или на olegaxenow.reformal.ru. Постараюсь учесть.

12 комментариев:

  1. aka skumo :)

    Есть еще один маленький ньюанс в сортировке по CASE. Если в Case описывает несколько возможных полей сортировки (в Вашем случае это ProductCategoryID и ParentProductCategoryID) то тип всех возможных полей должен быть таким чтобы мог быть неявно приведен сервером к типу ПЕРВОГО поля описанного в CASE-e.
    Пример - скрип ниже.
    При значениях @SortMode равному 1, 2 и даже 3 скрипт будет віполнять отлично, а вот если 4 - валит ошибку

    Msg 245, Level 16, State 1, Line 28
    Conversion failed when converting the nvarchar value '3.14159' to data type int.

    Так что будьте внимательны !
    --------
    IF object_id('tempdb..#TmpTbl', 'U') IS NOT NULL
    DROP TABLE #TmpTbl

    CREATE TABLE #TmpTbl(IId INT, IIdExt INT, NCode NVARCHAR(10), NCodeExt NVARCHAR(10))

    ;WITH LineNumber AS (
    SELECT F1.INo + F2.INo * 10 AS INo
    FROM (
    SELECT 0 as INo
    UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
    UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS F1
    CROSS JOIN (
    SELECT 0 as INo
    UNION SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS F2

    )
    INSERT INTO #TmpTbl
    SELECT ln.Ino, ln.Ino * SIN(ln.Ino), STR(ln.Ino), ln.Ino * PI() FROM LineNumber AS ln

    DECLARE @SortMode INT
    SET @SortMode = 4

    SELECT t.* FROM #TmpTbl AS t ORDER BY
    CASE @SortMode
    WHEN 1 THEN t.IId
    WHEN 2 THEN t.IIdExt
    WHEN 3 THEN t.NCode
    WHEN 4 THEN t.NCodeExt
    END
    ASC

    ОтветитьУдалить
  2. Не спорю, тем более, что я про это написал в главе про CASE :) Или недостаточно акцентировал?

    ОтветитьУдалить
  3. Добрый день, а вот такой вопрос:
    Допустим в процедуру поступает несколько параметров-фильтров.
    Использовать Case вместо копипаста удобно и к примеру получается так:
    select * from table
    where country=
    case @par
    when 1 then 'Ukraine'
    when 2 then 'Russia'
    ...
    when null then country

    (т.е. последняя строчка указывает что все страны попадут в выборку) - насколько это верное решение, повлияет ли лишнее сравнение на скорость?

    И второй вопрос: если в одном случае нужно использованть 'like' - для нестрогого совпадения, а для строгого лучше было бы '=', то можно ли как то конструкцию case применить в этом случае; пока приходилось все фильтровать через like (country like country для строгого) или копипастить всю процедуру с изменением 1й строчки (возможно так все и делают, и ничего такого в этом нет, в общем в этом то заключается и вопрос))
    Спасибо

    ОтветитьУдалить
  4. To @serg

    В общем случае, если заботиться о производительности, лучше разворачивать через "OR".
    Для конкретных случаев, можно посмотреть план выполнения для двух вариантов (при условии, что объем данных достаточен) и выбрать учитывая разницу (или ее отсутствие) стоимости выполнения.

    ОтветитьУдалить
  5. Здравствуйте!
    Хочу спросить: может ли CASE возвращать НЕСКОЛЬКО значений?

    У меня есть дочерняя таблица, у которой ДВЕ родительские таблицы. Для того, чтобы решать, какая именно родительская таблица у каждой записи, есть поле ParentType (если значение 'Database', то родительская таблица [Database], а если 'Server', то родительская таблица [Server].
    Соответственно, для ссылки на запись в родительской таблице поле Parent_ID.
    Пока мне, когда мне нужно было, делая выборку из дочерней таблицы, включать в неё только одно значение из родительской таблицы, проблем не было. Писал так:

    CASE ParentType
    WHEN 'Server' THEN SELECT IP FROM [Server] WHERE [Server].ID=Parent_ID
    WHEN 'Database' THEN SELECT Name FROM [Database] WHERE [Database].ID=Parent_ID
    END AS ParentName

    А теперь я хочу получать ещё одно поле из родительской таблицы. Конечно, я могу в список требуемых значений запроса вставить ещё один аналогичный CASE,но может быть, можно обойтись одним?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте! В вашем случае удобнее вместо подзапросов использовать LEFT JOIN. И, заодно, быстрее работать будет.

      Если не очень уверенно знаете джойны - можно эту статью почитать: https://www.olegaxenow.com/2011/03/osnovy-sql-dml-select-chast-ii.html

      Удалить
  6. Спасибо большое, всё получилось!
    Для меня изначально была проблема в том, что родительских таблиц ДВЕ (была бы одна - воспользовался бы джойном не задумываясь), но я вспомнил про UNION, добавил в родительский запрос псевдостолбец ParentType (с текстовыми константами 'Server' и 'Database') и подвязал дочернюю таблицу как положено.
    Ещё раз спасибо!

    ОтветитьУдалить
    Ответы
    1. Пожалуйста :)
      Я думал про вариант сделать просто два left join (можно с условием по типу родительской таблицы). Но если вариант с union устраивает по производительности - так тоже можно.

      Удалить
    2. Да вот и у меня теперь большие сомнения по производительности UNION, поэтому подумаю над двумя JOIN

      Удалить
    3. Попробовал - получается. Вам спасибо за ликбез - теперь я попробовал, как в "ON" помимо равенства столбцов включать дополнительные условия.
      Издержки: столбцов из родительских таблиц возвращается в два раза больше (соответственно, половина из них пустые), и для столбцов из каждой родительской таблицы нужно отдельное имя.
      Да и само по себе, left join предполагает, что слева родительская таблица, а справа дочерняя, а тут наоборот.

      Удалить
  7. Что будет быстрее на MS SQL (2008, другого пока нет):

    Sum(case тут достаточно много условий end) as Summa

    или

    Sum(поле) as Summa
    where первое условие из case

    union
    Sum(поле) as Summa
    where второе условие из case
    ...
    ???

    ОтветитьУдалить
    Ответы
    1. Попробовал аналог

      SELECT COUNT(*) FROM SalesLT.Product
      SELECT COUNT(*) FROM SalesLT.Product WHERE Color = 'Red'
      SELECT COUNT(*) FROM SalesLT.Product WHERE Color = 'Black'

      А так, используя “SUM по CASE”:

      SELECT COUNT(*) AS TotalCount,
      SUM(CASE WHEN Color = 'Red' THEN 1 ELSE 0 END) AS RedCount,
      SUM(CASE WHEN Color = 'Black' THEN 1 ELSE 0 END) AS BlackCount
      FROM SalesLT.Product

      На своей рабочей БД.
      Получилась экономия в 12%

      Вопрос снят.

      Удалить