ROW_NUMBER () in MySQL

C’è un modo carino in MySQL per replicare la funzione SQL Server ROW_NUMBER() ?

Per esempio:

 SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 

Quindi, ad esempio, potrei aggiungere una condizione per limitare intRow a 1 per ottenere una singola riga con il col3 più col3 per ciascuna (col1, col2) .

Voglio la riga con il singolo col3 più alto per ciascuna coppia (col1, col2).

Questo è un massimo groupwise , una delle domande SQL più comuni (poiché sembra che dovrebbe essere facile, ma in realtà non lo è).

Spesso mi piacciono per un autoaggiunto null:

 SELECT t0.col3 FROM table AS t0 LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3 WHERE t1.col1 IS NULL; 

“Ottieni le righe nella tabella per le quali nessun’altra riga con corrispondenza di col1, col2 ha un valore più alto di col3.” (Noterai che questa e la maggior parte delle soluzioni groupwise-maximum restituiranno più righe se più di una riga ha lo stesso col1, col2 , col3. Se questo è un problema potresti aver bisogno di qualche post-elaborazione.)

Non c’è funzionalità di classificazione in MySQL. Il più vicino che puoi ottenere è usare una variabile:

 SELECT t.*, @rownum := @rownum + 1 AS rank FROM YOUR_TABLE t, (SELECT @rownum := 0) r 

quindi come funzionerebbe nel mio caso? Avrei bisogno di due variabili, una per ciascuna di col1 e col2? Col2 avrebbe bisogno di essere ripristinato in qualche modo quando col1 è cambiato ..?

Sì. Se fosse Oracle, potresti usare la funzione LEAD per raggiungere il valore successivo. Per fortuna, Quassnoi copre la logica per ciò che è necessario implementare in MySQL .

Finisco sempre seguendo questo schema. Dato questo tavolo:

 +------+------+ | i | j | +------+------+ | 1 | 11 | | 1 | 12 | | 1 | 13 | | 2 | 21 | | 2 | 22 | | 2 | 23 | | 3 | 31 | | 3 | 32 | | 3 | 33 | | 4 | 14 | +------+------+ 

Puoi ottenere questo risultato:

 +------+------+------------+ | i | j | row_number | +------+------+------------+ | 1 | 11 | 1 | | 1 | 12 | 2 | | 1 | 13 | 3 | | 2 | 21 | 1 | | 2 | 22 | 2 | | 2 | 23 | 3 | | 3 | 31 | 1 | | 3 | 32 | 2 | | 3 | 33 | 3 | | 4 | 14 | 1 | +------+------+------------+ 

Eseguendo questa query, che non ha bisogno di alcuna variabile definita:

 SELECT ai, aj, count(*) as row_number FROM test a JOIN test b ON ai = bi AND aj >= bj GROUP BY ai, aj 

Spero possa aiutare!

 SELECT @i:[email protected]+1 AS iterator, t.* FROM tablename AS t, (SELECT @i:=0) AS foo 

Dai un’occhiata a questo articolo, mostra come imitare SQL ROW_NUMBER () con una partizione in MySQL. Mi sono imbattuto in questo stesso scenario in una implementazione di WordPress. Avevo bisogno di ROW_NUMBER () e non c’era.

http://www.explodybits.com/2011/11/mysql-row-number/

L’esempio nell’articolo utilizza una singola partizione per campo. Per partizionare con campi aggiuntivi puoi fare qualcosa del genere:

  SELECT @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber ,t.col1 ,t.col2 ,t.Col3 ,t.col4 ,@prev_value := concat_ws('',t.col1,t.col2) FROM table1 t, (SELECT @row_num := 1) x, (SELECT @prev_value := '') y ORDER BY t.col1,t.col2,t.col3,t.col4 

L’utilizzo di concat_ws gestisce i valori nulli. Ho provato questo contro 3 campi utilizzando un int, data e varchar. Spero che questo ti aiuti. Controlla l’articolo in quanto interrompe questa query e la spiega.

Vorrei anche votare per la soluzione di Mosty Mostacho con modifiche minori al suo codice di ricerca:

 SELECT ai, aj, ( SELECT count(*) from test b where aj >= bj AND ai = bi ) AS row_number FROM test a 

