วิธีนับของที่อยู่ใน Array ของแต่ละแถวใน BigQuery

[zkan]

การนับฟังดูแล้วง่ายๆ บางครั้งเราเขียนโปรแกรมวนลูปนับไปก็เสร็จ แต่ในกรณีที่เรามีข้อมูลใหญ่มากๆ การวนลูปแบบนั้นก็อาจจะใช้เวลานานเป็นวัน

การเขียน SQL บน powerful data warehouse อย่าง BigQuery ก็เป็นทางเลือกที่ดีทางหนึ่ง เพราะเราใช้ infrastructure ของ Google ไปเลย ไม่ต้องห่วงเรื่องการ scale

ยกตัวอย่างถ้าเรามีข้อมูลอยู่ประมาณนี้

ถ้าใครอยากลองทำตามสามารถใช้ query ด้านล่างนี้ได้เลย

WITH data AS (
  SELECT ["A", "A", "B"] AS item
  UNION ALL
  SELECT ["B", "A", "C"] AS item
  UNION ALL 
  SELECT ["C", "C", "B"] AS item
)

SELECT * FROM data

ซึ่งแต่ละแถวเก็บ item ที่เป็นชนิด array ไว้ จะมีจำนวน item ที่แตกต่างกันก็ได้นะ ไม่จำเป็นต้องเท่ากันเหมือนในตัวอย่าง

ทีนี้ถ้าเราอยากจะนับว่า

• แถวที่ 1 มี A 2 มี B 1
• แถวที่ 2 มี A, B, C อย่างละ 1
• แถวที่ 3 มี B 1 และ C 2

เราเขียน query อย่างไรดี? เราสามารถเขียนแบบนี้ได้

SELECT
  ARRAY(
    SELECT AS STRUCT item, COUNT(*) AS c
  FROM data.item as item
  GROUP BY item
  ) t
FROM
  data

จะได้ผลแบบนี้

เรียบร้อย!

ซึ่งหลายคนที่ไม่คุ้น ถ้าได้เห็นก็อาจจะกรีดร้องเลยใช่ไหมครับ ว่ามันคืออะไร ผมขออธิบาย subquery ด้านในก่อน

SELECT AS STRUCT item, COUNT(*) AS c
FROM data.item as item
GROUP BY item

ตรง SELECT AS STRUCT หมายความว่าเรากำลังจะ select ข้อมูลออกมาให้อยู่ในรูปแบบของ STRUCT (เป็น data structure ที่เก็บ key กับ value) ที่จะมี key คือตัว item และมี value คือจำนวนนับของ key นั้นๆ เพราะว่าผม group by ตัว item นั่นเอง

แต่ประเด็นคือ subquery นี้จะอยู่โดดๆ อย่างเดียวดายไม่ได้ จำเป็นที่จะต้องมี query มาครอบอีกที สังเกตตรง data.item ตรงนี้ผมต้องใช้ select ข้อมูลจาก data มาครอบก่อน ถึงจะใช้ data ข้างใน subquery นี้ได้

ผลลัพธ์ที่ได้จาก subquery นี้ จะออกมาอยู่ในรูปแบบของ array ซึ่งใน BigQuery เวลาจะแสดงผลที่ได้จากผลลัพธ์ที่เป็น array แบบนี้ เราจะต้องเอา ARRAY มาครอบด้วย แล้วเราถึงค่อย select ซึ่ง query สุดท้ายก็จะได้ตามที่แปะไว้ด้านบนก่อนหน้านี้นั่นเอง

[zkan]

เพิ่มเติม ถ้าเราอยากจะทำคล้ายๆ กับ transpose ให้ item ที่เรานับกลายมาเป็นหัว column แทน เราสามารถเอา select มาครอบต่อได้แบบนี้

SELECT
  (SELECT c FROM UNNEST(t) WHERE item = 'A') AS A,
  (SELECT c FROM UNNEST(t) WHERE item = 'B') AS B,
  (SELECT c FROM UNNEST(t) WHERE item = 'C') AS C
FROM (
  SELECT
    ARRAY(
      SELECT AS STRUCT item, COUNT(*) AS c
      FROM data.item as item
      GROUP BY item
    ) t
  FROM
    data
)

ที่เราเอา select มาครอบอีกชั้นก็เพื่อที่จะดึงข้อมูลจำนวน c ออกมาจาก array ซึ่งตรงนี้เราจะใช้ UNNEST เพื่อรับ array ที่เราตั้งชื่อไว้ว่า t เสร็จแล้วก็ดึง c ออกมา ตรง WHERE เราก็ใส่ item ที่เราต้องการ และสุดท้ายเราก็ตั้งชื่อ column เราให้เป็นชื่อเดียวกับชื่อ item

ผลลัพธ์ที่ออกมาก็จะงดงามประมาณนี้เลย