over - row_number mysql ejemplo

ROW_NUMBER() en MySQL

Question

¿Existe una buena forma en MySQL para replicar la función de SQL Server ROW_NUMBER() ?

Por ejemplo:

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

Entonces podría, por ejemplo, agregar una condición para limitar intRow a 1 para obtener una fila con la col3 más alta para cada par (col1, col2) .

Answer 1

Quiero la fila con el único col3 más alto para cada par (col1, col2).

Eso es un máximo grupal , una de las preguntas de SQL más frecuentes (ya que parece que debería ser fácil, pero en realidad no lo es).

A menudo me niego para una auto-unión nula:

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;

“Obtenga las filas en la tabla para las cuales ninguna otra fila con col1 coincidente, col2 tiene una col3 más alta”. (Notará que esta y la mayoría de las soluciones de groupwise-máximo devolverán múltiples filas si más de una fila tiene la misma col1, col2 , col3. Si ese es un problema, es posible que necesites un poco de procesamiento posterior.

Answer 2

Definiría una función:

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

entonces podría hacer:

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

Ahora no tienes una subconsulta, que no puedes tener en vistas.

Answer 3

Desde MySQL 8.0.0 y superiores, se pueden usar de forma nativa las funciones de ventana.

1.4 Lo nuevo en MySQL 8.0 :

Funciones de ventana.

MySQL ahora admite funciones de ventana que, para cada fila de una consulta, realizan un cálculo utilizando filas relacionadas con esa fila. Estas incluyen funciones como RANK (), LAG () y NTILE (). Además, varias funciones agregadas existentes ahora se pueden usar como funciones de ventana; por ejemplo, SUM () y AVG ().

ROW_NUMBER () over_clause :

Devuelve el número de la fila actual dentro de su partición. Los números de las filas van desde 1 hasta el número de filas de partición.

ORDER BY afecta el orden en que se numeran las filas. Sin ORDER BY, la numeración de filas es indeterminada.

Manifestación:

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;

Demostración de DBFiddle

Answer 4

Echa un vistazo a este artículo, muestra cómo imitar SQL ROW_NUMBER () con una partición en MySQL. Me encontré con este mismo escenario en una implementación de WordPress. Necesitaba ROW_NUMBER () y no estaba allí.

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

El ejemplo en el artículo está utilizando una sola partición por campo. Para particionar por campos adicionales puedes hacer algo como esto:

  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 

El uso de concat_ws maneja null. Probé esto contra 3 campos usando un int, date y varchar. Espero que esto ayude. Echa un vistazo al artículo, ya que descompone esta consulta y la explica.

Answer 5

Esto funciona perfectamente para mí para crear RowNumber cuando tenemos más de una columna. En este caso dos columnas.

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

Answer 6

Esto también podría ser una solución:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees

Answer 7

La funcionalidad rownumber no puede ser imitada. Es posible que obtengas los resultados que esperas, pero lo más probable es que te desilusiones en algún momento. Esto es lo que dice la documentación de mysql:

Para otras declaraciones, como SELECT, puede obtener los resultados que espera, pero esto no está garantizado. En la siguiente declaración, podría pensar que MySQL evaluará @a primero y luego realizará una asignación: SELECCIONE @a, @a: = @ a + 1, ...; Sin embargo, el orden de evaluación de las expresiones que involucran variables de usuario no está definido.

Saludos, Georgi.

Answer 8

La solución que encontré para trabajar mejor fue usar una subconsulta como esta:

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

Las columnas PARTICIÓN POR solo se comparan con ''='' y se separan con AND. Las columnas ORDER BY se compararán con ''<'' o ''>'', y se separarán por OR.

He encontrado que esto es muy flexible, incluso si es un poco costoso.

Answer 9

MariaDB 10.2 está implementando "Funciones de ventana", incluyendo RANK (), ROW_NUMBER () y varias otras cosas:

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

Basado en una charla en Percona Live este mes, están razonablemente bien optimizados.

La sintaxis es idéntica al código en la pregunta.

Answer 10

No hay ninguna función como rownum , row_num() en MySQL, pero la forma es como se muestra a continuación:

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

Answer 11

No hay una funcionalidad de clasificación en MySQL. Lo más cercano que puedes conseguir es usar una variable:

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

Entonces, ¿cómo funcionaría eso en mi caso? Necesitaría dos variables, una para cada uno de col1 y col2? Col2 necesitaría reiniciarse de alguna manera cuando cambia col1 ..?

Sí. Si fuera Oracle, podría usar la función LEAD para alcanzar el pico en el siguiente valor. Afortunadamente, Quassnoi cubre la lógica de lo que necesita implementar en MySQL .

Answer 12

No veo ninguna respuesta simple que cubra la parte de "PARTICIÓN POR", así que aquí está la mía:

SELECT
    *
FROM (
    select
        CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_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 cláusula ORDER BY debe reflejar su necesidad de ROW_NUMBER. Por lo tanto, ya existe una limitación clara: no puede tener varias "emulaciones" de ROW_NUMBER de esta forma al mismo tiempo.
• El orden de la "columna calculada" importa . Si tiene mysql, calcule esa columna en otro orden, puede que no funcione.

• En este sencillo ejemplo, solo pongo uno, pero puedes tener varias partes de "PARTICIÓN POR"

      CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_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

Answer 13

Siempre termino siguiendo este patrón. Dada esta tabla:

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

Puede obtener este resultado:

+------+------+------------+
|    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 |
+------+------+------------+

Al ejecutar esta consulta, que no necesita ninguna variable definida:

SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

¡Espero que ayude!

Answer 14

También un poco tarde, pero hoy tuve la misma necesidad, así que busqué en Google y finalmente encontré un enfoque general simple aquí en el artículo de Pinal Dave http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

Quería concentrarme en la pregunta original de Paul (ese también era mi problema), así que resumo mi solución como un ejemplo práctico.

Debido a que queremos particionar en dos columnas, crearía una variable SET durante la iteración para identificar si se inició un nuevo grupo.

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

El 3 significa en el primer parámetro de MAKE_SET que quiero ambos valores en el SET (3 = 1 | 2). Por supuesto, si no tenemos dos o más columnas construyendo los grupos, podemos eliminar la operación MAKE_SET. La construcción es exactamente la misma. Esto está funcionando para mí según sea necesario. Muchas gracias a Pinal Dave por su clara demostración.

Answer 15

También votaría por la solución de Mosty Mostacho con una pequeña modificación en su código de consulta:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

Lo que dará el mismo resultado:

+------+------+------------+
|    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 |
+------+------+------------+

para la mesa:

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

Con la única diferencia de que la consulta no usa UNIR y GRUPO POR, confiando en la selección anidada en su lugar.

Answer 16

Un poco tarde pero también puede ayudar a alguien que busca respuestas ...

Ejemplo entre filas / número de fila: consulta recursiva que se puede usar en cualquier 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

Answer 17

consulta para row_number en mysql

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

Answer 18

Esto permite que se logre la misma funcionalidad que ROW_NUMBER () Y PARTITION BY proporciona para 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

Answer 19

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

Answer 20

SELECT 
    col1, col2, 
    count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc

Answer 21

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;