sentencia - Función de rango en MySQL

saber si una fecha esta dentro de un rango mysql

Question

No soy un experto en MySQL. Necesito averiguar el rango de clientes. Aquí estoy agregando la consulta SQL estándar ANSI correspondiente para mi requerimiento. Por favor, ayúdame a convertirlo a MySQL.

 SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person

¿Hay alguna función para averiguar el rango en MySQL?

Answer 1

@Sam, tu punto es excelente en concepto, pero creo que malinterpretaste lo que dicen los documentos de MySQL en la página de referencia - o no entiendo bien :-) - y solo quería agregar esto para que si alguien se siente incómodo con el @ La respuesta de Daniel será más tranquilizada o al menos profundizará un poco más.

Verá que el "@curRank: = @curRank + 1 rango AS" dentro de SELECT no es "una declaración", es una parte "atómica" de la declaración, por lo que debería ser segura.

El documento al que hace referencia muestra ejemplos donde la misma variable definida por el usuario en 2 partes (atómicas) del enunciado, por ejemplo, "SELECT @curRank, @curRank: = @curRank + 1 AS rank".

Se podría argumentar que @curRank se usa dos veces en la respuesta de @ Daniel: (1) el "@curRank: = @curRank + 1 rango AS" y (2) el "(SELECCIONAR @curRank: = 0) r", pero desde el segundo el uso es parte de la cláusula FROM, estoy bastante seguro de que se garantiza que se evaluará primero; esencialmente convirtiéndolo en una segunda y anterior declaración.

De hecho, en esa misma página de documentos MySQL a la que hizo referencia, verá la misma solución en los comentarios, podría ser donde @Daniel lo obtuvo; Sí, sé que son los comentarios, pero son comentarios en la página oficial de documentos y eso tiene cierto peso.

Answer 2

Aquí hay una solución genérica que ordena una tabla basada en una columna y asigna rango; las filas con lazos tienen asignado el mismo rango (usa una variable adicional para este propósito):

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT id, rank_column, CASE
WHEN @prev_value = rank_column THEN @rank_count
WHEN @prev_value := rank_column THEN @rank_count := @rank_count + 1
END AS rank
FROM rank_table
ORDER BY rank_column

Tenga en cuenta que hay dos instrucciones de asignación en la segunda cláusula WHEN . Data de muestra:

CREATE TABLE rank_table(id INT, rank_column INT);
INSERT INTO rank_table (id, rank_column) VALUES
(1, 10),
(2, 20),
(3, 30),
(4, 30),
(5, 30),
(6, 40),
(7, 50),
(8, 50),
(9, 50);

Salida:

+------+-------------+------+
| id | rank_column | rank |
+------+-------------+------+
| 1 | 10 | 1 |
| 2 | 20 | 2 |
| 3 | 30 | 3 |
| 4 | 30 | 3 |
| 5 | 30 | 3 |
| 6 | 40 | 4 |
| 7 | 50 | 5 |
| 8 | 50 | 5 |
| 9 | 50 | 5 |
+------+-------------+------+

SQL Fiddle

Answer 3

Combinación de la respuesta de Daniel y Salman. Sin embargo, el rango no dará como sigue la secuencia con lazos existe. En cambio, se saltea el rango al siguiente. Por lo tanto, el máximo siempre alcanza el conteo de filas.

 SELECT first_name,
age,
gender,
IF(age=@_last_age,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
@_sequence:=@_sequence+1,@_last_age:=age
FROM person p, (SELECT @curRank := 1, @_sequence:=1, @_last_age:=0) r
ORDER BY age;

Esquema y caso de prueba:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));
INSERT INTO person VALUES (1, ''Bob'', 25, ''M'');
INSERT INTO person VALUES (2, ''Jane'', 20, ''F'');
INSERT INTO person VALUES (3, ''Jack'', 30, ''M'');
INSERT INTO person VALUES (4, ''Bill'', 32, ''M'');
INSERT INTO person VALUES (5, ''Nick'', 22, ''M'');
INSERT INTO person VALUES (6, ''Kathy'', 18, ''F'');
INSERT INTO person VALUES (7, ''Steve'', 36, ''M'');
INSERT INTO person VALUES (8, ''Anne'', 25, ''F'');
INSERT INTO person VALUES (9, ''Kamal'', 25, ''M'');
INSERT INTO person VALUES (10, ''Saman'', 32, ''M'');

Salida:

