BigQuery ดึงแถวข้อมูลที่ซ้ำกัน ตามค่าของคอลัมน์
Toy
พี่ทอย ผมอยากดึงข้อมูลล่าสุด แต่มันมีมากกว่า 1 แถวจะทำยังไงดีพี่?
เดี๋ยวพี่จะเล่าให้ฟังๆ
ทุกคนเคยเจอปัญหา การมีข้อมูลที่ซ้ำซ้อนกัน หรือข้อมูลที่มีการอัพเดทอยู่ในฐานข้อมูลของเรา แล้วเราจะใช้เฉพาะอันที่อัพเดทล่าสุดบ้างไหมครับ ยกตัวอย่างเช่น ตารางด้านล่าง เราอยากจะได้ข้อมูลเฉพาะแถวที่มีการไฮไลท์ไว้ เพื่อนำไปทำงานต่อ แล้วเราสามารถจัดการมันได้อย่างไรบ้าง วันนี้เราจะมาแนะนำ พร้อมลงมือทำไปด้วยกันครับ
| post_id | message | engagement | updated_at |
|---|---|---|---|
| 10201_024105 | Qualify vs Rank in BigQuery | 111 | 2024-05-10 |
| 10201_024105 | Qualify vs Rank in BigQuery | 114 | 2024-05-11 |
| 10201_024105 | Qualify vs Rank in BigQuery | 127 | 2024-05-12 |
| 10201_024105 | Qualify vs Rank in BigQuery | 134 | 2024-05-14 |
| 10201_024105 | Qualify vs Rank in BigQuery | 165 | 2024-05-15 |
| 10201_024188 | Optimize query with qualify | 18 | 2024-05-09 |
| 10201_024188 | Optimize query with qualify | 29 | 2024-05-10 |
| 10201_024197 | Qualify + Row_number | 301 | 2024-05-11 |
| 10201_024197 | Qualify + Row_number | 481 | 2024-05-12 |
| 10201_024197 | Qualify + Row_number | 776 | 2024-05-13 |
1. Introduction
ปกติแล้วข้อมูลทั่วไป ก่อนจะใช้งานจะต้องมีการคลีนข้อมูลเบื้องต้น ซึ่งอาจจะไม่เจอปัญหาตามที่กล่าวไว้ แต่สำหรับคนที่ทำงานกับข้อมูลที่มีการอัพเดทตัวเองซ้ำกัน ๆ ยกตัวอย่าง เช่น การดึงข้อมูลการเข้าสู่ระบบของผู้ใช้งาน หรือแม้กระทั่งค่าสถิติต่าง ๆ ของ Social Media Post ที่มีการอัพเดทตัวเองตามวันที่เราดึงข้อมูล เราจึงจะเป็นที่จะต้องถึงข้อมูลล่าสุดมาแสดงผล มีวิธีการทำที่หลากหลายที่ได้ผลลัพธ์เหมือนกัน
ก่อนที่จะเริ่มต้น เราสามารถสร้าง Table ได้ตามคำสั่งสั่งด้านล่างนี้ เพื่อใช้ในการรันคำสั่งชุดต่อไป
/* Step 1: Create the table */
CREATE TABLE IF NOT EXISTS project_id.dataset.facebook_insight (
post_id STRING,
message STRING,
engagement INT64,
updated_at DATE
);
/* Step 2: Insert the data */
INSERT INTO project_id.dataset.facebook_insight (post_id, message, engagement, updated_at)
VALUES
('10201_024105', 'Qualify vs Rank in BigQuery', 111, '2024-05-10'),
('10201_024105', 'Qualify vs Rank in BigQuery', 114, '2024-05-11'),
('10201_024105', 'Qualify vs Rank in BigQuery', 127, '2024-05-12'),
('10201_024105', 'Qualify vs Rank in BigQuery', 134, '2024-05-14'),
('10201_024105', 'Qualify vs Rank in BigQuery', 165, '2024-05-15'),
('10201_024188', 'Optimize query with qualify', 18, '2024-05-09'),
('10201_024188', 'Optimize query with qualify', 29, '2024-05-10'),
('10201_024197', 'Qualify + Row_number', 301, '2024-05-11'),
('10201_024197', 'Qualify + Row_number', 481, '2024-05-12'),
('10201_024197', 'Qualify + Row_number', 776, '2024-05-13');
โดยปกติการ query สำหรับหาข้อมูล หรือเปลี่ยนแปลงข้อมูล ให้อยู่ในรูปแบบที่ใช้งานได้ กรณีที่เรามีข้อมูลแถวที่ซ้ำกันแต่ต้องการดึงเฉพาะแถวที่อยู่ล่าสุด สามารถทำได้ทั้งหมด 3 วิธี บางวิธีสามารถประยุกต์ใช้ร่วมกันได้ด้วย
1. Rank
วิธีแรก คือการใช้ RANK () ในการทำเรียงลำดับข้อมูล โดย Rank ถือเป็น Analytic Function จะไม่สามารถใช้งานใน Where clause ได้ เราจึงจำเป็นจะต้องทำเป็น CTE หรือ Subquery แล้วค่อยใส่ชื่อตัวแปรเพื่อกำหนด Rank ที่ต้องการ
WITH master as (
SELECT
post_id, message, engagement, updated_at ,
RANK () OVER
( PARTITION BY post_id ORDER BY updated_at desc) as lastest_updated
FROM
project_id.dataset.facebook_insight
)
SELECT
* except (lastest_updated)
FROM
master
WHERE lastest_updated = 1คำสั่ง SQL ด้านบน ก็คือการสร้างตารางด้านบน โดยเพิ่มคอลัมน์ lastest_updated เพื่อใช้ในการฟิลเตอร์ใน SELECT Statement ด้านล่าง โดยเราจะให้มันแบ่งตาม post_id แล้วมาเช็คว่า post_id ไหนที่มี updated_at เป็นค่ามากที่สุด ให้แสดงผลเป็นอันดับแรก แล้วเรียงลงมาเรื่อย ๆ ตามลำดับ
2. Row Number
WITH master as (
SELECT
post_id, message, engagement, updated_at ,
ROW_NUMBER () OVER
( PARTITION BY post_id ORDER BY updated_at desc) as lastest_updated
FROM
project_id.dataset.facebook_insight
)
SELECT
* except (lastest_updated)
FROM
master
WHERE lastest_updated = 1วิธีที่สองคือการใช้ ROW_NUMBER ( ) ในการเรียงลำดับข้อมูล โดยจะใช้ Query คล้ายๆ กับ RANK () แต่ ROW_NUMBER () จะมี performance ที่ดีกว่า เพราะทำหน้าที่แค่เพียง assign ค่า แต่ก็ยังไม่สามารถใช้ใน Where clause โดยไม่มีการทำ Subquery ไม่ได้ เช่นกัน
| value | row_number | rank | dense_rank |
|---|---|---|---|
| 10 | 1 | 1 | 1 |
| 10 | 2 | 1 | 1 |
| 10 | 3 | 1 | 1 |
| 11 | 4 | 4 | 2 |
| 12 | 5 | 5 | 3 |
row_number จะคืนค่าเฉพาะในแต่ละแถว แบบไม่ซ้ำกัน
rank จะคืนค่าตามลำดับ หากค่าเท่ากัน จะมีลำดับเหมือนกัน และค่าต่อไปจะนับตามจำนวนแถวที่มีก่อนหน้านั้น
dense_rank จะคืนค่าตามลำดับ หากค่าเท่ากัน จะมีลำดับเหมือนกัน ค่าต่อไปจะเป็นค่าต่อเนื่องจากค่าล่าสุด
3.Qualify
อีกวิธีที่สามารถทำได้ คือการใช้ Qualify ในการเลือกข้อมูล ซึ่งวิธีนี้จะสั้นกว่าวิธีแรกมาก ๆ และไม่จำเป็นต้องเขียน Subquery เพิ่ม ซึ่งในรูปแบบนี้ที่แสดงผลด้านล่างจะมีการใช้ MAX () ในการกำหนดแถวด้วยคอลัมน์ที่ต้องการ ทำให้ Performance ในการ Query ช้าลง เผลอ ๆ อาจจะใกล้ ๆ การใช้ RANK ( )
SELECT
post_id, message, engagement, updated_at
FROM
project_id.dataset.facebook_insight
qualify updated_at = max(updated_at) OVER (PARTITION BY post_id)4.Qualify and Row Number
แต่การใช้ Qualify จริงๆ มีข้อเสียตรง performance query จะช้ากว่า Row_number () แต่ถ้าเราใช้ผสมกันก็จะทำให้ Performance ดีขึ้น และเขียนได้สั้นลง ด้านตัวอย่างล่าง
SELECT
post_id, message, engagement, updated_at
FROM
project_id.dataset.facebook_insight
QUALIFY ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY updated_at DESC) = 1
ตัวเลข Elapsed time นับเป็นมิลลิวินาที ที่ใช้ในการ Query ยิ่งมีจำนวนมาก ยิ่งช้า ส่วนตัวเลข Slot time consumed เป็นส่วนประมวลผลของ unit ที่ใช้ในการคำนวณ ยิ่งน้อย ยิ่งประหยัด ทั้งนี้ข้อมูล Table ที่เรา Query หากมีการทำ PARTITION หรือ CLUSTER และมีการฟิลเตอร์ข้อมูลที่ดีก็จะทำให้เราสามารถดึงข้อมูลได้อย่างมีประสิทธิภาพ และลดค่าใช้จ่ายได้ดียิ่งขึ้น
สรุปเนื้อหา
การดึงข้อมูลแถวสุดท้ายของตาราง สามารถดึงได้หลายวิธี การใช้ ROW_NUMBER จะช่วยในเรื่องของ Performance ที่ดี แต่เราควรจะต้องตรวจสอบก่อนว่าข้อมูลของเรา ไม่มีแถวที่มีข้อมูลซ้ำกัน ในกรณีที่ข้อมูลซ้ำกัน จะเลือกใช้ RANK หรือ DENSE_RANK ก็ได้ตามความเหมาะสม แต่ถ้าอยากเขียนสั้น ๆ ให้เขียน Qualify คู่กับ ROW_NUMBER หรือ RANK จะได้ไม่ต้องเขียน CTE และ Subquery ยาว ๆ
Share your experience with this Blog
