Вы когда-нибудь слышали о языке SQL? Возможно, вы слышали о нем в контексте анализа данных, но никогда не думали, что это может быть применимо к вам как к маркетологу. Или вы могли подумать: «Это для продвинутых пользователей данных. Я никогда не смогу этого сделать». Вы ошибаетесь. Самые успешные маркетологи ориентируются на данные, а одна из самых важных составляющих такого подхода — быстрый сбор данных из баз данных. SQL — самый популярный инструмент для этого. Если ваша компания уже хранит данные в базе данных, вам может понадобиться изучить SQL, чтобы получить доступ к этим данным. Но не волнуйтесь — вы находитесь в правильном месте, чтобы начать. Давайте сразу приступим.
Как запрашивать базу данных SQL
- Убедитесь, что у вас есть приложение для управления базами данных (например, MySQL Workbench, Sequel Pro).
- Если нет, загрузите приложение для управления базами данных и вместе с компанией подключите свою базу данных.
- Поймите свою базу данных и ее иерархию.
- Выясните, какие поля содержатся в ваших таблицах.
- Начните писать SQL-запрос, чтобы получить нужные данные.
Что такое SQL?
SQL — это язык программирования, позволяющий управлять и манипулировать реляционными базами данных. Обычно произносится как «сиквел», SQL — это незаменимый инструмент для компаний, которым необходимо регулярно получать доступ и анализировать большие массивы данных. SQL позволяет получать определенные данные с помощью запроса, обновлять существующие данные, вставлять новые данные, удалять данные и многое другое. С помощью SQL вам не нужно загружать и открывать огромную таблицу Excel, чтобы получить ответы на интересующие вас вопросы. Вы можете задавать вопросы типа «Какие клиенты приобрели красный комбинезон за последние шесть месяцев?», и SQL извлекает данные из вашей базы данных и возвращает их вам без необходимости вручную просматривать CSV.
Зачем использовать SQL?
SQL — полезный инструмент для компаний, которые используют данные (подскажите, большинство из них так и делают). Вот несколько примеров и причин, по которым вы можете захотеть сесть на поезд SQL.
- Ваши данные в SQL более безопасны, поскольку пользователям сложнее случайно удалить или повредить их по сравнению с листом Excel.
- SQL позволяет управлять наборами данных, превышающими тысячи записей
- SQL позволяет нескольким пользователям беспрепятственно обращаться к одной и той же базе данных.
- Ролевые полномочия позволяют контролировать видимость конфиденциальных данных.
- SQL обеспечивает мощную визуализацию данных
- SQL обеспечивает целостность данных, поэтому ваши данные всегда точны и непротиворечивы.
Иерархия баз данных SQL
База данных SQL — это реляционная база данных, то есть данные структурированы в таблицах, которые связаны друг с другом на основе заранее определенных отношений. Информация в базе данных SQL структурирована иерархически, подобно семейному дереву, то есть элементы на верхнем уровне имеют более широкий охват и разветвляются вниз на множество более конкретных подэлементов. В контексте SQL верхний уровень — это сервер базы данных, также называемый экземпляром. Ваш экземпляр — это место, где хранятся все ваши данные. Внутри экземпляра может быть несколько баз данных, каждая из которых содержит данные, организованные по некоторой широкой классификации. База данных разбита на таблицы. Таблица — это место, где хранятся фактические данные. На уровне таблицы данные упорядочиваются по столбцам и строкам и размещаются в полях, почти как в электронных таблицах Excel. Представим, что мы работаем с несколькими базами данных о жителях Соединенных Штатов. Введя запрос «SHOW DATABASES;», вы увидите все базы данных в вашей системе, включая одну с названием NewEngland. База данных содержит таблицы, в которых хранятся ваши данные. Если мы используем запрос «SHOW TABLES in NewEngland;», то в результате получим таблицы для каждого штата Новой Англии: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland и people_vermont. Наконец, вам нужно выяснить, какие поля содержатся в таблицах. Поля — это конкретные фрагменты данных, которые вы можете извлечь из базы данных. Например, если вы хотите получить чей-то адрес, имя поля может быть не просто «адрес» — оно может быть разделено на address_city, address_state, address_zip. Чтобы выяснить это, используйте запрос «Describe people_massachusetts;». Здесь представлен список всех данных, которые можно получить с помощью SQL. Давайте сделаем краткий обзор иерархии на примере Новой Англии:
- Наша база данных — NewEngland.
- Наши таблицы в этой базе данных — people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland и people_vermont.
- Наши поля в таблице people_massachusetts включают: адрес_города, адрес_штата, адрес_молнии, цвет волос, возраст, имя_фамилия и фамилия_фамилия.
Теперь давайте напишем несколько простых SQL-запросов, чтобы получить данные из нашей базы данных NewEngland.
Использование команды FROM
FROM указывает таблицу, из которой вы хотите получить данные. В предыдущем разделе мы узнали, что для каждого из шести штатов Новой Англии существует шесть таблиц: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland и people_vermont. Поскольку мы ищем людей именно в Массачусетсе, мы возьмем данные из этой конкретной таблицы. Вот наш SQL-запрос:
SELECT
first_name,
last_name
FROM
people_massachusetts
;
Использование команды WHERE
WHERE позволяет отфильтровать запрос, чтобы сделать его более конкретным. В нашем примере мы хотим отфильтровать запрос так, чтобы в него попали только люди с рыжими волосами, родившиеся в 2003 году. Давайте начнем с фильтра по рыжим волосам.
Запрос 2:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
;
hair_color мог бы быть частью вашего первоначального оператора SELECT, если бы вы хотели просмотреть всех людей в Массачусетсе и их цвет волос. Но если вы хотите отфильтровать только людей с рыжими волосами, вы можете сделать это с помощью оператора WHERE.
Использование команды BETWEEN
Помимо равенства (=), BETWEEN — это еще один оператор, который можно использовать для условных запросов. Оператор BETWEEN верен для значений, которые находятся между указанными минимальным и максимальным значениями. В нашем случае мы можем использовать BETWEEN, чтобы вытащить записи за определенный год, например 2003.
Запрос 3:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
Использование команды AND
AND позволяет добавить дополнительные критерии в оператор WHERE. Помните, мы хотим отфильтровать людей с рыжими волосами в дополнение к тем, кто родился в 2003 году. Поскольку наш оператор WHERE занят критерием рыжих волос, как мы можем отфильтровать их по определенному году рождения? Вот тут-то и приходит на помощь оператор AND. В данном случае оператор AND представляет собой свойство даты, но это не обязательно так. (Примечание: проверьте формат дат с помощью команды разработчиков продукта, чтобы убедиться в их правильности).
Запрос 4:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
Использование команды OR
OR также можно использовать в операторе WHERE. При использовании AND оба условия должны быть истинными, чтобы появиться в результатах (например, цвет волос должен быть рыжим и должен быть рожден в 2003 году). При использовании OR любое из условий должно быть истинным, чтобы появиться в результатах (например, цвет волос должен быть рыжим или должен быть рожден в 2003 году). Вот как выглядит оператор OR в действии.
Запрос 5:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
OR
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
;
Использование команды NOT
NOT используется в операторе WHERE для отображения значений, в которых указанное условие не соответствует действительности. Если мы хотим найти всех жителей Массачусетса без рыжих волос, мы можем использовать следующий запрос.
Запрос 6:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE NOT
hair_color = ‘red’
;
Использование команды ORDER BY
Вычисления и организация также могут быть выполнены в запросе. Именно здесь на помощь приходят функции ORDER BY и GROUP BY. Сначала мы рассмотрим наши SQL-запросы с функциями ORDER BY, а затем GROUP BY. Затем мы вкратце рассмотрим разницу между ними. Предложение ORDER BY позволяет отсортировать данные по любому из полей, указанных в операторе SELECT. В данном случае упорядочим по фамилии.
Запрос 7:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
last_name
;
Использование команды GROUP BY
GROUP BY аналогичен ORDER BY, но объединяет схожие данные. Например, если в ваших данных есть дубликаты, вы можете использовать GROUP BY для подсчета количества дубликатов в ваших полях.
Запрос 8:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
GROUP BY
last_name
;
ORDER BY VS. GROUP BY
Чтобы показать разницу между оператором ORDER BY и оператором GROUP BY, давайте ненадолго выйдем за рамки нашего примера с Массачусетсом и рассмотрим очень простой набор данных. Ниже представлен список из четырех идентификационных номеров и имен сотрудников.
Если бы мы использовали оператор ORDER BY для этого списка, имена сотрудников были бы отсортированы в алфавитном порядке. Результат будет выглядеть следующим образом:
Если бы мы использовали оператор GROUP BY, то сотрудники были бы подсчитаны по количеству их появления в исходной таблице. Обратите внимание, что Питер дважды встречается в исходной таблице, поэтому результат будет выглядеть следующим образом:
Вы со мной? Хорошо, давайте вернемся к SQL-запросу о рыжеволосых жителях Массачусетса, родившихся в 2003 году.
Использование функции LIMIT
В зависимости от объема данных в базе данных выполнение запросов может занять много времени. Это может расстроить, особенно если вы допустили ошибку в запросе и теперь должны подождать, прежде чем продолжить. Если вы хотите протестировать запрос, функция LIMIT позволяет ограничить количество получаемых результатов. Например, если мы подозреваем, что в Массачусетсе тысячи людей имеют рыжие волосы, мы, возможно, захотим проверить наш запрос с помощью LIMIT, прежде чем запускать его полностью, чтобы убедиться, что мы получим нужную информацию. Допустим, мы хотим увидеть только первые 100 человек в нашем результате.
Запрос 8:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
last_name
LIMIT
100
;
Использование команды INSERT INTO
Помимо получения информации из реляционной базы данных, SQL также можно использовать для изменения содержимого базы данных. Конечно, вам потребуется разрешение на изменение данных вашей компании. Но если вы когда-нибудь будете отвечать за управление содержимым базы данных, мы поделимся с вами некоторыми запросами, которые вы должны знать. Во-первых, это оператор INSERT INTO, позволяющий вводить новые значения в базу данных. Если мы хотим добавить нового человека в таблицу Massachusetts, мы можем сделать это, сначала указав имя таблицы, которую мы хотим изменить, и поля в таблице, которые мы хотим добавить. Далее мы пишем VALUE с каждым соответствующим значением, которое мы хотим добавить.
Запрос 9:
INSERT INTO
people_massachusetts (address_city, address_state, address_zip, hair_color, age, first_name, last_name)
VALUES
(Cambridge, Massachusetts, 02139, blonde, 32, Jane, Doe)
;
Если же вы добавляете значение в каждое поле таблицы, то поля указывать не нужно. Значения будут добавлены в столбцы в том порядке, в котором они перечислены в запросе.
Запрос 10:
INSERT INTO
people_massachusetts
VALUES
(Cambridge, Massachusetts, 02139, blonde, 32, Jane, Doe)
;
Если вы хотите добавить значения только в определенные поля, необходимо указать эти поля. Допустим, мы хотим вставить запись только с именами first_name, last_name и address_state — мы можем использовать следующий запрос.
Запрос 11:
INSERT INTO
people_massachusetts (first_name, last_name, address_state)
VALUES
(Jane, Doe, Massachusetts)
;
Использование команды UPDATE
Вы можете использовать UPDATE, если хотите заменить существующие значения в базе данных на другие. Что если, например, в базе данных кто-то записан как обладатель рыжих волос, а на самом деле у него каштановые волосы? Мы можем обновить эту запись с помощью операторов UPDATE и WHERE.
Запрос 12:
UPDATE
people_massachusetts
SET
hair_color = ‘brown’
WHERE
first_name = ‘Jane’
AND
last_name = ‘Doe’
;
Или, скажем, в вашей таблице есть проблема, когда некоторые значения для «address_state» отображаются как «Massachusetts», а другие — как «MA». Чтобы изменить все значения «MA» на «Massachusetts», мы можем использовать простой запрос и обновить несколько записей одновременно.
Запрос 13:
UPDATE
people_massachusetts
SET
address_state = ‘Massachusetts’
WHERE
address_state = MA
;
Будьте осторожны при использовании UPDATE. Если в операторе WHERE не указать, какие записи нужно изменить, вы измените все значения в таблице.
Использование команды DELETE
DELETE удаляет записи из вашей таблицы. Как и в случае с UPDATE, не забудьте включить оператор WHERE, чтобы случайно не удалить всю таблицу. Или, если мы случайно обнаружим в таблице people_massachusetts несколько записей, которые на самом деле жили в штате Мэн, мы можем быстро удалить эти записи, обратившись к полю address_state.
Запрос 13:
DELETE FROM
people_massachusetts
WHERE
address_state = ‘maine’
;
Бонус: Продвинутые советы по SQL
Теперь, когда вы узнали, как создать простой SQL-запрос, давайте обсудим некоторые другие приемы, которые можно использовать для повышения уровня запросов, начиная со звездочки.
* (звездочка)
Когда вы добавляете символ звездочки в SQL-запрос, он сообщает запросу, что вы хотите включить все столбцы данных в результаты. В примере с Массачусетсом мы использовали только два названия столбцов: first_name и last_name. Но, допустим, у нас есть 15 столбцов данных, которые мы хотим видеть в результатах — было бы очень неприятно вводить все 15 названий столбцов в операторе SELECT. Вместо этого, если заменить имена этих столбцов звездочкой, запрос будет знать, что нужно вытащить все столбцы в результаты. Вот как будет выглядеть SQL-запрос.
Запрос 13:
SELECT
*
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
last_name
LIMIT
100
;
% (символ процента)
Символ процента — это символ подстановки, то есть он может обозначать один или несколько символов в значении базы данных. Символы подстановки полезны для поиска записей, содержащих общие символы. Обычно они используются вместе с оператором LIKE для поиска закономерностей в данных. Например, если мы хотим получить имена всех людей в нашей таблице, чей почтовый индекс начинается с «02», мы можем написать следующий запрос.
Запрос 14:
SELECT
first_name,
last_name
WHERE
address_zip LIKE ‘02%’
;
Здесь «%» обозначает любую группу цифр, следующих за «02», поэтому этот запрос выводит все записи со значением address_zip, начинающимся с «02».
ПОСЛЕДНИЕ 30 ДНЕЙ
Когда я начал регулярно использовать SQL, я обнаружил, что один из моих основных запросов включает в себя поиск людей, которые совершили то или иное действие или выполнили определенный набор критериев в течение последних 30 дней. Представим, что сегодня 1 декабря 2021 года. Вы могли бы создать эти параметры, сделав так, чтобы дата_рождения находилась в диапазоне от 1 ноября 2021 года до 30 ноября 2021 года. Такой SQL-запрос будет выглядеть следующим образом:
Запрос 15:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2021-11-01’ AND ‘2021-11-30’
ORDER BY
last_name
LIMIT
100
;
Но для этого придется учитывать, какие даты охватывают последние 30 дней, и постоянно обновлять этот запрос. Вместо этого, чтобы даты автоматически охватывали последние 30 дней, независимо от того, какой это день, вы можете ввести следующее в поле AND: birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30)) (Примечание: вам следует перепроверить этот синтаксис с командой разработчиков, поскольку он может отличаться в зависимости от программного обеспечения, которое вы используете для составления SQL-запросов). Таким образом, ваш полный SQL-запрос будет выглядеть следующим образом.
Запрос 16:
SELECT
first_name,
last_name
FROM
people_massachusetts
WHERE
hair_color = ‘red’
AND
birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))
ORDER BY
last_name
LIMIT
100
;
COUNT
В некоторых случаях вам может понадобиться подсчитать, сколько раз появляется тот или иной критерий поля. Например, вам нужно подсчитать, сколько раз встречаются разные цвета волос у людей, которых вы подсчитываете по штату Массачусетс. В этом случае пригодится COUNT, и вам не придется вручную подсчитывать количество людей с разным цветом волос или экспортировать эту информацию в Excel. Вот как будет выглядеть этот SQL-запрос:
Запрос 17:
SELECT
hair_color,
COUNT(hair_color)
FROM
people_massachusetts
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
GROUP BY
hair_color
;
AVG
AVG вычисляет среднее значение атрибута в результатах запроса, исключая значения NULL (пустые). В нашем примере мы могли бы использовать AVG для расчета среднего возраста жителей Массачусетса в нашем запросе. Вот как может выглядеть наш SQL-запрос:
Запрос 18:
SELECT
AVG(age)
FROM
people_massachusetts
;
SUM
SUM — это еще одно простое вычисление, которое можно выполнить в SQL. Он вычисляет общее значение всех атрибутов из вашего запроса. Так, если мы хотим сложить все возрасты жителей Массачусетса, мы можем использовать следующий запрос.
Запрос 19:
SELECT
SUM(age)
FROM
people_massachusetts
;
Использование MIN и MAX
MIN и MAX — это две функции SQL, которые позволяют получить наименьшее и наибольшее значения данного поля. Мы можем использовать их для определения самых старых и самых молодых членов нашей таблицы Massachusetts: Следующий запрос даст нам данные о самых молодых людях.
Запрос 20:
SELECT
MIN(age)
FROM
people_massachusetts
;
И этот запрос выдает нам самый старый:
Запрос 21:
SELECT
MAX(age)
FROM
people_massachusetts
;
Использование команды JOIN
Может возникнуть ситуация, когда в одном SQL-запросе вам нужно получить доступ к информации из двух разных таблиц. В SQL для этого можно использовать предложение JOIN. (Для тех, кто знаком с формулами Excel, это похоже на использование формулы VLOOKUP, когда вам нужно объединить информацию с двух разных листов в Excel). Допустим, у нас есть одна таблица, в которой хранятся данные об идентификаторах пользователей и датах рождения всех жителей Массачусетса. Кроме того, у нас есть совершенно отдельная таблица, содержащая идентификаторы пользователей всех жителей Массачусетса и их цвет волос. Если мы хотим определить цвет волос жителей Массачусетса, родившихся в 2003 году, нам нужно получить доступ к информации из обеих таблиц и объединить их. Это работает, потому что обе таблицы имеют общий столбец: идентификаторы пользователей. Наш оператор SELECT также немного изменится, поскольку мы будем вызывать поля из двух разных таблиц. Вместо того чтобы просто перечислить поля, которые мы хотим включить в результаты, нам нужно будет указать, из какой таблицы они берутся. (Примечание: здесь может пригодиться функция «звездочка», чтобы запрос включил в результаты обе таблицы). Чтобы указать поле из конкретной таблицы, достаточно объединить имя таблицы с именем поля. Например, наш оператор SELECT будет выглядеть так: «table.field» — с точкой, разделяющей имена таблицы и поля. В данном случае мы также предполагаем несколько вещей:
- Таблица Massachusetts birthdate включает следующие поля: first_name, last_name, user_id, birthdate
- Таблица цвета волос Massachusetts включает следующие поля: user_id, hair_color
Ваш SQL-запрос будет выглядеть следующим образом.
Запрос 21:
SELECT
birthdate_massachusetts.first_name,
birthdate_massachusetts.last_name
FROM
birthdate_massachusetts JOIN haircolor_massachusetts USING (user_id)
WHERE
hair_color = ‘red’
AND
birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’
ORDER BY
last_name
;
Этот запрос соединит две таблицы, используя поле «user_id», которое присутствует как в таблице birthdate_massachusetts, так и в таблице haircolor_massachusetts. В результате вы увидите таблицу людей с рыжими волосами, родившихся в 2003 году.
Использование оператора CASE
Используйте оператор CASE, когда хотите вернуть разные результаты запроса в зависимости от того, какое условие выполнено. Условия оцениваются по порядку. При выполнении условия возвращается соответствующий результат, а все последующие условия пропускаются. В конце можно включить условие ELSE, если ни одно из условий не выполнено. Без ELSE запрос вернет NULL, если ни одно условие не выполнено. Вот пример использования CASE для возврата строки на основе запроса.
Запрос 22:
SELECT
first_name,
last_name
FROM
people_massachusetts
CASE
WHEN hair_color = ‘brown’ THEN ‘This person has brown hair.’
WHEN hair_color = ‘blonde’ THEN ‘This person has blonde hair.’
WHEN hair_color = ‘red’ THEN ‘This person has red hair.’
ELSE ‘Hair color not known.’
END
;
Основные SQL-запросы, которые должны знать маркетологи
Поздравляем! Вы готовы к выполнению собственных SQL-запросов. Хотя с помощью SQL можно сделать еще много чего, я надеюсь, что этот обзор основ будет полезен вам, и вы сможете приступить к работе. Овладев основами, вы сможете лучше ориентироваться в SQL и работать над более сложными примерами.