+------------+------+--------+------+--------------------------+-----------------+
| first_name | age | gender | rank | @_sequence:=@_sequence+1 | @_last_age:=age |
+------------+------+--------+------+--------------------------+-----------------+
| Kathy | 18 | F | 1 | 2 | 18 |
| Jane | 20 | F | 2 | 3 | 20 |
| Nick | 22 | M | 3 | 4 | 22 |
| Kamal | 25 | M | 4 | 5 | 25 |
| Anne | 25 | F | 4 | 6 | 25 |
| Bob | 25 | M | 4 | 7 | 25 |
| Jack | 30 | M | 7 | 8 | 30 |
| Bill | 32 | M | 8 | 9 | 32 |
| Saman | 32 | M | 8 | 10 | 32 |
| Steve | 36 | M | 10 | 11 | 36 |
+------------+------+--------+------+--------------------------+-----------------+

Answer 4

La solución más directa para determinar el rango de un valor dado es contar el número de valores que tiene ante sí. Supongamos que tenemos los siguientes valores:

10 20 30 30 30 40

• Los 30 valores se consideran terceros
• Los 40 valores se consideran sexto (rango) o cuarto (rango denso)

Ahora volvamos a la pregunta original. Aquí hay algunos datos de muestra que se ordenan como se describe en OP (los rangos esperados se agregan a la derecha):

+------+-----------+------+--------+ +------+------------+
| id | firstname | age | gender | | rank | dense_rank |
+------+-----------+------+--------+ +------+------------+
| 11 | Emily | 20 | F | | 1 | 1 |
| 3 | Grace | 25 | F | | 2 | 2 |
| 20 | Jill | 25 | F | | 2 | 2 |
| 10 | Megan | 26 | F | | 4 | 3 |
| 8 | Lucy | 27 | F | | 5 | 4 |
| 6 | Sarah | 30 | F | | 6 | 5 |
| 9 | Zoe | 30 | F | | 6 | 5 |
| 14 | Kate | 35 | F | | 8 | 6 |
| 4 | Harry | 20 | M | | 1 | 1 |
| 12 | Peter | 20 | M | | 1 | 1 |
| 13 | John | 21 | M | | 3 | 2 |
| 16 | Cole | 25 | M | | 4 | 3 |
| 17 | Dennis | 27 | M | | 5 | 4 |
| 5 | Scott | 30 | M | | 6 | 5 |
| 7 | Tony | 30 | M | | 6 | 5 |
| 2 | Matt | 31 | M | | 8 | 6 |
| 15 | James | 32 | M | | 9 | 7 |
| 1 | Adams | 33 | M | | 10 | 8 |
| 18 | Smith | 35 | M | | 11 | 9 |
| 19 | Zack | 35 | M | | 11 | 9 |
+------+-----------+------+--------+ +------+------------+

Para calcular RANK() OVER (PARTITION BY Gender ORDER BY Age) para Sarah , puede usar esta consulta:

SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)
+------+------------+
| rank | dense_rank |
+------+------------+
| 6 | 5 |
+------+------------+

Para calcular RANK() OVER (PARTITION BY Gender ORDER BY Age) para Todas las filas, puede usar esta consulta:

SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id

Y aquí está el resultado (los valores unidos se agregan a la derecha):

+------+------+------------+ +-----------+-----+--------+
| id | rank | dense_rank | | firstname | age | gender |
+------+------+------------+ +-----------+-----+--------+
| 11 | 1 | 1 | | Emily | 20 | F |
| 3 | 2 | 2 | | Grace | 25 | F |
| 20 | 2 | 2 | | Jill | 25 | F |
| 10 | 4 | 3 | | Megan | 26 | F |
| 8 | 5 | 4 | | Lucy | 27 | F |
| 6 | 6 | 5 | | Sarah | 30 | F |
| 9 | 6 | 5 | | Zoe | 30 | F |
| 14 | 8 | 6 | | Kate | 35 | F |
| 4 | 1 | 1 | | Harry | 20 | M |
| 12 | 1 | 1 | | Peter | 20 | M |
| 13 | 3 | 2 | | John | 21 | M |
| 16 | 4 | 3 | | Cole | 25 | M |
| 17 | 5 | 4 | | Dennis | 27 | M |
| 5 | 6 | 5 | | Scott | 30 | M |
| 7 | 6 | 5 | | Tony | 30 | M |
| 2 | 8 | 6 | | Matt | 31 | M |
| 15 | 9 | 7 | | James | 32 | M |
| 1 | 10 | 8 | | Adams | 33 | M |
| 18 | 11 | 9 | | Smith | 35 | M |
| 19 | 11 | 9 | | Zack | 35 | M |
+------+------+------------+ +-----------+-----+--------+

Answer 5

Mientras que la respuesta más votada se clasifica, no se divide en particiones. Puedes unirte a ti mismo para obtener también todo dividido:

SELECT a.first_name,
a.age,
a.gender,
count(b.age)+1 as rank
FROM person a left join person b on a.age>b.age and a.gender=b.gender
group by a.first_name,
a.age,
a.gender

