我有一個包含 Orgin、Dest 和 Month 的航班資料。我試圖在一年中的一個月內獲得從起點到終點的航班頻率。
我已經搞定了：
dbGetQuery(conn,"
SELECT
Month AS Month,
COUNT(OriDes) AS Freq,
OriDes
FROM(
SELECT Origin || '-' || Dest AS OriDes,
Month AS Month
FROM flights
)
GROUP BY OriDes, Month
")
首先結合資料庫中的 Origin 和 Dest，然后計算它的頻率以獲得以下輸出：
Month Freq OriDes
1 1 123 ABE-ATL
2 2 140 ABE-ATL
3 3 117 ABE-ATL
4 4 112 ABE-ATL
5 5 111 ABE-ATL
6 6 120 ABE-ATL
7 7 113 ABE-ATL
8 8 124 ABE-ATL
9 9 94 ABE-ATL
10 10 119 ABE-ATL
11 11 116 ABE-ATL
12 12 124 ABE-ATL
13 6 1 ABE-AVP
14 1 66 ABE-CLE
15 2 72 ABE-CLE
16 3 99 ABE-CLE
17 4 89 ABE-CLE
18 5 91 ABE-CLE
19 6 97 ABE-CLE
20 7 92 ABE-CLE
但因為這是一個大資料，我希望過濾并只處理 Freq > 500。所以我嘗試了：
dbGetQuery(conn,"
SELECT
Month AS Month,
COUNT(OriDes) AS Freq,
OriDes
FROM(
SELECT Origin || '-' || Dest AS OriDes,
Month AS Month
FROM flights
)
GROUP BY OriDes, Month
WHERE Freq > 500
")
但我會收到一個錯誤：
Error: near "WHERE": syntax error
有人可以向我解釋我做錯了什么嗎？
uj5u.com熱心網友回復：
正如評論中建議的那樣：
SELECT
Month AS Month,
COUNT(OriDes) AS Freq,
OriDes
FROM(
SELECT Origin || '-' || Dest AS OriDes,
Month AS Month
FROM flights
)
GROUP BY OriDes, Month
HAVING COUNT(OriDes) > 500
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/448507.html