10.05.2024 10:38
badcasedaily1 41 14000 Источник

Привет, Хабр!

Для аналитиков, владение SQL — это база. И от познаний в SQL зачастую зависит, отправит ли вам компания, где вы собеседуетесь - оффер.

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

Как оптимизировать SQL запросы?

Очень частый вопрос. Выделим три основных вида оптимизации.

Индексация

Индексация работает по принципу книжного указателя, позволяя БД быстро найти нужную строку без необходимости просмотра всех записей.

CREATE INDEX idx_customer_name ON customers (name);

Индекс поможет ускорить выполнение запросов, которые фильтруют или сортируют данные по столбцу name в таблице customers. Например, запрос:

SELECT * FROM customers WHERE name = 'Иван Иванов';

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

Партиционирование

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

CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT,
  sale_date DATE,
  amount DECIMAL(10, 2),
  PRIMARY KEY (sale_id)
) PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p0 VALUES LESS THAN (1991),
  PARTITION p1 VALUES LESS THAN (1992),
  PARTITION p2 VALUES LESS THAN (1993),
  ...
);

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

Нормализация

Нормализация — процесс организации данных в БД, т.е устранение избыточности и зависимостей.

Перед нормализацией:

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(100),
  customer_address VARCHAR(255),
  order_date DATE
);

После нормализации:

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  address VARCHAR(255)
);

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Информация о клиенте вынесена в отдельную таблицу, что уменьшает дублирование данных и упрощает их обновление.

Про джоины

Работа с JOINs — это основа для многих сложных аналитических задач. JOINы позволяют объединять данные из разных таблиц для получения комплексных отчетов и анализа данных.

INNER JOIN

Допустим, нужно получить список всех заказов с информацией о клиентах.

SELECT orders.order_id, customers.name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date > '2021-01-01';

Запрос объединяет таблицы orders и customers по customer_id для получения информации о заказах, сделанных после определенной даты.

LEFT JOIN

Нужно выявить клиентов, которые не делали заказов.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

LEFT JOIN используется для объединения данных о клиентах с данными о заказах. В результат включаются все клиенты, но заказы присутствуют только у тех, кто что-то заказывал. Фильтр по orders.order_id IS NULL позволяет идентифицировать клиентов без заказов.

RIGHT JOIN

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

SELECT products.product_name, orders.order_id
FROM products
RIGHT JOIN orders ON products.product_id = orders.product_id;

RIGHT JOIN аналогичен LEFT JOIN, но в этом случае в результат попадут все заказы, и если какие-то товары не были в заказах, то соответствующие поля product_name будут заполнены как NULL.

FULL OUTER JOIN

Интегрируем данные о клиентах и заказах для полного отчета.

SELECT customers.name, orders.order_id
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN объединяет LEFT и RIGHT JOIN, включая все строки из обеих таблиц. Если совпадений нет, соответствующие поля заполняются NULLами.

CROSS JOIN

Нужно сгенерировать все возможные пары продуктов для анализа кросс-продаж.

SELECT A.product_name, B.product_name
FROM products A
CROSS JOIN products B
WHERE A.product_id != B.product_id;

CROSS JOIN создает декартово произведение двух таблиц, что мастхев для анализа всех возможных комбинаций элементов из двух таблиц. В данном случае генерируются все возможные пары продуктов, за исключением пар с одинаковыми продуктами.

Оконные функции

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

ROW_NUMBER() для уникальной нумерации строк

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

SELECT customer_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS order_sequence
FROM orders;

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

RANK() и DENSE_RANK() для ранжирования данных

Ранжировать продажи сотрудников в рамках их отделов.

SELECT department_id, employee_id, sales_amount,
       RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS sales_rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS dense_sales_rank
FROM sales_records;

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

Кумулятивная сумма с использованием SUM()

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

SELECT order_date, daily_sales,
       SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales
FROM sales_by_date;

SUM() с оконной спецификацией OVER позволяет рассчитать кумулятивную сумму продаж, суммируя значения daily_sales начиная с начала набора данных до текущей строки.

Вычисление скользящего среднего

Вычисляем 7-дневное скользящее среднего объема продаж.

