用SQL求得資料順位

原本是想要看看PHP Plurk API,在亂點roga部落格的MySQL分類時看到了roga大在去年六月有在想SQL的問題,於是心血來潮地試了一下。

(卻花了我兩個多小時,而這篇文不知道又要打多久了XD)

在仔細寫本文之前,先把結論呈現一下:DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id <= t2.id
) AS t3
GROUP BY id
) AS t4
WHERE tbl.id = t4.id
AND t4.place > 1500
其中tbl是roga大大的url_detail_history,單純是名字太長所以被我換掉。此法受限於資料庫系統對Multiple-table DELETE的支援度,已知MySQL支援。


以下詳述我的思考過程(有些是繞圈子,所以看起來可能跟結果沒啥關係)
roga原本的需求大致上是「資料表中url_id欄位相同的,只留下最新的1500筆」。而該資料表有一個`id`主鍵欄位是AUTO_INCREMENT。
我試著將問題從最簡單的模式開始推導:

第一步:求「指定url_id的最新資料」

這很直覺,使用Aggregate Function中的MAX()就可以了:SELECT MAX(id) FROM tbl WHERE url_id = 'XXXX'

第二步:求「各url_id最新的一筆」

也是基本題,加上GROUP BY就可以了:SELECT url_id, COUNT(*)
FROM tbl
GROUP BY url_id
雖然沒什麼困難,不過我們稍比較一下這個和前一個SQL後會發現:
  1. WHERE變成GROUP BY
  2. SELECT區塊也要多一欄url_id,也就是GROUP BY用的欄位


第三步:求「各url_id最新的n筆」

咦....慘了,沒頭緒,也許我們第二步不應該這樣走。回到第一步,把問題改成「指定url_id的最新n筆」。(注意到題目的不同嗎?我一次只變動題目的一部分,嘗試在這樣的步驟中統整出比較有系統的解法。)

新的第二步:求「指定url_id的最新n筆」

嗯....如果不用MAX()的話,確實不難做到:SELECT id
FROM tbl
WHERE url_id = 'XXXX'
ORDER BY id DESC
LIMIT n


還是第三步:求「各url_id最新的n筆」

....我還是不知道要怎麼做

我卡了一個多小時,直到想起多年前在網路上看到的某個題目:「用SQL將名次排序出來」,雖然已經忘了網址在哪,但是解法卻非常的有趣。(效率如何我就不清楚了):
假設資料表ss的資料如下:+---------+-------+
| student | score |
+---------+-------+
| 1 | 7 |
| 2 | 5 |
| 4 | 3 |
| 5 | 4 |
+---------+-------+
而我們要產出的結果為:+---------+-------+-------+
| student | score | place |
+---------+-------+-------+
| 1 | 7 | 1 |
| 2 | 5 | 2 |
| 4 | 3 | 4 |
| 5 | 4 | 3 |
+---------+-------+-------+
也就是學生1是第一名;學生5是第三名;...

試重新思考「名次」的定義。我們直覺上對於此詞彙的解釋是:「分數第n高者為第n名」,但這個作法則是將「第n名」界定為「有n個人(含自己)的分數跟自己相同或較高」。
依照這個想法,要排序名次,資料表就得跟自身比較。讓我們試寫「列出分數不比自己低的人」的指令,也就是:SELECT t1.student, t2.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
結果為:+------------+------------+
| t1.student | t2.student |
+------------+------------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 4 | 1 |
| 4 | 2 |
| 4 | 4 |
| 4 | 5 |
| 5 | 1 |
| 5 | 2 |
| 5 | 4 |
+------------+------------+

但其實我們並不需要知道「哪些人」分數不比自己低,而是要知道有「幾個人」,所以即使沒有顯示t2.student也沒有關係:SELECT t1.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
假設這張表叫做t3,那麼「列出分數不比自己低的人的總數」,SQL指令就是:SELECT student, COUNT(*)
FROM t3
GROUP BY student
(有沒有覺得最後的指令簡潔到令人吐血?)
當然啦,通常你不需要另存t3,而會把整串步驟寫成一個指令:SELECT student, COUNT(*) AS place
FROM (
SELECT t1.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
) t3
GROUP BY student
得出來的就會是名次列表

而如果資料表裡面的主鍵並不是單一欄位組成,比方說:+---------+---------+-------+
| student | subject | score |
+---------+---------+-------+
其中Primary Key為(student, subject),那麼題目就變成「各學生在各科目的排名」,SQL指令為:SELECT student, subject, COUNT(*)
FROM (
SELECT t1.student, t1.subject
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
AND t1.subject = t2.subject
) t3
GROUP BY student, subject


上述的作法遇到考試科目同分時會有些狀況,不過由於今天我只想針對roga大的問題,也就是對primary key做排序,所以就不管了。

根據以上的作法,我們就可以輕易得知每一筆資料的排序順位,也就可以很輕易的執行相關的操作。而像是「列出各科目中考最差的五名」這樣的問題,即使每個科目的修課人數不同,也只要將「名次」的定義改成「分數不比自己高的人數」就可以輕易做到了。
回歸正題,roga大的狀況就會變成:DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id <= t2.id
) AS t3
GROUP BY id
) AS t4
WHERE tbl.id = t4.id
AND t4.place > 1500
至於哪邊用大於,哪邊用小於,可得想清楚了....

(編輯終了,本文花了我一個半小時)

1 則留言:

roga 提到...

真詳細的解說 & 謝謝您的回覆 :)