查詢指南與查詢範例

大量資料匯出查詢的最佳做法

一律使用匯總函式

我們不保證會依照日期、網址、網站或是任何索引鍵的組合來合併表格中的資料。因此，請務必匯總你的欄位 (SUM、COUNT 等)，以確保資料列的值正確合併。舉例來說，表格中可能有多個資料列含有「肥貓」查詢項目：

查詢     日期       搜尋類型   曝光次數 ...
肥貓 10-10-2022      網頁          1000
肥貓 10-10-2022      網頁           200
肥貓 10-10-2022    圖片          5000

如果只搜尋「肥貓」這個查詢項目，即使指定了特定日期和搜尋類型，還是有可能產生多個資料列。因此，建議你按照下列方式匯總資料，以便查看曝光次數總和：

/* 查看最熱門查詢的錯誤方法：每項查詢只會取得 1 個資料列 */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

結果：
肥貓   5000

/* 查看最熱門查詢的正確方式 */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* 過濾掉匿名查詢 */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

結果：
肥貓   6200

依日期限制查詢，以節省處理費用

提醒你，在 BigQuery 上執行查詢時，系統會向你收取查詢費用，此外，你的資料表可能會變得相當大。系統匯出的表格是按日期分區，可有效將輸入掃描的範圍限制在所要查詢的日子 - 對於大型網站而言，這麼做可大幅節省查詢費用。如要盡量減少查詢費用，最好的方法就是使用 WHERE 子句來限制日期分區資料表中的日期範圍。

過濾掉空值查詢字串

匿名查詢通常是網站最常見的單一查詢。匿名查詢在資料表中會記錄為長度為零的字串。如果你想根據特定條件查看最熱門的查詢，建議從 SQL 查詢中過濾掉長度為零的查詢值。

查詢範例

以下提供幾個查詢範例，協助你熟悉資料運用方式。

按網站顯示結果

所有網站統計資料，按日排序 (過去兩週)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Added one below, because position is zero-based */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

美國熱門行動版網站查詢，按點擊次數排序 (過去兩週)

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

按網址顯示結果

FAQ 複合式搜尋結果的查詢 + 網址統計資料總數 (過去兩週)

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

你的所有流量，按網址排序 (過去兩週)

SELECT
  url,
  search_type,
  sum(impressions) as impressions,
  sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

包含「<你的品牌>」的查詢曝光次數 (過去兩週)

SELECT
  regexp_contains(query, 'your_brand') AS contains_brand,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 2 desc
LIMIT 1000

這對您有幫助嗎？

我們應如何改進呢？