我有一位很推崇的 Google 試算表大師,叫做
Ben Collins,是一位全方位的 Google 試算表和 Google Apps Script 的專家。我有固定訂閱他的 e-mail 週報,就在最近的一集,他提到了一個很酷的 QUERY 指令,甚至連官方文件都還沒有寫的秘技,叫
「SKIPPING」!
今天就來解說一下這個秘技,並且會提供一些我的實測結果、以及使用建議給各位參考,希望可以給大家更多使用 QUERY 的技巧。
我在方格子上寫了一系列使用 QUERY 的教學文章,也歡迎你來看看:
所以 SKIPPING 是什麼呢?
SKIPPING 可以讓 QUERY 每隔指定行數就抽取一行資料。
我們來快速看看 SKIPPING 的效果。假如我這邊有筆資料長這樣:
那假如我想要每隔 2 行抽取一行資料的話,就會像是這樣:
嗚喔喔喔喔喔喔!語法簡單但是威力強大啊!至於怎麼應用,我目前想到兩個方法:
- 在資料清理或分析的前置作業中,用 SKIPPING 來了解資料大致狀況
我們可以先用 SKIPPING 了解每個欄位的資料類型、大致會出現怎麼樣的資料,比起直接用 QUERY 開始爬資料又多了一層安心感。
- 已知資料庫有 ID,想要按照 ID 特性(只取奇數 ID、偶數 ID 等)抽取資料
例如你的資料有像是 ID-0001、ID-0002、ID-0003 等等的 ID,那你想要先取得有奇數 ID 的資料、再做後續處理的話,也可以用 SKIPPING 做到。
另外,SKIPPING 也可以和 WHERE、ORDER BY 等其他語句使用,但順序要注意,得放在 ORDER BY 和 LIMIT 中間。
欸?LIMIT 跟 OFFSET 呢?
對,雖然我們有 LIMIT 跟 OFFSET 可以用,取得前 N 筆、第 N 筆的資料,但是有時候總會覺得只挑特定 N 筆可能沒那麼有代表性(例如說:前 N 筆都是來自同一個日期、同一種客戶等等),想要看到資料庫的大致概況時, LIMIT 跟 OFFSET 或許就沒那麼適合。
那我們來看看語法怎麼寫吧!
語法
很單純,只要寫:
SKIPPING 你要抽取的間隔行數
如果你想要請 QUERY 每隔 100 行抽取資料,那就是:
SKIPPING 100
每隔 20 行,那就是:
SKIPPING 20
SKIPPING 可以寫在 SELECT 後面:
=QUERY(資料, "SELECT A, B, C SKIPPING 20")
--> 回傳 A、B、C 欄,並且每隔 20 行抽取一行資料
如果想要全選所有欄位,那也可以省略 SELECT,直接以 SKIPPING 開頭:
=QUERY(資料, "SKIPPING 20")
--> 回傳所有欄位,並且每隔 20 行抽取一行資料
如果你想要先用 WHERE 篩選條件、再抽取資料,也當然是可以的:
=QUERY(資料,"WHERE A = 'abc' SKIPPING 100'")
--> 回傳所有欄位,並且 A 欄是「abc」、每隔 20 行抽取一行資料
要注意的順序問題
經過實測,發現 SKIPPING 的順序落在 ORDER BY 之後、LIMIT 之前,也就是:
SELECT
WHERE
GROUP BY
PIVOT
ORDER BY
SKIPPING
LIMIT
OFFSET
LABEL
FORMAT
不過我個人覺得 SKIPPING 跟聚集類的語句(SUM()、AVG()、COUNT() 等)有點沒那麼搭,畢竟聚集類的語句使用目的是為了抓到全部資料的聚集資訊,加入 SKIPPING 處理的話,會先抽取資料、再做聚集,這麼一來可能很難得到太有代表性的數字,沒那麼理想。但或許你有很清楚的用途,那大膽用下去也沒問題!
歡迎大家打開 Google 試算表,找個資料庫自己練習看看吧!之後如果有什麼關於 SKIPPING 的新發現,也會在這跟大家聊聊。
如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
我是喜特先生,Mr. Sheet,我們下個教學見!