MySQL ว่าด้วยลำดับและการจัดอันดับข้อมูล

เดิมทีแล้ว MySQL ไม่มีฟังก์ชั่น (Row Number) สำหรับเรียกลำดับที่ (1, 2, 3 …n) ของแถว/เร็คคอร์ดในฐานข้อมูลเหมือนดั่งเช่นเจ้าอื่น ๆ เพราะฉนั้นเหล่านักรบส่วนใหญ่ก็มักสร้างฟังก์ชั่นใส่ลำดับที่แถวเอง

สมมุติเรามีชุดข้อมูลนึงแสดงคะแนนของแต่ละอำเภอ (เอ๊ะ !! คุ้น ๆ ) ตามนี้

CREATE TABLE IF NOT EXISTS `rstanding` (

`id` int(6) unsigned NOT NULL,

`name` varchar(20) NOT NULL,

`score` decimal(10,2) NOT NULL,

PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8;

INSERT INTO `rstanding` (`id`, `name`, `score`) VALUES

('1', 'A', 98.52),

('2', 'B', 97.24),

('3', 'C', 96.33),

('4', 'E', 96.33),

('5', 'F', 95.02),

('6', 'G', 94.88),

('7', 'H', 94.88);

CREATE TABLE IF NOT EXISTS `rstanding` ( `id` int(6) unsigned NOT NULL, `name` varchar(20) NOT NULL, `score` decimal(10,2) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `rstanding` (`id`, `name`, `score`) VALUES ('1', 'A', 98.52), ('2', 'B', 97.24), ('3', 'C', 96.33), ('4', 'E', 96.33), ('5', 'F', 95.02), ('6', 'G', 94.88), ('7', 'H', 94.88);

CREATE TABLE IF NOT EXISTS `rstanding` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `rstanding` (`id`, `name`, `score`) VALUES
  ('1', 'A', 98.52),
  ('2', 'B', 97.24),
  ('3', 'C', 96.33),
  ('4', 'E', 96.33),  
  ('5', 'F', 95.02),
  ('6', 'G', 94.88),
  ('7', 'H', 94.88);

การใส่ลำดับที่ของแถวก็จะสร้างฟังก์ชั่นเพื่อนับจำนวนของแถวไปจนสิ้นสุดชุดข้อมูล

SELECT

@rownum := @rownum + 1 AS row_number,

t1.*

FROM rstanding t1,

(SELECT @rownum := 0) r

SELECT @rownum := @rownum + 1 AS row_number, t1.* FROM rstanding t1, (SELECT @rownum := 0) r

SELECT 
    @rownum := @rownum + 1 AS row_number,
    t1.*
FROM rstanding t1, 
(SELECT @rownum := 0) r

จะเห็นได้ว่าคอลัมภ์ row_number เพิ่มขึ้นมาโดยเป็นลำดับที่แถว/เรคคอร์ดนั้น ๆ ในที่นี้เราไม่ได้จัดเรียง (ORDER BY Field [ASC/DESC]) ชุดข้อมูลด้วยคอลัมภ์ใด ๆ

ต่อเนื่องในเรื่องของอันดับ

อันดับ เป็นชื่อของตำแหน่ง ที่มีคำบอกระดับของ ขั้น,ชั้น ที่บ่งบอกถึงความต่างระดับ ซึ่งเป็นผลพวงมาจากผลงานที่มีการประกวดหรือแข่งขัน

การใส่ลำดับที่โดยการจัดเรียงตามคะแนน (คอลัมภ์ score) จากคะแนนสูงสุดไปหาน้อยสุด

ตัวอย่างวิธีที่ 1 ใช้ฟังก์ชั่นเดิมแต่เพิ่มเติมด้วยการใส่เงื่อนไขเพื่อให้จัดลำดับได้

SELECT

@rownum := @rownum + 1 AS row_number,

@ranknum := IF(t1.score < @score, @ranknum + 1, @ranknum) AS row_ranking,

t1.*,

@score := t1.score AS dummy

FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r

ORDER BY t1.score DESC

SELECT @rownum := @rownum + 1 AS row_number, @ranknum := IF(t1.score < @score, @ranknum + 1, @ranknum) AS row_ranking, t1.*, @score := t1.score AS dummy FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r ORDER BY t1.score DESC

SELECT 
    @rownum := @rownum + 1 AS row_number,
    @ranknum := IF(t1.score < @score, @ranknum + 1, @ranknum) AS row_ranking,    
    t1.*,
@score := t1.score AS dummy
FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r
ORDER BY t1.score DESC

ผลลัพธ์การจัดเรียงก็จะได้ตามนี้

จากผลลัพธ์ที่ได้ในวิธีที่ 1 เราสามารถกรองได้ว่าเราสนใจลำดับที่ได้ลำดับอะไรบ้าง เช่นต้องการทราบว่าคนที่ได้ลำดับที่ 3 มีใครบ้างก็สามารถกรองได้จากคอลัมภ์

WHERE row_ranking = ?

WHERE row_ranking = ?

SELECT

t2.*

FROM (

SELECT

@rownum := @rownum + 1 AS row_number,

@ranknum := IF(t1.score < @score,

@ranknum + 1, @ranknum) AS row_ranking,

t1.*,

@score := t1.score AS dummy

FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r

ORDER BY t1.score DESC

) t2

WHERE t2.row_ranking = 3

SELECT t2.* FROM ( SELECT @rownum := @rownum + 1 AS row_number, @ranknum := IF(t1.score < @score, @ranknum + 1, @ranknum) AS row_ranking, t1.*, @score := t1.score AS dummy FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r ORDER BY t1.score DESC ) t2 WHERE t2.row_ranking = 3

SELECT
    t2.*
FROM (
  SELECT 
      @rownum := @rownum + 1 AS row_number,
      @ranknum := IF(t1.score < @score, 		
      @ranknum + 1, @ranknum) AS row_ranking,    
      t1.*,
      @score := t1.score AS dummy
  FROM rstanding t1 , (SELECT @score:=0, @rownum:=0, @ranknum :=1) r
  ORDER BY t1.score DESC
) t2 
WHERE t2.row_ranking = 3

ตัวอย่างวิธีที่ 2 กรณีที่สนใจลำดับที่นั้น ๆ ก็นับจำนวน (แบบไม่ซ้ำ) ในลำดับก่อนหน้า เช่น สนใจลำดับที่ 3 ลำดับก่อนหน้าคือ 2 ลำดับ

SELECT

t1.*

FROM rstanding t1

WHERE 2 = (SELECT COUNT(DISTINCT t2.score) FROM rstanding t2 WHERE t1.score < t2.score)

SELECT t1.* FROM rstanding t1 WHERE 2 = (SELECT COUNT(DISTINCT t2.score) FROM rstanding t2 WHERE t1.score < t2.score)

SELECT
    t1.*
FROM rstanding t1
WHERE 2 = (SELECT COUNT(DISTINCT t2.score) FROM rstanding t2 WHERE t1.score < t2.score)

ตัวอย่างวิธีที่ 3 หาจำนวน LIMIT

LIMIT offset, count;

LIMIT offset, count;

SELECT

t1.*

FROM rstanding t1

WHERE t1.score = (SELECT t2.score FROM rstanding t2 ORDER BY t2.score DESC LIMIT 3,1);

SELECT t1.* FROM rstanding t1 WHERE t1.score = (SELECT t2.score FROM rstanding t2 ORDER BY t2.score DESC LIMIT 3,1);

SELECT
    t1.*
FROM rstanding t1
WHERE t1.score = (SELECT t2.score FROM rstanding t2 ORDER BY t2.score DESC LIMIT 3,1);

ตัวอย่างวิธีที่ 4 ทุกอย่างง่ายขึ้น MySQL เห็นคงเห็นว่าจะทำให้ยุ่งยากไปใยเล่า เรามีฟังก์ชั่นให้ใช้นะ แต่ช้าก่อน มันมีในเวอร์ชั่น 8 เป็นต้นไปนะจ๊ะ เวอร์ชั่นก่อนหน้าก็อด

ROW_NUMBER() Number of current row within its partition
DENSE_RANK() Rank of current row within its partition, without gaps
RANK() Rank of current row within its partition, with gaps

ดูวิธีการใช้งานกันเลย

WITH ranked_score AS (

SELECT

ROW_NUMBER() OVER (ORDER BY score DESC) rownum,

RANK() OVER (ORDER BY score DESC) row_ranking,

DENSE_RANK() OVER (ORDER BY score DESC) drow_ranking,

id,

name,

score

FROM rstanding

)

SELECT * FROM ranked_score; -- WHERE drow_ranking = 3;

WITH ranked_score AS ( SELECT ROW_NUMBER() OVER (ORDER BY score DESC) rownum, RANK() OVER (ORDER BY score DESC) row_ranking, DENSE_RANK() OVER (ORDER BY score DESC) drow_ranking, id, name, score FROM rstanding ) SELECT * FROM ranked_score; -- WHERE drow_ranking = 3;

WITH ranked_score AS (
  SELECT 
      ROW_NUMBER() OVER (ORDER BY score DESC) rownum,
      RANK() OVER (ORDER BY score DESC) row_ranking,
      DENSE_RANK() OVER (ORDER BY score DESC) drow_ranking,
      id,
      name,
      score
  FROM rstanding
)
SELECT * FROM ranked_score; -- WHERE drow_ranking = 3;

ผลลัพธ์กรณีที่เราไม่กรองใด ๆ ก็จะแสดงผลดังนี้

จบปิ๊ง ^_^
#แด่ตัวสำรองอันดับหนึ่ง

ป.ล.

ไปลองเล่นกันได้ที่ https://www.db-fiddle.com/f/j4JjnrxbgTVePxu4XpRQuU/0
แปะเพลงดัก “ที่หนึ่งไม่หวายยยยยยยยยยย”

Published by Man Friday