17.12.2019 17:25
sokolov_alexr 34 10700 Источник

Введение


Язык 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'ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Комментарии (34)


  1. ikle
    17.12.2019 21:00
    #21023744
    +1

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

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


    1. sokolov_alexr Автор
      18.12.2019 12:14
      #21026478

      1. Да, действительно, выбрал тактику и стоило ее придерживаться) Здесь решил в итоге добавить короткую секцию, чтобы совсем новичкам было, с чем играться, пока ожидаю модерации и пишу вторую часть статьи
      2. Это правда, в следующей части постараюсь осветить это)


  1. kolu4iy
    17.12.2019 21:09
    #21023782
    -2

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


    1. sokolov_alexr Автор
      18.12.2019 12:19
      #21026524

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

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


  1. demon416nds
    17.12.2019 22:23
    #21024008
    +1

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


    1. sokolov_alexr Автор
      18.12.2019 12:20
      #21026532

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


  1. BlessYourHeart
    17.12.2019 22:55
    #21024146

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


    1. sokolov_alexr Автор
      18.12.2019 12:22
      #21026554

      Про having правда)) В начале статьи я кратко описал общую структуру запроса, подумаю, как можно акцентировать внимание читателя на последовательности запроса


  1. x893
    17.12.2019 23:07
    #21024180

    Самое интересное видимо будет, когда до лягушки дойдёте.
    Пока начальная подготовка.


  1. styzhatel
    17.12.2019 23:13
    #21024214

    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?


  1. A114n
    17.12.2019 23:13
    #21024216

    Зачем эта статья? Чем она отличается от любой справки по SQL?


    1. sshikov
      18.12.2019 07:32
      #21025048

      30-ти летней давности, заметим. Потому что на таком уровне изложения язык не отличается от того, каким он был в SQL/DS, к п
      римеру


    1. sokolov_alexr Автор
      18.12.2019 12:25
      #21026594

      Моя ЦА: новички, студенты
      Таргет: быстро, просто, с достаточным количеством примеров и практикой

      Те справки, которые я встречал, не отвечали одновременно всем критериям, поэтому родилась эта статья)


      1. A114n
        18.12.2019 13:16
        #21027042
        +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
        Быстро, просто, с примерами и практикой.

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

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

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


        1. sokolov_alexr Автор
          18.12.2019 13:35
          #21027178

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


      1. sshikov
        18.12.2019 19:09
        #21029656

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


  1. Compolomus
    18.12.2019 01:40
    #21024582

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


    1. pOmelchenko
      18.12.2019 08:45
      #21025198

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


    1. sokolov_alexr Автор
      18.12.2019 12:27
      #21026618

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


      1. ProSev
        18.12.2019 15:31
        #21028162

        sokolov_alexr
        Спасибо за пост!

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

        Жду обещанного продолжения.


        1. sokolov_alexr Автор
          18.12.2019 15:43
          #21028262

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

          Спасибо за отзыв)


  1. qant
    18.12.2019 10:34
    #21025640

    Есть ли ide для SQL кода с завершением? Чтобы писать начало команды а программа дополняла? Что бы форматировала код? И какие еще инструменты для ускорения и упрощения работы есть?


    1. Milein
      18.12.2019 10:51
      #21025806

      Ничего круче PL/SQL Developer от Allround Automations я не видел, но как можно понять из названия он годится только под Oracle.

      Datagrip (ну и IDEA Ultimate тоже) от JetBrains умеют предлагать и форматировать, так что если вариант выше не подходит, то я в итоге пришёл к этому решению.


    1. strelkan
      20.12.2019 10:42
      #21038478

      dbForge хороший и бесплатный


  1. Funky_Alex
    18.12.2019 12:27
    #21026624

    Самое то для новичков.


    1. sokolov_alexr Автор
      18.12.2019 12:28
      #21026632

      Спасибо! Помочь новичкам и было моей основной целью)


  1. ProSev
    18.12.2019 15:39
    #21028222

    Мне, как начинающему, этот материал оказался полезен.


    1. sokolov_alexr Автор
      18.12.2019 20:33
      #21030102

      Рад, что понравилось!


  1. uaggster
    18.12.2019 19:27
    #21029764

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


    1. sokolov_alexr Автор
      18.12.2019 20:24
      #21030064

      Это пересказ собственных знаний в сжатом (и немного урезанном) виде для новичков)


  1. Akina
    18.12.2019 20:24
    #21030072

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

    Но главным недостатком считаю следующее. Да, для того, кто вообще никогда и ни в чём не программил, сойдёт — вот только такой товарищ ничего не поймёт, текст предполагает наличие минимальных навыков программистского мышления. А для того, кто уже программировал, упущена очень важная необходимость «сломать мышление». В программировании надо думать итерациями (выбираем по одному то, что подходит), а в SQL надо наоборот, думать множествами (сваливаем всё в кучу, а потом выбрасываем то, что не подходит). Если этого не сделать — мозг начинает порождать сонмы сто лет ненужных коррелированных подзапросов, в которых чёрт ногу сломит.


    1. sokolov_alexr Автор
      18.12.2019 20:33
      #21030098

      Спасибо за развернутый фидбек!
      СТЕ и оконные функции здесь намеренно не рассматривал, чтобы не запутать вливающегося новичка, об этом имхо лучше говорить, когда есть база в голове.
      Насчет «ничего не поймет» не соглашусь, перед написанием статьи я давал этот материал кусочно в личных сообщениях и вживую людям, которые была далеки от программирования в принципе и получал положительную обратную связь, что, собственно, и подтолкнуло к тому, чтобы написать статью.
      Насчет HAVING хорошее замечание, вы правы, внесу правку, спасибо!


  1. Slavina_L
    19.12.2019 20:21
    #21036430
    -1

    Как начинающий скажу — статья очень и очень понятна. После чтения www.sql-tutorial.ru/ru/book_simple_select_statement/page1.html — вообще небо и земля. Спасибо! Буду ждать остальные части.


    1. sokolov_alexr Автор
      19.12.2019 20:21
      #21036434

      Спасибо большое! Бальзам на душу от таких отзывов))