Привет Хабр! Меня зовут Татьяна Ошуркова, я системный аналитик, разработчик и автор телеграм-канала IT Talks. Использование искусственного интеллекта в работе системного аналитика и не только можно назвать одной из самой популярных и хайповых тем. Скажу честно, я не придерживаюсь мнения, что искусственный интеллект заменит реальных специалистов, а также сможет решить все наши проблемы и задачи.

Мне очень нравится цитата: «ИИ приумножает твои компетенции». Его использование должно быть дополнительным инструментом и возможностью. Тем, что поможет тебе в работе, а не сделает её за тебя.

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

А ещё у меня в боте можно скачать бесплатный гайд, где ты найдешь шаблоны пяти основных диаграмм на PlantUML в практических кейсах с описанием.

Постановка задачи

Рассмотрим написание выборки для работы с заказами клиента в некой абстрактной системе. В заказах клиентов есть продукты, у каждого продукта может быть цена, количество и признак участия в акции. Акции бывают двух видов: 2=1 или 3=1 – то есть покупка двух или трёх товаров по цене одного. Необходимо написать выборку, где будут заказы клиента и их итоговая сумма. Но нужно учитывать, что в заказах могут быть акционные товары, их стоимость нельзя просто просуммировать.

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

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

• Вместо этого к сумме обычных продуктов добавляется максимальная сумма среди акционных продуктов этого заказа в зависимости от акции 2=1 или 3=1.

• Цены продуктов могут храниться в двух таблицах: основная цена и акционная цена. Если акционная цена отсутствует, берётся основная.

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

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

План решения

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

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

Одной из основных проблем использования ИИ я бы назвала неумение анализировать и искать связи, выполнять аналитическую работу. Именно поэтому сразу задачу целиком задачу решить не удалось. Далее я разбила процесс на следующие шаги:

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

Далее доработать выборку с использованием ИИ:

• Сформировать итоговую сумму для неакционных продуктов.

• Найти максимальную цену среди акционных продуктов.

• Сложить полученные суммы.

• Оптимизировать запрос, объединив повторяющиеся части.

-- Заказы и товары
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT
);

-- Основные цены
CREATE TABLE product_prices (
    product_id INT,
    price DECIMAL(10, 2)
);

-- Акционные цены
CREATE TABLE promo_prices (
    product_id INT,
    promo_price DECIMAL(10, 2)
);

-- Основной источник информации об акции
CREATE TABLE product_promotions (
    product_id INT,
    promo_type VARCHAR(10)  -- Например, '2=1', '3=1'
);

-- Альтернативный источник информации об акции (обновления)
CREATE TABLE product_updates (
    product_id INT,
    promo_type VARCHAR(10)
);

-- Заказы и товары
INSERT INTO order_items VALUES
(1, 101, 1), (1, 102, 2), (1, 103, 1), (1, 104, 2), (1, 105, 3),
(2, 201, 2), (2, 202, 1), (2, 203, 3), (2, 204, 1), (2, 205, 2),
(3, 301, 2), (3, 302, 1), (3, 303, 1), (3, 304, 2), (3, 305, 2);

-- Основные цены товаров
INSERT INTO product_prices VALUES
(101, 100.00), (102, 200.00), (103, 300.00), (104, 250.00), (105, 150.00),
(201, 120.00), (202, 180.00), (203, 260.00), (204, 230.00), (205, 140.00),
(301, 160.00), (302, 180.00), (303, 300.00), (304, 350.00), (305, 200.00);

-- Акционные цены (есть не у всех)
INSERT INTO promo_prices VALUES
(103, 280.00), (104, 220.00),
(203, 240.00),
(304, 320.00);

-- Признаки акций из product_promotions (только 2=1 для заказов 1 и 3)
INSERT INTO product_promotions VALUES
(103, '2=1'),
(104, '2=1'),
(303, '2=1');

-- Признаки акций из product_updates (только 3=1 в заказе 2 и 2=1 в заказе 3)
INSERT INTO product_updates VALUES
(202, '3=1'),
(203, '3=1'),
(204, '3=1'),
(304, '2=1');

Реализация

Шаг 1. Написание базовой выборки

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

