BigQuery'de diziler ve iç içe veri: ARRAY, STRUCT, UNNEST

Bir hücrede birden çok değer

BigQuery, bir sütunda dizi (ARRAY) ve yapı (STRUCT) saklayabilir. Bu, JOIN gerektirmeden ilişkili veriyi tek satırda tutmayı sağlar; Google Analytics 4 dışa aktarımındaki event_params bunun en bilinen örneğidir.

Metni bölüp satırlara açmak

WITH notlar AS (
  SELECT 1 AS siparis, 'kupon,hediye paketi' AS etiketler UNION ALL
  SELECT 2, 'hızlı kargo'
)
SELECT n.siparis, etiket
FROM notlar AS n, UNNEST(SPLIT(n.etiketler, ',')) AS etiket;

FROM tablo, UNNEST(tablo.dizi) AS x yazımı her satırı kendi dizisinin elemanlarıyla birleştirir (korelasyonlu çapraz birleştirme).

Satırları diziye toplamak

SELECT customer_id,
       ARRAY_LENGTH(ARRAY_AGG(order_id)) AS siparis_sayisi,
       ARRAY_TO_STRING(ARRAY_AGG(status ORDER BY order_date DESC), ' > ') AS durum_gecmisi
FROM orders
WHERE customer_id <= 3
GROUP BY customer_id;

Takvim üretmek

SELECT gun, COUNT(e.event_id) AS olay
FROM UNNEST(GENERATE_DATE_ARRAY('2024-11-24', '2024-11-30')) AS gun
LEFT JOIN web_events e ON DATE(e.event_time) = gun
GROUP BY gun
ORDER BY gun;

STRUCT ve GA4 verisi

-- GA4 dışa aktarımında her olay, parametrelerini bir STRUCT dizisinde taşır
SELECT event_date,
       event_name,
       (SELECT value.string_value
        FROM UNNEST(event_params)
        WHERE key = 'page_location') AS sayfa
FROM `proje.analytics_123456.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20241101' AND '20241130'
  AND event_name = 'page_view';