Comments 92
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
В задаче не указано, как быть, если type принимает иные значения. Пользователь дал буквальное решение предложенной задачи - не выводить иных записей.
А вот ваш вариант с CASE даст иной результат.
Не знаю,какой из них вы сочтете желательным, но во всяком случае не стоит утверждать " А вот то же самое с CASE".
Там просто данные так устроены, что всего 2 значения - либо 0, либо 1. Поэтому в данном контексте CASE вполне решает полностью идентично.
Надо было просто указать это в тексте задачи, согласны :)
SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t
еще так можно:
SELECT id, sum*(-2*type+1) FROM transactions
WHEN ... THEN ... ELSE понятнее для чтения.
С sign выглядит красивее, и я сам написал этот вариант в комментарии, не увидев ваш. Но по времени case раза в полтора быстрее sign и в два раза быстрее варианта с union. Тестил на posgresql
Красиво) Только, конечно, для понимания посложней - придется подумать немного :D
Там просто данные так устроены, что всего 2 значения - либо 0, либо 1.
В задании этого НЕТ. А всё, что не описано явно, может быть как угодно. А потому решение пользователя - правильное, а предлагаемое вместо него, с CASE - ошибочное и не соответствующее условию.
Кстати, вот именно это и есть типичнейшая ошибка неправильного использования CASE - применение ELSE до того, как все возможные варианты обработаны соответствующими WHEN.
LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Я бы не стал заморачиваться, сделал бы по-простому:
FIRST_VALUE(...) (... ORDER BY ... DESC).
Часто сталкивался с непониманием в разнице запросов
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>
ON A.ID=B.ID AND A.FIELD=<Value>зачем так писать? с целью запутать?
Иногда ключ составной. Ну или из модели данных известно, что имеет смысл брать только те строки, для которых A.FIELD имеет какое-то определенное значение.
Иногда ключ составной.
покажите пример
Ну или из модели данных известно, что имеет смысл брать только те строки, для которых 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 … должны указываться условия связи таблиц и точка.
Если мы джойним к таблице B, но не хотим рассматривать записи с признаком IS_DELETED (ну или STATUS='Deleted'), расскажите, пожалуйста, как это сделать через WHERE. Я напомню, что речь про LEFT JOIN, т.е. записи в таблице A мы отсекать не планируем.
(Да, это решаемо, например, с использованием WITH или VIEW, например, но все-таки куда проще через ON IS_DELETED=FALSE).
но не хотим рассматривать записи с признаком IS_DELETED
в какой таблице?
В таблице B
посмотрите на запрос внимательнее, там фильтр по A.FIELD
OK, мы хотим вывести все договоры (таблица A), и для тех из них, у которых статус "Просрочен очередной платеж" хотим вывести дату последнего платежа, которую получаем из приджойненой таблицы B. Как выразить это через WHERE?
WHERE тут совсем не нужен. Все можно сделать через CASE
Условие написанное в WHERE будет применено позже объединения таблиц и применяться будет ко всему объёму записей, который получился в результате объединения.
В случае больших таблиц, условие в JOIN позволяет сразу отбросить лишние записи.
Это еще одно БОЛЬШОЕ заблуждение думать, что движок СУБД будет выполнять запрос так как вы думаете. :)
Для того и существует внутри его оптимизатор запросов. Который, скорее всего, сделает все строго наоборот! То есть сначала отфильтрует записи (особенно если есть соответствующий индекс), а затем будет выполнять объединение.
Пытаться оптимизировать таким способом запросы бесполезно.
зачем так писать? с целью запутать?
Я вижу 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 Но, мне кажется, первый запрос проще в понимании и более оптимальный в плане производительности.
Вот более реалистичный пример:
признаю, был неправ, смысл в таком использовании может присутствовать.
Понимания как работает JOIN никакого...
нет, непонятно было не как работает, а зачем
смысл в таком использовании может присутствовать.
Скажем так… В правильно спроектированных БД такое использование — экзотика. Но, т.к. в жизни правильно спроектированные БД сами являются экзотикой :), то этот прием применяется довольно часто.
Вот еще пример из жизни, я его называю "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. Я вот так сходу и не соображу как это красиво переписать без использования констант в предикатах.
нет, непонятно было не как работает, а зачем
Надеюсь разъяснил :)
Еще более интересная ситуация с 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Аналогичные запросы, например, в 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. У меня по прочтении создалось очень неприятное ощущение. Впечатление такое, что позвал начальник работника и сказал: "Придумалось мне шикарное название для статьи, и чтобы оно не пропадало, запили-ка ты мне к завтрему статью на хабре с этим названием...". Он и запилил.
Хм, а что не так с case? И почему кандидату жирный минус? А то коммент у вас весьма агрессивный, придирки к словам (как будто если назвать вещественные числа дробными собеседник вам не поймет). Вы покажите как правильно писать тогда, а то having у вас не тот, coalesce не правильный, case неверный. По большей части вполне себе часто встречающиеся косяки начинающих sqlщиков, на которые не лишний раз стоит обратить внимание, статья как статья, говорит о правильных вещах, без воды, кому-то обязательно пригодится
а что не так с case? И почему кандидату жирный минус?
Более подробно я это раскрыл в другом комментарии, см. выше (в ответ на комментарий itresume, время его комментария 18:28, моего комментария 21:02).
придирки к словам (как будто если назвать вещественные числа дробными собеседник вам не поймет)
Слова, которые собеседник авось поймёт, допустимы под пиво на лавочке. А в технической статье допустимы только точные термины.
По большей части вполне себе часто встречающиеся косяки начинающих sqlщиков
Знаете, я не один год и не на одном форуме помогаю именно по SQL. И по опыту могу сказать - только самый последний косяк, с неправильным применением оконных функций, действительно частый. Причём, как правило, не по причине неверно заданного фрейма (LAST_VALUE вообще крайне редко применяется начинающими), а по причинам, описанным мной в предыдущем комментарии. Ну ещё туда-сюда шестой косяк - да, встречается, но нечасто. Остальное - это скорее выдуманная экзотика, чем реальная практика.
А я б наоборот за union минус бы влепил. Потому что с юнион код не поддерживаемый и как его в реальный случай влепить (о чем кстати автор упоминает) непонятно. И не дай бог ещё лепить начнут. А с case, одно уточняющие слово соискателю и если он правит сразу понятно сечет человек или нет.
По поводу корректных определений, отчасти согласен, но когда речь идёт о полуформальной статье (я техническую статью тут не вижу, это статья из типа хозяйке на заметку) то это из плана не к чему придраться, придерись к грамматике
Сидеть на форумах конечно полезно, а если вы так долго сидите, я так понимаю вам скорее всего на простые вопросы отвечать уже не интересно. А там, в глубине форумов, совсем зелёные sqlщики пишут совсем зелёные вопросы. И вот такая статья собственно и позволяет уменьшить количество мусорных вопросов.
Потому что с юнион код не поддерживаемый и как его в реальный случай влепить (о чем кстати автор упоминает) непонятно.
Насчёт поддержки ничего не буду говорить. Обычно все проблемы поддержки определены тем, что инструменты поддержки в принципе не реализуют её для кода на стороне SQL-сервера. Но так это претензии к инструменту, а не к методу. Кстати, именно отсюда как правило и вылезают гениальные советы использовать SQL серверы как тупое хранилово, а всю логику реализовывать в клиенте. Ну а что при этом лишние гигабайты туда-сюда по сети летают - так это мелочи, сети у нас нынче широкие...
Но из реальной практики - CASE в списке вывода именно как альтернатива UNION весьма редок, в подавляющем большинстве случаев CASE суют в условия отбора. А вот тут запрос c UNION ALL по производительности кроет вариант с CASE как бык овцу. Потому что каждый отдельный подзапрос в UNION прекрасно оптимизируется и использует индексы, тогда как у запроса с CASE вообще нет шансов избежать fullscan.
Я к тому что если попросили сконвертить поле, а мне выдали бы union, я б сильно призадумался. Поэтому согласно постановку задаче case вполне адекватное решение. Про фильтры там вроде вопроса не была
А так и на case индекс можно навесить
У меня такое впечатление, что мы разные статьи читали...
Цитирую:
Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.
Где тут хоть полслова про конвертацию?
Задача поставлена вполне чёткая и однозначная - вывести -sum при type=1 и +sum при type=0. Ограничения на type не описаны - значит, другие значения (включая NULL) могут присутствовать. Необходимость выведения таких записей не указано - значит, их не выводить. Решение кандидата корректно, ибо полностью соответствует задаче, решение с CASE некорректно, потому что выведет записи, необходимость вывода которых не указана.
А так и на case индекс можно навесить
Можно, конечно. Но индекс для одного запроса? да ещё с необходимостью воспроизводить выражение индекса литерально? маловероятно, что существование такого индекса будет признано разумным.
По большинству пунктов не увидели противоречия в Ваших высказываниях с тем, что мы описали в статье :)
Что касается COALESCE - да, мы как раз говорим, что его использовать нужно. А про NULLIF согласны - тоже крайне полезная штука. Тем более они дополняют друг друга.
А про общее ощущение по статье... Мне кажется, у Вас просто довольно высокий уровень. Статья призвана помочь не гулять по граблям довольно неопытным ребятам. Ну или тем, кто по какой-то причине пропустил тот или иной пункт - может в практике не довелось наткнуться. Поверьте, для них это будет полезно, проверено многократно ;) Мы же из наработанной статистики эти косяки взяли, а не из головы)
А про общее ощущение по статье...
Ощущение в значительной степени навеяно, вероятно, тем, что описываемые Вами проблемы реально на форумах практически не встречаются.
Вот, например, косяки, связанные с не-учётом приоритетов - это весьма распространённая проблема. Причём ладно бы там с логическими операторами в условиях отбора.
Вот типичный пример действительно часто встречающейся проблемы приоритетов:
SELECT *
FROM t1, t2 LEFT JOIN t3 ON t1.f1=t3.f1 AND t2.f2=t3.f2 Результат - "Unknown column t1.f1". Причина которого для начинающего совсем даже не очевидна.
Нет никаких дробных чисел. Ни в одной из указанных СУБД. Есть целые (INTEGER и т.п.), есть вещественные точные (DECIMAL, NUMERIC и т.п.), есть вещественные с плавающей точкой (FLOAT, DOUBLE). Дробных — нет.
вы неправы, с точки зрения математики все эти numeric и float — рациональные (или дробные) числа. и для записи их на компьютере (в sql в том числе) обычно используется десятичная дробь
У меня по прочтении создалось очень неприятное ощущение
вот с этим согласен, унылое «послевкусие» от статьи
с точки зрения математики все эти numeric и float — рациональные (или дробные) числа
А как насчёт с точки зрения их реализации? Для FLOAT вполне нормально, когда два выражения не равны, потому что одно даёт 2, а второе 1,9999999. А вот у DECIMAL этой проблемы нет. А когда есть - то это проблема не от неточного представления, а от недостаточной (неправильно выбранной) точности.
Опять же с точки зрения реализации - именно дробное число реализуется не как одно значение, а как пара целых чисел, числитель-знаменатель. Достаточно редкий тип данных, по-моему, вообще нигде не реализованный как встроенный...
Опять же с точки зрения реализации — именно дробное число реализуется не как одно значение, а как пара целых чисел, числитель-знаменатель
именно так и хранятся и decimal, и float. только для хранения знаменателя используются очевидные оптимизации.
в первом случае знаменатель фиксирован и является целой степенью 10, во втором знаменатель является степенью 2, плюс добавляется целая (возможно отрицательная) десятичная экспонента.
P. S. вам словосочетание «десятичная дробь» ничего не говорит? 10.25, например.
это тоже дробь, отличается степенью 10 в знаменателе и способом записи. тип decimal именно это и реализует же.
Если сложить три "дробных числа" 1/3 согласно правилам сложения "дробных" получим 1/1 и при сравнении с целой 1 получим, видимо, равенство. При сложении трех "float" вида 0.333333..333 вряд ли получите обычное равенство представлению 1.000000....000
omg
из вики:
Термин дробное число (дробь) иногда[уточнить] используется как синоним к термину рациональное число, а иногда синоним любого нецелого числа.
очевидно, что в статье имеется в виду первый вариант (необходимость хранить иррациональные числа в БД — явная экзотика).
numeric и float — способы представления дробных чисел. все числа, которые могут храниться в numeric и float — рациональные (то есть представимые в виде дроби).
но не все рациональные числа могут быть точно представлены в виде float и numeric.
1/3 нельзя точно представить ни с помощью numeric, ни с помощью float.
0.1 можно точно представить с помощью numeric, но нельзя с помощью float.
1/8 можно точно представить и с помощью numeric, и с помощью float.
ну и вдогонку картинка из статьи на хабре, показывающая как раз как устроен float (а точнее double):
даже специально для вас запись в виде дроби привели )))
очевидно, что в статье имеется в виду первый вариант
Вариант, который "иногда", всегда надо явно описывать. При отсутствии такого уточнения всегда используется каноническое определение термина.
Игнорирование CASE
Иногда игнорирование CASE и тупое UNION запросов с разными фильтрами WHERE способно существенно ускорить время выполнения
Само собой, это не какая-то волшебная пилюля. Но, как минимум, в каждой ситуации стоит подумать - а оправдан ли здесь юнион или можно просто кейс.
это как??
в запрос с case тоже можно (и нужно) поставить WHERE type in (0,1) и заюзать индекс и никакой юнион быстрее не будет никак
Тема достойна отдельного исследования) Но из личного (субьективного) опыта, запросы не всегда написаны оптимально, а COALESCE и CASE очень любят пихать всюду, как серебрянную пулю чтобы собрать один запрос который будет повеливать всеми лаконичнен и читаем. Поэтому, например, когда CASE сидит в переусложненных условиях join'ов, зачастую дешевле разобрать сложные условия свичей на запросы соединенные юнионом. Ну и насколько я помню, внутренний оптимизатор запросов очень даже рад видеть UNION ALL.
Так то целочисленное деление и в С может неожиданно удивить ;)
В статье не нашёл одну из наиболее встречающихся «неточностей» — когда есть таблица с Column_name, Column_id и при вычислении каких либо агрегатов используется GROUP BY по текстовым полям, вместо группировки по ID. Гораздо медленнее отрабатывает, чем группировка по ID, особенно при росте объемов данных.
Как вариант «best practicies» — сначала группировать по Column_id, а потом подтягивать текстовые значения джойном.
Вижу два варианта.
Вариант первый - в паре Column_name, Column_id при одном и том же значении одного поля в разных записях во втором поле также будут одинаковые значения. В этом случае "неточность" не в том, что для группировки используется поле строкового типа (хотя и это тоже), а имеется проблема денормализованной структуры данных. Которую править надо не изменением текста запроса, а более пристальным рассмотрением структуры БД и, скорее всего, изменением её с вынесением Column_name в отдельную связанную таблицу.
Вариант второй - описанного однозначного соответствия нет. Но тогда совет из Вашего комментария неприменим.
описанного однозначного соответствия нет. Но тогда совет из Вашего комментария неприменим.
Применим и еще как, но с использованием подзапроса с ROW_NUMBER() AS n и дальнейшего фильтра по n=1
Ну как он может быть применим на данных типа
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) будет достаточно быстрым.
Куда тут применить "сначала группировать по 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Сразу говорю, я с вами полностью согласен, что, в этом случае, это плохо спроектированная БД. Но, в реальной жизни, с какими только данными не приходится работать, увы.
Ну как бы вы в своих примерах уже совсем, совсем далеко уехали от исходной формулировки abratash. Который призывал при наличии возможности группировки одновременно как по имени так и по коду, отдать предпочтение группировке по коду.
Простите, Вы не могли бы ткнуть пальцем в то место, где он указывает на возможность "группировки одновременно как по имени так и по коду"? я ничего подобного не вижу. Я вообще не вижу чего-либо, указывающего на связь между значениями этих полей. Кроме похожих имён.
Я, например, не уверен что это будет работать быстрее во всех случаях. А скорее всего для обычных имен даже и медленнее.
Всё зависит от того, какие индексы имеются в таблице. Но при прочих равных комплексный запрос потребует больше чтений, и соответственно будет медленнее. Кстати, при наличии индекса по текстовому полю разница между группировкой по тексту и по числу на самом деле невелика. Она начнётся в тот момент, когда промежуточный диапазон поиска по числу уже укладывается в страницу индекса, а по тексту ещё нет. Но так как из индекса крайне редко читается именно одна страница (а тут ещё ОС поможет со своим кэшированием и предчтением, СУБД попросила страницу 16 кб, а ОС прочитает под это дело 1-4 мегабайта), то разница будет ещё менее значительна.
сначала читать другие комментарии
Чаще всего абсолютно бесполезно говорить "так делать не нужно" не поясняя почему. Например - рассмотрев план запроса, причем желательно конкретного.
Еще ошибка, с которой встречался больше одного раза. Допустим, мы связываем три таблицы 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-ы, которые могут повлиять на результирующий набор строк.
На этот случай есть простое правило: после первого в цепочке LEFT JOIN могут идти только LEFT JOIN. Если это не так, то нужно группировать запросы с помощью скобок или использовать подзапросы. Мне ближе второй вариант.
При этом не для каждой записи в A есть соответствие в B, и тут нужен LEFT JOIN.
from a
left join b
join c
on b.id = c.b_id
on a.id = b.a_id
В первом примере - как насчет деления на ноль?
>>Как говорил дядюшка Кнут:
>Преждевременная оптимизация — корень всех зол
Эту цитату я обожаю кстати. Тут же недавно был прекрасный ответ на это, я тоже процитирую (автора привести не смогу — он не умер и не писал книг, потому не запомнился): «Пока твои неудачные решения не насрали тебе лично, ты не будешь считать их неудачными.»
Спасибо за интерсный материал.
Не знаю писали выше или нет, но в п. 3 кроется одна из самых страшных ошибок по перфомансу, а именно-вычисление слева в фильтрации, что практически всегда приведет к скану всей таблицы.
Все вычисления должны быть строго справа, а не слева как в фильтрациях, так и в условиях соединений.
Это какой же должен быть примитивный оптимизатор, что бы это было правдой?
Оптимизаторы не всё могут, а данный пример показывает незнание того, что оптимизатор не может применить индексы и статистику, если слева выражение. Лучше переписать так, чтобы выражение было справа. Данный эффект будет и в слоне, и в мускуле, и в MS SQL (на счет оракла не знаю, но уверен что там также, т к принципы обработки дерева возможных планов запросов примерно одинаковые).
Всегда нужно писать код так, чтобы он был предсказуем при выполнении.
Не стоит путать язык SQL и его диалекты с языками C#, Java и т д, где в последних практически как напишите-так и будет работать. В SQL не так-вы пишите что хотите получить, а вот как-остается за оптимизатором, и ему всегда нужно помочь в этом, если хотите получить более-менее устойчивое решение (поведение) при изменении многих факторов (кол-ва строк, нагрузки, размеры строк и т д и т п).
Временные таблицы тоже ведь не просто так используют вместо одного гигантского запроса.
Пример СУБД в студию, в которой план выполнения запроса с
WHERE 2+2 = column
отличается от
WHERE column = 2+2
Это очевидные вещи, проверьте сами, а также советую читать официальную документацию к СУБД, где это написано.
Я то проверил (MS SQL 2008) и убедился еще раз в том, что и так знал. Планы идентичны.
Теперь ваши пруфы в студию.
Вы, похоже, путаете ситуацию с абсолютно идентичным в плане результата запросом по фильтру:
WHERE 2 = column - 2Вот тут будет полный скан, причем независимо от того, где стоит выражение, справа или слева.
Лучше всегда избегать выражений слева
Неправильная мантра :)
Надо избегать выражений по индексируемым полям.
Вместо
ABS(column) = 100
Надо писать
column in (-100,100)
Вместо
YEAR(column) =2008
надо
column between '20080101' AND '20081231'
и т.д. и т.п.
Кроме первого предложения, во всём согласен.
Про то, что слева нельзя писать выражения останусь при своём.
Каждый пусть выбирает на свой страх и риск.
то есть 100=abs(field) будет по-вашему нормально работать, слева же нет выражения? )))
Нет, конечно
Уточнение: слева должно быть проиндексированное поле без выражения (исключение только если для этого выражения есть функциональный индекс)
слева должно быть проиндексированное поле без выражения
Ну сказки же... построителю плана запроса глубоко фиолетово, как написано условие. Попробуйте создать (для любой упомянутой СУБД) пример, когда планы различаются для "правой" и "левой" записей... и ни хрена не получится (или найдёте баг в построителе плана).
Если Вы для себя решили, что делать надо только так - да хоть два раза... вот только не надо под это дело подводить всякую чушь, выдавая её за обоснование.
Странно это все читать с тем учетом, что при определенных обстоятельствах оптимизатор даже в таком ключе не использует индекс:
Index Field = Field_2 + Field_3 + Const
А Вы пишите что и чуть сложнее оптимизатор съест. Да, может и съест сегодня, а вот завтра например уже и не съест.
Достаточно один раз обжечься на этой теме, чтобы ввести в правила оформления кода запрет на выражения слева в фильтрах и в соединениях для команды или компании (да, такие запреты видел в крупных компаниях+сам вводил 2 раза в менее крупных).
Это такое же правило, как писать всегда букву N перед юникодом, чтобы не было неявных преобразований типов значений (да, это преобразование невсегда будет-но если случится, то будет больно при высокой нагрузке).
Устал спорить про очевидные вещи. Если не удалось мне кого-то убедить, надеюсь что инцидент научит, но первый мой комментарий был адресован автору публикации. И холивар уж точно не хотел разводить и поддерживать далее его не намерен.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6ba54d22f2a1756c16ccb07274ff644d
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f1ac2a41462819f3e9faeaccc036b48c
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1609df781bf16738f8dc8414ee090d31
Найдите десять отличий.
Или откорректируйте так, чтобы хотя бы одно появилось.
Достаточно один раз обжечься на этой теме
Ну покажите, хоть разочек, хоть примерчик, хоть в какой СУБД... а то слов много, а фактов что-то не видать. Прям как Пауэл с пробиркой...
Ну покажите, хоть разочек, хоть примерчик, хоть в какой СУБД
Предполагаю, что, в каких то древних СУБД, такое поведение могло и быть. С той поры и дуют на воду :)
Ну, скажем, MySQL не поддерживает FULL JOIN, а SQLite не поддерживает ни RIGHT, ни FULL JOIN... это же не повод заявлять, что JOIN должен быть исключительно LEFT, верно? Я уж не говорю о том, что есть адепты, которые в принципе не признают, ни INNER, ни CROSS - у них JOIN должен быть LEFT, и точка! причём как минимум половина из них - пэхапэшники.
del
Неподдержка алиасов в WHERE — проблема вашей базы, в SQLite это работает, например.
Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка
А вот это вообще неграмотно, не делится он на "операторы", это всё части одного запроса.
7 распространенных ошибок в SQL-запросах, которые делал каждый (почти)