Важно, что особенность задачи заключалась в том, что информация о цене товара могла храниться в двух разных таблицах: основной (product_prices) и акционной (promo_prices). Если для товара есть акционная цена, именно она должна использоваться при расчёте, а не основная. Для этого используется функция COALESCE, которая возвращает первое ненулевое значение – в данном случае сначала promo_price, если она есть, а если нет – обычную price.

Аналогичная ситуация и с признаком участия товара в акции. Он может быть записан либо в основной таблице product_promotions, либо в таблице обновлений product_updates, что связано с архитектурными особенностями базы данных. Чтобы учесть это, также применяется функция COALESCE, указывая приоритет основного источника (product_promotions), и при его отсутствии – дополнительного (product_updates).

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

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

WITH product_data AS (
  SELECT
    oi.order_id,
    oi.product_id,
    COALESCE(pp.promo_price, pr.price) AS effective_price,
    COALESCE(promo.promo_type, upd.promo_type) AS promo_type,
    oi.quantity
  FROM order_items oi
  JOIN product_prices pr ON oi.product_id = pr.product_id
  LEFT JOIN promo_prices pp ON oi.product_id = pp.product_id
  LEFT JOIN product_promotions promo ON oi.product_id = promo.product_id
  LEFT JOIN product_updates upd ON oi.product_id = upd.product_id
)
SELECT
  order_id,
  product_id,
  effective_price,
  promo_type,
  quantity
FROM product_data
ORDER BY order_id, product_id;

Шаг 2. Расчёт суммы заказа без учёта акционных товаров

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

Рассмотрим вариант запроса, который можно использовать для данного шага:

У меня есть таблица product_data с полями: order_id, product_id, effective_price, promo_type, quantity. Мне нужно для каждого заказа посчитать сумму товаров без акции, то есть тех, у которых promo_type IS NULL. Верни SQL-запрос, который возвращает order_id и сумму обычных товаров, с учётом количества (quantity * effective_price). Итоговая таблица должна содержать только одну строку на заказ.

Рассмотрим полученный новый запрос и логику его работы. Согласно правилам бизнес-логики, если товар участвует в акции «2=1» или «3=1», то он не должен входить в основную сумму заказа обычным способом (т.е. по формуле цена × количество). Вместо этого будет действовать особое правило, которое мы реализуем позже (на шаге 3), а здесь мы должны исключить такие товары из расчёта.

Технически это реализовано довольно просто: строки фильтруются по условию promo_type IS NULL, что означает, что товар не участвует в акции. При этом мы используем ранее построенный CTE (product_data), в котором уже содержится нужная информация по каждому товару: ID заказа, цена, признак акции и количество.

Для каждой строки, удовлетворяющей этому фильтру, мы перемножаем effective_price на quantity – таким образом, получаем итоговую стоимость обычного товара в заказе. Затем группируем эти значения по order_id, чтобы получить итоговую сумму по каждому заказу.

Важно отметить, что на этом шаге рассчитывается именно базовая сумма заказа – без учёта акционных позиций. Это своего рода «чистая» сумма, к которой мы позже прибавим дополнительную стоимость, если в заказе есть акционные товары.

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

Таким образом, второй шаг позволил отделить обычные товары от акционных и обеспечить корректность расчёта именно той части суммы, которая подчиняется стандартной логике «цена × количество».

WITH product_data AS (
  SELECT
    oi.order_id,
    oi.product_id,
    COALESCE(pp.promo_price, pr.price) AS effective_price,
    COALESCE(promo.promo_type, upd.promo_type) AS promo_type,
    oi.quantity
  FROM order_items oi
  JOIN product_prices pr ON oi.product_id = pr.product_id
  LEFT JOIN promo_prices pp ON oi.product_id = pp.product_id
  LEFT JOIN product_promotions promo ON oi.product_id = promo.product_id
  LEFT JOIN product_updates upd ON oi.product_id = upd.product_id
),
base_sum AS (
  SELECT
    order_id,
    SUM(effective_price * quantity) AS main_sum
  FROM product_data
  WHERE promo_type IS NULL
  GROUP BY order_id
)
SELECT
  order_id,
  main_sum
