Сегодня SQL используют уже буквально все на свете: и аналитики, и программисты, и тестировщики, и т.д. Отчасти это связано с тем, что базовые возможности этого языка легко освоить.
Однако работая с большим количеством junior-ов, мы раз от раза находим в их решениях одни и те же ошибки. Реально — иногда просто создается ощущение, что они копируют друг у друга код.
Кстати, иногда такая же участь постигает и специалистов более высокого полета.
Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали.
Примечание: Ошибки будут 2 видов — реальные ошибки и своего рода best practices, которым часто не следуют.
Но, обо всем по порядку :)
Кстати, будем рады видеть вас в своих социальных сетях — ВКонтакте Телеграм Инстаграм
1. Преобразование типов
Мы привыкли, что в математике мы всегда можем разделить одно число на другое и получить ответ. Если нацело не получается, то в виде дроби.
В SQL это не всегда так работает. Например, в PostgreSQL деление двух целых чисел друг на друга даст целочисленный ответ. Это можно проверить как для целочисленных столбцов, так и для чисел.
SELECT a/b FROM demo
# столбец целых чисел
SELECT 1 / 2
# 0Аналогичные запросы, например, в MySQL дадут дробное число, как и положено.
Если Вы точно не уверены или хотите подстраховаться, то лучше всегда явно делать преобразование типов. Например:
SELECT a::NUMERIC/b FROM demo
SELECT a*1.0/b FROM demo
SELECT CAST(1 AS FLOAT)/2 FROM demoВсе перечисленные примеры дадут нужный ответ.
2. HAVING вместо WHERE
Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!
WHERE производит фильтрацию строк в исходном наборе данных, отсеивая неподходящие. После этого GROUP BY формирует группы и оператор HAVING производит фильтрацию уже целых групп (будто группа — одно запись).
Например:
SELECT date, COUNT(*)
FROM transactions t
WHERE date >= '2019-01-01'
GROUP BY date
HAVING COUNT(*) = 2 Здесь мы сначала отсеиваем строки, в которых хранятся записи до 2019 года. После этого формируем группы и оставляем только те, в которых ровно две записи.
Некоторые же пишут так:
SELECT date, COUNT(*)
FROM transactions t
GROUP BY date
HAVING COUNT(*) = 2 AND date >= '2019-01-01'Так делать не нужно :)
Кстати, для закрепления этой темы мы специально делали задачку «Отфильтрованные продажи» у себя на платформе. Если интересно порешать и другие задачки по SQL - welcome :)
3. Алиасы и план запроса
Если «проговаривать SQL-запрос» словами, то получится что-то такое:
В таблице есть старая цена, а есть новая цена. Их разность я назову diff. Я хочу отобрать только те строки, где значение diff больше 100.
Звучит вполне логично. Но в SQL прям так реализовать не получится - и многие попадаются в эту ловушку.
Вот неправильный запрос:
SELECT old_price - new_price AS diff
FROM goods
WHERE diff > 100Ошибка его заключается в том, что мы используем алиас столбца diff внутри оператора WHERE.
Да, это выглядит вполне логичным, но мы не можем так сделать из-за порядка выполнения операторов в SQL-запросе. Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка:
ERROR: column "diff" does not existПравильно будет использовать подзапрос или переписать запрос следующим образом:
SELECT old_price - new_price AS diff
FROM goods
WHERE old_price - new_price > 100Важно: Внутри ORDER BY вы можете указывать алиас - этот оператор выполняется уже после SELECT.
Кстати, мы тут делали карточку, где наглядно показывается последовательность выполнения операторов. Возможно, это вам пригодится.
4. Не использовать COALESCE
Пришло время неочевидных пунктов. Но сейчас мы поясним свои чаяния.
COALESCE - это оператор, который принимает N значений и возвращает первое, которое не NULL. Если все NULL, то вернется NULL.
Нужен этот оператор для того, чтобы в расчеты случайно не попадали пропуски. Такие пропуски всегда сложно заметить, потому что при расчете среднего на основании ста тысяч строк вы вряд ли заметите подвох, даже если 1000 просто будет отсутствовать. Обычно такие численные пропуски заполняют средними значениями/минимальными/максимальными/медианными/средними или с помощью какой-то интерполяции — зависит от задачи.
Мы же рассмотрим нечисловой пример, а вполне себе бизнесовый. Например, есть таблица клиентов Clients. В поле name заносится имя пользователя.
Отдел маркетинга решил сделать email-рассылку, которая начинается с фразы:
Приветствуем, имя_пользователя!
Очевидно, что если name is NULL, то это превратится в тыкву:
Приветствуем, !
Вот в таких случаях и помогает COALESCE:
SELECT COALESCE(name, 'Дорогой друг') FROM ClientsСовет: Лучше всегда перестраховываться. Особенно это касается вычислений и агрегирований - там вы не найдете ошибку примерно никогда, так что лучше подложить соломку.
5. Игнорирование CASE
Если вы используете CASE, то иногда вы можете сократить свои запросы в несколько раз.
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
Пользователь предложил такое решение:
SELECT id, sum FROM transactions t WHERE type = 0
UNION ALL
SELECT id, -sum FROM transactions t WHERE type = 1В целом, не так плохо. Но это всего лишь промежуточный запрос, задача была намного масштабней и таких конструкций в итоге было наворочено очень много.
А вот то же самое с CASE:
SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t Согласитесь, получше?
Так более того, CASE можно использовать еще много для чего. Например, чтобы сделать из «длинной» таблицы «широкую».
А еще, кстати, COALESCE, который мы обсуждали выше — это просто «синтаксический сахар» и обертка вокруг CASE. Если интересно — мы подробно это описали в статье.
6. Лишние подзапросы
Из-за того, что многие пишут SQL-запросы также, как это «звучит» в их голове, получается нагромождение подзапросов.
Это проходит с опытом — начинаешь буквально «мыслить на SQL» и все становится ок. Но первое время появляются такие штуки:
SELECT id, LAG(neg) OVER(ORDER BY id) AS lg
FROM (
SELECT id, sm, -sm AS neg
FROM (
SELECT id, sum AS sm FROM transactions t
) t
) t1И это еще не все — можно и побольше накрутить. Но зачем так, если можно так:
SELECT id, LAG(-sum) OVER(ORDER BY id) FROM transactions t Совет: Если пока сложно, не надо сразу бросаться писать оптимизированными конструкциями. Напишите сначала, как сможете, а потом пытайтесь сократить.
Как говорил дядюшка Кнут:
Преждевременная оптимизация — корень всех зол
7. Неправильное использование оконных функций
Вообще говоря, оконные функции — довольно продвинутый инструмент. Считается, что им владеют специалисты уровня Middle и выше. Но по факту, их нужно знать всем — сейчас без них уже сложно жить (это чистое имхо).
И если базовые вещи по оконным функциям можно освоить довольно быстро, то всякая экзотика и нестандартное поведение осваивается, как правило, только на собственных шишках.
Одна из таких вещей — поведение оконной функции LAST_VALUE и прочих.
Например, когда мы пишем запрос:
WITH cte AS (
SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
UNION
SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
union
SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
UNION
SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year) AS emp
FROM cte cМы ожидаем увидеть 2 раза по 10 для департамента Маркетинг и 2 раза по 25 для Продаж. Однако такой запрос дает иную картину:
Получается, что запрос тупо продублировал значения из столбца employees. Как так?
Лезем в документацию PostgreSQL и видим:
Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей.
Ага, вот и ответ. То есть каждый раз у нас окно — это не весь набор строк, а только до текущей строки.
Получается, есть два способа вылечить такое поведение:
Убрать ORDER BY
Добавить определение рамки
Вот, например, второй вариант:
WITH cte AS (
SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year
UNION
SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year
union
SELECT 'Sales' AS department, 35 AS employees, 2018 AS year
UNION
SELECT 'Sales' AS department, 25 AS employees, 2019 AS year
)
SELECT c.*,
LAST_VALUE(employees) OVER (
PARTITION BY department
ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS emp
FROM cte cКстати, такую тему подняла наша подписчица в Телеграме под постом «7 самых важных оконных функций». Спасибо ей!
А вас рады будем видеть в числе подписчиков :)
Эпилог
Эти 7 ошибок — не единственные, которые часто встречаются среди новичков и даже профессионалов. У нас есть еще одна пачка тезисов по этому поводу — но это уже тема другой статьи.
Если вам есть что добавить — будем рады продолжить обсуждение в комментариях. Возможно, чей-то код станет лучше и чище в результате нашей беседы :)
Комментарии (66)
Danik-ik
16.02.2022 19:45+3LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Я бы не стал заморачиваться, сделал бы по-простому:
FIRST_VALUE(...) (... ORDER BY ... DESC).
ITResume Автор
16.02.2022 19:57Ну бывают иногда ситуации, на самом деле, когда такие косяки происходит. Как минимум, стоит быть настороже)
Но тут у вас лайфхак получился, да)
Geckelberryfinn
16.02.2022 20:31+3Часто сталкивался с непониманием в разнице запросов
SELECT ... FROM A LEFT OUTER JOIN B ON A.ID=B.ID AND A.FIELD=<Value>
И
SELECT ... FROM A LEFT OUTER JOIN B ON A.ID=B.ID WHERE A.FIELD=<Value>
edo1h
17.02.2022 02:22ON A.ID=B.ID AND A.FIELD=<Value>зачем так писать? с целью запутать?
izogfif
17.02.2022 02:49Иногда ключ составной. Ну или из модели данных известно, что имеет смысл брать только те строки, для которых A.FIELD имеет какое-то определенное значение.
edo1h
17.02.2022 03:03+2Иногда ключ составной.
покажите пример
Ну или из модели данных известно, что имеет смысл брать только те строки, для которых A.FIELD имеет какое-то определенное значение.
так привели же альтернативу
JOIN B ON A.ID=B.ID WHERE A.FIELD=<Value>у меня конструкция
FROM A JOIN … ON A.FIELD=constвызывает ассоциации сif (a=b-1)в си: синтаксически корректно, и может использоваться осмысленно, но неочевидно, значит, это зло.
вJOIN … ON …должны указываться условия связи таблиц и точка.
pankraty
17.02.2022 06:42Если мы джойним к таблице B, но не хотим рассматривать записи с признаком IS_DELETED (ну или STATUS='Deleted'), расскажите, пожалуйста, как это сделать через WHERE. Я напомню, что речь про LEFT JOIN, т.е. записи в таблице A мы отсекать не планируем.
(Да, это решаемо, например, с использованием WITH или VIEW, например, но все-таки куда проще через ON IS_DELETED=FALSE).
edo1h
17.02.2022 09:23но не хотим рассматривать записи с признаком IS_DELETED
в какой таблице?
pankraty
17.02.2022 09:41В таблице B
edo1h
17.02.2022 10:37+1посмотрите на запрос внимательнее, там фильтр по
A.FIELDpankraty
17.02.2022 10:58OK, мы хотим вывести все договоры (таблица A), и для тех из них, у которых статус "Просрочен очередной платеж" хотим вывести дату последнего платежа, которую получаем из приджойненой таблицы B. Как выразить это через WHERE?
npocmu
17.02.2022 11:05WHERE тут совсем не нужен. Все можно сделать через CASE
pankraty
17.02.2022 11:16Можно, но зачем? Тем более, если доля просроченных записей невелика, а выборка большая, сделав более селектиный джойн мы еще и запрос сделаем более эффективным, тогда как в случае CASE данные из связанной таблицы будут запрошены, даже если они нам не понадобятся.
npocmu
17.02.2022 12:02+1Ну вопрос же ваш звучал так:
Как выразить это через WHERE?
а не "зачем" :). А так то я не спорю с нужностью дополнительных условий в JOIN.
Olgeir
17.02.2022 16:07Условие написанное в WHERE будет применено позже объединения таблиц и применяться будет ко всему объёму записей, который получился в результате объединения.
В случае больших таблиц, условие в JOIN позволяет сразу отбросить лишние записи.
npocmu
17.02.2022 16:46+3Это еще одно БОЛЬШОЕ заблуждение думать, что движок СУБД будет выполнять запрос так как вы думаете. :)
Для того и существует внутри его оптимизатор запросов. Который, скорее всего, сделает все строго наоборот! То есть сначала отфильтрует записи (особенно если есть соответствующий индекс), а затем будет выполнять объединение.Пытаться оптимизировать таким способом запросы бесполезно.
npocmu
17.02.2022 10:35+5зачем так писать? с целью запутать?
Я вижу Geckelberryfinn прав. Понимания как работает JOIN никакого...
SELECT… FROM A LEFT OUTER JOIN B ON A.ID=B.ID AND A.FIELD=<Value>
Так пишут, когда нужно вывести ВСЕ строки из таблицы А и только для некоторых подключить поля из таблицы B.
SELECT… FROM A LEFT OUTER JOIN B ON A.ID=B.ID WHERE A.FIELD=<Value>
А так, когда надо вывести только те строки из таблицы А в которых A.FIELD=<Value> и подключить для них поля из таблицы B.
Вот более реалистичный пример:
SELECT Parts.*, COALESCE(Manufacturer1.comment, Manufacturer2.descr, Parts.Description) AS Description FROM Parts LEFT OUTER JOIN Manufacturer1 ON Parts.extID=Manufacturer1.num_id AND Parts.manufacturer='m1' LEFT OUTER JOIN Manufacturer2 ON Parts.extID=Manufacturer2.partID AND Parts.manufacturer='m2'Запрос с WHERE выдаст ну абсолютно не то что нужно, даже если не обращать внимание на количество строк, т.к. нумерация у каждого производителя своя и может запросто пересекаться. Аналогом же будет следующий запрос:
SELECT Parts.*, CASE Parts.manufacturer WHEN 'm1' THEN COALESCE(Manufacturer1.comment,Parts.Description) WHEN 'm2' THEN COALESCE(Manufacturer2.descr,Parts.Description) ELSE Parts.Description END AS Description FROM Parts LEFT OUTER JOIN Manufacturer1 ON Parts.extID=Manufacturer1.num_id LEFT OUTER JOIN Manufacturer2 ON Parts.extID=Manufacturer2.partIDНо, мне кажется, первый запрос проще в понимании и более оптимальный в плане производительности.
edo1h
17.02.2022 10:46+1Вот более реалистичный пример:
признаю, был неправ, смысл в таком использовании может присутствовать.
Понимания как работает JOIN никакого...
нет, непонятно было не как работает, а зачем
npocmu
17.02.2022 11:58+2смысл в таком использовании может присутствовать.
Скажем так… В правильно спроектированных БД такое использование — экзотика. Но, т.к. в жизни правильно спроектированные БД сами являются экзотикой :), то этот прием применяется довольно часто.
Вот еще пример из жизни, я его называю "switch JOIN":SELECT Main.*, COALESCE(T3.id,T2.id,T1.id) refT FROM Main LEFT OUTER JOIN T T1 ON Main.Cod = T1.Cod AND T1.Flag=1 LEFT OUTER JOIN T T2 ON T1.id IS NULL -- второй раз подключаем справочник, если по полю Cod соответствия не найдено. AND Main.Num BETWEEN T2.MinNum AND T2.MaxNum AND T2.Flag=2 LEFT OUTER JOIN T T3 ON T2.id IS NULL AND Main.Text Like T3.Pattern AND T3.Flag=3Подключаем записи из справочника в соответствии с приоритетом. Если найдено совпадение по полю Cod, то берутся они. Если ни одной такой записи не найдено, то пробуем найти соответствие по полю Num, и, наконец, если ничего не нашли, то по полю Text. Я вот так сходу и не соображу как это красиво переписать без использования констант в предикатах.
нет, непонятно было не как работает, а зачем
Надеюсь разъяснил :)
npocmu
17.02.2022 11:02+1Еще более интересная ситуация с FULL OUTER JOIN!
Если я вижу как запрос:SELECT ... FROM A FULL OUTER JOIN B ON A.ID=B.ID AND A.FIELD=<Value>так и запрос
SELECT ... FROM A FULL OUTER JOIN B ON A.ID=B.ID WHERE A.FIELD=<Value>то, с вероятностью 99% они оба ошибочные и делают не то, что имел в виду автор.
Правильный паттерн для FULL OUTER JOIN обычно такой:SELECT ... FROM ( SELECT * FROM A WHERE A.FIELD=<Value> ) A FULL OUTER JOIN B ON A.ID=B.ID
Akina
16.02.2022 20:58+11Аналогичные запросы, например, в MySQL дадут дробное число, как и положено.
Нет никаких дробных чисел. Ни в одной из указанных СУБД. Есть целые (INTEGER и т.п.), есть вещественные точные (DECIMAL, NUMERIC и т.п.), есть вещественные с плавающей точкой (FLOAT, DOUBLE). Дробных - нет.
Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!
Дальнейшее объяснение - сплошной бред. То, где применять условие, определяется исключительно требуемой для получения результата логикой. И логика требует либо там, либо там. А если условие требует применения его во WHERE, но его пытаются применить в HAVING - это разве что свидетельство того, что программист неправильно понял задачу.
WHERE производит фильтрацию строк в исходном наборе данных, отсеивая неподходящие. После этого GROUP BY формирует группы и оператор HAVING производит фильтрацию уже целых групп (будто группа — одно запись).
А это вообще безобразно-неверное утверждение - с учётом того, что статья тегована для MySQL.Там запрос вообще может не содержать GROUP BY. И при этом содержать HAVING. В том числе именно для того, чтобы не повторять выражение поля выходного набора в условии отбора. То же - и по "ошибке номер три".
4. Не использовать COALESCE
А тут у меня полное ощущение, что дальше идёт строго обратное утверждение - использовать COALESCE надо. Во всяком случае, нет ни полслова о том, что его НЕ надо использовать.
Очевидно, что если name is NULL, то это превратиться в
тыкву:Смотря как именно это сделать. А то и вся фраза превратится в тыкву, а не только её часть.
К слову, если name - пустая строка, то тыква будет та же, и COALESCE не поможет. А поможет достаточно красивая конструкция с редко(и незаслуженно)используемой NULLIF. Лучше было бы её показать.
А вот то же самое с CASE:
...
Согласитесь, получше?
Не соглашусь. Логика второго запроса не соответствует ни логике первого, ни заданию. Он просто ошибочный.
Кстати, неплохой вопрос для собеседования - если кандидат напишет вариант с CASE как в статье, ему жирный минус.
7. Неправильное использование оконных функций
Не увидел неправильного использования. Увидел только рассказ про незнание того, каковы границы фрейма по умолчанию.
Это при том, что именно неправильного использования оконных функций - вагон и маленькая тележка. То они во WHERE, то они в агрегирующем запросе, то ещё где... но вот именно таких примеров и нет.
PS. У меня по прочтении создалось очень неприятное ощущение. Впечатление такое, что позвал начальник работника и сказал: "Придумалось мне шикарное название для статьи, и чтобы оно не пропадало, запили-ка ты мне к завтрему статью на хабре с этим названием...". Он и запилил.
SevenLines
16.02.2022 22:26+2Хм, а что не так с case? И почему кандидату жирный минус? А то коммент у вас весьма агрессивный, придирки к словам (как будто если назвать вещественные числа дробными собеседник вам не поймет). Вы покажите как правильно писать тогда, а то having у вас не тот, coalesce не правильный, case неверный. По большей части вполне себе часто встречающиеся косяки начинающих sqlщиков, на которые не лишний раз стоит обратить внимание, статья как статья, говорит о правильных вещах, без воды, кому-то обязательно пригодится
Akina
16.02.2022 23:27+2а что не так с case? И почему кандидату жирный минус?
Более подробно я это раскрыл в другом комментарии, см. выше (в ответ на комментарий itresume, время его комментария 18:28, моего комментария 21:02).
придирки к словам (как будто если назвать вещественные числа дробными собеседник вам не поймет)
Слова, которые собеседник авось поймёт, допустимы под пиво на лавочке. А в технической статье допустимы только точные термины.
По большей части вполне себе часто встречающиеся косяки начинающих sqlщиков
Знаете, я не один год и не на одном форуме помогаю именно по SQL. И по опыту могу сказать - только самый последний косяк, с неправильным применением оконных функций, действительно частый. Причём, как правило, не по причине неверно заданного фрейма (LAST_VALUE вообще крайне редко применяется начинающими), а по причинам, описанным мной в предыдущем комментарии. Ну ещё туда-сюда шестой косяк - да, встречается, но нечасто. Остальное - это скорее выдуманная экзотика, чем реальная практика.
SevenLines
17.02.2022 07:05+1А я б наоборот за union минус бы влепил. Потому что с юнион код не поддерживаемый и как его в реальный случай влепить (о чем кстати автор упоминает) непонятно. И не дай бог ещё лепить начнут. А с case, одно уточняющие слово соискателю и если он правит сразу понятно сечет человек или нет.
По поводу корректных определений, отчасти согласен, но когда речь идёт о полуформальной статье (я техническую статью тут не вижу, это статья из типа хозяйке на заметку) то это из плана не к чему придраться, придерись к грамматике
Сидеть на форумах конечно полезно, а если вы так долго сидите, я так понимаю вам скорее всего на простые вопросы отвечать уже не интересно. А там, в глубине форумов, совсем зелёные sqlщики пишут совсем зелёные вопросы. И вот такая статья собственно и позволяет уменьшить количество мусорных вопросов.
Akina
17.02.2022 11:55+4Потому что с юнион код не поддерживаемый и как его в реальный случай влепить (о чем кстати автор упоминает) непонятно.
Насчёт поддержки ничего не буду говорить. Обычно все проблемы поддержки определены тем, что инструменты поддержки в принципе не реализуют её для кода на стороне SQL-сервера. Но так это претензии к инструменту, а не к методу. Кстати, именно отсюда как правило и вылезают гениальные советы использовать SQL серверы как тупое хранилово, а всю логику реализовывать в клиенте. Ну а что при этом лишние гигабайты туда-сюда по сети летают - так это мелочи, сети у нас нынче широкие...
Но из реальной практики - CASE в списке вывода именно как альтернатива UNION весьма редок, в подавляющем большинстве случаев CASE суют в условия отбора. А вот тут запрос c UNION ALL по производительности кроет вариант с CASE как бык овцу. Потому что каждый отдельный подзапрос в UNION прекрасно оптимизируется и использует индексы, тогда как у запроса с CASE вообще нет шансов избежать fullscan.
SevenLines
17.02.2022 13:57Я к тому что если попросили сконвертить поле, а мне выдали бы union, я б сильно призадумался. Поэтому согласно постановку задаче case вполне адекватное решение. Про фильтры там вроде вопроса не была
А так и на case индекс можно навесить
Akina
17.02.2022 15:20+1У меня такое впечатление, что мы разные статьи читали...
Цитирую:
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
Где тут хоть полслова про конвертацию?
Задача поставлена вполне чёткая и однозначная - вывести -sum при type=1 и +sum при type=0. Ограничения на type не описаны - значит, другие значения (включая NULL) могут присутствовать. Необходимость выведения таких записей не указано - значит, их не выводить. Решение кандидата корректно, ибо полностью соответствует задаче, решение с CASE некорректно, потому что выведет записи, необходимость вывода которых не указана.
А так и на case индекс можно навесить
Можно, конечно. Но индекс для одного запроса? да ещё с необходимостью воспроизводить выражение индекса литерально? маловероятно, что существование такого индекса будет признано разумным.
Ivan22
17.02.2022 17:50если есть индекс по type - скорость будет одинаковой, если нету - case быстрее
ITResume Автор
16.02.2022 23:56По большинству пунктов не увидели противоречия в Ваших высказываниях с тем, что мы описали в статье :)
Что касается COALESCE - да, мы как раз говорим, что его использовать нужно. А про NULLIF согласны - тоже крайне полезная штука. Тем более они дополняют друг друга.А про общее ощущение по статье... Мне кажется, у Вас просто довольно высокий уровень. Статья призвана помочь не гулять по граблям довольно неопытным ребятам. Ну или тем, кто по какой-то причине пропустил тот или иной пункт - может в практике не довелось наткнуться. Поверьте, для них это будет полезно, проверено многократно ;) Мы же из наработанной статистики эти косяки взяли, а не из головы)
Akina
17.02.2022 12:03А про общее ощущение по статье...
Ощущение в значительной степени навеяно, вероятно, тем, что описываемые Вами проблемы реально на форумах практически не встречаются.
Вот, например, косяки, связанные с не-учётом приоритетов - это весьма распространённая проблема. Причём ладно бы там с логическими операторами в условиях отбора.
Вот типичный пример действительно часто встречающейся проблемы приоритетов:
SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.f1=t3.f1 AND t2.f2=t3.f2Результат - "Unknown column t1.f1". Причина которого для начинающего совсем даже не очевидна.
Ivan22
17.02.2022 17:51я уже лет 15 объясняю новичкам, что никаких джоинов через запятую использовать не следует, если не хочется запутать самого себя и всех вокруг
edo1h
17.02.2022 02:01+3Нет никаких дробных чисел. Ни в одной из указанных СУБД. Есть целые (INTEGER и т.п.), есть вещественные точные (DECIMAL, NUMERIC и т.п.), есть вещественные с плавающей точкой (FLOAT, DOUBLE). Дробных — нет.
вы неправы, с точки зрения математики все эти numeric и float — рациональные (или дробные) числа. и для записи их на компьютере (в sql в том числе) обычно используется десятичная дробь
У меня по прочтении создалось очень неприятное ощущение
вот с этим согласен, унылое «послевкусие» от статьи
Akina
17.02.2022 12:12с точки зрения математики все эти numeric и float — рациональные (или дробные) числа
А как насчёт с точки зрения их реализации? Для FLOAT вполне нормально, когда два выражения не равны, потому что одно даёт 2, а второе 1,9999999. А вот у DECIMAL этой проблемы нет. А когда есть - то это проблема не от неточного представления, а от недостаточной (неправильно выбранной) точности.
Опять же с точки зрения реализации - именно дробное число реализуется не как одно значение, а как пара целых чисел, числитель-знаменатель. Достаточно редкий тип данных, по-моему, вообще нигде не реализованный как встроенный...
edo1h
17.02.2022 16:12Опять же с точки зрения реализации — именно дробное число реализуется не как одно значение, а как пара целых чисел, числитель-знаменатель
именно так и хранятся и decimal, и float. только для хранения знаменателя используются очевидные оптимизации.
в первом случае знаменатель фиксирован и является целой степенью 10, во втором знаменатель является степенью 2, плюс добавляется целая (возможно отрицательная) десятичная экспонента.P. S. вам словосочетание «десятичная дробь» ничего не говорит? 10.25, например.
это тоже дробь, отличается степенью 10 в знаменателе и способом записи. тип decimal именно это и реализует же.
KislyFan
16.02.2022 21:12+2Игнорирование CASE
Иногда игнорирование CASE и тупое UNION запросов с разными фильтрами WHERE способно существенно ускорить время выполнения
ITResume Автор
16.02.2022 23:51Само собой, это не какая-то волшебная пилюля. Но, как минимум, в каждой ситуации стоит подумать - а оправдан ли здесь юнион или можно просто кейс.
Ivan22
17.02.2022 17:53это как??
в запрос с case тоже можно (и нужно) поставить WHERE type in (0,1) и заюзать индекс и никакой юнион быстрее не будет никак
KislyFan
17.02.2022 23:23Тема достойна отдельного исследования) Но из личного (субьективного) опыта, запросы не всегда написаны оптимально, а COALESCE и CASE очень любят пихать всюду, как серебрянную пулю чтобы собрать один запрос который будет
повеливать всемилаконичнен и читаем. Поэтому, например, когда CASE сидит в переусложненных условиях join'ов, зачастую дешевле разобрать сложные условия свичей на запросы соединенные юнионом. Ну и насколько я помню, внутренний оптимизатор запросов очень даже рад видеть UNION ALL.
unsignedchar
16.02.2022 21:28Так то целочисленное деление и в С может неожиданно удивить ;)
ITResume Автор
16.02.2022 23:48+1Если хочется испытать боль - просто можно порешать задачи целочисленного программирования)) Аж сердце закололо)
abratash
16.02.2022 21:42В статье не нашёл одну из наиболее встречающихся «неточностей» — когда есть таблица с Column_name, Column_id и при вычислении каких либо агрегатов используется GROUP BY по текстовым полям, вместо группировки по ID. Гораздо медленнее отрабатывает, чем группировка по ID, особенно при росте объемов данных.
Как вариант «best practicies» — сначала группировать по Column_id, а потом подтягивать текстовые значения джойном.
Akina
16.02.2022 23:33+1Вижу два варианта.
Вариант первый - в паре Column_name, Column_id при одном и том же значении одного поля в разных записях во втором поле также будут одинаковые значения. В этом случае "неточность" не в том, что для группировки используется поле строкового типа (хотя и это тоже), а имеется проблема денормализованной структуры данных. Которую править надо не изменением текста запроса, а более пристальным рассмотрением структуры БД и, скорее всего, изменением её с вынесением Column_name в отдельную связанную таблицу.
Вариант второй - описанного однозначного соответствия нет. Но тогда совет из Вашего комментария неприменим.
npocmu
17.02.2022 12:07описанного однозначного соответствия нет. Но тогда совет из Вашего комментария неприменим.
Применим и еще как, но с использованием подзапроса с ROW_NUMBER() AS n и дальнейшего фильтра по n=1
Akina
17.02.2022 12:28Ну как он может быть применим на данных типа
Column_name | Column_id name 1 | 1 name 2 | 2 name 1 | 2 name 2 | 3и запросе типа
SELECT Column_name, MAX(Column_id) greatest_id FROM table GROUP BY Column_nameКуда тут применить "сначала группировать по Column_id, а потом подтягивать текстовые значения джойном"?
с использованием подзапроса с ROW_NUMBER() AS n
Во-первых, надо, чтобы ROW_NUMBER был реализован (систем, работающих на MySQL 5.7, а порой и младше - пока ещё предостаточно, хостеры как-то не рвутся обновлять свои СУБД). Во-вторых, при весьма обширной таблице вычисление ROW_NUMBER для каждой записи может отправить сервер в нирвану, тогда как запрос с группировкой при наличии индекса по (Column_name, Column_id) будет достаточно быстрым.
npocmu
17.02.2022 14:52Куда тут применить "сначала группировать по Column_id, а потом подтягивать текстовые значения джойном"?
Ну как бы вы в своих примерах уже совсем, совсем далеко уехали от исходной формулировки abratash. Который призывал при наличии возможности группировки одновременно как по имени так и по коду, отдать предпочтение группировке по коду. В вашем примере
SELECT Column_name, MAX(Column_id) greatest_id FROM table GROUP BY Column_nameникаких взаимозаменяемых группировок нет и поэтому и применить нечего.
Вот пример соответствующий формулировке abratash.
SELECT Column_id, Column_name, SUM(amount) FROM table GROUP BY Column_id, Column_nameКогда есть однозначное соответствие Column_id, Column_name можно написать:
SELECT T.Column_id, N.Column_name, T.amount FROM ( SELECT Column_id, SUM(amount) amount FROM table GROUP BY Column_id ) T INNER JOIN (SELECT DISTINCT Column_id,Column_name FROM table) N ON T.Column_id = N.Column_idЯ, например, не уверен что это будет работать быстрее во всех случаях. А скорее всего для обычных имен даже и медленнее.
Если нет однозначного соответствия Column_id и Column_name (название не нормализовано и может содержать орфографические и прочие ошибки), и какое конкретно название выберется не принципиально, то можно написать в качестве подзапроса N:
(SELECT Column_id, MAX(Column_name) AS Column_name FROM table GROUP BY Column_id) NЕсли важно взять последнее название, то тогда в ход идет ROW_NUMBER() :
( SELECT Column_id, Column_name FROM ( SELECT Column_id, Column_name, ROW_NUMBER() OVER (PARTITION BY Column_id ORDER BY Date DESC) n FROM table ) WHERE n=1 ) NСразу говорю, я с вами полностью согласен, что, в этом случае, это плохо спроектированная БД. Но, в реальной жизни, с какими только данными не приходится работать, увы.
Akina
17.02.2022 15:35Ну как бы вы в своих примерах уже совсем, совсем далеко уехали от исходной формулировки abratash. Который призывал при наличии возможности группировки одновременно как по имени так и по коду, отдать предпочтение группировке по коду.
Простите, Вы не могли бы ткнуть пальцем в то место, где он указывает на возможность "группировки одновременно как по имени так и по коду"? я ничего подобного не вижу. Я вообще не вижу чего-либо, указывающего на связь между значениями этих полей. Кроме похожих имён.
Я, например, не уверен что это будет работать быстрее во всех случаях. А скорее всего для обычных имен даже и медленнее.
Всё зависит от того, какие индексы имеются в таблице. Но при прочих равных комплексный запрос потребует больше чтений, и соответственно будет медленнее. Кстати, при наличии индекса по текстовому полю разница между группировкой по тексту и по числу на самом деле невелика. Она начнётся в тот момент, когда промежуточный диапазон поиска по числу уже укладывается в страницу индекса, а по тексту ещё нет. Но так как из индекса крайне редко читается именно одна страница (а тут ещё ОС поможет со своим кэшированием и предчтением, СУБД попросила страницу 16 кб, а ОС прочитает под это дело 1-4 мегабайта), то разница будет ещё менее значительна.
valkumei
16.02.2022 22:38+3Чаще всего абсолютно бесполезно говорить "так делать не нужно" не поясняя почему. Например - рассмотрев план запроса, причем желательно конкретного.
ITResume Автор
16.02.2022 23:50Ну тут большинство пунктов не про неоптимальность (хотя такое тоже есть, как например с CASE). Тут именно про возможные ошибки - как было описано про COALESCE, про LAST_VALUE и прочее :)
pankraty
17.02.2022 06:56Еще ошибка, с которой встречался больше одного раза. Допустим, мы связываем три таблицы A, B, C такие, что мы знаем, что для каждой записи в B есть запись в C, и их можно связывать через INNER JOIN: B JOIN C ON...
При этом не для каждой записи в A есть соответствие в B, и тут нужен LEFT JOIN.
В результате получается что-то вроде
FROM A LEFT JOIN B ON... JOIN C ON...
И в результате записи из A отсекаются, как если бы мы применили A JOIN B.
На модельном примере с тремя таблицами очевидно, а в большом запросе может быть непросто заметить, что замены одного INNER на LEFT может быть недостаточно, и надо "каскадно" заменить остальные INNER-ы, которые могут повлиять на результирующий набор строк.
npocmu
17.02.2022 10:48+3На этот случай есть простое правило: после первого в цепочке LEFT JOIN могут идти только LEFT JOIN. Если это не так, то нужно группировать запросы с помощью скобок или использовать подзапросы. Мне ближе второй вариант.
aleksandy
17.02.2022 11:00+1При этом не для каждой записи в A есть соответствие в B, и тут нужен LEFT JOIN.
from a left join b join c on b.id = c.b_id on a.id = b.a_idAkina
17.02.2022 12:06Такой вариант без скобок должен приводить к syntax error. Правильно
from a left join ( b join c on b.id = c.b_id ) on a.id = b.a_id
LeVoN_CCCP
17.02.2022 22:42Самая большая ошибка с обоих сторон (как пишущего так и исправляющего) делать что-то не видя задачу глобально. И под этим я имею в виду не только что надо бизнесу, а также общей схемы БД и не имея представления о примерной выборке каждой части запроса, чтобы представить как он может пойти.
>>Как говорил дядюшка Кнут:
>Преждевременная оптимизация — корень всех зол
Эту цитату я обожаю кстати. Тут же недавно был прекрасный ответ на это, я тоже процитирую (автора привести не смогу — он не умер и не писал книг, потому не запомнился): «Пока твои неудачные решения не насрали тебе лично, ты не будешь считать их неудачными.»
AlexMih
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
В задаче не указано, как быть, если type принимает иные значения. Пользователь дал буквальное решение предложенной задачи - не выводить иных записей.
А вот ваш вариант с CASE даст иной результат.
Не знаю,какой из них вы сочтете желательным, но во всяком случае не стоит утверждать " А вот то же самое с CASE".
ITResume Автор
Там просто данные так устроены, что всего 2 значения - либо 0, либо 1. Поэтому в данном контексте
CASEвполне решает полностью идентично.Надо было просто указать это в тексте задачи, согласны :)
Emulyator
SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions tеще так можно:
SELECT id, sum*(-2*type+1) FROM transactionskost
WHEN ... THEN ... ELSEпонятнее для чтения.holodoz
С sign выглядит красивее, и я сам написал этот вариант в комментарии, не увидев ваш. Но по времени case раза в полтора быстрее sign и в два раза быстрее варианта с union. Тестил на posgresql
ITResume Автор
Красиво) Только, конечно, для понимания посложней - придется подумать немного :D
Akina
В задании этого НЕТ. А всё, что не описано явно, может быть как угодно. А потому решение пользователя - правильное, а предлагаемое вместо него, с CASE - ошибочное и не соответствующее условию.
Кстати, вот именно это и есть типичнейшая ошибка неправильного использования CASE - применение ELSE до того, как все возможные варианты обработаны соответствующими WHEN.
ITResume Автор
В задании, которое видел пользователь, это было :)
izogfif
Просто добавьте в статью фразу "type может принимать только значения 0 и 1" и все замечания отпадут.