關於 MySQL 中使用 AVG 聚合函數與 LIMIT 的問題

昨晚檢查一周來的財工程式執行數據, 發現計算出來的平均值不正確, 在 Google 搜尋過後才搞清楚, 原來 MySQL 的 LIMIT 功能不是我想的那樣, 它是在最後才 apply 的, 例如下面的 SQL :

"SELECT AVG(close) FROM ".$table." ORDER BY trade_date DESC LIMIT 5";

MySQL 會先對 close 欄位的所有數據取平均值後, 再取前面五筆, 由於平均值結果只有一筆, 所以也就得到一筆資料, 但卻是全部 close 的平均, 而非最近五筆 close 的平均, 參考下列文章 :

# Select average from MySQL table with LIMIT
# Subquery with limit clause : Sub query « Select Clause « SQL / MySQL
# Limit not working for avg query
# avg() and limit

"you have to consider how LIMIT works. It's the last thing that happens as the query is executed."

所以, 上面所有計算平均值的 SQL 指令都必須修改, 例如 MA5 要改成 :

      $SQL="SELECT AVG(items.close) FROM (SELECT close FROM ".$table.
           " ORDER BY trade_date DESC LIMIT 5) AS items";

亦即要採取 sub-query 的兩層查詢方法, 先用一個 SELECT ... LIMIT  5 指令找出最近的五筆紀錄, 並命名為 items, 再用 AVG 去計算 close 欄位的平均值.

以上資料亦同步修改之前的文章, 參見 :

# 用 PHP 計算移動平均線 MA 與指數移動平均線 EMA 的方法