SQL запросы быстро. Часть 1

Введение


Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join'ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика


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

Кликнуть здесь

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

Структура sql-запросов


Общая структура запроса выглядит следующим образом:

SELECT ('столбцы или * для выбора всех столбцов; обязательно')
FROM ('таблица; обязательно')
WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно')
GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно')
HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно')
ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS'ом.

SELECT, FROM


SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers


WHERE


WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers
where City IN ('London', 'Berlin')

select * from Customers
where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers
where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15

select * from Customers
where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY


GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

  1. перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
  2. агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:

select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:


select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:


select City, count(CustomerID) from Customers
WHERE Country = 'Germany'
GROUP BY City

Переименование столбца с агрегацией с помощью оператора AS. По умолчанию название столбца с агрегацией равно примененной агрегатной функции, что далее может быть не очень удобно для восприятия.

select City, count(CustomerID) AS Number_of_clients from Customers
group by City

HAVING


HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).

Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:


select City, count(CustomerID) from Customers
group by City
HAVING count(CustomerID) >= 5 


В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:


select City, count(CustomerID) as number_of_clients from Customers
group by City
HAVING number_of_clients >= 5

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:


select City, count(CustomerID) as number_of_clients from Customers
WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend')
group by City
HAVING number_of_clients >= 5

ORDER BY


ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:


select * from Customers
ORDER BY City

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


select * from Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:


select * from Customers
order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

select * from Customers
order by Country DESC, City

JOIN


JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,


select * from Orders
join Customers on Orders.CustomerID = Customers.CustomerID
where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN'ов можно увидеть на замечательной картинке ниже:


