Больше способов сосредоточиться на главном

В нашей предыдущей лекции мы представили основные части SQL-запроса, обеспечивающие фильтрацию только одного абзаца. Фильтрация — более широкая тема, и этот урок научит вас еще нескольким способам нарезки таблиц, чтобы вы могли извлечь из них полезную информацию. Мы узнаем об условиях, логике высказываний и способах фильтрации наших данных в столбцах, содержащих текстовую информацию.

Вы можете следить за примерами и выполнять код самостоятельно на Strata Scratch (www.stratascratch.com), платформе, которая поможет вам изучить SQL и Python для профессионалов/студентов в области маркетинга и бизнеса. Я использую эту платформу, чтобы научить своих студентов программировать на SQL и Python.

Условия и операторы

Все, что имеет вид column_name <operator> <value> или column_name <operator> other_column_name, называется условием. Вот некоторые условия:

  • survived = 1
  • pclass = 1
  • sex = 'male'

survived, pclass и sex — столбцы, = — оператор, а 1, 1 и 'male' — значения.

В качестве условия можно использовать любой столбец, если, конечно, он является частью таблицы. Список операторов фиксирован, а тип значений, которые вы можете использовать, зависит от типа данных. На данный момент мы изучим числовые и текстовые типы данных, а более сложные случаи будут объяснены в следующей лекции.

Операторы

Самый простой оператор равен (=), который проверяет, равны ли левая и правая стороны. Этот оператор может использоваться как для чисел, так и для текста.

Операторы < и > означают строго меньше и строго больше. Поведение такое же, как и в математике. Эти операторы имеют смысл для чисел. Они также имеют смысл для текста, но пока предположим, что < и > не работают для текста, даже если они работают. Поведение с текстовыми данными объясняется в следующей лекции.

Вы также можете использовать <= и >=, которые меньше или равны и больше или равны.

Конечным оператором является <>, который не равен. Это может показаться странным, и это так, но поскольку на клавиатуре нет простого способа ввода символа ≠, люди, создающие SQL, решили, что <> — лучший способ представить намерение не равно.

Числа и текст

Есть несколько правил, которые следует помнить при работе с числами и текстом:

  1. Числовые константы вводятся числовыми символами, например 1, 5, 111, 12157751 — все это допустимые числовые константы в SQL.
  2. Текстовые константы заключаются в одинарные кавычки, например «мужской», «C85», «13». Важно отметить, что вы не можете использовать двойные кавычки.
  3. «13» и 13 не равны, одно — текст, а другое — число.
  4. Также важно отметить, что «БОЛЬШОЙ» отличается от «большого». Прописные буквы обрабатываются иначе, чем строчные.

Примеры

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

SELECT
    *
FROM datasets.titanic
WHERE
    survived = pclass

Как вы думаете, к чему это привело и почему?

Логика высказываний

Мы научим вас трем ключевым словам, которые придают каждому утверждению WHERE силу, необходимую для того, чтобы оно сияло. Мы начнем с AND, пройдемся по ON, продолжим с NOT и остановимся на способах их объединения.

Я и ты, пассажиры 1-го класса

В SQL AND имеет то же значение, что и в английском языке. Когда мы говорим, что пассажир выжил, имел салон 1-го класса и был мужчиной, у нас есть три условия, из которых все 3 должны быть истинными, чтобы наша выходная таблица включала пассажира. Следующий запрос дает нам все объекты, которые удовлетворяют всем этим трем условиям.

SELECT
    *
FROM datasets.titanic
WHERE
    survived = 1 AND
    pclass   = 1 AND
    sex      = 'male'

Мы можем иметь столько условий и соединить столько AND, сколько захотим.

Поведение AND описано в следующей таблице:

По сути, вы получите строку обратно только в том случае, если оба условия верны. Когда у вас есть более 2 условий, как в нашем предыдущем примере, вы получите строку обратно только тогда, когда все условия верны, то есть пассажир выжил, пассажир был в первом классе, а пассажир был мужчиной.

Новичок или эксперт, все учатся

В мире SQL OR имеет то же значение, что и в обычной жизни и математике. Некоторая сущность считается подходящей для включения в нашу выходную таблицу, если выполняется одно из условий. Например, если у нас есть вопрос «Мы хотим только пассажиров 1-го и 3-го классов», наши условия будут выглядеть так:

  • pclass = 1
  • pclass = 3