Caso de uso

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));
INSERT INTO person VALUES (1, ''Bob'', 25, ''M'');
INSERT INTO person VALUES (2, ''Jane'', 20, ''F'');
INSERT INTO person VALUES (3, ''Jack'', 30, ''M'');
INSERT INTO person VALUES (4, ''Bill'', 32, ''M'');
INSERT INTO person VALUES (5, ''Nick'', 22, ''M'');
INSERT INTO person VALUES (6, ''Kathy'', 18, ''F'');
INSERT INTO person VALUES (7, ''Steve'', 36, ''M'');
INSERT INTO person VALUES (8, ''Anne'', 25, ''F'');

Respuesta :

Bill 32 M 4
Bob 25 M 2
Jack 30 M 3
Nick 22 M 1
Steve 36 M 5
Anne 25 F 3
Jane 20 F 2
Kathy 18 F 1

Answer 6

Si desea clasificar a una sola persona, puede hacer lo siguiente:

SELECT COUNT(Age) + 1
FROM PERSON
WHERE(Age < age_to_rank)

Este ranking corresponde a la función de RANGO de Oracle (donde si tienes personas con la misma edad obtienen el mismo rango, y el ranking después de eso no es consecutivo).

Es un poco más rápido que usar una de las soluciones anteriores en una subconsulta y seleccionar de eso para obtener el ranking de una persona.

Esto se puede utilizar para clasificar a todos, pero es más lento que las soluciones anteriores.

SELECT
Age AS age_var,
(
SELECT COUNT(Age) + 1
FROM Person
WHERE (Age < age_var)
) AS rank
FROM Person

Answer 7

Un ajuste de la versión de Daniel para calcular el percentil junto con el rango. También dos personas con las mismas marcas obtendrán el mismo rango.

set @totalStudents = 0;
select count(*) into @totalStudents from marksheets;
SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank,
@percentile := IF(@prevVal=score, @percentile, (@totalStudents - @studentNumber + 1)/(@totalStudents)*100),
@studentNumber := @studentNumber + 1 as studentNumber,
@prevVal:=score
FROM marksheets, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
) r
ORDER BY score DESC

Resultados de la consulta para una muestra de datos -

+----+-------+------+---------------+---------------+-----------------+
| id | score | rank | percentile | studentNumber | @prevVal:=score |
+----+-------+------+---------------+---------------+-----------------+
| 10 | 98 | 1 | 100.000000000 | 2 | 98 |
| 5 | 95 | 2 | 90.000000000 | 3 | 95 |
| 6 | 91 | 3 | 80.000000000 | 4 | 91 |
| 2 | 91 | 3 | 80.000000000 | 5 | 91 |
| 8 | 90 | 5 | 60.000000000 | 6 | 90 |
| 1 | 90 | 5 | 60.000000000 | 7 | 90 |
| 9 | 84 | 7 | 40.000000000 | 8 | 84 |
| 3 | 83 | 8 | 30.000000000 | 9 | 83 |
| 4 | 72 | 9 | 20.000000000 | 10 | 72 |
| 7 | 60 | 10 | 10.000000000 | 11 | 60 |
+----+-------+------+---------------+---------------+-----------------+

Answer 8

Una opción es usar una variable de clasificación, como la siguiente:

SELECT first_name,
age,
gender,
@curRank := @curRank + 1 AS rank
FROM person p, (SELECT @curRank := 0) r
ORDER BY age;

La parte (SELECT @curRank := 0) permite la inicialización de la variable sin requerir un comando SET separado.

Caso de prueba:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));
INSERT INTO person VALUES (1, ''Bob'', 25, ''M'');
INSERT INTO person VALUES (2, ''Jane'', 20, ''F'');
INSERT INTO person VALUES (3, ''Jack'', 30, ''M'');
INSERT INTO person VALUES (4, ''Bill'', 32, ''M'');
INSERT INTO person VALUES (5, ''Nick'', 22, ''M'');
INSERT INTO person VALUES (6, ''Kathy'', 18, ''F'');
INSERT INTO person VALUES (7, ''Steve'', 36, ''M'');
INSERT INTO person VALUES (8, ''Anne'', 25, ''F'');

Resultado:

+------------+------+--------+------+
| first_name | age | gender | rank |
+------------+------+--------+------+
| Kathy | 18 | F | 1 |
| Jane | 20 | F | 2 |
| Nick | 22 | M | 3 |
| Bob | 25 | M | 4 |
| Anne | 25 | F | 5 |
| Jack | 30 | M | 6 |
| Bill | 32 | M | 7 |
| Steve | 36 | M | 8 |
+------------+------+--------+------+
8 rows in set (0.02 sec)