SELECT order_date, sales_amount,
       AVG(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM sales_records;

Юзаем AVG() с оконной функцией, заданной для расчета среднего значения sales_amount за текущий и предыдущие 6 дней.

Работа с подзапросами и CTE

Подзапросы и CTE улучшают структуру запросов.

Использование подзапросов

Находим клиентов, которые тратят больше среднего по всем клиентам.

SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) AS customer_spending
WHERE total_spent > (
    SELECT AVG(total_spent) FROM (
        SELECT SUM(amount) AS total_spent
        FROM purchases
        GROUP BY customer_id
    ) AS average_spending
);

Здесь внутренний запрос (подзапрос) используется для расчета общих трат каждого клиента, а внешний запрос сравнивает эти траты со средним значением, также полученным через подзапрос.

CTE для разделения сложных задач

Проведем анализ цепочки поставок для определения замедления доставки.

WITH SupplierData AS (
    SELECT supplier_id, region, COUNT(*) AS num_orders
    FROM orders
    GROUP BY supplier_id, region
),
RegionalDelays AS (
    SELECT region, AVG(delay) AS avg_delay
    FROM deliveries
    GROUP BY region
)
SELECT sd.supplier_id, sd.region, sd.num_orders, rd.avg_delay
FROM SupplierData sd
JOIN RegionalDelays rd ON sd.region = rd.region
WHERE rd.avg_delay > 10;

В запросе два CTE SupplierData и RegionalDelays используются для сегментации данных по поставщикам и региональным задержкам. Эти выражения затем объединяются для анализа.

Рекурсивные CTE

Генерируем серии дат.

WITH RECURSIVE DateSeries AS (
    SELECT '2021-01-01' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE date < '2021-01-31'
)
SELECT *
FROM DateSeries;

Рекурсивный CTE в этом запросе создает последовательность дат, что юзабельно для заполнения пропущенных дат в временных рядах или для создания календарей.

Перейдем к задачам

Первая задача

Нужно разработать запрос для идентификации пользователей, которые никогда не испытывали скуку (Bored) в любой рекламной кампании, но испытали восторг (Excited) в последней кампании.

Предполагается наличие таблицы campaigns, которая содержит поля user_id, campaign_id, impression, и timestamp. Поле impression указывает на эмоциональную реакцию пользователя ("Bored" или "Excited"), а timestamp фиксирует время реакции.

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

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

WITH LastImpression AS (
    SELECT user_id, MAX(timestamp) AS LastTime
    FROM campaigns
    GROUP BY user_id
),
ExcitedLast AS (
    SELECT l.user_id
    FROM LastImpression l
    JOIN campaigns c ON l.user_id = c.user_id AND l.LastTime = c.timestamp
    WHERE c.impression = 'Excited'
),
NeverBored AS (
    SELECT user_id
    FROM campaigns
    GROUP BY user_id
    HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0
)
SELECT n.user_id
FROM NeverBored n
JOIN ExcitedLast e ON n.user_id = e.user_id;

CTE LastImpression создает временное представление, содержащее user_id и время последнего взаимодействия для каждого пользователя.

CTE ExcitedLast выбирает пользователей, чья последняя реакция была Excited, используя данные из LastImpression.

CTE NeverBored:выбирает пользователей, которые никогда не испытывали скуку. HAVING фильтр здесь проверяет, что суммарное количество Bored впечатлений равно 0.

Финальный SELECT соединяет пользователей из NeverBored и ExcitedLast для получения идентификаторов тех, кто никогда не испытывал скуку, но был восторжен в последней кампании.

Вторая задача

Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день​

Есть таблица orders со следующими колонками:

  • customer_id - идентификатор клиента

  • order_date - дата заказа

  • order_cost - стоимость заказа

Делаем запрос:

SELECT customer_id, SUM(order_cost) AS total_cost
FROM (
    SELECT customer_id, order_date, SUM(order_cost) AS order_cost
    FROM orders
    WHERE order_date BETWEEN '2019-02-01' AND '2024-05-31'
    GROUP BY customer_id, order_date
) AS daily_orders
GROUP BY customer_id
ORDER BY total_cost DESC
LIMIT 1;

daily_orders: запрос фильтрует заказы, включая только те, что были сделаны с 1 февраля по 31 мая 2024 года.

Далее запрос группирует заказы по customer_id и order_date, суммируя стоимость заказов, сделанных в один день. Это сделано для того, что если клиент сделал несколько заказов в один день, их стоимости будут суммированы.

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

