¿Cómo SELECCIONAR los cuatro artículos más nuevos por categoría?

Tengo una base de datos de artículos. Cada elemento se clasifica con un ID de categoría de una tabla de categorías. Estoy intentando crear una página que enumere cada categoría y debajo de cada categoría quiero mostrar los 4 elementos más nuevos de esa categoría.

Por ejemplo:

Suministros de mascotas

img1
img2
img3
img4

Alimentos para mascotas

img1
img2
img3
img4

Sé que podría resolver fácilmente este problema consultando la base de datos para cada categoría de esta manera:

SELECCIONE la identificación DE la categoría

Luego, itere sobre esos datos y consulte la base de datos para cada categoría para obtener los elementos más nuevos:

SELECCIONE la imagen DEL elemento donde categoría_id =: categoría_id
ORDENAR POR date_listed DESC LIMIT 4

Lo que estoy tratando de averiguar es si puedo usar 1 consulta y obtener todos esos datos. Tengo 33 categorías, así que pensé que tal vez ayudaría a reducir la cantidad de llamadas a la base de datos.

¿Alguien sabe si esto es posible? O si 33 llamadas no son gran cosa y debería hacerlo de la manera más fácil.

Sep 18 '09 10:09 justinl

Este es el mayor problema de n por grupo y es una pregunta SQL muy común.

Así es como lo soluciono con uniones externas:

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

Supongo que la clave principal de la itemtabla es item_idy que es una pseudoclave que aumenta monótonamente. Es decir, un valor mayor en item_idcorresponde a una fila más nueva en item.

Así es como funciona: para cada artículo, hay otros artículos que son más nuevos. Por ejemplo, hay tres elementos más nuevos que el cuarto elemento más nuevo. No hay artículos más nuevos que el artículo más nuevo. Entonces queremos comparar cada elemento ( i1) con el conjunto de elementos ( i2) que son más nuevos y tienen la misma categoría que i1. Si el número de esos artículos más nuevos es inferior a cuatro, i1es uno de los que incluimos. De lo contrario, no lo incluyas.

Lo bueno de esta solución es que funciona sin importar cuántas categorías tengas y continúa funcionando si cambias las categorías. También funciona incluso si el número de elementos en algunas categorías es inferior a cuatro.

Otra solución que funciona pero se basa en la función de variables de usuario de MySQL:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 3;

MySQL 8.0.3 introdujo soporte para funciones de ventana estándar de SQL. Ahora podemos resolver este tipo de problemas de la misma manera que lo hacen otros RDBMS:

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

Sep 18 '2009 06:09 Bill Karwin

Esta solución es una adaptación de otra solución SO . Gracias RageZ por localizar esta pregunta relacionada/similar.

NOTA

Esta solución parece satisfactoria para el caso de uso de Justin. Dependiendo de su caso de uso, es posible que desee consultar las soluciones de Bill Karwin o David Andres en esta publicación. ¡La solución de Bill tiene mi voto! Vea por qué, ya que puse ambas consultas una al lado de la otra ;-)

El beneficio de mi solución es que devuelve un registro por categoría_id (la información de la tabla de elementos está "acumulada"). El principal inconveniente de mi solución es su falta de legibilidad y su creciente complejidad a medida que aumenta el número de filas deseadas (por ejemplo, tener 6 filas por categoría en lugar de 6). También puede ser un poco más lento a medida que crece el número de filas en la tabla de elementos. (De todos modos, todas las soluciones funcionarán mejor con una cantidad menor de filas elegibles en la tabla de elementos y, por lo tanto, es recomendable eliminar o mover periódicamente los elementos más antiguos y/o introducir una marca para ayudar a SQL a filtrar las filas con anticipación)

Primer intento (¡¡¡no funcionó!!!)...

El problema con este enfoque era que la subconsulta produciría [con razón pero mal para nosotros] muchas filas, basadas en los productos cartesianos definidos por las autouniones...

SELECT id, CategoryName(?), tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  here_some_addtional l criteria if needed
ORDER BY id ASC;

Segundo intento. (¡funciona bien!)

Se agregó una cláusula WHERE para la subconsulta, lo que obliga a que la fecha indicada sea la última, la segunda más reciente, la tercera más reciente, etc. para i1, i2, i3, etc. respectivamente (y también permite los casos nulos cuando hay menos de 4 elementos para una identificación de categoría determinada). También se agregaron cláusulas de filtro no relacionadas para evitar mostrar entradas que están "vendidas" o entradas que no tienen una imagen (requisitos agregados)

Esta lógica supone que no hay valores de fecha duplicados en la lista (para un ID de categoría determinado). De lo contrario, estos casos crearían filas duplicadas. Efectivamente, este uso de la fecha indicada es el de una clave primaria incrementada monótonamente como se define/requiere en la solución de Bill.

SELECT id, CategoryName, tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4, i4.date_listed
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed AND i2.sold = FALSE AND i2.image IS NOT NULL
          AND i1.sold = FALSE AND i1.image IS NOT NULL
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed AND i3.sold = FALSE AND i3.image IS NOT NULL
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed AND i4.sold = FALSE AND i4.image IS NOT NULL
    WHERE NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i1.date_listed)
      AND (i2.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i2.date_listed AND date_listed <> i1.date_listed)))
      AND (i3.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i3.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed)))
      AND (i4.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i4.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed AND date_listed <> i3.date_listed)))
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  --
ORDER BY id ASC;

Ahora... compare lo siguiente donde introduzco una clave item_id y uso la solución de Bill para proporcionar la lista de estos a la consulta "externa". Puedes ver por qué el enfoque de Bill es mejor...

SELECT id, CategoryName, image, date_listed, item_id
FROM item I
LEFT OUTER JOIN category C ON C.id = I.category_id
WHERE I.item_id IN 
(
SELECT i1.item_id
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id
      AND i1.sold = 'N' AND i2.sold = 'N'
      AND i1.image <> '' AND i2.image <> ''
      )
GROUP BY i1.item_id
HAVING COUNT(*) < 4
)
ORDER BY category_id, item_id DESC

Sep 18 '2009 04:09 mjv

En otras bases de datos puedes hacer esto usando la ROW_NUMBERfunción.

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        ROW_NUMBER() OVER (PARTITION BY category_id
                           ORDER BY date_listed DESC) AS rn
    FROM item
) AS T1
WHERE rn <= 4

Desafortunadamente MySQL no soporta la ROW_NUMBERfunción, pero puedes emularla usando variables:

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        @rn := IF(@prev = category_id, @rn + 1, 1) AS rn,
        @prev := category_id
    FROM item
    JOIN (SELECT @prev := NULL, @rn = 0) AS vars
    ORDER BY category_id, date_listed DESC
) AS T1
WHERE rn <= 4

Véalo funcionando en línea: sqlfiddle

Funciona de la siguiente manera:

Inicialmente, @prev se establece en NULL y @rn se establece en 0.
Para cada fila que vemos, verificamos si el Category_id es el mismo que el de la fila anterior.
- En caso afirmativo, incremente el número de fila.
- De lo contrario, comience una nueva categoría y restablezca el número de fila a 1.
Cuando se completa la subconsulta, el paso final es filtrar para que solo se conserven las filas con un número de fila menor o igual a 4.

Aug 24 '2012 23:08 Mark Byers