Когда вы отказываетесь от заданных операций в SQL и переходите к процедурным?

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

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

Set operations are not as expressive as procedural operations

Возможно, больше похоже на: «Операции Set не так знакомы разработчику, как процедурные операции, для процедурных языков» ;-)

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

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

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

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

Для одного покупателя

1. Получите список всех заказанных продуктов (с названием продукта)
2. Получите год первой покупки
3. Получить даты последних трех покупок
4. Получить комментарий к последнему заказу
5. Получите сумму покупок товаров за последние 12 месяцев

Ваша процедура - это шаги 1–5, и SQL возвращает вам данные.

Для меня это похоже на проект хранилища данных. Если вам нужны такие вещи, как «три самых последних события» и «сумма чего-то за последние 12 месяцев», сохраните их, то есть денормализуйте.

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

Одна из ловушек, в которую попадают люди, - это думать, что весь отчет должен быть создан с помощью одного SQL-запроса. Не правда! Как отмечает Тони Эндрюс, большинство отчетов не помещаются в прямоугольник. Существует множество сводок, сводок, особых случаев и т. д., Поэтому и проще, и эффективнее получать части отчета в отдельных запросах. Точно так же на процедурном языке вы не стали бы пытаться выполнять все свои вычисления в одной строке кода или даже в одной функции (надеюсь).

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

Получить список все заказанные продукты (с названием продукта), даты последних трех покупок и комментарий к последнему заказу просто:

SELECT o.*, l.*, p.*
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
ORDER BY o.order_date;

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

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

SELECT YEAR(MIN(o.order_date)) FROM Orders o WHERE o.customer_id = ?;

Сумма покупок товаров за последние 12 месяцев лучше всего рассчитывать отдельным запросом:

SELECT SUM(l.quantity * p.price)
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
 AND o.order_date > CURDATE() - INTERVAL 1 YEAR;

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

SELECT o1.order_date
FROM Orders o1
  LEFT OUTER JOIN Orders o2 
  ON (o1.customer_id = o2.customer_id AND (o1.order_date < o2.order_date 
      OR (o1.order_date = o2.order_date AND o1.order_id < o2.order_id)))
WHERE o1.customer_id = ?
GROUP BY o1.order_id
HAVING COUNT(*) <= 3;

Если вы можете использовать немного специфичных для поставщика функций SQL, вы можете использовать Microsoft / Sybase TOPп или MySQL / PostgreSQL LIMIT:

SELECT TOP 3 order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC;

SELECT order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 3;

Обновлено: Это совершенно новый подход к решению, без использования временных таблиц или странных подподподзапросов. Однако он будет работать ТОЛЬКО с SQL 2005 или новее, так как он использует новую в этой версии команду «pivot».

Основная проблема - это желаемый поворот из набора строк (в данных) в столбцы в выходных данных. Обдумывая этот вопрос, я вспомнил, что в SQL Server теперь есть оператор «поворота», который справляется с этим.

Это работает в SQL 2005 Только, используя образец данных Northwind.

-- This could be a parameter to a stored procedure
-- I picked this one because he has products that he ordered 4 or more times
declare @customerId nchar(5)
set @customerId = 'ERNSH'

select c.CustomerID, p.ProductName, products_ordered_by_cust.FirstOrderYear,
    latest_order_dates_pivot.LatestOrder1 as LatestOrderDate,
    latest_order_dates_pivot.LatestOrder2 as SecondLatestOrderDate,
    latest_order_dates_pivot.LatestOrder3 as ThirdLatestOrderDate,
    'If I had a comment field it would go here' as LatestOrderComment,
    isnull(last_year_revenue_sum.ItemGrandTotal, 0) as LastYearIncome
from
    -- Find all products ordered by customer, along with first year product was ordered
    (
        select c.CustomerID, od.ProductID,
            datepart(year, min(o.OrderDate)) as FirstOrderYear
        from Customers c
            join Orders o on o.CustomerID = c.CustomerID
            join [Order Details] od on od.OrderID = o.OrderID
        group by c.CustomerID, od.ProductID
    ) products_ordered_by_cust
    -- Find the grand total for product purchased within last year - note fudged date below (Northwind)
    join (
        select o.CustomerID, od.ProductID, 
            sum(cast(round((od.UnitPrice * od.Quantity) - ((od.UnitPrice * od.Quantity) * od.Discount), 2) as money)) as ItemGrandTotal
        from
            Orders o
            join [Order Details] od on od.OrderID = o.OrderID
        -- The Northwind database only contains orders from 1998 and earlier, otherwise I would just use getdate()
        where datediff(yy, o.OrderDate, dateadd(year, -10, getdate())) = 0
        group by o.CustomerID, od.ProductID
    ) last_year_revenue_sum on last_year_revenue_sum.CustomerID = products_ordered_by_cust.CustomerID
        and last_year_revenue_sum.ProductID = products_ordered_by_cust.ProductID
    -- THIS is where the magic happens.  I will walk through the individual pieces for you
    join (
        select CustomerID, ProductID,
            max([1]) as LatestOrder1,
            max([2]) as LatestOrder2,
            max([3]) as LatestOrder3
        from
        (
            -- For all orders matching the customer and product, assign them a row number based on the order date, descending
            -- So, the most recent is row # 1, next is row # 2, etc.
            select o.CustomerID, od.ProductID, o.OrderID, o.OrderDate,
                row_number() over (partition by o.CustomerID, od.ProductID order by o.OrderDate desc) as RowNumber
            from Orders o join [Order Details] od on o.OrderID = od.OrderID
        ) src
        -- Now, produce a pivot table that contains the first three row #s from our result table,
        -- pivoted into columns by customer and product
        pivot
        (
            max(OrderDate)
            for RowNumber in ([1], [2], [3])
        ) as pvt
        group by CustomerID, ProductID
    ) latest_order_dates_pivot on products_ordered_by_cust.CustomerID = latest_order_dates_pivot.CustomerID
        and products_ordered_by_cust.ProductID = latest_order_dates_pivot.ProductID
    -- Finally, join back to our other tables to get more details
    join Customers c on c.CustomerID = products_ordered_by_cust.CustomerID
    join Orders o on o.CustomerID = products_ordered_by_cust.CustomerID and o.OrderDate = latest_order_dates_pivot.LatestOrder1
    join [Order Details] od on od.OrderID = o.OrderID and od.ProductID = products_ordered_by_cust.ProductID
    join Products p on p.ProductID = products_ordered_by_cust.ProductID
where c.CustomerID = @customerId
order by CustomerID, p.ProductID