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

**URL:** <https://discuss.dataengineercafe.io/t/array-bigquery/119>\
**Category:** BigQuery\
**Tags:** sql\
**Created:** [February 20, 2022, 8:09am UTC](https://discuss.dataengineercafe.io/t/array-bigquery/119 "2022-02-20T08:09:10Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![zkan](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.dataengineercafe.io/zkan/32/2_2.png) [@zkan](https://discuss.dataengineercafe.io/u/zkan)\
**Post date:** [February 20, 2022, 8:09am UTC](https://discuss.dataengineercafe.io/t/array-bigquery/119/1 "2022-02-20T08:09:10Z")

</div>

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

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

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

![Screen Shot 2565-02-20 at 14.57.06](https://canada1.discourse-cdn.com/flex035/uploads/dataengineercafe/original/1X/ce298831cb3a9a151b2540ecd5d51de4918b9b65.png)

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

```sql
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 อย่างไรดี? เราสามารถเขียนแบบนี้ได้

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

```

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

![Screen Shot 2565-02-20 at 15.03.01](https://canada1.discourse-cdn.com/flex035/uploads/dataengineercafe/original/1X/0241b2d287da1bea68f1a8444a6b43e45474a276.png)

เรียบร้อย! 🎉

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

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

```

ตรง `SELECT AS STRUCT` หมายความว่าเรากำลังจะ select ข้อมูลออกมาให้อยู่ในรูปแบบของ [STRUCT](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type) (เป็น 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 สุดท้ายก็จะได้ตามที่แปะไว้ด้านบนก่อนหน้านี้นั่นเอง

---

<div class="post-metadata">

**Author:** ![zkan](https://yyz1.discourse-cdn.com/flex035/user_avatar/discuss.dataengineercafe.io/zkan/32/2_2.png) [@zkan](https://discuss.dataengineercafe.io/u/zkan)\
**Post date:** [February 20, 2022, 8:14am UTC](https://discuss.dataengineercafe.io/t/array-bigquery/119/2 "2022-02-20T08:14:38Z")

</div>

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

```sql
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](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator) เพื่อรับ array ที่เราตั้งชื่อไว้ว่า `t` เสร็จแล้วก็ดึง c ออกมา ตรง `WHERE` เราก็ใส่ item ที่เราต้องการ และสุดท้ายเราก็ตั้งชื่อ column เราให้เป็นชื่อเดียวกับชื่อ item

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

![Screen Shot 2565-02-20 at 15.11.39](https://canada1.discourse-cdn.com/flex035/uploads/dataengineercafe/original/1X/51c276060651d6084307cf61156b1dafdc13a630.png)