Lekérdezési irányelvek és mintalekérdezések

Bevált módszerek a tömeges adatexportálási lekérdezésekhez

Mindig összesítő függvényeket használjon

Nem garantáljuk, hogy a táblázatokban az adatok dátum, URL, webhely vagy a kulcsok bármilyen kombinációja alapján összesítve szerepelnek. Ezért a sorértékek egységesítése érdekében mindig összesítse a mezőit (SUM, COUNT stb.). Tegyük fel például, hogy egy táblázatban több sor is van a „fat cats” lekérdezéssel:

query     date       search_type   impressions ...
fat cats 10-10-2022      WEB          1000
fat cats 10-10-2022      WEB           200
fat cats 10-10-2022    IMAGE          5000

Ha egyszerűen rákeres a „fat cats” lekérdezésre, akár egy adott dátumra és keresési típusra vonatkozóan is több sort kaphat eredményül. A megjelenítések teljes összegét a következőképpen összesítheti:

/* BAD way to see most popular query: will get only 1 row per query */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Eredmény:
fat cats   5000

/* RIGHT way to see the most popular query */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Filter out anonymized queries */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Eredmény:
fat cats   6200

Korlátozza a lekérdezéseket dátum szerint a feldolgozási költségek csökkentéséhez

Ne feledje, hogy BigQuery-lekérdezés futtatásáért díjat számolunk fel, a táblázatok pedig meglehetősen nagyok lehetnek. Az exportált táblázatok dátum szerint vannak felosztva, ami lehetővé teszi a bemenő keresések hatékony korlátozását csak az Önt érdeklő napokra. A nagy webhelyek esetében ez igazi megtakarítást eredményezhet a lekérdezési költségek terén. A lekérdezési költségeket úgy küszöbölheti ki a legkönnyebben, ha a dátumalapú táblázatban korlátozza a dátumtartományt.

Szűrje ki a null értékű lekérdezési karakterláncokat

Gyakran anonimizált lekérdezésekből áll a webhely leggyakoribb lekérdezése. A táblázatban egy anonimizált lekérdezés jelenik meg nulla hosszúságú karakterláncként. Ha meg szeretné tekinteni a leggyakoribb lekérdezést bizonyos feltételek esetén, érdemes lehet kiszűrni a nulla hosszúságú lekérdezésértékeket az SQL-lekérdezésből.

Mintalekérdezések

Az alábbiakban néhány mintalekérdezést talál, amelyek segítenek az adatok használatának megkezdésében.

Találatok webhely szerint

Összes webes statisztika nap szerint (az elmúlt két hétből)

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

A legnépszerűbb USA-beli mobilwebes lekérdezések kattintások szerint (az elmúlt két hétből)

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

Találatok URL szerint

A legnépszerűbb Discover-URL-ek kattintások szerint (egy adott napon)

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

A GYIK bővített találataira vonatkozó lekérdezések és URL-ek összesített statisztikája (az elmúlt két hétből)

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

A teljes forgalom URL szerint (az elmúlt két hétből)

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

A „your_brand” kifejezést tartalmazó lekérdezések megjelenítései (az elmúlt két hétből)

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