Тогда наш запрос будет выглядеть так:

SELECT
    *
FROM datasets.titanic
WHERE
    pclass = 1 OR
    pclass = 3

Как вы думаете, что произойдет, если мы будем использовать AND вместо OR в запросе?

Для справки вот таблица истинности для OR:

Таким образом, объект считается непригодным для включения в выходную таблицу только в том случае, если не выполняются оба условия.

В - наддувом ИЛИ

Значение IN по существу такое же, как и значение нескольких условий, связанных с OR, за исключением того, что проще писать запросы с помощью IN.

Пример достоин тысячи определений, вот одно из них:

SELECT
    *
FROM datasets.titanic
WHERE
    pclass IN (1, 3)

Запрос выше и запрос ниже абсолютно идентичны по результатам, которые они дадут.

SELECT
    *
FROM datasets.titanic
WHERE
    pclass = 1 OR
    pclass = 3

Вот еще один пример, когда использование OR приведет к большому количеству ввода, но IN хорошо справляется с проблемой.

Предположим, поставлен вопрос: «Найти всех пассажиров, возраст которых точно кратен 10, то есть 10, 20, 30, 40, 50,…»

SELECT
    *
FROM datasets.titanic
WHERE
    age IN (10, 20, 30, 40, 50, 60, 70, 80, 90)

Альтернативой является выполнение: age = 10 OR age = 20 OR age = 30 OR age = 40 и т. д.

МЕЖДУ двумя мирами

Эта операция аналогична IN за тем исключением, что IN берет дискретный набор значений для проверки, а BETWEEN берет непрерывный диапазон. Возможно, пример прояснит это лучше всего:

«Кто те пассажиры, которые заплатили за проезд от 10 до 20 долларов?»

SELECT
    *
FROM datasets.titanic
WHERE
    fare BETWEEN 10 AND 20

Как видите, перевод с английского языка прошел очень гладко, единственное изменение заключалось в том, что мы не используем знак доллара в нашем запросе. Имейте в виду, что это AND здесь связано с AND из предыдущего отношения:

fare BETWEEN 10 AND 20 совпадает с fare >= 10 AND fare <= 20

Предпочтительнее использовать BETWEEN, потому что так легче читать запрос.

НЕ конец

Отрицание Истинного есть Ложь, а отрицание Ложного есть Истина. Отрицание констант не очень полезно, но отрицание переменных и выражений может быть очень полезным. Возьмем следующий пример: «Найти всех пассажиров, которым не исполнилось 24 года».

Есть два способа думать об этом состоянии:

  • NOT age = 20 (age <> 20)
  • age = 1 OR age = 2 OR age = 3 ... OR age = 23 OR age = 25 ... OR age = 100 (обратите внимание, мы пропустили 24)

Понятно, с кем легче работать. В этом простом примере это может показаться не таким уж важным, но когда вы пишете более сложные запросы, разумное использование NOT может сделать ваши блоки WHERE намного короче, но при этом давать правильные результаты.

Логическое отрицание — это способ связать AND с OR, используя следующую формулу: x OR y = ((NOT x) AND (NOT y))

Фильтрация текстовых данных с помощью LIKE и ILIKE

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

Прелесть или ужас текста в том, что в отличие от чисел он имеет весьма неоднородную структуру. Возьмем, к примеру, число 1. Когда вы пишете в виде числа, оно всегда равно 1, но когда вы пишете в виде текста, это может быть «ОДИН», «один», «туз», «AcE» или многие другие варианты. Возьмите также имена, это Джон или Джон?

Для борьбы с этими проблемами были разработаны LIKE и ILIKE. ILIKE — брат LIKE, нечувствительный к регистру, поэтому все, что мы знаем о LIKE, применимо и к ILIKE.

В самой простой форме LIKE действует точно так же, как оператор равенства (=). Вот пример запроса, показывающий, что:

SELECT
    *
FROM datasets.titanic
WHERE
    sex LIKE 'male'

Сила лайка заключается в символах «_» и «%».

  • ‘_’ означает соответствие ровно одному символу
  • «%» означает совпадение символов любой длины.