FROM base_sum
ORDER BY order_id;

Шаг 3. Расчёт максимальной суммы среди акционных товаров

После того как мы отдельно рассчитали сумму товаров в заказе без акции, следующим шагом необходимо определить сумму акционной части заказа. Согласно бизнес-логике, если в заказе есть товары, участвующие в акциях «2=1» или «3=1», их стоимость нельзя учитывать в общей сумме как обычные товары. Вместо этого нужно выделить часть стоимости этих акционных товаров, которая будет оплачена в рамках акции.

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

Этот шаг получилось реализовать с помощью ИИ не с первого раза. Мой изначальный запрос был другой. Я предполагала, что ИИ корректно понимает логику акций. Но на практике не учитывалось количество товара. Рассмотрим запрос к ИИ, который я сформировала как итог по всем, которые использовались на данном шаге:

У меня есть таблица с товарами в заказах: order_id, product_id, price, promo_type, quantity. В некоторых товарах указана акция: «2=1» или «3=1». Нужно для каждого заказа посчитать, сколько нужно заплатить за товары с акцией.
По условиям акции: если указана «2=1», то из каждых двух единиц товара оплачивается только одна, если указана «3=1», то из трёх – тоже одна. Если товаров больше, чем кратно акции, оставшиеся тоже должны быть оплачены.
Надо выбрать те единицы, которые оплачиваются, и посчитать их суммарную стоимость. Нужен запрос, который вернёт order_id и сумму к оплате по акционным товарам.

Задача решается с помощью агрегатной функции MAX() в сочетании с условием, фильтрующим только акционные товары (promo_type IS NOT NULL). Таким образом, из всех товаров в заказе выбираются только акционные, для каждого считается полная стоимость с учётом количества, и затем выбирается максимальное значение.

На этом этапе мы определяем, сколько акционных товаров в каждом заказе нужно оплатить согласно условиям акций «2=1» и «3=1». Для этого в запросе каждую единицу товара разворачивают в отдельную строку – так можно работать с количеством товаров на уровне отдельных единиц.

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

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

WITH product_data AS (
  SELECT
    oi.order_id,
    oi.product_id,
    COALESCE(pp.promo_price, pr.price) AS effective_price,
    COALESCE(promo.promo_type, upd.promo_type) AS promo_type,
    oi.quantity
  FROM order_items oi
  JOIN product_prices pr ON oi.product_id = pr.product_id
  LEFT JOIN promo_prices pp ON oi.product_id = pp.product_id
  LEFT JOIN product_promotions promo ON oi.product_id = promo.product_id
  LEFT JOIN product_updates upd ON oi.product_id = upd.product_id
  WHERE oi.quantity > 0 AND COALESCE(promo.promo_type, upd.promo_type) IS NOT NULL
),

-- Разворачиваем каждую единицу товара в отдельную строку
expanded_units AS (
  SELECT
    order_id,
    promo_type,
    effective_price,
    ROW_NUMBER() OVER (PARTITION BY order_id, promo_type ORDER BY effective_price DESC) AS rn
  FROM product_data, 
       generate_series(1, quantity)
),

-- Вычисляем, сколько единиц должны быть оплачены
units_to_pay AS (
  SELECT
    order_id,
    promo_type,
    COUNT(*) AS total_units,
    CASE
      WHEN promo_type = '2=1' THEN FLOOR(COUNT(*) / 2) + MOD(COUNT(*), 2)
      WHEN promo_type = '3=1' THEN FLOOR(COUNT(*) / 3) + MOD(COUNT(*), 3)
      ELSE COUNT(*)
    END AS units_to_pay
  FROM expanded_units
  GROUP BY order_id, promo_type
),

-- Получаем N самых дорогих товаров к оплате
promo_payment AS (
  SELECT
    e.order_id,
    SUM(e.effective_price) AS promo_amount
  FROM expanded_units e
  JOIN (
    SELECT
      order_id,
      promo_type,
      units_to_pay
    FROM units_to_pay
  ) u ON e.order_id = u.order_id AND e.promo_type = u.promo_type
  WHERE e.rn <= u.units_to_pay
  GROUP BY e.order_id
)

SELECT
  order_id,
  promo_amount