Внешний запрос также сортирует результаты по убыванию общей стоимости заказов и выбирает клиента с наибольшей общей стоимостью заказов, используя LIMIT 1 для возвращения только одного записи с макс. стоимостью.

Третья задача

Напишите запрос для определения трех отделов с наибольшим числом сотрудников, где более 10% сотрудников получают заработную плату выше $100,000.

Предполагается, что есть две таблицы: employees с колонками employee_id, department_id, salary и departments с колонками department_id, department_name.

WITH SalaryStats AS (
    SELECT 
        department_id,
        COUNT(*) AS total_employees,
        SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners
    FROM employees
    GROUP BY department_id
),
QualifiedDepartments AS (
    SELECT 
        s.department_id,
        d.department_name,
        s.total_employees,
        s.high_earners,
        (s.high_earners * 100.0 / s.total_employees) AS percent_high_earners
    FROM SalaryStats s
    JOIN departments d ON s.department_id = d.department_id
    WHERE (s.high_earners * 100.0 / s.total_employees) > 10
)
SELECT 
    department_id,
    department_name,
    total_employees,
    high_earners,
    percent_high_earners
FROM QualifiedDepartments
ORDER BY total_employees DESC
LIMIT 3;

CTE SalaryStats считает общее количество сотрудников в каждом отделе и определяет количество сотрудников с зарплатой выше $100,000, после группирует данные по department_id.

CTE QualifiedDepartments присоединяет информацию о названии отдела из таблицы departments, а после вычисляет процент сотрудников с зарплатой выше $100,000 в каждом отделе и фильтрует отделы, где этот процент больше 10.

Выборка данных выводит данные о квалифицированных отделах, упорядоченные по убыванию общего числа сотрудников и ограничивает вывод тремя отделами с наибольшим числом сотрудников.

Какой вопрос по SQL на ваших собеседованиях когда-либо вводил вас в заблуждение? Было бы интересно узнать в ваших комментариях, если вопрос будет интересным — добавим его во вторую часть статьи.