Подстановочный знак «%» используется гораздо чаще, поэтому мы сосредоточимся на нем. Вот наглядный пример: «Найти всех людей по имени Джон».

Сначала нам нужно переформулировать этот вопрос следующим образом: «Найти всех людей, чей столбец name содержит слово «Джон». Вероятно, просто написав запрос как name = 'John', мы можем пропустить результаты, где есть текст до или после Джона. Например, ваши данные могут иметь такие результаты, как John Doe или D. John. Так как же нам прочесать данные и найти любой экземпляр, в котором значение содержит слово John?

Мы можем использовать подстановочные знаки — '%John%'. Это означает, что какой бы текст ни шел перед Джоном, и какой бы текст ни шел после Джона, нас интересует только то, что слово Джон существует где-то там. При объединении всего этого получаем следующий запрос:

SELECT
    *
FROM datasets.titanic
WHERE
    name LIKE '%John%'

Рассмотрим другой пример: «Найти всех женатых пассажиров?»

Во-первых, мы определяем женатых, если их столбец name содержит либо «мистер». или «миссис». Затем мы определяем два шаблона '%Mr.%' и '%Mrs.%' и OR их вместе.

SELECT
    *
FROM datasets.titanic
WHERE
    name LIKE '%Mr.%' OR
    name LIKE '%Mrs.%'

Вот задача, которую вам нужно решить:

"Найти всех женщин с двумя именами". Используйте только LIKE и один шаблон.

*Подсказка: второе имя заключено в скобки, например «Насер, миссис Николас (Адель Ачем)».

NULL и ЕСТЬ NULL

Сбор данных не является безупречным процессом, и очень часто некоторая информация отсутствует из-за соображений конфиденциальности, законов, ошибочных процедур или сбоев в сборе полных данных. Отсутствующая информация представлена ​​специальным значением NULL, которое применяется как к числам, так и к тексту.

В нашем титаническом наборе данных у нас отсутствуют данные почти во всех столбцах, особенно в столбцах cabin и age.

Мы можем игнорировать значения NULL в наших запросах, используя операцию IS NOT NULL. Вот пример запроса, в котором мы игнорируем всех пассажиров, чей возраст неизвестен.

SELECT
    *
FROM datasets.titanic
WHERE
    age IS NOT NULL

Примеры

  1. Найдите всех людей, чье имя состоит ровно из шести букв.
SELECT
    *
FROM datasets.titanic
WHERE
    name LIKE '______,%'

Мы проверяем это, используя шесть последовательных символов «_».

  1. Кто самая старая незамужняя женщина-пассажир?
SELECT
    name, age
FROM datasets.titanic
WHERE
    name ILIKE '%Miss%' AND
    age IS NOT NULL
ORDER BY 
    age DESC
LIMIT 1

Мы игнорируем здесь отсутствующие возрасты, потому что при заказе NULL всегда одновременно является и самым низким, и самым высоким.

  1. Презентация операторов
SELECT
    *
FROM datasets.titanic
WHERE
    age BETWEEN 18 AND 30 AND
    name ILIKE '%Henry%' AND
    pclass IN (2, 3) AND 
    (survived <> 0 OR fare > 10)

Это составленный запрос, который использует почти все операторы в одном запросе.

Вопрос, на который он отвечает, будет звучать примерно так: «Найти всех пассажиров по имени/фамилии Генри старше 18 и моложе 30 лет, которые были во втором или третьем классе и либо выжили, либо заплатили за проезд более 10 долларов».

Попробуй сам

Вы можете выполнять эти упражнения на Strata Scratch (www.stratascratch.com), платформе SQL, которую я использую для обучения SQL своих студентов.

  • Найдите всех выживших, у которых был билет только с номерами. (Подсказка: только номера билетов без пробелов и или максимальной длины 7)

Вы можете решить эту проблему, проверив, имеет ли он длину 1 и не содержит пробелов, имеет длину 2 и не содержит пробелов или что-то еще.

  • Найдите все каюты, которые не являются нулевыми, и начните с «B». Что вы заметили в отношении пассажирского класса к количеству салонов?
  • Найдите всех мужчин, чей возраст неизвестен и у которых нет второго имени. (Подсказка: проверьте, чтобы в имени не было 2 или 3 пробелов)