การรวมกลุ่มข้อมูลแบบพิเศษใน BigQuery

BigQuery เบื้องต้น

Matthew Forrest

Field CTO

แนะนำฟังก์ชัน aggregate แบบพิเศษ

ฟังก์ชัน	หมวดหมู่	คำอธิบาย
`APPROX_COUNT_DISTINCT`, `APPROX_QUANTILES`, `APPROX_TOP_COUNT`, `APPROX_TOP_SUM`	Approximate Aggregation	ประมาณค่าสำหรับการคำนวณบางประเภท ช่วยลดเวลาและทรัพยากรที่ใช้
`ARRAY_CONCAT_AGG`, `STRING_AGG`	Array and String Manipulation	รวบรวม เชื่อมต่อ และจัดการ array และ string
`LOGICAL_AND`, `LOGICAL_OR`	Logical Operations	ประเมินการดำเนินการ AND และ OR เชิงตรรกะบนชุด boolean expression

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG() แก้ข้อจำกัดของ ARRAY_AGG()

SELECT
  order_id,
  ARRAY_CONCAT_AGG(order_items) AS all_items
FROM sales_data
GROUP BY order_id;

| order_id | all_items                          |
|----------|------------------------------------|
| 1        | [shoes, electronics]               |
| 2        | [electronics, household, clothing] |

STRING_AGG

STRING_AGG() เชื่อมต่อ string หลายค่าเป็น string เดียว

SELECT STRING_AGG(customer_id, ', ') AS all_customers
FROM sales_data
WHERE delivery_date 
BETWEEN CURRENT_TIMESTAMP() 
AND TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 DAY) ;

| all_customers   |
|-----------------|
| 123, 456, 789   |

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT() ประมาณจำนวนค่าที่ไม่ซ้ำกันอย่างรวดเร็ว

SELECT
  customer_id,
  APPROX_COUNT_DISTINCT(order_id) AS unique_orders
FROM sales_data
GROUP BY customer_id;

| customer_id | unique_orders |
|-------------|---------------|
| 1           | 5             |
| 2           | 2             |

APPROX_QUANTILES

APPROX_QUANTILES() ให้ค่า quantile โดยประมาณ

SELECT
  category,
  APPROX_QUANTILES(value, 4) AS quartiles
FROM sales_data
GROUP BY category;

| category    | quartiles         |
|-------------|-------------------|
| shoes       | [25, 40, 55, 100] |
| electronics | [10, 40, 95, 300] |

APPROX_TOP_COUNT

APPROX_TOP_COUNT() ระบุ K องค์ประกอบที่ปรากฏบ่อยที่สุด

SELECT
  category,
  APPROX_TOP_COUNT(customer_id, 3) AS customers
FROM sales_data
GROUP BY category;

| category    | customers   |
|-------------|-------------|
| shoes       | [1, 7, 19]  |
| electronics | [8, 19, 22] |

APPROX_TOP_SUM

APPROX_TOP_SUM(el, weight, K) หา K องค์ประกอบ el ที่มีค่า weight สูงสุด

SELECT
  seller_id,
  APPROX_TOP_SUM(item_id, cost, 3) AS top_items
FROM sales_data
GROUP BY seller_id;

| seller_id | top_items                                    |
|-----------|----------------------------------------------|
| 1         | [[1024, 5000], [1567, 3000], [3489, 2000]]   |
| 2         | [[5647, 6500], [9867, 3500], [1074, 2100]]   |

LOGICAL_AND และ LOGICAL_OR

SELECT
  customer_id,
  -- true if ALL are true
  LOGICAL_AND(order_status = 'shipped') AS all_shipped,
  -- true if at least one is true
  LOGICAL_OR(order_status = 'shipped') AS one_shipped
FROM sales_data
GROUP BY customer_id;

| customer_id | all_shipped | one_shipped |
|-------------|-------------|-------------|
| 1           | false       | true        |
| 2           | true        | true        |

มาฝึกกันเถอะ!

BigQuery เบื้องต้น