1
votes

UNION & ORDER deux tables dans l'expression de table commune

J'ai un CTE dans une procédure stockée SQL qui est UNION des valeurs de deux bases de données - les valeurs sont les numéros de client et la date de la dernière commande de ce client.

Voici le SQL d'origine -

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.

Exemple de résultats:

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

(1 row(s) affected)

Cela n'applique évidemment pas la règle des enregistrements distincts UNION car les valeurs de date ne correspondent pas, ce qui signifie que SQL a renvoyé la valeur maximale des deux tables (c'est-à-dire que le jeu d'enregistrements final n'était pas distinct)

Pour essayer de contourner ce problème, j'ai essayé une autre méthode a emprunté à cette question et mis en œuvre le regroupement:

;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
)

Exemple de résultats:

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

(2 row(s) affected)

Cela avait la particularité (hah) de fonctionner correctement, jusqu'à ce que la règle claque dans la règle qui empêche ORDER BY dans les expressions de table communes, qui sont nécessaires pour choisir le groupe le plus bas (ce qui impliquerait des commandes en direct (groupe 1), dont la date doit prendre e priorité sur l'archive (groupe 2)).

;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
)

Toute aide ou idée appréciée.

sql-server common-table-expression sql-server-2008-r2

2 commentaires

vous devez COMMANDER en dehors du CTE, sauf si vous utilisez SELECT TOP ou si vous avez besoin d'une commande. Vous pouvez COMMANDER en dehors du CTE. Alors, quelle commande avez-vous besoin? Qu'essayez-vous de revenir ici? Pouvez-vous donner des exemples de données et de résultats? Êtes-vous en train de dire que vous voulez le maximum des deux tableaux? si c'est le cas, unissez les deux tables dans une sous-requête, puis prenez le maximum de la sous-requête.

Plus de détails ajoutés, y compris les résultats.

4 Réponses :

2
votes

Plutôt que de regrouper, puis d'unir, puis de regrouper à nouveau, pourquoi ne pas unir les tables de commandes et travailler à partir de là:

;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

Rappelez-vous, en SQL, votre travail consiste à dire au système ce que vous voulez , pas les étapes / procédures à suivre pour obtenir ces résultats . Ce qui précède décrit logiquement ce que nous voulons - nous voulons la dernière date de commande des commandes de chaque client, et nous ne nous soucions pas de savoir s'il s'agissait d'une commande archivée ou non.

Depuis nous allons réduire les informations de commande à une seule ligne (par client) pendant le comportement GROUP BY de toute façon, nous n'avons pas non plus besoin de UNION pour supprimer les doublons, je suis donc passé à UNION ALL .

(J'avoue, je ne pouvais pas vraiment voir ce que le code ORDER BY > était censé ajouter au mix à ce stade, donc je n'ai pas essayé de l'inclure ici. Si cela va dans un CTE, alors réfléchissez au fait que les CTE, tout comme les tables et les vues, n'ont pas de l'ordre inhérent. La seule clause ORDER BY qui affecte l'ordre des lignes de résultats est celle appliquée au SELECT le plus externe / final)

Donner la priorité aux commandes sur archived_orders:

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

4 commentaires

De très bons points à souligner et la preuve qu'il est toujours bon d'avoir un regard neuf sur ces choses. Cela tente de modifier le travail effectué par un tiers sur la base d'un changement de spécification imprévu et j'essayais de renforcer un script qui était déjà (apparemment) trop complexe. J'ai aussi oublié KISS. :RÉ

@Optimaximal - vous voudrez peut-être revenir sur l'acceptation pour le moment. Votre modification a clairement indiqué que toute commande doit avoir la priorité sur une commande archivée et cette requête ne l'est pas encore. J'y travaille.

@Optimaximal - voir la modification en bas avec la priorité également implémentée.

Bien que je vous remercie pour le concept ajouté, je ne pense pas que ce soit nécessaire. La nécessité de donner la priorité aux commandes en direct sur celles archivées résultait de l'utilisation de la méthode de regroupement. Les commandes archivées sont simplement toutes les commandes de plus de 3 ans et elles ne peuvent pas exister dans les deux tables - Si toutes les données sont regroupées, la simple commande MAX () convient. Gardez votre tique! :)

1
votes

Une autre approche pourrait consister à extraire uniquement le client de la table d'archive où nous n'avons pas de table actuelle. Quelque chose comme:

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)
)

0 commentaires

1
votes

Je n'essaierais pas d'imbriquer SQL pour obtenir un ensemble de résultats distinct, c'est la même logique de regroupement par client dans les deux requêtes unifiées. Si vous voulez un ensemble ordonné distinct, vous pouvez le faire en dehors du CTE

Que diriez-vous:

;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

1 commentaires

Le CTE est en cours de création pour contenir les dernières dates de commande afin de les référencer dans le cadre d'une commande INSERT ultérieure dans la procédure stockée, elles doivent donc être pré-triées.

1
votes

si vous unissez toutes les lignes possibles ensemble, puis calculez un row_number, partitionné sur client et commandé sur 'group' puis last_order_date décroissant, vous pouvez alors sélectionner toutes les lignes = 1 pour donner le 'top 1' par client

;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

0 commentaires