company_banner

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Original author: Terence Shin
  • Translation
Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT, FROM и WHERE. Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.



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

  1. Изучение механизмов, которые выходят за пределы базового знания SQL.
  2. Рассмотрение нескольких практических задач по работе с SQL.

В статье рассмотрено 5 вопросов по SQL, взятых с Leetcode. Они представляют собой практические задачи, которые часто встречаются на собеседованиях.

Вопрос №1: второе место по зарплате


Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников (Employee) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200. Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

▍Решение А: использование IFNULL и OFFSET


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

  • IFNULL(expression, alt): эта функция возвращает свой аргумент expression в том случае, если он не равен null. В противном случае возвращается аргумент alt. Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
  • OFFSET: этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).

Вот — готовый запрос:

SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

▍Решение B: использование MAX


В запросе, представленном ниже, используется функция MAX. Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты


Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

▍Решение А: COUNT в подзапросе


Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1. Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email
) as email_count
WHERE count > 1

▍Решение B: выражение HAVING


  • HAVING: это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY.

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

Вопрос №3: растущая температура


Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

▍Решение: DATEDIFF


  • DATEDIFF: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

Если сформулировать обычным языком следующий запрос, то окажется, что он выражает следующую идею: нужно выбрать такие идентификаторы, чтобы температура, соответствующая представляемым ими датам, была бы больше, чем температура на «вчерашние» по отношению к ним даты.

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении


В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе (Id) сотрудника, о его имени (Name), о зарплате (Salary) и о подразделении компании, где он работает (Department).

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

▍Решение: команда IN


Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR. Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения (Department), имя сотрудника (Employee) и его заработную плату (Salary). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения (DepartmentID) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary) 
    IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
 )

Вопрос №5: пересаживание учеников


Мэри — учительница в средней школе. У неё есть таблица seat, хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN


SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT 
    CASE 
        WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

Итоги


Мы разобрали несколько задач по SQL, попутно обсудив некоторые продвинутые средства, которые можно использовать при составлении SQL-запросов. Надеемся, то, что вы сегодня узнали, пригодится вам при прохождении собеседований по SQL и окажется полезным в повседневной работе.

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

RUVDS.com
VDS/VPS-хостинг. Скидка 10% по коду HABR

