[SQL] 找median

数据科学 SQL
#1

SQL問題: 找median (原題link )

我的思路是先用window function排序，若行數是奇數，則取排序為(行數+1)/2的數; 若行數是偶數，則取排序為行數/2和行數/2+1的數的平均

但我用以下query在sqliteonline的PostgreSQL運行，得到錯誤訊息"CASE types record and bigint cannot be matched"，看不懂這個錯誤，請問老師該如何修改? 這樣的思路有更精簡的寫法嗎?

SELECT ROUND(AVG(LAT_N), 4)
FROM (SELECT LAT_N, ROW_NUMBER() OVER(ORDER BY LAT_N) AS rnk
      FROM STATION) A
WHERE rnk IN (SELECT CASE WHEN COUNT(*) % 2 != 0 THEN (COUNT(*) + 1) / 2
        			      ELSE (COUNT(*) / 2, COUNT(*) / 2 + 1) END
              FROM STATION)
#2
  1. 你在hackerrank上选mysql也是可以跑的，你写的query其实用的都是通用的关键词，没有什么特殊的function。
  2. 报错是说你的case when里面的type有问题，你的else里面想返回的是两个数，但是case when then只能返回一个值
  3. 按你的思路简洁一点的话这样写(除法我这里认为是小数除法，例如11/2=5.5而不是5)：
SELECT ROUND(AVG(LAT_N), 4)
FROM (SELECT LAT_N, 
             ROW_NUMBER() OVER(ORDER BY LAT_N) AS rnk, 
             count(*) OVER() as cnt
      FROM STATION) A
WHERE rnk between cnt/2 and cnt/2 + 1
#3

謝謝老師的回答

是因為我的query在hackerrank上顯示runtime error，所以才去sqlite上找原因。原來case when只能返回一個值!

不過依照老師的寫法，當cnt是奇數時，不會返回正確的median，cnt為奇數的狀況該如何處理呢?

#4

奇数也work的，你自己拿个例子试一下就明白了。

#5

謝謝老師，我試了行數為6時，rnk是between 3 and 4, 但行數為5時，rnk是between 2 and 3, 不太明白，我們不是只要rnk = 3的數嗎?

原來老師這裡用的除法是小數除法，當行數為5時，rnk是between 2.5 and 3.5，所以只會取rnk = 3的數
但我試了幾個SQL database, 除法都不是小數除法，所以這樣寫rnk是between 2 and 3

#6

那就*1.0不就变成了小数了嘛…