Quale darà lo stesso risultato:

 +------+------+------------+ | i | j | row_number | +------+------+------------+ | 1 | 11 | 1 | | 1 | 12 | 2 | | 1 | 13 | 3 | | 2 | 21 | 1 | | 2 | 22 | 2 | | 2 | 23 | 3 | | 3 | 31 | 1 | | 3 | 32 | 2 | | 3 | 33 | 3 | | 4 | 14 | 1 | +------+------+------------+ 

per il tavolo:

 +------+------+ | i | j | +------+------+ | 1 | 11 | | 1 | 12 | | 1 | 13 | | 2 | 21 | | 2 | 22 | | 2 | 23 | | 3 | 31 | | 3 | 32 | | 3 | 33 | | 4 | 14 | +------+------+ 

Con la sola differenza che la query non usa JOIN e GROUP BY, invece si basa sulla selezione nidificata.

Da MySQL 8.0.0 e versioni successive è ansible utilizzare in modo nativo funzioni con windows.

1.4 Novità di MySQL 8.0 :

Funzioni della finestra.

MySQL ora supporta le funzioni della finestra che, per ciascuna riga di una query, eseguono un calcolo utilizzando le righe relative a tale riga. Questi includono funzioni come RANK (), LAG () e NTILE (). Inoltre, diverse funzioni di aggregazione esistenti ora possono essere utilizzate come funzioni di finestra; ad esempio, SUM () e AVG ().

ROW_NUMBER () over_clause :

Restituisce il numero della riga corrente all’interno della sua partizione. I numeri delle righe vanno da 1 al numero di righe della partizione.

ORDER BY influisce sull’ordine in cui le righe sono numerate. Senza ORDER BY, la numerazione delle righe è indeterminata.

demo:

 CREATE TABLE Table1( id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT); INSERT INTO Table1(col1, col2, col3) VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'), (2,1,'x'),(2,1,'y'),(2,2,'z'); SELECT col1, col2,col3, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1; 

Demo di DBFiddle

Definirei una funzione:

 delimiter $$ DROP FUNCTION IF EXISTS `getFakeId`$$ CREATE FUNCTION `getFakeId`() RETURNS int(11) DETERMINISTIC begin return if(@fakeId, @fakeId:[email protected]+1, @fakeId:=1); end$$ 

allora potrei fare:

 select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2; 

Ora non hai una sottoquery, che non puoi avere nelle visualizzazioni.

Non esiste una funtion come rownum , row_num() in MySQL ma il modo in cui è simile è il seguente:

 select @s:[email protected]+1 serial_no, tbl.* from my_table tbl, (select @s:=0) as s; 

query per row_number in mysql

 set @row_number=0; select (@row_number := @row_number +1) as num,id,name from sbs 

La soluzione che ho trovato per funzionare al meglio è stata l’utilizzo di una sottoquery come questa:

 SELECT col1, col2, ( SELECT COUNT(*) FROM Table1 WHERE col1 = t1.col1 AND col2 = t1.col2 AND col3 > t1.col3 ) AS intRow FROM Table1 t1 

Le colonne PARTITION BY vengono semplicemente confrontate con “=” e separate da AND. Le colonne ORDER BY verranno confrontate con “<" o ">” e separate da OR.

Ho trovato questo molto flessibile, anche se è un po ‘costoso.

La funzionalità Rownumber non può essere imitata. Potresti ottenere i risultati che ti aspetti, ma a un certo punto probabilmente rimarrai deluso. Ecco cosa dice la documentazione di mysql:

Per altre istruzioni, come SELECT, potresti ottenere i risultati che ti aspetti, ma ciò non è garantito. Nella seguente dichiarazione, potresti pensare che MySQL valuterà prima @a e poi eseguirà un secondo incarico: SELECT @a, @a: = @ a + 1, …; Tuttavia, l’ordine di valutazione per le espressioni che coinvolgono variabili utente non è definito.

Saluti, Georgi.

MariaDB 10.2 sta implementando “Funzioni della finestra”, incluso RANK (), ROW_NUMBER () e molte altre cose:

https://mariadb.com/kb/en/mariadb/window-functions/

Sulla base di un discorso al Percona Live di questo mese, sono ragionevolmente ben ottimizzati.

La syntax è identica al codice nella domanda.

Un po ‘tardi ma può anche aiutare qualcuno che cerca risposte …

