Início / coding / Perguntas / 79590635
Accepted
Banone
Asked: 2025-04-24 20:45:18 +0800 CST

Oracle-DB, Unindo 3 Tabelas Limitando a segunda sem usar LATERAL() ou aninhamento

  • 772

Olá a todos, estou com um problema específico. Estou usando o Oracle DB

Para o exemplo, usarei o banco de dados Northwind.

Quero unir as tabelas: Clientes , Pedidos , Detalhes do pedido e Produtos .

Solução simples:

SELECT Customers.customerid, Customers.companyname, 
       Orders.orderid, Orders.orderdate, 
       Products.productname, Products.unitprice
FROM Customers
INNER JOIN Orders
  ON Customers.customerid= Orders.customerid
INNER JOIN Orderdetails 
  ON Orders.orderid = Orderdetails.orderid
INNER JOIN Products 
  ON Orderdetails.productid = Products.productid

Agora, quero limitar a saída aos 3 pedidos mais recentes por cliente, mostrando apenas o produto mais caro do pedido. A saída ficaria mais ou menos assim (Edição: a tabela não está sendo exibida corretamente):

ID do cliente nome da empresa id do pedido data do pedido nome do produto preço unitário
ALFKI Alfreds Futterkiste 11011 1998-04-09 Flotemysost 22
ALFKI Alfreds Futterkiste 10952 1998-03-16 Chucrute Rössle 46
ALFKI Alfreds Futterkiste 10835 15/01/1998 Raclette Courdavault 55
ANATR Ana Trujillo Emparedados e sorvetes 10926 1998-03-04 Mussarela de Giovanni 35
ANATR Ana Trujillo Emparedados e sorvetes 10759 28/11/1997 Mascarpone Fabioli 32
ANATR Ana Trujillo Emparedados e sorvetes 10625 1997-08-08 Camembert Pierrot 34
ANTÔN Antonio Moreno Taqueria 10856 28/01/1998 Mudança 19
... ... ... ... ... ...

Tenho uma solução usando LATERAL()e uma subseleção, mas, especialmente em big data, quero usar isso, LATERAL()tem muitos custos e torna o processo bastante lento. Então, quero ter uma solução SEMLATERAL()

Outra solução que tenho tentado é usar, ROW_NUMBER()que é muito mais rápida:

Select * FROM( 
  SELECT Customers.customerid, Customers.companyname, 
         Orders.orderid, Orders.orderdate, 
         Products.productname, Products.unitprice,
         ROW_NUMBER() OVER (PARTITION BY Customers.customerid
                            ORDER BY o.orderdate desc, p.unitprice desc ) AS rn
        ...
 ) WHERE rn <= 3;

Agora, se o limite fosse 1, não haveria problema. Mas não consigo encontrar uma solução aceitável para um limite >= 2 para os pedidos, como expliquei acima. Tentei uma segunda solução ROW_NUMBER() OVER (PARTIONION BY Orders.orderid ...), mas para que isso funcionasse, precisaria de algum aninhamento. O problema com o aninhamento é que estou prestes a criar algo como um analisador sintático para uma aplicação. E aninhamento em excesso pode se tornar muito complicado e confuso (especialmente para fins de manutenção).

Então eu estava pensando em uma junção, existe uma solução SQL pura no Oracle para:

  • Junte 3 ou mais mesas.
  • Use um Limite >= 2 em uma tabela (B) no meio da Junção que tem várias junções com outra tabela a seguir.
  • Isso não usa LATERAL()
  • Não é necessário aninhamento?

Editar/Adicionado: A maneira que tenho usado LATERAL():

Como este é um banco de dados de exemplo, tive que reestruturar o LATERAL que estava usando. (Para o exemplo do Northwind, tenho que unir produtos em detalhes do pedido em pedidos, no meu banco de dados não preciso da etapa extra com uma tabela entre eles)

SELECT C.customerid, C.companyname, 
       O.orderid, O.orderdate, 
       P.productname, P.unitprice
FROM Customers C
INNER JOIN LATERAL( 
  SELECT Orders.orderid, Orders.orderdate FROM Orders 
  WHERE Orders.customerid = C.customerid 
  ORDER BY orderdate DESC FETCH FIRST 3 ROWS ONLY ) O 
  ON 1=1 
INNER JOIN LATERAL( 
  SELECT Products.unitprice, Products.productname FROM Orderdetails 
  INNER JOIN Products ON Products.productid = Orderdetails.productid 
  WHERE Orderdetails.orderid = O.orderid 
  ORDER BY Products.unitprice DESC FETCH FIRST 1 ROWS ONLY) P on 1=1

Usei ON 1=1porque a instrução on é obrigatória para o Oracle em uma instrução Join, mas já estou unindo dentro do LATERAL().

sql

1 respostas

  1. Best Answer

    Você pode usar uma visualização em linha dentro da FROMcláusula para filtrar as tabelas unidas.

    Algo como:

    SELECT c.customer_id,
       c.company_name, 
       o.order_id,
       o.order_date, 
       odp.product_name,
       odp.unit_price
FROM   Customers c
       INNER JOIN (
         SELECT customer_id,
                order_id,
                order_date,
                ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
         FROM   Orders
       ) o
       ON c.customer_id= o.customer_id AND o.rn <= 3
       INNER JOIN (
         SELECT od.order_id,
                p.product_name,
                p.unit_price,
                ROW_NUMBER() OVER (PARTITION BY od.order_id ORDER BY p.unit_price DESC) AS rn
         FROM   Order_details od
                INNER JOIN Products p
                ON od.product_id = p.product_id
       ) odp
       ON o.order_id = odp.order_id AND odp.rn = 1
ORDER BY customer_id, order_date DESC

    No entanto, você deve ser capaz de usar uma LATERALjunção equivalente:

    SELECT c.customer_id,
       c.company_name, 
       o.order_id,
       o.order_date, 
       odp.product_name,
       odp.unit_price
FROM   Customers c
       CROSS JOIN LATERAL (
         SELECT order_id,
                order_date
         FROM   Orders o
         WHERE  c.customer_id = o.customer_id
         FETCH FIRST 3 ROWS ONLY
       ) o
       CROSS JOIN LATERAL (
         SELECT od.order_id,
                p.product_name,
                p.unit_price
         FROM   Order_details od
                INNER JOIN Products p
                ON od.product_id = p.product_id
         WHERE  o.order_id = od.order_id
         FETCH FIRST ROW ONLY
       ) odp

    violino

    • 6

relate perguntas