sql - una - relacion uno a varios ejemplos

Unión SQL: selección de los últimos registros en una relación uno a varios

Question

Supongamos que tengo una tabla de clientes y una tabla de compras. Cada compra pertenece a un cliente. Quiero obtener una lista de todos los clientes junto con su última compra en una declaración SELECT. cual es la mejor practica? ¿Algún consejo sobre la construcción de índices?

Por favor, use estos nombres de tabla / columna en su respuesta:

• cliente: id, nombre
• compra: id, customer_id, item_id, fecha

Y en situaciones más complicadas, ¿sería beneficioso (en términos de rendimiento) desnormalizar la base de datos al colocar la última compra en la tabla de clientes?

Si se garantiza que la identificación (de compra) esté ordenada por fecha, ¿se pueden simplificar las declaraciones utilizando algo como LIMIT 1 ?

Answer 1

Encontré este hilo como una solución a mi problema.

Pero cuando los probé el rendimiento fue bajo. Bellow es mi sugerencia para un mejor rendimiento.

With MaxDates as (
SELECT  customer_id,
                MAX(date) MaxDate
        FROM    purchase
        GROUP BY customer_id
)

SELECT  c.*, M.*
FROM    customer c INNER JOIN
        MaxDates as M ON c.id = M.customer_id 

Esperamos que esto sea útil.

Answer 2

Este es un ejemplo del greatest-n-per-group problema greatest-n-per-group que ha aparecido regularmente en .

Así es como generalmente recomiendo resolverlo:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
WHERE p2.id IS NULL;

Explicación: dada una fila p1 , no debe haber una fila p2 con el mismo cliente y una fecha posterior (o en el caso de vínculos, una id posterior). Cuando encontramos que eso es cierto, entonces p1 es la compra más reciente para ese cliente.

Con respecto a los índices, crearía un índice compuesto en la purchase través de las columnas ( id . De customer_id , date , id ). Eso puede permitir que la unión externa se realice utilizando un índice de cobertura. Asegúrese de realizar pruebas en su plataforma, ya que la optimización depende de la implementación. Utilice las características de su RDBMS para analizar el plan de optimización. Ej. EXPLAIN en MySQL.

Algunas personas usan subconsultas en lugar de la solución que muestro anteriormente, pero creo que mi solución hace que sea más fácil resolver los vínculos.

Answer 3

Intenta esto, te ayudará.

He usado esto en mi proyecto.

SELECT 
*
from
customer c
OUTER APPLY(SELECT top 1 * FROM purchase pi 
WHERE pi.Id = p.Id order by pi.Id desc) AS [LastPurchasePrice]

Answer 4

No has especificado la base de datos. Si es una que permite funciones analíticas, puede ser más rápido usar este enfoque que el de GROUP BY (definitivamente más rápido en Oracle, probablemente más rápido en las últimas ediciones de SQL Server, no conozca otras).

La sintaxis en SQL Server sería:

SELECT c.*, p.*
FROM customer c INNER JOIN 
     (SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
             FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1

Answer 5

Otro enfoque sería utilizar una condición NOT EXISTS en su condición de unión para probar compras posteriores:

SELECT *
FROM customer c
LEFT JOIN purchase p ON (
       c.id = p.customer_id
   AND NOT EXISTS (
     SELECT 1 FROM purchase p1
     WHERE p1.customer_id = c.id
     AND p1.id > p.id
   )
)

Answer 6

Por favor, intente esto,

SELECT 
c.Id,
c.name,
(SELECT pi.price FROM purchase pi WHERE pi.Id = MAX(p.Id)) AS [LastPurchasePrice]
FROM customer c INNER JOIN purchase p 
ON c.Id = p.customerId 
GROUP BY c.Id,c.name;

Answer 7

Probado en SQLite:

SELECT c.*, p.*, max(p.date)
FROM customer c
LEFT OUTER JOIN purchase p
ON c.id = p.customer_id
GROUP BY c.id

La función de agregado max() se asegurará de que se seleccione la última compra de cada grupo (pero se supone que la columna de fecha está en un formato en el que max () proporciona la última, lo que normalmente es el caso). Si desea gestionar las compras con la misma fecha, puede utilizar max(p.date, p.id) .

En términos de índices, usaría un índice en la compra con (Id. De cliente, fecha, [cualquier otra columna de compra que desee devolver en su selección)).

LEFT OUTER JOIN (a diferencia de INNER JOIN ) se asegurará de que también se incluyan los clientes que nunca han realizado una compra.

Answer 8

Si está usando PostgreSQL, puede usar DISTINCT ON para encontrar la primera fila en un grupo.

SELECT customer.*, purchase.*
FROM customer
JOIN (
   SELECT DISTINCT ON (customer_id) *
   FROM purchase
   ORDER BY customer_id, date DESC
) purchase ON purchase.customer_id = customer.id

PostgreSQL Docs - Distinct On

Tenga en cuenta que los campos DISTINCT ON - aquí customer_id - deben coincidir con el (los) campo (s) más a la izquierda en la cláusula ORDER BY .

Advertencia: Esta es una cláusula no estándar.

Answer 9

También puedes intentar hacer esto usando una selección secundaria

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

La selección debe unirse a todos los clientes y su última fecha de compra.