Больше практических навыков по SQL и не только вы можете получить в рамках практических онлайн-курсов по аналитике от экспертов отрасли.

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


  1. FinnParnish
    10.05.2024 11:40
    #26809043

    Сохранил. Когда-нибудь мне это обязательно пригодится..


    1. Akina
      10.05.2024 11:40
      #26814777
      +8

      Поверь на слово - не пригодится. То, что тут написано - написано человеком, ну очень плохо понимающим, что и о чём он пишет. И даже написано всё это не потому, что он что-то знает или хотя бы думает, что знает, а потому, что кто-то (например, начальник или преподаватель) потребовал создать публикацию, и при этом вариант "отказаться" в принципе не возможен.


  1. Ivan22
    10.05.2024 11:40
    #26809093
    +8

    четыре частых вопроса по SQL джуну-аналитику

    Как оптимизировать SQL запросы?

    Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов. Ему это и не надо. И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.


    1. miksoft
      10.05.2024 11:40
      #26809455

      Никто никогда и нигде не спрашивает у джуна! аналитика!! про оптимизицию запросов.

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

      И даже матерые лиды аналитики сами ничего не оптимизируют (да их никто к оптимизации и не подпускает), это задача дата-инженеров.

      Точной границы нет. Если могут, то оптимизируют. Например, лучший аналитик в нашей команде очень хорошо умеет оптимизировать. И у нас это важный скилл, так как иначе результата запроса для анализа можно и несколько суток прождать.


      1. Ivan22
        10.05.2024 11:40
        #26809723
        +6

        так может еще и ООП спрашиваеть? а заодно и линукс-администрирование? Вдруг можно будет рассмотреть на соседнюю позицию


        1. nronnie
          10.05.2024 11:40
          #26810099
          +2

          У меня однажды на собеседовании на C# backend разработчика спросили про модель OSI. Душа собеседующего - потемки (а иногда так вообще полный сумрак).


          1. cat-chi
            10.05.2024 11:40
            #26814213
            +1

            Ну может там бэкенд был тесно связан с сетями ;)

            Я тоже как-то спрашивал кандидата по OSI... почему? Потому что он в резюме указал соответствующий опыт. Вот и спросил. А он "плавает", причём вяленько так. И это сразу наводит на мысль, что в реальности не всё так красиво, как в резюме.


    1. NechkaP
      10.05.2024 11:40
      #26813869

      Меня спрашивали в двух разных местах на мидла+, и на одном месте работы сразу после устройства призвали послушать внутренний курс по основам оптимизации запросов, так что я ничему не удивлюсь (что где-то и от джуна ждут)


  1. qelael
    10.05.2024 11:40
    #26809179
    +3

    Ощущение, что нейросеть писала. Во второй задаче вообще зачем добавлена группировка по дням? Достаточно сделать group by по клиенту из orders и sum(cos) с отбором по периоду.


  1. Fell-x27
    10.05.2024 11:40
    #26809349
    +11

    Нормализация - это оптимизация? И что она оптимизирует, кроме хранилища? Мне сейчас приходится тесно работать с БД на сотни миллионов записей с глубокой нормализацией, и единственный способ оптимизировать скорость работы этого монстра - введение денормализованных таблиц, автоматически пополняемых по триггерам из основного хранилища. Иначе альтернатива - 10этажные джоины по нескольку секунд, а то и минут. В то время как денормализованный буфер позволяет делать выборку простым селектом за милисекунды, размазывая сложность по атомарным мгновенным операциям вставки по мере заполнения "основы".

    Это что касается просто приложений. А о какой оптимизации для аналитика-джуна идет речь? С каких пор аналитику нужна оптимизация прям? Ок, допустим, индексы. С каких пор джун имеет права в продакшене, достаточные для создания индексов, например? Не страшно? ДБА как спит по ночам, после выдачи таких привелегий?


    1. Ivan22
      10.05.2024 11:40
      #26809727
      +1

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


      1. Filiber
        10.05.2024 11:40
        #26812873
        +1

        до гендира.

        вдруг вывезет, тогда самим и делать ни хрена не надо :)


    1. redfox0
      10.05.2024 11:40
      #26815147

      Materialized view не подходят?


      1. Fell-x27
        10.05.2024 11:40
        #26816443
        +1

        Матвьюхи не тащат там, где есть требования к рилтайму. Они же обновляются по запросу, то бишь это эдакий крон раз в N времени. При этом, каждое обновление, даже если оно неблокирующее, занимает столько же времени, сколько создание новой вьюхи. Даже больше, если речь идет именно о неблокирующем рефреше. Это совсем грустно в конкретном случае.

        А когда важно, чтобы данные падали мгновенно, тут триггеры без вариантов.


  1. miksoft
    10.05.2024 11:40
    #26809445
    +3

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


  1. Batalmv
    10.05.2024 11:40
    #26809521

    Ну, я бы не сказал, что это задачи для джуна. Честно говоря, если аналитики, которые "живут" в базе и да, они такое решат. Как минимум в части написания запросов. Оптимизация - уже не факт, часто там техника хромает.

    Но остальные банально заваляться на

    Находим клиентов, которые тратят больше среднего по всем клиентам.

    Рекурсивный запрос - это очень редко и специфично, спрашивать такое - это уже перебор, как по мне.


  1. longclaps
    10.05.2024 11:40
    #26809753
    +2

    Вторая задача

    Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года, суммируя затраты за каждый день, если клиент сделал несколько заказов в один день​

    Идиотская постановка задачи. Вот неидиотская:

    Напишите запрос для нахождения клиента с наибольшей общей стоимостью заказов за период с февраля 2019 по май 2024 года

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

    Вы что там курите?


    1. nronnie
      10.05.2024 11:40
      #26810417

      умеет ли соискатель во вложенные запросы

      А вообще, вот, интересно, есть ли ситуация, когда вложенный запрос (исключая клинический случай коррелированного) может быть в каком-либо отношении лучше чем CTE?


      1. Ivan22
        10.05.2024 11:40
        #26810447

        ну тот же постгресс например всегда материализует CTE. А вложенные запросы нет. Так что можно подобрать случай когда это хуже.


        1. ptr128
          10.05.2024 11:40
          #26814337

          PostgreSQL материализует CTE когда явно указано MATERIALIZED. В остальных случаях, теоретически, необходимость материализации определяет оптимизатор. А практически, я не припомню ни одного случая, чтобы оптимизатор материализовал запрос.


          1. Ivan22
            10.05.2024 11:40
            #26816535

            ясно, новшевства появилиь в 12-м постгресе.


            1. ptr128
              10.05.2024 11:40
              #26817265

              Так 12-я - самая старая версия на поддержке. Причем скоро и она с поддержки слетит, с выходом 17-й версии.


  1. nronnie
    10.05.2024 11:40
    #26810089
    +2

    Следовало бы упомянуть, что речь идет (похоже) о Postgres, потому что, например, в MSSQL partitioning делается не так.


    1. Gapon65
      10.05.2024 11:40
      #26810327

      Так, как показано в статье, точно делается в MySQL/MariaDB и их клонах.


      1. Ivan22
        10.05.2024 11:40
        #26811277

        ну по скольку как бы собес на дата-аналитика! то будем считать что это аналитическая субд, пусть будет greenplum


        1. Ballins
          10.05.2024 11:40
          #26817519

          Если речь о gp то какие нафиг индексы? Вообще оптимизация запросов она не про индексы это совсем другой уровень решений. Оптимизация это про потоки данных в запросе и их наиболее быстрое колапсирование и фильтрацию на более ранних этапах. На разделение запроса на более простые и использование временных таблиц и т.д.

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


  1. OlegAxenow
    10.05.2024 11:40
    #26814055

    Держите более компактный вариант решения 3 задачи (план выполнения, думаю, аналогичный).

    SELECT 
	s.department_id,
    department_name,
    total_employees,
    high_earners,
    s.high_earners * 100.0 / s.total_employees AS percent_high_earners
