[SQL] MySQL의에서 순위 기능
SQLMySQL의에서 순위 기능
나는 고객의 순위를 알 필요가있다. 여기 내 요구 사항에 해당하는 ANSI 표준 SQL 쿼리를 추가하고있다. 내가 MySQL을로 변환 도와주세요.
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
MySQL의에서 순위를 찾을 수있는 기능이 있습니까?
해결법
-
==============================
1.하나의 옵션은 다음과 같은 순위 변수를 사용하는 것입니다 :
하나의 옵션은 다음과 같은 순위 변수를 사용하는 것입니다 :
SELECT first_name, age, gender, @curRank := @curRank + 1 AS rank FROM person p, (SELECT @curRank := 0) r ORDER BY age;
제 (SELECT @curRank는 = 0) 부분은 별도 SET 명령을 필요로하지 않고, 변수를 초기화 할 수있다.
테스트 케이스 :
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');
결과:
+------------+------+--------+------+ | 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)
-
==============================
2.여기에 양수인이 행에 대한 파티션을 통해 순위를 조밀하는 일반적인 솔루션입니다. 그것은 사용자 변수를 사용합니다 :
여기에 양수인이 행에 대한 파티션을 통해 순위를 조밀하는 일반적인 솔루션입니다. 그것은 사용자 변수를 사용합니다 :
CREATE TABLE person ( id INT NOT NULL PRIMARY KEY, firstname VARCHAR(10), gender VARCHAR(1), age INT ); INSERT INTO person (id, firstname, gender, age) VALUES (1, 'Adams', 'M', 33), (2, 'Matt', 'M', 31), (3, 'Grace', 'F', 25), (4, 'Harry', 'M', 20), (5, 'Scott', 'M', 30), (6, 'Sarah', 'F', 30), (7, 'Tony', 'M', 30), (8, 'Lucy', 'F', 27), (9, 'Zoe', 'F', 30), (10, 'Megan', 'F', 26), (11, 'Emily', 'F', 20), (12, 'Peter', 'M', 20), (13, 'John', 'M', 21), (14, 'Kate', 'F', 35), (15, 'James', 'M', 32), (16, 'Cole', 'M', 25), (17, 'Dennis', 'M', 27), (18, 'Smith', 'M', 35), (19, 'Zack', 'M', 35), (20, 'Jill', 'F', 25); SELECT person.*, @rank := CASE WHEN @partval = gender AND @rankval = age THEN @rank WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1 WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1 END AS rnk FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x ORDER BY gender, age;
변수 할당이 CASE 표현식 내부에 배치되는 것을 알 수 있습니다. 이것은 (이론적으로) 평가 문제의 순서로 처리합니다. 는 NULL은 데이터 형식 변환 및 단락 문제를 처리하기 위해 추가되지 않습니다.
PS : 그것은 쉽게 넥타이를 확인 모든 조건을 제거하여 파티션을 통해 ROW_NUMBER로 변환 할 수 있습니다.
| id | firstname | gender | age | rank | |----|-----------|--------|-----|------| | 11 | Emily | F | 20 | 1 | | 20 | Jill | F | 25 | 2 | | 3 | Grace | F | 25 | 2 | | 10 | Megan | F | 26 | 3 | | 8 | Lucy | F | 27 | 4 | | 6 | Sarah | F | 30 | 5 | | 9 | Zoe | F | 30 | 5 | | 14 | Kate | F | 35 | 6 | | 4 | Harry | M | 20 | 1 | | 12 | Peter | M | 20 | 1 | | 13 | John | M | 21 | 2 | | 16 | Cole | M | 25 | 3 | | 17 | Dennis | M | 27 | 4 | | 7 | Tony | M | 30 | 5 | | 5 | Scott | M | 30 | 5 | | 2 | Matt | M | 31 | 6 | | 15 | James | M | 32 | 7 | | 1 | Adams | M | 33 | 8 | | 18 | Smith | M | 35 | 9 | | 19 | Zack | M | 35 | 9 |
DB <> 바이올린에 데모
-
==============================
3.가장 upvoted 답변 계급 반면, 파티션, 당신은 자기가 모든 일 또한 분할 얻기 위해 가입 할 수 없습니다 :
가장 upvoted 답변 계급 반면, 파티션, 당신은 자기가 모든 일 또한 분할 얻기 위해 가입 할 수 없습니다 :
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
사용 사례
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');
대답:
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
-
==============================
4.다니엘의 버전의 비틀기는 순위와 함께 백분위 수 계산합니다. 또한 같은 표시가있는 두 사람이 같은 순위를 얻을 것이다.
다니엘의 버전의 비틀기는 순위와 함께 백분위 수 계산합니다. 또한 같은 표시가있는 두 사람이 같은 순위를 얻을 것이다.
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
샘플 데이터에 대한 쿼리의 결과 -
+----+-------+------+---------------+---------------+-----------------+ | 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 | +----+-------+------+---------------+---------------+-----------------+
-
==============================
5.다니엘의 조합 및 살만의 대답. 그러나 순위는 관계가 존재하여 순서를 계속 제공하지 않습니다. 대신 다음에 순위를 건너 뜁니다. 최대 그래서 항상 행 개수에 도달합니다.
다니엘의 조합 및 살만의 대답. 그러나 순위는 관계가 존재하여 순서를 계속 제공하지 않습니다. 대신 다음에 순위를 건너 뜁니다. 최대 그래서 항상 행 개수에 도달합니다.
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;
스키마 및 테스트 케이스 :
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');
산출:
+------------+------+--------+------+--------------------------+-----------------+ | 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 | +------------+------+--------+------+--------------------------+-----------------+
-
==============================
6.MySQL의 8부터는 마지막으로 MySQL은 또한 윈도우 기능을 사용할 수 있습니다 : https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
MySQL의 8부터는 마지막으로 MySQL은 또한 윈도우 기능을 사용할 수 있습니다 : https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
귀하의 질의는 정확히 같은 방법을 쓸 수있다 :
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS `Partition by Gender`, FirstName, Age, Gender FROM Person
-
==============================
7.@Sam, 요점이 개념이 우수하지만 MySQL의 문서가 참조 된 페이지에 무슨 말을 당신이 오해 생각 - 또는 I 오해 :-) - 난 그냥 사람이 불편을 느끼면 있도록이를 추가하고 싶었 @ 다니엘의 대답은 그들은 더 안심 또는 적어도 깊은 조금 파고됩니다.
@Sam, 요점이 개념이 우수하지만 MySQL의 문서가 참조 된 페이지에 무슨 말을 당신이 오해 생각 - 또는 I 오해 :-) - 난 그냥 사람이 불편을 느끼면 있도록이를 추가하고 싶었 @ 다니엘의 대답은 그들은 더 안심 또는 적어도 깊은 조금 파고됩니다.
당신은 "@curRank을 = @curRank + 순위대로 1"을 참조 SELECT 내부 "하나 개의 문장이"아니다는 안전해야하므로,이 명령문의 하나 "원자"부분입니다.
"= @curRank + 1 랭크 AS SELECT @curRank, @curRank"문서 참조하면 여기서, 예를 들면 문 (2) (원자) 부분에서 동일한 사용자 정의 변수, 예를 나타낸다로 진행한다.
하나는 @curRank 다니엘의 대답 @ 두 번 사용한다고 주장 수도 있습니다 : (1)은 "@curRank을 = @curRank + 1 순위 AS"(2) "(SELECT @curRank : = 0) R"하지만 두 번째 이후 사용법은 FROM 절에, 내가 확신이 먼저 계산 보장 해요의 일부입니다; 본질적으로 두 번째 만들기, 그리고, 문 앞.
사실, 당신은 참조 같은 MySQL의 문서 페이지에, 당신은 코멘트에 동일한 솔루션을 볼 수 있습니다 - @Daniel가에서 그것을 가지고 곳이 될 수있다; 그래, 나는 의견의 것을 알고 있지만 공식 문서 페이지에 대한 의견이며 그 어떤 무게를 수행한다.
-
==============================
8.주어진 값의 순위를 결정하기 위해 가장 직진 해결책은 이전 값들의 수를 계산하는 것이다. 우리는 다음과 같은 값을 가지고 가정 :
주어진 값의 순위를 결정하기 위해 가장 직진 해결책은 이전 값들의 수를 계산하는 것이다. 우리는 다음과 같은 값을 가지고 가정 :
10 20 30 30 30 40
이제 다시 원래의 질문에. 여기에 (예상 순위는 오른쪽에 추가됩니다) 영업 이익에 설명 된대로 정렬됩니다 일부 샘플 데이터는 다음과 같습니다
+------+-----------+------+--------+ +------+------------+ | 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 | +------+-----------+------+--------+ +------+------------+
사라에 대한 계산의 RANK () OVER (나이 성별 ORDER BY 파티션)에, 당신은이 쿼리를 사용할 수 있습니다 :
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 | +------+------------+
계산 순위 () OVER이 쿼리를 사용할 수있는 모든 행에 대해 (나이 성별 ORDER BY PARTITION) :
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
그리고 여기 (에 합류 값은 오른쪽에있는 추가) 결과입니다 :
+------+------+------------+ +-----------+-----+--------+ | 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 | +------+------+------------+ +-----------+-----+--------+
-
==============================
9.당신이 순위에 원하는 경우 한 사람 다음을 수행 할 수 있습니다 :
당신이 순위에 원하는 경우 한 사람 다음을 수행 할 수 있습니다 :
SELECT COUNT(Age) + 1 FROM PERSON WHERE(Age < age_to_rank)
이 순위 (같은 나이의 사람들이 있다면 그들은 같은 순위를 얻고, 비 연속이며 그 후 순위) 오라클 RANK 기능에 해당합니다.
그것은 한 사람의 순위를 얻기 위해 조금 더 빠른 하위 쿼리에서 위의 해결 방법 중 하나를 사용하여 그 중에서 선택 이상입니다.
이 순위 모두에 사용할 수 있지만, 위의 솔루션에 비해 느리다.
SELECT Age AS age_var, ( SELECT COUNT(Age) + 1 FROM Person WHERE (Age < age_var) ) AS rank FROM Person
-
==============================
10.은 "그러나"다니엘과 살만의 답변의 조합 Erandac의 대답에, 하나는 아래의 "파티션 해결 방법"중 하나를 사용할 수 있습니다 피하기 위해
은 "그러나"다니엘과 살만의 답변의 조합 Erandac의 대답에, 하나는 아래의 "파티션 해결 방법"중 하나를 사용할 수 있습니다 피하기 위해
SELECT customerID, myDate -- partition ranking works only with CTE / from MySQL 8.0 on , RANK() OVER (PARTITION BY customerID ORDER BY dateFrom) AS rank, -- Erandac's method in combination of Daniel's and Salman's -- count all items in sequence, maximum reaches row count. , IF(customerID=@_lastRank, @_curRank:=@_curRank, @_curRank:=@_sequence+1) AS sequenceRank , @_sequence:=@_sequence+1 as sequenceOverAll -- Dense partition ranking, works also with MySQL 5.7 -- remember to set offset values in from clause , IF(customerID=@_lastRank, @_nxtRank:=@_nxtRank, @_nxtRank:=@_nxtRank+1 ) AS partitionRank , IF(customerID=@_lastRank, @_overPart:=@_overPart+1, @_overPart:=1 ) AS partitionSequence , @_lastRank:=customerID FROM myCustomers, (SELECT @_curRank:=0, @_sequence:=0, @_lastRank:=0, @_nxtRank:=0, @_overPart:=0 ) r ORDER BY customerID, myDate
이 코드에 3 변형 순위 파티션은 연속 순위 번호를 반환합니다. 이 결과에 의해 격벽 위에 랭크 ()에 유사한 데이터 structur 이어질 것이다. 예를 들어, 아래 참조. 특히, partitionSequence는 항상이 방법을 사용하여, 각각의 새로운 partitionRank 1로 시작합니다 :
customerID myDate sequenceRank (Erandac) | sequenceOverAll | | partitionRank | | | partitionSequence | | | | lastRank ... lines ommitted for clarity 40 09.11.2016 11:19 1 44 1 44 40 40 09.12.2016 12:08 1 45 1 45 40 40 09.12.2016 12:08 1 46 1 46 40 40 09.12.2016 12:11 1 47 1 47 40 40 09.12.2016 12:12 1 48 1 48 40 40 13.10.2017 16:31 1 49 1 49 40 40 15.10.2017 11:00 1 50 1 50 40 76 01.07.2015 00:24 51 51 2 1 76 77 04.08.2014 13:35 52 52 3 1 77 79 15.04.2015 20:25 53 53 4 1 79 79 24.04.2018 11:44 53 54 4 2 79 79 08.10.2018 17:37 53 55 4 3 79 117 09.07.2014 18:21 56 56 5 1 117 119 26.06.2014 13:55 57 57 6 1 119 119 02.03.2015 10:23 57 58 6 2 119 119 12.10.2015 10:16 57 59 6 3 119 119 08.04.2016 09:32 57 60 6 4 119 119 05.10.2016 12:41 57 61 6 5 119 119 05.10.2016 12:42 57 62 6 6 119 ...
-
==============================
11.
select id,first_name,gender,age, rank() over(partition by gender order by age) rank_g from person 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,'AKSH',32,'M');
from https://stackoverflow.com/questions/3333665/rank-function-in-mysql by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
[SQL] 왜 SELECT는 유해한 것으로 간주 *입니까? (0) | 2020.03.05 |
---|---|
[SQL] 여러 테이블에서 SQL 쿼리 데이터를 반환 (0) | 2020.03.05 |
[SQL] 표현에 의해 ORA-00979 그룹에없는 (0) | 2020.03.05 |
[SQL] MySQL은 - 열을 행 (0) | 2020.03.05 |
[SQL] 내부와 업데이트 문은 오라클에 합류 (0) | 2020.03.05 |