FROM promo_payment
ORDER BY order_id;

Шаг 4. Объединение базовой суммы и суммы по акции

После того как мы отдельно рассчитали сумму по товарам без акции на шаге 2 и определили максимальную стоимость среди акционных товаров на шаге 3, нам необходимо объединить полученные результаты в единую выборку для расчета итоговой суммы заказа. Это будет нашим основным результатом.

Согласно расчёту, финальная сумма по заказу должна включать:

• Сумму всех товаров без акции – main_sum.

• Одну наибольшую стоимость среди товаров, участвующих в акции – promo_amount.

Рассмотрим запрос к ИИ на данном шаге:

У меня есть два CTE: base_sum, который содержит order_id и сумму обычных товаров, и promo_payment, который содержит order_id и сумму акционных товаров к оплате. Мне нужно объединить их по order_id и посчитать итоговую сумму заказа как сумму обычных товаров и акционных. Верни полный итоговый запрос.

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

Если в заказе отсутствуют акционные товары, значение суммы по акции будет NULL, и его необходимо заменить на 0 с помощью функции COALESCE.

На техническом уровне мы объединяем два ранее построенных CTE – с базовой суммой (base_sum) и с суммой оплачиваемых акционных товаров (promo_payment) – по полю order_id. Основу составляет base_sum, так как заказ не может существовать без хотя бы одного обычного товара.

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

WITH product_data AS (
  SELECT
    oi.order_id,
    oi.product_id,
    COALESCE(pp.promo_price, pr.price) AS effective_price,
    COALESCE(promo.promo_type, upd.promo_type) AS promo_type,
    oi.quantity
  FROM order_items oi
  JOIN product_prices pr ON oi.product_id = pr.product_id
  LEFT JOIN promo_prices pp ON oi.product_id = pp.product_id
  LEFT JOIN product_promotions promo ON oi.product_id = promo.product_id
  LEFT JOIN product_updates upd ON oi.product_id = upd.product_id
  WHERE oi.quantity > 0
),

-- Считаем сумму по обычным товарам (без акции)
normal_sum AS (
  SELECT
    order_id,
    SUM(effective_price * quantity) AS normal_amount
  FROM product_data
  WHERE promo_type IS NULL
  GROUP BY order_id
),

-- Разворачиваем каждую единицу акционного товара в отдельную строку
expanded_units AS (
  SELECT
    order_id,
    promo_type,
    effective_price,
    ROW_NUMBER() OVER (PARTITION BY order_id, promo_type ORDER BY effective_price DESC) AS rn
  FROM product_data,
       generate_series(1, quantity)
  WHERE promo_type IS NOT NULL
),

-- Считаем сколько единиц нужно оплатить по каждой акции и заказу
units_to_pay AS (
  SELECT
    order_id,
    promo_type,
    COUNT(*) AS total_units,
    CASE
      WHEN promo_type = '2=1' THEN FLOOR(COUNT(*) / 2) + MOD(COUNT(*), 2)
      WHEN promo_type = '3=1' THEN FLOOR(COUNT(*) / 3) + MOD(COUNT(*), 3)
      ELSE COUNT(*)
    END AS units_to_pay
  FROM expanded_units
  GROUP BY order_id, promo_type
),

-- Считаем сумму к оплате за акционные товары: выбираем самые дорогие N единиц
promo_payment AS (
  SELECT
    e.order_id,
    SUM(e.effective_price) AS promo_amount
  FROM expanded_units e
  JOIN units_to_pay u ON e.order_id = u.order_id AND e.promo_type = u.promo_type
  WHERE e.rn <= u.units_to_pay
  GROUP BY e.order_id
)

-- Итог: сумма обычных товаров + сумма акционных товаров
SELECT
  COALESCE(n.order_id, p.order_id) AS order_id,
  COALESCE(n.normal_amount, 0) + COALESCE(p.promo_amount, 0) AS total_order_amount
FROM normal_sum n
FULL OUTER JOIN promo_payment p ON n.order_id = p.order_id
ORDER BY order_id;

Подведем итоги

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

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

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

Для работы с запросами в статье можно использовать сервис https://www.db-fiddle.com/.