FROM (
	SELECT 
	    department_id,
	    COUNT(*) AS total_employees,
	    SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners
	FROM employees
	GROUP BY department_id
	HAVING (SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) > 10
) s JOIN departments d ON s.department_id = d.department_id
ORDER BY total_employees DESC
LIMIT 3;


  1. rpc1
    10.05.2024 11:40
    #26814109
    +1

    Больше похоже на вопросы на должность data engineer.


    1. MITH146
      10.05.2024 11:40
      #26814135

      Да похоже на компанию, которая ищет себе уровень мидл-сеньор за зарплату джуниора.


  1. ptr128
    10.05.2024 11:40
    #26814355
    +2

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

    Во-вторых, нормализация почти всегда снижает производительность. Её цели совершенно другие. И наоборот, для повышения производительности не редко приходится использовать денормализацию.


  1. LeVoN_CCCP
    10.05.2024 11:40
    #26815031

    >Как оптимизировать SQL запросы

    Ну может самый первый шаг собственно оптимизировать сам запрос, чем обвешивать таблицу индексами?

    >SELECT * FROM customers WHERE name = 'Иван Иванов';

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

    >Использование подзапросов

    После запроса отсюда у меня начал дёргаться глаз и я дальше не читал.

    PS в некоторых случаях вместо простого джойна лучше использовать outer apply / join lateral, они явно укажут плану с каких таблиц надо стартовать, это вопрос к плохой статистике / битым индексам. Обслуживание базы это конечно же исправит, но подстраховаться стоит кодом.


    1. ilyas974
      10.05.2024 11:40
      #26817571

      подскажите правильные ответы)

      1. Звездочка

      2. Не указана схема таблицы

      3. Возможно не указана таблица в where?


  1. alldark
    10.05.2024 11:40
    #26815483
    +2

    Задача на одних и тех же данных должна давать одинаковый ответ по одной и той же выборке. Во второй задаче Order BY + LIMIT приводит к неопределённости в плане выбора клиента, если таковых с наибольшей суммой не один - лучше при словах наибольший/наименьший having sum = min/max sum. Не так быстро, но по смыслу правильнее - а если нужен случайный из подходящей выборки - лучше об этом писать.


  1. speshuric
    10.05.2024 11:40
    #26816487

    Статья очень плохая.

    Какой-то винегрет из неудачных вопросов, не очень корректных ответов и почти случайных (опять же не всегда корректных) фактов непонятно про какую СУБД.

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

    Очень странные "способы оптимизации". Не основанные ни на анализе планов, ни на анализе ресурсов, ни на анализе хотя бы времени выполнения. Ладно, первый способ я еще могу представить на собеседовании аналитика, но второй и третий вызывают у меня вопросы к квалификации интервьюера.
    Про вторую оптимизацию. Просто из любопытства - попробуйте сделать такое заполнение секционированной таблицы из примера и запрос к ней, в которой секционирование будет стабильно лучше любого индекса, приведите пример с временем, io и планом. Если получилось, то скорее всего вы создали неудачный индекс.
    Про нормализацию отметили выше, что обычно она понижает скорость выборки, а не ускоряет.

    В разделе про джойны:

    Фильтр по orders.order_id IS NULL позволяет идентифицировать клиентов без заказов.

    Так делать иногда можно, но по умолчанию лучше использовать exists.

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

    CTE. В postgresql, например, CTE имеют тенденцию к материализации. До версии 12 с этим было вообще плохо, сейчас получше, но всё равно можно выстрелить себе в ногу. Тут уж в статье надо было выбрать - либо удобство аналитика, либо акцент на оптимизации.

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

    HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0

    За 25 лет работы с БД я, конечно, встречал такой код. И даже смогу вспомнить сколько-то раз, когда это не приводило к ухудшению плана запроса. И даже, наверное, вспомню пару раз, когда это решение было хорошим. Но не в этот раз. Не делайте так. Просуммировать выражение с CASE от поля только чтобы убедиться что таких значений нет - очень плохая идея. После этого ситуацию уже индексами, например, исправить нельзя.


    1. Ivan22
      10.05.2024 11:40
      #26816549
      +1

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


      1. speshuric
        10.05.2024 11:40
        #26816769

        Ну этот подход тоже не работает. Вот есть аналитик, у него есть отдельная БД аналитики (sqlite, pg, mysql, vertica, clickhouse или еще что-то), ему надо вытащить данные, он пишет запрос, запускает и внезапно понимает, что прошло уже 20 минут, а сервер думает. Было бы неплохо, если бы этот аналитик умел сам переделать запрос, чтобы запрос данные таки вывел. Каждый запрос к программистам не набегаешься же.
        Но это точно не про джуна (да он бы хоть какой-то запрос написал) и точно не решающий вопрос на собеседовании.


  1. RedWolf
    10.05.2024 11:40
    #26816715

    Относительно задачи 3: какой вопрос - такой ответ

    select d.department_name, count(*) total_employees
