Come selezionare i quattro elementi più recenti per categoria?

Ho un database di oggetti. Ogni articolo è classificato con un ID di categoria da una tabella di categorie. Sto provando a creare una pagina che elenca ogni categoria e sotto ogni categoria voglio mostrare i 4 articoli più recenti in quella categoria.

Per esempio:

Prodotti per animali

img1 img2 img3 img4 

Cibo per animali

 img1 img2 img3 img4 

So che potrei facilmente risolvere questo problema interrogando il database per ogni categoria in questo modo:

  SELEZIONA id FROM category 

Quindi iterando su quei dati e interrogando il database per ogni categoria per afferrare gli elementi più recenti:

  SELEZIONA l'immagine FROM object dove category_id =: category_id 
 ORDINA PER date_listed DESC LIMIT 4 

Quello che sto cercando di capire è se posso usare solo 1 query e prendere tutti questi dati. Ho 33 categorie quindi ho pensato che forse avrebbe aiutato a ridurre il numero di chiamate al database.

Qualcuno sà se questo è ansible? O se 33 chiamate non sono un grosso problema e dovrei farlo nel modo più semplice.

Questo è il problema più grande di n per gruppo, ed è una domanda SQL molto comune.

Ecco come lo risolvo con join esterni:

 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; 

item_id che la chiave primaria della tabella degli item sia item_id e che sia una item_id -chiave monotonicamente crescente. Cioè, un valore maggiore in item_id corrisponde a una riga più nuova item .

Ecco come funziona: per ogni articolo, ci sono alcuni altri elementi che sono più recenti. Ad esempio, ci sono tre elementi più recenti rispetto al quarto elemento più recente. Ci sono zero articoli più nuovi rispetto all'object più nuovo. Quindi vogliamo confrontare ogni elemento ( i1 ) con l'insieme di elementi ( i2 ) che sono più recenti e hanno la stessa categoria di i1 . Se il numero di questi articoli più recenti è inferiore a quattro, i1 è uno di quelli che includiamo. Altrimenti, non includerlo.

La bellezza di questa soluzione è che funziona indipendentemente da quante categorie hai e continua a funzionare se cambi le categorie. Funziona anche se il numero di elementi in alcune categorie è inferiore a quattro.


Un'altra soluzione che funziona, ma si basa sulla caratteristica delle variabili utente 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 ha introdotto il supporto per le funzioni della finestra standard SQL. Ora possiamo risolvere questo tipo di problema come fanno gli altri 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; 

Questa soluzione è un adattamento da un’altra soluzione SO , grazie RageZ per aver individuato questa domanda correlata / simile.

NOTA

Questa soluzione sembra soddisfacente per il caso d’uso di Justin. A seconda del tuo caso d’uso, potresti voler controllare le soluzioni di Bill Karwin o David Andres in questo post. La soluzione di Bill ha il mio voto! Guarda perché, come ho messo entrambe le query l’una accanto all’altra 😉

Il vantaggio della mia soluzione è che restituisce un record per category_id (le informazioni dalla tabella degli articoli sono “arrotolate”). Lo svantaggio principale della mia soluzione è la sua mancanza di leggibilità e la sua crescente complessità al crescere del numero di righe desiderate (diciamo di avere 6 righe per categoria anziché 6). Inoltre potrebbe essere leggermente più lento con il crescere del numero di righe nella tabella degli articoli. (Indipendentemente da ciò, tutte le soluzioni funzioneranno meglio con un numero minore di righe idonee nella tabella degli articoli, ed è quindi consigliabile eliminare o spostare periodicamente gli oggetti più vecchi e / o introdurre un flag per aiutare SQL a filtrare le righe in anticipo)

Primo tentativo (non ha funzionato !!!) …

Il problema con questo approccio era che la subquery avrebbe [giustamente ma male per noi] produrre moltissime righe, basate sui prodotti cartesiani definiti dagli auto join …

 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; 

Secondo tentativo. (funziona bene!)

Aggiunta una clausola WHERE per la sottoquery, che impone la data elencata come ultima, seconda più recente, thrird più tardi ecc. Per i1, i2, i3 ecc. Rispettivamente (e anche per i casi null quando ci sono meno di 4 elementi per un determinato ID di categoria). Sono state aggiunte anche clausole di filtro non correlate per impedire la visualizzazione di voci “vendute” o voci che non hanno un’immagine (requisiti aggiunti)

Questa logica presuppone che non ci siano valori di date con data duplicata (per una data id di categoria). Tali casi altrimenti creerebbero righe duplicate. In effetti questo uso della data elencata è quello di una chiave primaria monotonicamente incrementata come definito / richiesto nella soluzione di 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; 

Ora … confronta il seguente dove introduco una chiave item_id e uso la soluzione di Bill per fornire l’elenco di questi alla query “esterna”. Puoi capire perché l’approccio di Bill è migliore …

 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 

In altri database puoi farlo utilizzando la funzione 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 

Sfortunatamente MySQL non supporta la funzione ROW_NUMBER , ma puoi emularlo usando le variabili:

 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 

Guardalo lavorando online: sqlfiddle

Funziona come segue:

  • Inizialmente @prev è impostato su NULL e @rn è impostato su 0.
  • Per ogni riga che vediamo, controlla se category_id è uguale alla riga precedente.
    • Se sì, incrementa il numero di riga.
    • In caso contrario, avvia una nuova categoria e reimposta il numero di riga su 1.
  • Al termine della sottoquery, il passaggio finale consiste nel filtrare in modo che vengano mantenute solo le righe con un numero di riga inferiore o uguale a 4.

non molto carina ma:

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

A seconda della costante delle categorie, la seguente è la via più semplice

 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 

il codice qui sotto mostra un modo per farlo in un loop che ha decisamente bisogno di molto editing, ma spero che aiuti.

  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 

ok dopo una ricerca su google la risposta rapida non sarebbe ansible almeno su mysql

questo questo thread per riferimento

forse dovresti memorizzare nella cache il risultato di quella query se hai paura di far cadere il server e vuoi che il codice funzioni meglio