Toplamalar

BigQuery'ye Giriş

Matthew Forrest

Field CTO

BigQuery’de toplamalar

SELECT
  SUM(sales) AS total_sales,
  AVG(quantity) AS avg_quantity,
  MAX(price) AS max_price,
  MIN(price) AS min_price
FROM sales_data;

| total_sales | avg_quantity | max_price | min_price |
|-------------|--------------|-----------|-----------|
| 50          | 8            | 100       | 2         |

Büyük veri kümelerini özetleyin
Eğilim ve örüntüleri belirleyin
BigQuery, toplamalar için optimize edilmiştir

GROUP BY ve ORDER BY

SELECT
  -- Daha sonra 'order_id' sütununa göre gruplayacağız
  order_id,
  SUM(sales) AS order_total
FROM total_sales
-- Burada sorguyu GROUP ve ORDER edebiliriz
GROUP BY order_id
ORDER BY order_total DESC;

| order_id | order_total |
|----------|-------------|
| 1        | 500         |
| 2        | 850         |

COUNT

SELECT
  category,
  -- Dönen satır sayısını COUNT ile hesaplayın
  COUNT(order_id) AS record_count
FROM total_sales
GROUP BY category;

| category    | record_count |
|-------------|--------------|
| shoes       | 238          |
| electronics | 183          |

SUM ve AVG

SELECT
  category,
  SUM(cost) AS total_cost,
  AVG(cost) AS average_payment
FROM total_sales
GROUP BY category;

| category    | total_cost | avg_cost |
|-------------|------------|----------|
| shoes       | 10345      | 54       |
| electronics | 9340       | 34       |

MIN ve MAX

SELECT
    MIN(product_photos_qty) as min_photo_count,
    MAX(product_photos_qty) as max_photo_count
FROM ecommerce.ecomm_products;

| min__photo_count | max__photo_count |
|----------|----------|
| 1    | 20 |

COUNTIF

SELECT
  category,
  -- Yalnızca maliyet 500$ üzerindeyse sayar
  COUNTIF(cost > 500) AS large_items
FROM total_sales
GROUP BY category;

| category    | large_items |
|-------------|-------------|
| shoes       | 2           |
| electronics | 35          |

HAVING

SELECT
category,
COUNT(order_id) as orders
FROM total_sales
-- Ortalama maliyeti 75$ üzerindeki kategoriler için koşul ekliyoruz
HAVING AVG(cost) > 75;

| category    | orders |
|-------------|--------|
| shoes       | 25     |
| electronics | 98     |

ANY_VALUE

SELECT
  order_id,
  -- Rastgele bir kategoriyi döndürür
  ANY_VALUE(category) as cat
  -- En yüksek maliyetli kategoriyi döndürür
  ANY_VALUE(category HAVING MAX cost) AS max_cat
FROM total_sales
GROUP BY order_id;

| order_id | cat       | max_cat     |
|----------|-----------|-------------|
| 1        | shoes     | electronics |
| 2        | household | exercise    |

Haydi pratik yapalım!

BigQuery'ye Giriş