from employees e
inner join departments d on d.id = e.department.id
group by d.department_name
having sum(case when salary > 100000) / count(*) > 0.1
order by 2 desc
limit 3

    Насчёт джоинов - это не объединение (union), а соединение. Это более сложная операция, у которой нет полноценных аналогов в диаграммах Венна. Я бы рассматривал inner join как декартово произведение всех записей из двух таблиц с дальнейшим отбрасыванием кортежей, не прошедших фильтрацию по условию соединения. Понятно, что сама СУБД работает по-другому, но именно для объяснения работы inner join-а имхо подойдёт. Тогда вам будет понятно, почему, например, джоиня юзеров с адресами, где на одного юзера может быть несколько адресов, вы будете получать несколько записей для некоторых юзеров.


  1. vichniakov
    10.05.2024 11:40
    #26817491

    пример того, как НЕ надо писать на SQL
    NeverBored AS ( SELECT user_id FROM campaigns GROUP BY user_id HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0)


  1. Oljuka
    10.05.2024 11:40
    #26817497

    "Нужно разработать запрос для идентификации пользователей, которые никогда не испытывали скуку (Bored) в любой рекламной кампании, но испытали восторг (Excited) в последней кампании. " Если эмоции всего 2 - Bored и Excited, то надо же всего навсего найти пользователей, которые всегда испытывали Excited, т.е достаточно такого запроса:
    SELECT [user_id]
    FROM Campaign
    GROUP BY [user_id]
    HAVING SUM(CASE WHEN impression = 'Bored' THEN 1 ELSE 0 END) = 0
    Разве нет?


  1. AutoExp
    10.05.2024 11:40
    #26817523

    Я в тексте не нашёл упоминание для какой именно версии SQL (SQLite, Oracle, Postgresql, MsSql) эти вопросы заданы, а ОСОБЕННО приведены ответы.

    Считаю необходимым обязательно указывать на это ограничение. Хотя и ТындексПрактикум, тоже не страдает


  1. jl_u_c
    10.05.2024 11:40
    #26817531

    Чувство, будто в примере с right join что-то напутали. Не "если какие-то товары не были в заказах", а скорее "если заказ без товара" или "если заказ ссылается на несуществующий товар"