Tra righe / row_number esempio – query ricorsiva che può essere utilizzata in qualsiasi SQL:

 WITH data(row_num, some_val) AS ( SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle UNION ALL SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number ) SELECT * FROM data WHERE row_num BETWEEN 5 AND 10 / ROW_NUM SOME_VAL ------------------- 5 11 6 16 7 22 8 29 9 37 10 46 

Ciò consente la stessa funzionalità che ROW_NUMBER () AND PARTITION BY consente di ottenere in MySQL

 SELECT @row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber FirstName, Age, Gender, @prev_value := GENDER FROM Person, (SELECT @row_num := 1) x, (SELECT @prev_value := '') y ORDER BY Gender, Age DESC 

Anche un po ‘tardi, ma oggi ho avuto la stessa necessità quindi ho fatto ricerche su Google e infine un semplice approccio generale trovato qui nell’articolo di Pinal Dave http://blog.sqlauthority.com/2014/03/09/mysql-reset-row -numero-for-each-group-partizione per riga-numero /

Volevo concentrarmi sulla domanda originale di Paul (che era anche il mio problema), quindi riassumo la mia soluzione come un esempio funzionante.

Poiché vogliamo partizionare su due colonne, creerò una variabile SET durante l’iterazione per identificare se è stato avviato un nuovo gruppo.

 SELECT col1, col2, col3 FROM ( SELECT col1, col2, col3, @n := CASE WHEN @v = MAKE_SET(3, col1, col2) THEN @n + 1 -- if we are in the same group ELSE 1 -- next group starts so we reset the counter END AS row_number, @v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value ) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group 

Il 3 indica al primo parametro di MAKE_SET che voglio entrambi i valori nel SET (3 = 1 | 2). Ovviamente, se non abbiamo due o più colonne che costruiscono i gruppi, possiamo eliminare l’operazione MAKE_SET. La costruzione è esattamente la stessa. Questo funziona per me, come richiesto. Mille grazie a Pinal Dave per la sua chiara dimostrazione.

Non vedo alcuna risposta semplice riguardante la parte “PARTITION BY”, quindi ecco la mia:

 SELECT * FROM ( select CASE WHEN @partitionBy_1 = l THEN @row_number:[email protected]_number+1 ELSE @row_number:=1 END AS i , @partitionBy_1:=l AS p , t.* from ( select @row_number:=0,@partitionBy_1:=null ) as x cross join ( select 1 as n, 'a' as l union all select 1 as n, 'b' as l union all select 2 as n, 'b' as l union all select 2 as n, 'a' as l union all select 3 as n, 'a' as l union all select 3 as n, 'b' as l ) as t ORDER BY l, n ) AS X where i > 1 
  • La clausola ORDER BY deve riflettere la tua esigenza di ROW_NUMBER. Pertanto esiste già una chiara limitazione: non è ansible avere più “emulazione” ROW_NUMBER di questo modulo contemporaneamente.
  • L’ordine della “colonna calcasting” è importante . Se hai mysql calcola queste colonne in un altro ordine, potrebbe non funzionare.
  • In questo semplice esempio ne metto solo uno, ma puoi avere diverse parti “PARTITION BY”

      CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:[email protected]_number+1 ELSE @row_number:=1 END AS i , @partitionBy_1:=part1 AS P1 , @partitionBy_2:=part2 AS P2 [...] FROM ( SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...] ) as x 

Questa potrebbe anche essere una soluzione:

 SET @row_number = 0; SELECT (@row_number:[email protected]_number + 1) AS num, firstName, lastName FROM employees 
 set @i = 1; INSERT INTO ARG_VALUE_LOOKUP(ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,UPDATE_TIMESTAMP,UPDATE_USER,VER_NBR,OBJ_ID) select @i:= @i+1 as ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,CURRENT_TIMESTAMP,'admin',1,UUID() FROM TEMP_ARG_VALUE_LOOKUP order by ARGUMENT_NAME; 

Questo funziona perfettamente per me per creare RowNumber quando abbiamo più di una colonna. In questo caso due colonne.

 SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber, `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name`, @prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`) FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name` FROM Employee ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z, (SELECT @row_num := 1) x, (SELECT @prev_value := '') y ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC 
 SELECT col1, col2, count(*) as intRow FROM Table1 GROUP BY col1,col2 ORDER BY col3 desc