UNION & ORDER две таблицы внутри общего выражения таблицы

У меня есть CTE внутри хранимой процедуры SQL, которая UNIONзначения из двух баз данных - это номера клиентов и дата последнего заказа этого клиента.

Вот оригинальный SQL -

;WITH CTE_last_order_date AS
(
SELECT c1.customer ,MAX(s2.dt_created) AS last_order_date
FROM customers c1 WITH (NOLOCK)

LEFT JOIN archive_orders s2 WITH (NOLOCK)
ON c1.customer = s2.customer

GROUP BY c1.customer

UNION ALL

SELECT c1.customer ,MAX(s1.dt_created) AS last_order_date
FROM customers c1 WITH (NOLOCK)

LEFT JOIN orders s1 WITH (NOLOCK)
ON c1.customer = s1.customer

GROUP BY c1.customer
)

Пример результатов:

customer,    last_order_date
CF122595,    2011-11-15 15:30:22.000
CF122595,    2016-08-15 10:01:51.230

(2 row(s) affected)

Это, очевидно, не относится к UNION Правило различных записей, потому что значения даты не совпадают, то есть SQL возвращал максимальное значение из обеих таблиц (т. е. окончательный набор записей не был отличным)

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

;WITH CTE_last_order_date AS
(
SELECT max(last_order_date) as 'last_order_date', customer
FROM (
SELECT distinct cust.customer, max(s2.dt_created) AS last_order_date, '2' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN archive_orders s2 WITH (NOLOCK)
ON c1.customer = s2.customer

GROUP BY c1.customer

UNION 

SELECT distinct c1.customer, max(sord.dt_created) AS last_order_date, '1' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN orders s1 WITH (NOLOCK)
ON cust.customer = sord.customer

GROUP BY
   c1.customer
   ) AS t
GROUP  BY customer
ORDER  BY MIN('group'), customer
)

Пример результатов:

customer,    last_order_date
CF122595,    2016-08-15 10:01:51.230

(1 row(s) affected)

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

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Все помощь или идеи приветствуются.

4 ответа

Решение

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

SELECT c1.customer ,MAX(s2.dt_created) AS last_order_date
FROM customers c1
INNER JOIN (select customer, dt_created from archive_orders
union all select customer, dt_created from orders) s2
ON c1.customer = s2.customer
GROUP BY c1.customer

Помните, что в SQL ваша задача - сообщить системе, чего вы хотите, а не какие шаги / процедуры выполнить, чтобы получить эти результаты. Вышеприведенное, логически, описывает то, что мы хотим - мы хотим, чтобы дата последнего заказа от заказов каждого клиента, и нас не волнует, был ли это заархивированный заказ или неархивированный.

Поскольку мы собираемся сократить информацию о заказе до одной строки (для каждого клиента) во время GROUP BY поведение в любом случае, нам также не нужно UNION чтобы удалить дубликаты, поэтому я перешел на UNION ALL,

(Признаюсь, я не мог реально увидеть, что ORDER BY в этот момент предполагалось добавить к миксу, поэтому я не пытался включить его сюда. Если это входит в CTE, то подумайте о том, что CTE, как и таблицы и представления, не имеют внутреннего порядка. Единственный ORDER BY Предложение, которое влияет на порядок строк результатов, применяется к внешнему / последнему SELECT)


дающий orders приоритет над archived_orders:

;With CTE1 as (
    SELECT c1.customer,group,MAX(s2.dt_created) as MaxInGroup
    FROM customers c1
    INNER JOIN (select customer, dt_created,2 as group from archive_orders
    union all select customer, dt_created,1 from orders) s2
    ON c1.customer = s2.customer
    GROUP BY c1.customer,group
), CTE2 as (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY customer ORDER BY group) as rn
    from CTE2
)
select * from CTE2 where rn = 1

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

Как насчет:

;WITH CTE_last_order_date AS
(
   SELECT c1.customer ,s2.dt_created AS last_order_date, '2' AS 'group'
   FROM customers c1 WITH (NOLOCK)
   LEFT JOIN archive_orders s2 WITH (NOLOCK) ON c1.customer = s2.customer

   UNION ALL

   SELECT c1.customer ,s1.dt_created AS last_order_date, '1' AS 'group'
   FROM customers c1 WITH (NOLOCK)
   LEFT JOIN orders s1 WITH (NOLOCK) ON c1.customer = s1.customer

)
SELECT customer, MAX(last_order_date)
FROM CTE_last_order_date
GROUP BY customer 
ORDER BY MIN('group'), customer

Альтернативным подходом может быть получение клиента только из архивной таблицы, в которой у нас нет текущей. Что-то вроде:

WITH CurrentLastOrders(customer, last_order_date) AS    -- Get current last orders
(
    SELECT o.customer, max(o.dt_created) AS last_order_date
    FROM orders s WITH (NOLOCK) ON c.customer = o.customer
    GROUP BY o.customer
),
ArchiveLastOrders(customer, last_order_date) AS -- Get archived last orders where customer does not have a current order
(
    SELECT o.customer, max(o.dt_created) AS last_order_date
    FROM archive_orders o WITH (NOLOCK)
    WHERE NOT EXISTS ( SELECT *
                        FROM CurrentLastOrders lo
                        WHERE o.customer = lo.customer)
    GROUP BY o.customer
),
AllLastOrders(customer, last_order_date) AS -- All customers with orders
(
    SELECT customer, last_order_date
    FROM CurrentLastOrders
    UNION ALL
    SELECT customer, last_order_date
    FROM ArchiveLastOrders
)
AllLastOrdersPlusCustomersWithNoOrders(customer, last_order_date) AS    -- All customerswith latest order if they have one
(
    SELECT customer, last_order_date
    FROM AllLastOrders
    UNION ALL
    SELECT customer, null
    FROM customers c WITH (NOLOCK)
    WHERE NOT EXISTS ( SELECT *
                        FROM AllLastOrders lo
                        WHERE c.customer = lo.customer)
)

Если объединить все возможные строки вместе, а затем вычислить row_number, разделить на клиентов и упорядочить по "group", а затем по убыванию last_order_date, то вы можете выбрать все строки =1, чтобы получить "top 1" для каждого клиента

;WITH CTE_last_order_date AS
(
SELECT max(last_order_date) as 'last_order_date', customer
FROM (
SELECT distinct cust.customer, max(s2.dt_created) AS last_order_date, '2' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN archive_orders s2 WITH (NOLOCK)
ON c1.customer = s2.customer

GROUP BY c1.customer

UNION 

SELECT distinct c1.customer, max(sord.dt_created) AS last_order_date, '1' AS 'group'
FROM customers c1 WITH (NOLOCK)

LEFT JOIN orders s1 WITH (NOLOCK)
ON cust.customer = sord.customer

GROUP BY
   c1.customer
   ) AS t
GROUP  BY customer

)
,   --row_number below is 'per customer' and can be used to make rn=1 the top 1 for each customerid
ROWN AS (SELECT Customer,last_order_date,[group], row_number() OVER(partition by customer order by [group] ASC, sord.dt_created DESC) AS RN)
SELECT * FROM Rown WHERE Rown.rn = 1
Другие вопросы по тегам