В следующей части подробнее поговорим о типах JOIN'ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 34

    +3
    Следующие две части будут посвящены подзапросам, Join'ам и специальным операторам.

    1. У вас секция с объединениями в первую часть скопировалась.
    2. Ну и если у вас в тегах указано SQLite, то неплохо бы сказать, что он не все виды объединений, определённых в стандарте, поддерживает, см. «SQL Features That SQLite Does Not Implement», а также что в этом случае делать.
      0
      1. Да, действительно, выбрал тактику и стоило ее придерживаться) Здесь решил в итоге добавить короткую секцию, чтобы совсем новичкам было, с чем играться, пока ожидаю модерации и пишу вторую часть статьи
      2. Это правда, в следующей части постараюсь осветить это)
      –4

      Не совсем в тему, но моего хорошего знакомого, который давно и успешно рулит всякими oracle bd, тоже зовут Александр Соколов. А я, хоть и не Александр, но тоже sql-программист и тоже Соколов… Скажите, а как вас затянуло на эту тернистую дорожку из бизнес-анализа?

        0
        Александр Соколов очень распространенная комбинация имя-фамилия, полных тезок за жизнь встречал дважды, если не считать просто Александров Соколовых)

        Я пользуюсь sql исключительно для выгрузок и витрин. Самой простой пример из последнего: формирования витрины данных жалоб с сайта для формирования дашборда по динамике NPS, количеству жалоб и пр.
        Дорожка действительно тернистая, особенно когда вынужден использовать одновременно PL/SQL, MySQL
        +6

        Все просто только пока дело не доходит до запросов с переподвыподвертом
        А уж когда нужно чтобы это ещё и работало быстро…
        Это я к чему собственно
        Учебников по элементарному sql в интернете навалом. Ещё один ничего не изменит.
        Лучше напишите о интересных и сложных случаях применения, может кому-нибудь пригодится.

          0
          Спасибо за замечание!
          гайдов много, но я не встречал таких, которые а) короткие, б) содержат много примеров, в) сразу предлагают практику
          Интересные примеры обязательно включу)
          0

          Имхо стоит добавить про последовательность выполнения запроса, что важно для понимания работы фильтров, а то тот же having очень часто удивляет людей, которые пытаются его использовать вместо where например.

            0
            Про having правда)) В начале статьи я кратко описал общую структуру запроса, подумаю, как можно акцентировать внимание читателя на последовательности запроса
            0
            Самое интересное видимо будет, когда до лягушки дойдёте.
            Пока начальная подготовка.
              +2
              JOINs это НЕ пересечения множеств. Диаграммы Венна только все запутывают.
              Сколько строк вернет следующий запрос?
              with
                a as (
              	select 1 x from dual
              	union all
              	select 1 x from dual
                      )
              , b as (
              	select 1 x from dual
              	union all
              	select 1 x from dual
                      )
              select a.*, b.*
                from a inner join b on a.x = b.x
              

              Can we stop with the SQL JOINs venn diagrams insanity?
                0
                Зачем эта статья? Чем она отличается от любой справки по SQL?
                  +1
                  30-ти летней давности, заметим. Потому что на таком уровне изложения язык не отличается от того, каким он был в SQL/DS, к п
                  римеру
                    0
                    Моя ЦА: новички, студенты
                    Таргет: быстро, просто, с достаточным количеством примеров и практикой

                    Те справки, которые я встречал, не отвечали одновременно всем критериям, поэтому родилась эта статья)
                      +1
                      Есть прекрасный учебник на SQL EX
                      www.sql-tutorial.ru/ru/book_simple_select_statement/page1.html
                      Быстро, просто, с примерами и практикой.

                      Немного старый, но отлично объясняющий основы учебник на SQL.ru
                      www.sql.ru/docs/sql/u_sql/ch3.shtml
                      Быстро, просто, с примерами и практикой.

                      Есть неплохой учебник на SQL Academy
                      sql-academy.ru/guide/syntax-sql-select
                      Быстро, просто, с примерами и практикой.

                      Я замечу, что всё это буквально на первой странице поисковой выдачи.

                      А теперь вы анонсировали ещё и «продолжение».

                      Почему, мистер Андерсон, почему? Во имя чего? Что вы делаете? Зачем продолжаете копипастить?
                        0

                        за мистера Андерсона не скажу, а за себя скажу)
                        вышеперечисленными учебниками я действительно пользовался, но они не отвечали моему критерию по затраченному времени и не всегда нравилась последовательность материала.
                        Я не просто так поставил тег «студентам» и, как уже писал выше, один из таргетов: скорость
                        но в любом случае спасибо! тем, кто захочет углубиться самостоятельно и имеет хороший запас времени, ресурсы будут действительно очень полезны)

                        0
                        Да ладно… А что-то типа Кодда не пробовали? Я не очень верю, что за более чем 30 лет не написан базовый учебник хорошего уровня. Не базовый — верю, хотя бы по причине различий в диалектах, но по той же причине в качестве основы сойдет учебник, описывающий любую СУБД — потому что отличия все равно потом придется изучать по справочнику.
                      0

                      А где удалить, обновить, вставить?

                        0

                        В следующих, трех, статьях

                          0
                          Да, это это действительно опишу дальше
                          Для новичка и пользователя с доступом «чтение» написанное в статье — самое важное
                          Создание/удаление/добавление — чуть повыше уровнем, но в любом случае, это опишу
                            0
                            sokolov_alexr
                            Спасибо за пост!

                            У меня вопрос-предложение – будет ли в аналогичной подаче пост о работе с самой базой данных? На примере используемой вами системы (или системы, которая наиболее распространена для подобной работы): как подключиться к базе, как создать таблицу(-ы) / добавить или удалить столбцы и записи, связать таблицы между собой (один-ко-многим и т.д.). Такой средний (часто встречающийся) набор стандартных операций над базой помимо синтаксиса SQL. Крайне желательно, конечно, чтобы был интерактив – как в данном посте.

                            Жду обещанного продолжения.
                              0
                              Да, определенно!
                              Либо включу в одну из следующий частей, либо сделаю отдельный пост с разбором)

                              Спасибо за отзыв)
                          0
                          Есть ли ide для SQL кода с завершением? Чтобы писать начало команды а программа дополняла? Что бы форматировала код? И какие еще инструменты для ускорения и упрощения работы есть?
                            +1
                            Ничего круче PL/SQL Developer от Allround Automations я не видел, но как можно понять из названия он годится только под Oracle.

                            Datagrip (ну и IDEA Ultimate тоже) от JetBrains умеют предлагать и форматировать, так что если вариант выше не подходит, то я в итоге пришёл к этому решению.
                              0
                              dbForge хороший и бесплатный
                              0
                              Самое то для новичков.
                                0
                                Спасибо! Помочь новичкам и было моей основной целью)
                                0
                                Мне, как начинающему, этот материал оказался полезен.
                                  0
                                  Рад, что понравилось!
                                  0

                                  Эээ… Это попытка пересказать "Понимание SQL" М.Грабера???

                                    0
                                    Это пересказ собственных знаний в сжатом (и немного урезанном) виде для новичков)
                                    0
                                    Про типы запросов уже сказано. Из «ещё» — не нравится описание JOIN, во-первых, потому, что он не там, где надо (а должен он быть подразделом FROM), во-вторых, потому, что, мягко говоря, привирает — то, что названо ключом, запросто может ссылаться на третьи таблицы и/или скалярные данные. HAVING (в тегах указано MySQL) порой используется и без агрегирования. Про CTE (совсем до) и оконные функции (совсем после) даже не говорю.

                                    Но главным недостатком считаю следующее. Да, для того, кто вообще никогда и ни в чём не программил, сойдёт — вот только такой товарищ ничего не поймёт, текст предполагает наличие минимальных навыков программистского мышления. А для того, кто уже программировал, упущена очень важная необходимость «сломать мышление». В программировании надо думать итерациями (выбираем по одному то, что подходит), а в SQL надо наоборот, думать множествами (сваливаем всё в кучу, а потом выбрасываем то, что не подходит). Если этого не сделать — мозг начинает порождать сонмы сто лет ненужных коррелированных подзапросов, в которых чёрт ногу сломит.
                                      0
                                      Спасибо за развернутый фидбек!
                                      СТЕ и оконные функции здесь намеренно не рассматривал, чтобы не запутать вливающегося новичка, об этом имхо лучше говорить, когда есть база в голове.
                                      Насчет «ничего не поймет» не соглашусь, перед написанием статьи я давал этот материал кусочно в личных сообщениях и вживую людям, которые была далеки от программирования в принципе и получал положительную обратную связь, что, собственно, и подтолкнуло к тому, чтобы написать статью.
                                      Насчет HAVING хорошее замечание, вы правы, внесу правку, спасибо!
                                      –1
                                      Как начинающий скажу — статья очень и очень понятна. После чтения www.sql-tutorial.ru/ru/book_simple_select_statement/page1.html — вообще небо и земля. Спасибо! Буду ждать остальные части.
                                        0
                                        Спасибо большое! Бальзам на душу от таких отзывов))

                                      Only users with full accounts can post comments. Log in, please.