Comments 83

    +32
    И где здесь Data Science?
      +12
      Так на PHP джуниоров только дата-саентистов и набирают.
      0
      Первая задача так же хорошо решается с помощью dense_rank
      • UFO just landed and posted this here
          +3
          Я вот как раз и зашел посмотреть на хитрые оконные функции, а тут ни одной. Печально на самом деле, эти люди потом базы проектируют.
            0

            А ещё есть lag :) и first_value() + distinct ..(для oracle с rownum() <2)
            Можно и без подзапроса…
            Ну и без дистинкт есть qualify но увы в терадате :)

              0
              rownum вы можете использовать только при отсутствии сортировки, в остальных случая window эффективнее
                0

                А кто-то говорил про сортировку?
                В задаче просто нужно а 1 строчка в ответе :)

              0
              Про оконные функции я с вами согласен, однако есть один важный момент.
              Решение с max возвращает всегда одну строку, в которой или вторая зарплата или null. И если я правильно понял, именно так и написано в условии задачи.

              А ваше решение в случае если второй зарплаты нет, вернет 0 строк, что не совсем соответствует условию задачи.
              • UFO just landed and posted this here
            • UFO just landed and posted this here
                +2
                + один раз настроить автоформатер.

                Красивый способ выравнивания условий, надо попробовать датагрип так настроить
                  +4

                  Был случай… Новый человек взял и прошёлся автоформатом по пакеты на 1000+ строк, пока вносил дополнения, а он был вылизан с расставленными комментариями, выровненными полями где это важно и собранными в длинные строки где не особо… Ну в общем как-то нехорошо получилось...

                    0

                    Надо было вылизывать автоформаттер, а не 1000+ строк кода, решая, где ещё не лень расставлять отступы, а где уже пофиг и не жаль читателя.

                      0
                      А вот над этой темой сам страдаю. Имхо либо базу под гит, либо код из нее достать вообще. Я не знаю как вообще можно жить с большой кодовой базой, кучей разработчиков и без репозитория
                    0

                    Спорно. Если вместо неявных соединений использовать явные — то верхний стиль как раз окажется проще выровнять:


                    FROM Weather a
                    JOIN Weather b ON DATEDIFF(a.Recorddate, b.Recorddate) = 1

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


                    Кроме того, во втором варианте кажется, что DISTINCT относится к конкретному столбцу, а не ко всему набору столбцов.

                      +1
                      Плюсую.
                      Я когда в новой команде так писал запросы, на меня смотрели как на колдуна — фигасе, как красиво! Меня же скорее удивляло, что для кого-то это не норма.
                      Наш тимлид вообще не запаривался и писал всё сплошной строкой, типа внутри кода никто не видит потому и пофиг. Это был просто ужас перфекциониста.
                        0
                        Я писателей одной строкой встречал очень и очень много. Это наверное какие то сверхлюди, я не понимаю как они это потом правят.
                          0
                          Тимлиду из предыдущего комментария не обязательно потом это править, он может это делегировать кому-нибудь.

                      +8
                      Неявные JOIN в 2020 это нормально?
                        –4
                        а что плохого в неявных джойнах, если запросы на пару таблиц, и нет какой-то сложной логики? (вопрос безотносительно подачи остального материала статьи, он, конечно, ужасен.)
                          +8
                          Устаревший синтаксис.
                          Плохая читаемость.
                          Выбивается из общего принципа построения запросов.

                          Зачем?
                            0
                            неправда. SQL92 и последующие не делают obsolete синтаксис SQL89.
                            да ну? разве запрос SELECT a.x, b.y FROM table1 a, table2 b WHERE b.c=a.c плохо читается?
                            из какого такого ещё «общего» принципа? где и кем он описан? в каком стандарте?

                            нездоровым догматизмом попахивает.
                              +2

                              В оракл тоже работает a.b(+) = c.d, но вот пойди вспомни что оно там означает в каких сочетаниях
                              Ваш пример, по мне, тоже неудобно, особенно если таблиц много.
                              В последнее время часто приходится использовать большие (сотни строк) запросы, так в них (если удаётся оптимизировать) стараюсь выносить параметры в первый with, чтобы хотябы собрать их в одном месте, и далее каждый подзапрос (with) начинать со таблицы параметров к которой уже идут join-ы, разобрав пару сложных запросов пришёл к выводу что лучше сразу писать более структурировано. хоть и многословнее.

                                0
                                a.b(+) = c.d

                                Если мне не изменяет память, объявили устаревшим лет 10 назад

                                По теме, вы конечно правы, во всех прочих языках уже давно стараются избегать всех возможных неявных моментах, а в SQL как будто дивный дикий мир.
                                  0
                                  В оракле какой-то версий (то ли 8, то ли 10) в одном из релизов был баг с анси-синтаксисиом, поэтому опытные ораклисты его не любят и побаиваются. Баг был потом закрыт, поэтому уже можно не бояться, но традиция осталась. Ну и сам по себе анси-sql завези в оракл далеко не сразу.
                                –1
                                неправда. SQL92 и последующие не делают obsolete синтаксис SQL89.
                                да ну? разве запрос SELECT a.x, b.y FROM table1 a, table2 b WHERE b.c=a.c плохо читается?
                                из какого такого ещё «общего» принципа? где и кем он описан? в каком стандарте?

                                нездоровым догматизмом попахивает.

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

                                Не знаю для кого эти SQL вопросы, и кто их составлял, но этому вопросу про температуру кросс джойн нужен как зайцу стоп-сигнал IMHO. Вроде чувак вменяемо изложил — пробежаться по дням и посмотреть, чтобы за предыдущий день температура была ниже. Ну так и закодируй эту мысль. Пробежался селектом, обозначил таблицу буквой a, окей. Теперь для WHERE добавь вложенный запрос с другой буквой для той же таблицы чтобы извлечь температуру предыдущего дня. Лучше на мой взгляд пронумеровать дни, чтобы не пользоваться DATEDIFF, которая неизвестно как работает на разных системах. И вся пестня.

                                Проверил на всякий случай. Management Studio 12 на одном старом компьютере стоит.
                                Таблицу сделал с двумя полями:
                                CREATE TABLE [dbo].[T](
                                	[DayNumber] [int] IDENTITY(1,1) NOT NULL,
                                	[Value] [int] NOT NULL
                                ) ON [PRIMARY]

                                30000 записей добавил туда со случайными значениями от 0 до 100 в поле [Value]

                                Первый запрос с кросс джойном:
                                SELECT a.DayNumber, a.Value
                                  FROM T a, T b
                                  WHERE a.Value > b.Value
                                  AND a.DayNumber - b.DayNumber = 1

                                Ну да, вроде как работает, почти 15000 записей ответ, ожидаемо, примерно полторы минуты.

                                Второй запрос с вложенным запросом:
                                SELECT a.DayNumber, a.Value 
                                  FROM T a
                                  WHERE a.Value > (SELECT b.Value  
                                			FROM T b 
                                			WHERE a.DayNumber - b.DayNumber = 1)

                                Результат тот же, время чуть меньше, но это продвинутый двигатель — MS SQL Server, на других разница может быть больше.
                                  0
                                  CREATE TABLE [dbo].[T](
                                  	[DayNumber] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
                                  	[Value] [int] NOT NULL
                                  ) ON [PRIMARY]
                                  

                                  SELECT a.DayNumber, a.Value
                                  FROM T a, T b
                                  WHERE a.Value > b.Value
                                   AND a.DayNumber = 1 + b.DayNumber
                                  

                                  Отработает мгновенно
                                    0
                                    Эх-хе.
                                    CREATE TABLE #T(
                                    	[DayNumber] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
                                    	[Value] [int] NOT NULL
                                    ) 

                                    
                                    SELECT 
                                        a.DayNumber
                                       ,a.Value 
                                    FROM #T a
                                    WHERE a.Value > (SELECT b.Value  
                                                     FROM #T b 
                                                     WHERE a.DayNumber - b.DayNumber = 1)
                                    OPTION (MAXDOP 1)


                                    Очень плох, потому что начинает потоково обрабатывать запрос. (в WHERE чачти вычисление, которое не даёт правильно оптимизировать запрос и он втупую по кускам данных пытается их сматчить с подзапросом)

                                    Намного лучше:
                                    SELECT 
                                        a.DayNumber
                                       ,a.Value 
                                    FROM #T a
                                    WHERE a.Value > (SELECT b.Value  
                                                     FROM #T b 
                                                     WHERE b.DayNumber = a.DayNumber - 1)
                                    OPTION (MAXDOP 1)


                                    План запроса у этого запроса такой же как и у
                                    
                                    SELECT 
                                        a.DayNumber
                                       ,a.Value
                                    FROM #T a, #T b
                                    WHERE a.Value > b.Value
                                      AND a.DayNumber = 1 + b.DayNumber
                                    OPTION (MAXDOP 1)

                                    И эффективность соответственная (лучше).

                                    Лучший вариант:
                                    ;WITH tmp AS (
                                    SELECT 
                                      a.*
                                     ,Lag(a.[Value]) OVER (ORDER BY [DayNumber]) AS lgValue
                                    FROM #T as a 
                                    )
                                    SELECT 
                                       t.DayNumber
                                      ,t.Value
                                    FROM tmp as t
                                    WHERE t.[Value] > t.lgValue
                                    OPTION (MAXDOP 1)

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

                                    Могу привести и планы запросов, но лучше сами проверьте, потому что картинки вставлять совсем не хочется.

                                    ЗЫ: MS SQL. ~250000 строк.
                                      0
                                      Шикарное дополнение, читать правда непривычно T-SQL.
                                      Небольшое пожелание: называйте пожалуйста CTE осмысленно, спасибо)
                                        0
                                        Вот сидел и думал — поменять название или нет. Потом — да ну, кому какое дело до синтетического примера… ))))

                                        Почему непривычно?
                                          0
                                          да ну, кому какое дело до синтетического примера

                                          В общем случае конечно никакого, но вас же джуны читают) Я буквально недавно объяснял человеку почему запрос с CTE вида

                                          with tmp1 as (),
                                               tmp2 as (),
                                               tmp3 as ()
                                               tmp4 as ()
                                          select from tmp1 
                                          left join tmp2
                                          left join tmp3
                                          left join tmp4
                                          


                                          Крайне хреновая идея. Ониж откуда то берутся. Даш разок слабину и все, толпа говнокодеров)

                                          Почему непривычно?

                                          Из-за квадратных скобочек. Смотрятся необычно, а сам я с этой базой не работаю еще с тех пор ка кона была виндовс онли
                                            0
                                            В общем случае конечно никакого, но вас же джуны читают)
                                            Чёрт! Срочно нужен маркер 6+ (стаж)!!)

                                            Крайне хреновая идея. Ониж откуда то берутся. Даш разок слабину и все, толпа говнокодеров)
                                            Ну всё, ну всё, мне стыдно )
                                            А берутся они из-за того, что программисты входят в профессию без надлежащего образования (хоть кого то в универе заставляли читать чистый код?), работают с базами данных через фреймворки (и не парятся что там как написано) и работают частенько не в команде (из-за чего объяснить им что то некому).

                                            Из-за квадратных скобочек. Смотрятся необычно, а сам я с этой базой не работаю еще с тех пор ка кона была виндовс онли
                                            Они и в МС не обязательны по большей части. =)
                                        0
                                        Очень плох, потому что начинает потоково обрабатывать запрос. (в WHERE чачти вычисление, которое не даёт правильно оптимизировать запрос и он втупую по кускам данных пытается их сматчить с подзапросом)

                                        Там в оригинале вообще DATEDIFF стоит. Смысл моего комментария был не в том, чтобы найти самое быстрое решение, смысл в том, что вообще кросс джойн там — перебор, можно вполне использовать вложенный запрос. Для этого я и привел код. Я не мог убрать упомянутое вычисление, так как тогда мне сказали бы, что я не корректно сравниваю. Я от DATEDIFF ушел не для оптимизации, а для упрощения кода, — с этими датами в разных системах скорее всего слишком много нюансов.

                                        Лучший вариант:

                                        ;WITH tmp AS (


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

                                        Я далек от этой темы, но рискну предположить, что не факт «Лучший вариант», возможно для большинства практических случаев это так и есть, но не для всех сценариев. Этот курсорообразный Lag может и хорош, когда записи в последовательности рядом, но если завтра понадобится переделать запрос под далеко отстоящие друг от друга записи, то это значит, что в памяти надо будет держать всю CTE временную таблицу, то есть, кто-то другой будет ждать, пока вы освободите этот десяток-другой гигабайт общей памяти. Плюс, может возникнуть вопрос к актуальности этих данных в памяти.

                                        В случае же вложенного запроса — таки да, есть повторные запросы к таблице. Но цена этих запросов не так уж велика, если поле проиндексировано, — с современными корпоративными SSD. Сколько надо дисковых операций, чтобы выйти, к примеру) на нужную из 2 в степени 40 записей, — всего пару десятков в среднем, типа того. (Тера записей) Не знаю, может вы и правы с этими CTE, но мне это не особенно очевидно для общего случая.
                                          0
                                          Я далек от этой темы, но рискну предположить, что не факт «Лучший вариант», возможно для большинства практических случаев это так и есть, но не для всех сценариев. Этот курсорообразный Lag может и хорош, когда записи в последовательности рядом, но если завтра понадобится переделать запрос под далеко отстоящие друг от друга записи, то это значит, что в памяти надо будет держать всю CTE временную таблицу, то есть, кто-то другой будет ждать, пока вы освободите этот десяток-другой гигабайт общей памяти. Плюс, может возникнуть вопрос к актуальности этих данных в памяти.

                                          В случае же вложенного запроса — таки да, есть повторные запросы к таблице. Но цена этих запросов не так уж велика, если поле проиндексировано, — с современными корпоративными SSD. Сколько надо дисковых операций, чтобы выйти, к примеру) на нужную из 2 в степени 40 записей, — всего пару десятков в среднем, типа того. (Тера записей) Не знаю, может вы и правы с этими CTE, но мне это не особенно очевидно для общего случая.


                                          Lag не курсорообразен, а окнообразен. И вполне себе работает так же по индексу — так что тут чистая выгода по сравнению с двойным обращением. =)
                                          С оконными функциями, конечно, свои нюансы (к примеру — забить память до сгружения вычислений на диск), но в таких ситуация и двойной запрос будет печален.
                                          В прочем — в жизни всякое бывает )) и, да — это только про MS SQL. В других системах CTE могут работать принципиально иначе.

                                          Там в оригинале вообще DATEDIFF стоит. Смысл моего комментария был не в том, чтобы найти самое быстрое решение, смысл в том, что вообще кросс джойн там — перебор, можно вполне использовать вложенный запрос. Для этого я и привел код. Я не мог убрать упомянутое вычисление, так как тогда мне сказали бы, что я не корректно сравниваю. Я от DATEDIFF ушел не для оптимизации, а для упрощения кода, — с этими датами в разных системах скорее всего слишком много нюансов.
                                          Очень двоякий вопрос эквивалентности разных решений с одинаковым ответом ))
                                          Но, как кто то выше заметил, нас могут читать джуны, поэтому лишний раз обратить внимание на такие мелочи (совсем не мелкие в плане оптимизации) — не лишне.
                                          ЗЫ: и всё-таки лучше использовать Datediff, потому что, к примеру, тип Date не поддерживает арифметические операции… (
                                    0
                                    Ну, чуть более сложные соединения же пишете через JOIN наверняка? Тогда зачем плодить разный синтаксис?
                                      0
                                      Я пишу так, как мне удобно в каждом конкретном случае. Если таблиц сильно больше двух, то чаще с явными джойнами. А если их в запросах сильно больше 10, то это значит, что я что-то неправильно напроектировал, и надо подумать, как переделать схему, чтобы не было этих километровых запросов.

                                      Но если я пишу ORM, и запросы генерируются по метаданным, то там явные джойны использовать будет совсем уж неудобно.

                                      Ну и, наконец, от базы зависит. Если Oracle, то джойны тоже будут неявные.
                                        +3
                                        Я пишу так, как мне удобно в каждом конкретном случае.
                                        Не говорю, что у вас так, но частенько это приводит к нечитабельному коду.

                                        А если их в запросах сильно больше 10, то это значит, что я что-то неправильно напроектировал, и надо подумать, как переделать схему, чтобы не было этих километровых запросов.
                                        Это называется — нормализация! )
                                        На самом деле ничего в большом количестве джоинов, обычно, плохого нет. Плохое обычно совсем в другой степи кроется )

                                        Но если я пишу ORM, и запросы генерируются по метаданным, то там явные джойны использовать будет совсем уж неудобно.
                                        Ну, дело, конечно, каждого, но не вижу разницы. =)

                                        Ну и, наконец, от базы зависит. Если Oracle, то джойны тоже будут неявные.
                                        ну это да.=)

                                          0
                                          Иной раз нужна хорошая денормализация %)

                                          За последнюю пару десятилетий я юзал СУБД всех крупных вендоров, кроме MS. Oracle, MySQL (включая MariaDB), Postgre, DB2, Firebird. Вообще особо без разницы, под что писать, если оставаться в рамках SQL99. Ну, последние несколько лет можно и фичи из SQL2003 юзать, особо не задумываясь, какая там база. Вот более новые стандарты как-то трудно внедряются.

                                          Обширная практика позволяет как-то не особо зацикливаться на синтаксисе запросов (особенно если руками пишется только шаблон) или наборе функций, просто больше думаешь о прагматике той системы, которую разрабатываешь. Требования на базу часто спускают сверху. И если в конкретной СУБД нету какой-нибудь window function, вот тогда приходится вспоминать, как это делалось 10 лет назад.
                                            0
                                            Иной раз нужна хорошая денормализация %)
                                            Всё хорошо в меру.

                                            За последнюю пару десятилетий я юзал СУБД всех крупных вендоров, кроме MS. Oracle, MySQL (включая MariaDB), Postgre, DB2, Firebird. Вообще особо без разницы, под что писать, если оставаться в рамках SQL99. Ну, последние несколько лет можно и фичи из SQL2003 юзать, особо не задумываясь, какая там база. Вот более новые стандарты как-то трудно внедряются.

                                            Обширная практика позволяет как-то не особо зацикливаться на синтаксисе запросов (особенно если руками пишется только шаблон) или наборе функций, просто больше думаешь о прагматике той системы, которую разрабатываешь. Требования на базу часто спускают сверху. И если в конкретной СУБД нету какой-нибудь window function, вот тогда приходится вспоминать, как это делалось 10 лет назад.
                                            Тогда наш опыт не пересекается. Я работаю только с MSSQL и в основном именно пишу и читаю запросы и логику, учитывая что вся логика и процессы на стороне сервера. Тут и шаблонов на всё не запасёшь и разрабатывали это не один десяток людей и лет. И что бы хотя бы понять что происходит — приходится много скриптов перекопать. И тут то и возникает большое желание привести всё в красивый, единообразный и читабельный вид. =)
                                          0
                                          А если их в запросах сильно больше 10, то это значит, что я что-то неправильно напроектировал

                                          Везёт. А вот у нас в среднем в репорте количество полей больше 15, и всё время норовят туда ещё чего-нибудь докинуть, чтобы "вся система на одном экране".

                                      +1
                                      Среди ораклистов, наоборот, считается дурным тоном использовать явный: )
                                  +1
                                  Так, хорошо.
                                  Уровень знаний sql у датасаентистов спрашивают примерно начальный.
                                  Или это у начинающих датасаентистов?
                                  Давайте дальше.
                                  По каким областям знаний ещё датасаентистов спрашивают?
                                    +2
                                    никаких оконных функций, именованных табличных выражений, рекурсии? вы серьезно? Задающий такие вопросы интервьюер не прошел бы собеседование у меня, например (а я даже не дата сейентист, обычная биг дата).
                                    Не говоря уже о том, что не указан хотя бы SQL-диалект, для которого приведены примеры. Учитывая неявные джойны — наверное, оракл? Впрочем, для таких слабых вопросов это, наверное, действительно неважно.
                                    Да, и большую часть вопросов здесь элементарно решить с помощью оконных функций — dense_rank в первом, lead в третьем, четвертый так вообще прямо напрашивается на max () over (partition by DepartmentID)
                                      +8
                                      У тебя цель — «показать свою крутизну» или решить задачу?
                                      Вот чего здесь не хватает — так это анализа времени выполнения в explain, с объяснением какой способ оптимальнее.
                                        +3

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

                                          0

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

                                            +1
                                            Почитал про hive. Если вы не против, не могли бы подсказать, в каких случая его едят? Я то по части РСУБД, в некоторых случая с time-series DB. А тут увидел незнакомые слова, полез гуглить, вдруг оно мне тоже надо)
                                              0
                                              в нашей компании hive используется для обработки больших (десятки терабайт) массивов данных.
                                                0
                                                Hive как SQL-обертку для работы с данными в HDFS используют там, где spark (pySpark/scala) не знают /irony
                                                Ну или стоит хорошо настроенный hdp с поднятыми llap-демонами / cloudera cdh с hive-on-spark в качестве движка, ибо классический MR для исполнения Hive SQL слишком медленный и в процессе выполнения запроса может временными файлами загадить пару сотен Тб в hdfs при неаккуратном обращении.
                                                PS если совсем серьёзно — нужно писать отдельную статью с тестами под конкретные случаи и обзором движков для исполнения запросов — но не уверен, что это кому-то будет интересно читать.
                                                  0
                                                  Благодарю, столько новых слов узнал) Моя вотчина PostgreSQL ибо занимаюсь как раз оперативными данными. При возникновении потребности сохранить/прочитать много сразу же сверху колоночную базу данных или стрим и агрегаторы поверх.
                                                    0
                                                    В таких cлучаях c hadoop поможет impala ( может не поддерживать все форматы данных, но умеет в массово-параллельные запросы, колоночность, сжатие и in-memory), spark (практически то же самое, но можно готовых библиотек добавить и построить мат.модели с графиками и тепловыми картами в пару команд) — будет быстро на достаточно больших объемах (несколько сотен Тб), но достаточно дорого по железу (а-ля 2*xeon 8xxx, 512 Gb+ RAM, 10*10Gb+ Hdd на каждую ноду, и нод от 5 до безграничности, со 100GbE /40 Gb infiniband картами)
                                                    PS Да, и ОС, вместе с журналами zookeeper/namenode/journalnode на m2 ssd писать — тогда сплошное благолепие.
                                                      0
                                                      нене, как нить потом. Как познаю все еспособыы выжать скорость из РСУБД, вот тогда подумаю чем бы еще заняться)
                                                        0
                                                        Реляционки и большие данные дополняют друг друга — в РСУБД отлично ложатся горячие данные, всякие OLAP-кубы и построенные витрины, а в bigdata — архивы, недоочищенные данные и разнообразные наборы под ML.
                                                        Но я вас отлично понимаю.
                                                0
                                                которые без них решить невозможно


                                                Без аналитики можно решить любую задачу. SQL все же алгебру множеств реализует. Просто с аналитикой эти задачи решаются гораздо эффективнее, а значит создаваемое ПО работает оптимально, а не абы как.
                                            +8
                                            Вопросы из разряда кто лучше помнит документацию тот и победил.
                                              +2
                                              Кроме всего прочего, половина запросов (ожидаемо) не выполнятся на MS SQL.

                                              Оптимизация и сравнение эффективности запросов (собственно, самое важное, обычно, в запросах в базе) не рассмотрено.
                                                0

                                                Сложилось впечатление что статья специально написана для вылавливания тех кто по ней готовился к собеседованию :)
                                                Как пример, первый запрос, вложенные подзапросы когда есть оконные функции, которые как раз хорошо подходят (не всегда и не во всех базах) как раз к операциям такого вида в классических СУБД, если уж предлагаются ответв то хорошо предложить варианты с объяснением того как это работает, ведь DS-ы как раз могут для удобства писать запросы с помощью impala либо hice, и на примере их можно было показать "как оно будет работать", и именно такие примеры ожидал увидеть :)

                                                • UFO just landed and posted this here
                                                    0

                                                    Есть начиная с восьмой версии кажется

                                                      0

                                                      Вроде как есть, в MySQL 8:
                                                      Window functions. MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions (for example, SUM() and AVG()). For more information, see Section 12.21, “Window Functions”.


                                                      MariaDB: 10.2:
                                                      Window functions have been introduced. This release adds support for a limited set of functions, and the basic execution algorithm. Development continues to expand the set of supported functions and optimize the execution.

                                                        0

                                                        Насколько я понимаю, вопросы и ответы взяты с Leetcode. А там, кажется, используется MySQL версии 5.7

                                                      +3

                                                      Пишу на MSSQL, конструкция LIMIT 1 OFFSET 1 в запросе выдаст ошибку.


                                                      Может стоит в список тегов добавить что это не просто SQL, а MySQL?

                                                        +1
                                                        Тогда еще стереть «дата-сайентистам» из заголовка, ибо mysql… mysql 5.7… Это как взять грабли покачественней да поизощренней.
                                                        +3
                                                        IFNULL(expression, alt): эта функция возвращает свой аргумент expression в том случае, если он не равен null. В противном случае возвращается аргумент alt. Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.

                                                        Я не спец по SQL но кажись данная функция в этой задаче не нужна.

                                                          0

                                                          Учитывая, что сейчас такие дата-сайентисты пошли (да и вообще инженеры, по-честному), что они лефт джойн не могут написать, эти вопросы достаточно сложные. Народ вообще пошел интересный: sql и регулярные выражения — два камня преткновения.

                                                            0

                                                            Это из-за редкости применения, по-моему. Я вот каждый раз как с нуля перечитываю спеку по regExp, исключительно потому, что пишу что-то сложнее "MyField\d+" обычно когда проверяю какие-то личные бредовые "а что если?". Ну а с SQL ещё проще — очень многим за глаза хватает ORM.

                                                            0
                                                            5й в оракле бдут так =)

                                                            with t as
                                                            (select 1 id,'Abbot' student from dual union all
                                                            select 2,'Doris' from dual union all
                                                            select 3,'Emerson' from dual union all
                                                            select 4,'Green' from dual union all
                                                            select 5,'Jeames' from dual)
                                                            
                                                            select 
                                                                id,
                                                                case when mod(id , 2) = 1 then nvl(lead (student,1) over (ORDER BY id),student) else nvl(lag (student,1) over (ORDER BY id),student) end
                                                            from t
                                                            

                                                              0

                                                              Много "хитростей" используют диалект MySQL.
                                                              В других реализациях такие финтьі ушами не пройдуть.

                                                                0
                                                                датасаентолог должен знать, что
                                                                что SQL — это не только SELECT

                                                                а еще и FROM.
                                                                сеньер так еще и WHERE знять обязан…
                                                                  0
                                                                  Senior должен знать ORDER BY DESC ))
                                                                    0
                                                                    Страшно подумать кто должен знать group by или, не дай бог, having.
                                                                      0
                                                                      А «select top 10 with ties»…
                                                                    +1

                                                                    Не знаю, намеренно получилось или нет, но "датасаентолог" отличный термин-антоним для "датасаентиста"! Теперь так и буду называть "специалистов", не знающих, чем отличается INNER от LEFT, но обладающих космическим самомнением.

                                                                    0
                                                                    max и having вроде есть везде, в отличие от limit и offset, так что нафига специфический/усложнённый ответ А, когда есть простой, понятный и работающий везде ответ B в первом и втором вопросах — не очень понятно :)
                                                                      +6

                                                                      Народ на полном серьёзе обсуждает проходную корпоративную заказуху, написанную студентом по заказу маркетингового отдела. Это я не знаю — как обсуждать сео тексты типа "Читатели часто спрашивают как приобрести пластиковые окна в рассрочку. Рассказываем, как приобрести пластиковые окна. Приступая к выбору пластиковых окон..." или разговаривать с роботом в онлайн чате.

                                                                        0
                                                                        Для меня так повод немного поизучать MS SQL, потренировать MS Management Studio на старом компьютере. Может быть мне полезно, — нужно неспешно выбрать на чем сделать небольшую базу данных, — то ли взять MS SQL Express, то ли My SQL (SQLite), то ли какое-то его современное ответвления — MariaDB, или может PostgreSQL. Эта «проходная корпоративная заказуха, написанная студентом по заказу маркетингового отдела» — всего лишь «seed field» (physics). Хабр рулит.
                                                                        +3
                                                                        Редкий случай, когда поставил статье минус. «Низкие технические качества статьи», имхо.
                                                                        Что задачи, что решения показывают крайне примитивный уровень владения SQL.
                                                                          0
                                                                          4й в оракле можно так:
                                                                          with Employee as  
                                                                          (select  1 id, 'Joe' name, 70000 salary, 1 DepartmentId  from dual union all
                                                                          select  2, 'Jim' , 90000, 1 from dual union all
                                                                          select  3, 'Henry', 80000, 2 from dual union all
                                                                          select  4, 'Sam' , 60000, 2 from dual union all
                                                                          select  5, 'Max' , 90000, 1 from dual)
                                                                          
                                                                          ,Department  as
                                                                          (select 1 id , 'IT' name from dual union all
                                                                          select  2, 'Sales' from dual)
                                                                          
                                                                          select 
                                                                          dn
                                                                          ,en
                                                                          ,salary
                                                                           from
                                                                          (select 
                                                                          Employee.name en
                                                                          ,salary
                                                                          ,Department.name dn
                                                                          ,DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC NULLS LAST) m
                                                                          from Employee, Department
                                                                          where Employee.DepartmentId = Department.id
                                                                          )
                                                                          where m=1
                                                                            0
                                                                            тот случай, когда комментарии намного ценнее самой статьи. Тоже напишу комментарий, который ценнее самой статьи.

                                                                            зачем задавать датасатанисту вопросы, которые бизнес сам может в два клика в powerBI/tableau узнать, не тратя время специалиста?

                                                                            мне кажется датасатанистов нужно интервьюировать не на чистые тех навыки, а на способность взять реальную бизнес-проблему, сформулировать в домене данных и продумать прототип решения, т.е. end-to-end анализ:
                                                                            1. кейс интервью с бизнесом, например с главой продажников, надо замечать какие вопросы кандидат будет задавать бизнесу
                                                                            2. формулирование бизнес-задачи в общем виде — надо увеличить продажи через сайт
                                                                            3. предложение парочки гипотез, типа а давайте делать А/B тесты на сайте и смотреть как улучшить конверсию
                                                                            4. сформулировать модель исследование (randomized controlled trial) — какие механизмы использовать для сбора данных и сбора метрик
                                                                            5. как анализировать данные и какие выводы делать из результатов анализа и т.д.
                                                                              0
                                                                              Мне вот интересно, почему там мало людей пользуется over...partition by? Эта конструкция ведь дает намного больше возможностей, более наглядна и универсальна
                                                                                0
                                                                                Ну лично мне она просто редко нужна. Возможно, есть задачи, где оконные функции популярны, у меня таких, видимо, мало. Я каждый раз их освежаю в памяти к собеседованиям.
                                                                                0
                                                                                Что-то легкие какие-то вопросы у вас на собеседованиях. У нас вон на SQL судоку решать предлагают.

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