sql - tabla - ¿Cómo SELECCIONAR los cuatro elementos más nuevos por categoría?

query recursiva sql

Question

Tengo una base de datos de artículos. Cada elemento está categorizado con una identificación de categoría de una tabla de categorías. Intento crear una página que enumere todas las categorías, y debajo de cada categoría quiero mostrar los 4 elementos más nuevos en 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, así:

SELECT id FROM category

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

SELECT image FROM item where category_id = :category_id ORDER BY date_listed DESC LIMIT 4

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

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

Answer 1

En otras bases de datos puede hacerlo utilizando la función ROW_NUMBER .

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 es compatible con la función ROW_NUMBER , pero puede emularlo mediante 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

sqlfiddle trabajar en línea: sqlfiddle

Funciona de la siguiente manera:

• Intially @prev se establece en NULL y @rn se establece en 0.
• Para cada fila que vemos, verifica si category_id es igual a la fila anterior.
  • Si es así, incremente el número de fila.
  • De lo contrario, inicie una nueva categoría y restablezca el número de fila nuevamente a 1.
• Cuando finaliza la subconsulta, el último paso es filtrar para que solo se conserven las filas con un número de fila inferior o igual a 4.

Answer 2

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 category_id (la información de la tabla de elementos es "acumulada"). El inconveniente principal de mi solución es su falta de legibilidad y su creciente complejidad a medida que crece la cantidad de filas deseadas (por ejemplo, para 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. (Independientemente, todas las soluciones tendrán un mejor rendimiento con un número menor de filas elegibles en la tabla de elementos, por lo que es aconsejable eliminar o mover periódicamente elementos más antiguos y / o introducir un indicador para ayudar a SQL a filtrar las filas anticipadamente)

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

El problema con este enfoque era que la subconsulta produciría muchas filas, con razón, pero mal para nosotros, en función de los productos cartesianos definidos por la auto unión ...

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, forzando a la fecha indicada a ser la última, la segunda más reciente, la tercera más tardía, etc. para i1, i2, i3, etc. respectivamente (y también para los casos nulos cuando hay menos de 4 elementos para una identificación de categoría dada). También se agregaron cláusulas de filtro no relacionadas para evitar que se muestren las entradas que se "venden" o las entradas que no tienen una imagen (requisitos adicionales)

Esta lógica asume que no hay valores listados de fecha duplicados (para un determinado id_categoría). Dichos casos crearían filas duplicadas. Efectivamente, este uso de la fecha indicada es el de una clave primaria monotónicamente incrementada 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 presento 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

Answer 3

Este es el mayor problema n-grupal, y es una pregunta SQL muy común.

Así es como lo resuelvo 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 tabla de item es item_id , y que es una pseudokey creciente monótonamente. Es decir, un valor mayor en item_id corresponde a una fila más nueva en el item .

Así es como funciona: para cada artículo, hay algunos otros artículos más nuevos. Por ejemplo, hay tres elementos más nuevos que el cuarto elemento más nuevo. Hay cero elementos más nuevos que el artículo más nuevo. Por lo tanto, 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 menor a cuatro, i1 es uno de los que incluimos. De lo contrario, no incluirlo.

La belleza de esta solución es que funciona independientemente de la cantidad de categorías que tenga, y continúa funcionando si cambia las categorías. También funciona incluso si el número de elementos en algunas categorías es menor a cuatro.

Otra solución que funciona pero que 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 (@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 presentó 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;

Answer 4

Según la constancia de sus categorías, la siguiente es la ruta más simple

SELECT C.CategoryName, R.Image, R.date_listed
FROM
(
    SELECT CategoryId, Image, date_listed
    FROM 
    (
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = ''Pet Supplies''
      ORDER BY date_listed DESC LIMIT 4
    ) T

    UNION ALL

    SELECT CategoryId, Image, date_listed
    FROM
    (        
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = ''Pet Food''
      ORDER BY date_listed DESC LIMIT 4
    ) T
) RecentItemImages R
INNER JOIN Categories C ON C.CategoryId = R.CategoryId
ORDER BY C.CategoryName, R.Image, R.date_listed

Answer 5

bien después de google la respuesta rápida no sería posible al menos en mysql

este este hilo de referencia

tal vez debería almacenar en caché el resultado de esa consulta si tiene miedo de caerse en el servidor y desea que el código funcione mejor

Answer 6

el siguiente código muestra una forma de hacerlo en un bucle, definitivamente necesita mucha edición, pero espero que ayude.

        declare @RowId int
 declare @CategoryId int
        declare @CategoryName varchar(MAX)

 create table PART (RowId int, CategoryId int, CategoryName varchar)
 create table  NEWESTFOUR(RowId int, CategoryId int, CategoryName varchar, Image image)
        select RowId = ROW_NUMBER(),CategoryId,CategoryName into PART from [Category Table]


        set @PartId = 0
 set @CategoryId = 0 
 while @Part_Id <= --count
 begin
   set @PartId = @PartId + 1
          SELECT @CategoryId = category_id, @CategoryName = category_name from PART where PartId = @Part_Id
          SELECT RowId = @PartId, image,CategoryId = @category_id, CategoryName = @category_name   FROM item into NEWESTFOUR where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

 end
 select * from NEWESTFOUR
 drop table NEWESTFOUR
        drop table PART

Answer 7

no muy bonito, pero:

SELECT image 
FROM item 
WHERE date_listed IN (SELECT date_listed 
                      FROM item 
                      ORDER BY date_listed DESC LIMIT 4)