เทคนิค SQL Investigation อาวุธลับของเหล่า QA

หลังจากหายหน้าหายตาไปซักพักกับงานยุ่งๆๆ คราวนี้เรามาเรียนรู้กับการใช้งานสิ่งสําคัญอีกอย่างนึงของ QA ที่ขาดไม่ได้เลย ซึ่งนั้นก็คือการใช้ SQL statements นั้นเอง 🙂

ทําไมถึงสําคัญ??? อย่างที่เรารู้กันว่า QA คือบุคคลที่จําเป็นต้องมีความเข้าใจในระบบอย่างมาก เข้าใจทุกจุดของของระบบเลย เราจะเข้าใจภาพกว้างของระบบ แต่อาจจะไม่ต้องลึกถึงขั้นโค้ดนี้มี Performance ยังไงหรอกน่ะ แต่ต้องเข้าใจเรื่องของ quality, constraint และ overall design ระบบ เช่น

• ระบบ retry กี่ครั้ง? timeout ภายในกี่นาที?
• ถ้าระบบ A เสียจะมีการ Fallback ไป B มั้ย?
• cache กี่นาที ถึงจะทําการ Update อีกที?
• database table ไหนไว้ทําอะไร?
• field ใน database field ไหนไว้ทําอะไร?
• configuration table มี Logic ยังไง?

ยังไม่รวมถึง Skills การ Investigate issues ของ QA

• สมมุติมีการแจ้งมาว่า มีการตัดบัตรของ user A ผิด โดยให้เราต้องช่วยตรวจสอบยอดใช้จ่ายทั้งหมดของ user A ว่ามันไปผ่านจุดไหนอะไรบ้าง? เราก็ต้องนั่งไล่ใน configuration table ของระบบ ซึ่งส่วนใหญ่ถ้าไม่ใช่ config file,logs ก็ database meta data นี่แหละ
• ไม่ก็เรื่องของอยู่ดีๆ สินค้าที่ต้องการขาย ไม่ขึ้นบนหน้าเว็ปหายไปเลย เราก็ต้อง query investigate พวก logic table เพื่อดูว่าเกิดอะไรขึันนั้นเอง เช่นมี Flag อะไรส่งผิดมั้ย? ทําไม response time นานไปจนไม่ขึ้นรึเปล่า?
• หรือ บางทีประเด็นสําคัญคือการต่อสู้กับ Product Owner ที่ต้องการจะเปลี่ยนแปลงอะไรต่างๆ โดยที่ไม่ได้คํานึงผลกระทบของ feature เราก็ต้องทําการหาหลักฐานและข้อมูลหลากหลายแบบมายืนยัน เช่น จํานวนของ transaction ที่เกิดขึ้นในระบบ และ จะหายไปเมื่อมีการเอา feature นี้ขึ้น เป็นจํานวนเงินเท่าไรนั้นเอง

ทีนี้การจะเข้าใจพวกนี้ได้นอกจากจะเข้าไป Invoke กับช่วง design แล้ว เราก็ต้อง query table ต่างๆใน database เป็นด้วย …. เพราะฉะนั้นถ้าจะ query แต่ไม่รู้ว่า query ยังไงมันก็คงจะลําบากหน่อยๆน่ะ 🙂

SQL Overview

SQL หรือเรียกเต็มๆว่า Structured Query Language เป็นภาษมาตราฐานในการใช้จัดการกับฐานข้อมูลทุกชนิด แต่สามารถแตกต่างออกไปได้ ในแต่ล่ะ database vendor เช่น ถ้า Mysql,MS SQL, Oracle ก็อาจจะมี syntax บางส่วนที่ไม่เหมือนชาวบ้าน หรือบาง Built-in function ที่ใส่ลงมา 🙂

โดยเราสามารถแบ่งภาพรวมของ SQL ออกมาได้ 5 ส่วนด้วยกันคือ

1. Selecting
2. Filtering
3. Aggregate
4. Sorting, Grouping
5. Having
6. Join

ซึ่งแต่ละตัวจะมีหน้าที่เฉพาะทางของมันเอง เช่น

• Selecting คือการที่เราเลือกข้อมูลขึ้นมาเพื่อวิเคราะห์
• Filtering คือการตัดข้อมูลที่ไม่จําเป็นออกด้วย where caluse นั้นเอง โดยเราสามารเอา function หลักๆอย่าง Between col AND col , col In , IS NOT NULL หรือ LIKE มาใช้ filter ได้ด้วย
• Aggregate คือ การใช้ function ของตัว sql เพื่อนํา set ของข้อมูล มารวมกันในตัวเดียว (aggregate) เช่น เรามี column ข้อมูลของยอดขายทั้งปีแล้วเราต้องการยอดรวม เราก็จะใช้ SUM(col) เป็นต้น โดย Aggregate function หลักๆที่มีคือ Max,Min, Avg, Sum และ Count เป็นต้น
• Sorting, Grouping คือ หลังจากที่เราดึงข้อมูลขึ้นมาแล้ว เราอาจจะต้องการจัดกรุ๊ปของข้อมูลที่จะแสดงผล เพื่อให้มันสามารถดูได้ง่ายขึ้น ก็จะทําการ Group ข้อมูลให้อยู่ในประเภทเดียวกัน เช่น timestamp ของเมื่อวาน มียอดขาย 10 รายการ เราต้องการดูยอดรวมทั้งหมดของเมื่อวาน เราก็จะ group โดยการใช้ timestamp เป็นต้น
• Having คือ conditional caluse ตัวนึง ที่ช่วยให้เราใส่เงื่อนไขเข้าไปเพิ่มเมื่อต้องการจะโชว์ผล เช่น ถ้าเราต้องการที่จะดูยอดขายของเมื่อวานทั้งหมด “ที่มีราคาเกิน 500 บาท” เจ้าตัวนี้ก็จะคือ Having นั้นเอง
• Join คือ การเอา table มารวมเข้าด้วยกัน เพื่อแสดงผล มีประโยชน์มากเมื่อต้องการดูข้อมูลที่มาจากหลายที่ โดยหลักๆจะมี Inner join, left join, right join เป็นต้น

SQL Statements ที่ใช้บ่อยๆในการ Investigate

Prerequisite ก่อนเริ่มม

ก่อนที่จะไปเริ่มเรียนรู้ SQL ที่ใช้บ่อยๆ เรามาดู Table ที่เราจะเอามาใช้เล่นวันนี้กันก่อน

นี้เป็น table แบบง่ายๆๆๆๆๆ ที่แสดงให้เห็นถึงการสั่งซื้อสินค้า ของบริษัทแห่งนึง 🙂 ซึ่ง Table พวกนี้จะใช้ใน Tutorial วันนี้ทั้งหมด โดยเราสามารถแบ่งออกเป็น 3 Tables ก็คือ

1. Customer
   • เก็บข้อมูลของลูกค้าทั้งหมด
2. OrderData
   • เก็บข้อมูลการสั่งซื้อของลูกค้า
3. OrderBreakDown
   • เก็บรายละเอียดข้อมูลการสั่งซื้อของลูกค้า

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

โดยเราจะนําเอา 3 tables ง่ายๆข้างบนเนี่ย มาใช้ในการแสดงการใช้งานของ SQL คําสั่งหลักๆข้างล่างนี้เพื่อใช้ในการ investigate นั้นเอง

• Join
• Count & Distinct
• With

Join คืออะไร?

คําสั่งนี้เป็นอะไรที่ชีวิตของ QA ต้องเจอมาแน่ๆๆ 🙂 Join !!! สุดยอดที่ทุกคนต้องเคยใช้งาน มันเป็นคําสั่งที่ใช้ในการเชื่อมข้อมูลของ 2 tables เข้าด้วยกัน ด้วยเงื่อนไขของ Condition นึงๆ ขอยกตัวอย่างจาก Table ที่อยู๋ใน SQLFiddle น่ะ

สมมุติว่า เราต้องการที่จะโทรศัพท์ไปหา User A เพื่อสอบถามว่ามีการสั่งซื้อสินค้าเข้ามาใช่มั้ย? ทีนี้จาก Table Schema พวกนั้นแปลว่าเราต้องรู้ว่า User A เบอร์มือถืออะไรใช่มั้ย? แล้วก็ต้องรู้ว่าเค้าสั่งของจริงใช่มั้ย?

ทีนี้ เราจะเขียนดื้อๆอย่าง query table customer ทีนึง แล้วก็ order data ทีนึงก็ได้ โดยไม่ต้องใช้ Join อะไรแบบนี้ เพราะมันอาจจะดูไร้สาระกับ Table เล็กๆแบบนี้ …. แต่ลองคิดกลับกันสิ ถ้า Table มีเป็น 1,000,000 records กาที่มาแยก query ชีวิตมันดูไม่มีความสุขเลยล่ะ 🙁

ก็เลยเกิดมาเหตุผลที่เรา Join 2 table เข้าด้วยกัน แล้วก็เลือกเฉพาะที่มันตรง condition อย่างว่า custId มีใน table ทั้งสอง table นั้นและ User ต้องชื่อ FirstName เป็น A นั้นเอง

SELECT MobilePhone FROM Customer as cust
INNER JOIN OrderData as orderd On cust.custid = orderd.custid
where cust.FirstName = ‘A’

จะเห็นว่ามันออกมาเป็น 3 rows เพราะลูกค้าคนนี้สั่งของ 3 อย่างนั้นเอง ทีนี้แต่เราต้องการแค่ MobilePhone rows เดียวก็ใช้วิธี DISTINCT เพื่อเอาข้อมูลซำ้ออกไปได้ เช่น

SELECT DISTINCT MobilePhone FROM Customer as cust
INNER JOIN OrderData as orderd On cust.custid = orderd.custid
where cust.FirstName = ‘A’

ก็จะได้ข้อมูลที่ไม่ซำ้กันแล้ววววว 🙂

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

มันก็มีหลากหลาย Join มากเลย แต่ชอบการอธิบายของเว็ปนี้มากที่สุดล่ะ ที่อธิบายถึงง่ายๆดี ซึ่งแบ่งการ Join ออกเป็นได้ 4 แบบ และ การ Join แต่ละแบบก็แตกต่างกันออกไป

• Inner Join
  • Join เฉพาะ intersection ของ Table ทั้งสอง มีใน A และ ใน B ด้วย

    

• Left Join
  • Join โดยเอาข้อมูลจากฝั่ง Table A เข้ามาแปะใน Table ทั้งหมดเลยยย โดยถ้าไม่มีข้อมูลในฝั่ง A ก็จะเป็น Null ให้

    

• Right Join
  • Join โดยเอาข้อมูลจากฝั่ง Table B เข้ามาแปะใน Table ทั้งหมดเลยยย โดยถ้าไม่มีข้อมูลในฝั่ง B ก็จะเป็น Null ให้

    

• Full Join
  • เอาข้อมูลทั้งสอง Table รวมกันดื้อๆเลย (พอดีดันใช้ไม่ได้ใน MySQL เพราะฉะนั้นเวลาเอาไปใช่กันเนี่ยก็ต้องดูก่อนน่ะ ว่า Database นั้นๆ Support หรือไม่)

แต่จริงๆแล้วพอเอาเข้าจริงๆ ในชีวิตเราก็ใช้ Inner Join ซะส่วนใหญ่ ไม่ได้ใช้พวก Left Join หรือ Right Join เลย แต่รู้ไว้ก็ดีจะได้รู้สึกฉลาดๆ 5555 🙂 ถ้าเป็นพวก BI หรือ DBA อาจจะเคยใช้อย่างอื่นบ้างแหละมั้งงงงง

Count & Distinct คืออะไร?

ตัวนี้สิแสนสนุกเลย Count !!! อย่างที่เรารู้กันแหละน่ะว่า Table ของจริงบน Production มันไม่ใช่แค่ 10-20 rows แน่ๆ ถ้า Production ใหญ่ๆเลยก็ เป็นล้านๆๆ rows เพราะฉะนั้นจะให้มานั่งนับว่ามีกี่ rows มันก็แย่เกินไปมั้ยยยยย 🙁

เราเลยมาเรียนรู้กับคําสั่ง Count นั้นเอง ซึ่งมันมีหน้าที่ในการนับจํานวน rows ของ column ที่เราระบุลงไปนั้นเอง เช่น

Table Customer เราจะนับได้ว่ามี ลูกค้ากี่คนด้วยการใช้คําสั่ง Count(columnName) ง่ายๆเลย

ที่นี้มันก็ดูดีหรอกน่ะ เหมือนกับว่า count() ง่ายๆ แต่ลองคิดดูน่ะ ถ้ามันมี record ซำ้ๆอย่างใน Table OrderData ล่ะ ผลลัพธ์เวลา count() จะไม่ตรงเลย ถ้าเราหาเงื่อนไขแบบ มีจํานวนคนสั่งสินค้ากี่คน? ถ้าเราไป count ตรงๆเลย มันจะได้ 8 แน่นอน เพราะมันนับทุก rows ใน table

เพราะฉะนั้นสิ่งที่เราต้องทํา ถ้าอยากรู้ว่ามี User กี่คนที่ทําการสั่งสินค้า เราก็ต้องเอาเฉพาะ Unique Customer Id นั้นเองโดยการใช้คําสั่ง Distinct

ถ้าเราย้อนกลับไปดูใน Table แล้วนับดูดีๆจะเห็นว่ามี User สั่งจริงๆแค่ 3 คนเอง 🙂 เพราะฉะนั้น Distinct & Count จะช่วยเราแยกและวิเคราะห์ข้อมูลได้เยอะเลย เวลา Investigate Issues

ทีนี้แต่ปกติแล้วเวลาเราใช้งานจริงๆ เราไม่มานั่งใส่ชื่อคอลลัมน์แบบนี้หรอก สิ่งที่เราทําก็คือใช้ count(*) นั้นเองงง คือนับทุก rows ที่ไม่ null

แต่ด้วยวิธีนี้ไม่สามารถใช้ count(distinct *) ได้น่ะ พังแน่ๆ ถ้าเราจะใช้คู่กับ * เทคนิตที่จะทําต้องใช้ท่าหน่อยคือเราไป Distinct แยกออกมาก่อน แล้วค่อย count(*) แบบรูปด้านล่างนี้ แต่จริงๆเรา count(distinct columnName) ไปเลยก็ได้ 5555

With คืออะไร?

จริงๆเจ้า With SQL Clause นี้ไม่ค่อยได้เห็น และ หลายคนอาจจะไม่ได้ใช้งาน แต่เนื่องจากสมัยนี้เป็นยุคของ Big Data แทบทุกบริษัทหันมาใช้ Hadoop Cloudera กัน ทีนี้เจ้า With Clause 1 , With Clause 2 นี้มีประโยชน์มากในการจัดการข้อมูลใน Database

ความสามารถของมันคือการสร้าง Temporary Table ขึ้นมา หรือจริงๆแล้วก็คือ Sub-Query ขึ้นมาเพื่อให้เรานําไปใช้ต่อได้ แต่รูปแบบของมันคืสร้าง subquery refactoring ที่สามารถนําไป resuse ได้นั้นเอง 🙂

WITH <alias_name> AS (sql_subquery_statement)
SELECT column_list FROM <alias_name>[,tablename]
[WHERE <join_condition>]

จะเห็นว่าตัวหลังจากสร้าง subquery ขึ้นมา แล้วก็เอาไปใช้ได้เลย 🙂  เราสร้าง complex subquery ทิ้งไว้ แล้วก็เอาไป reuse ง่ายๆแค่นี้แหละ

สรุปละ

การ investigate issues, negotiate Product Owner ด้วย information เป็นสิ่งที่โคตรสําคัญเลย ซึ่ง query หลักๆที่ใช้ในการ Investigate ก็มีแค่นี้แหละ

1. Join
2. Count & Distinct
3. With

เพียงความรู้ของ SQL นี้กับ ความรู้ Business domain ของ Product นั้นๆๆ ก็ทําให้เราได้ข้อมูลที่เราต้องการได้แล้ว ทีนี้ก็สนุกกับการ Investigate ได้แล้วล่ะ 🙂

Add-on:

• Subquery เป็นอีก Technique นึงที่น่าสนใจน่าการใช้เขียน query เพราะมันจะช่วยให้เราสามารถ query หลายๆ condition ได้พร้อมกัน โดยมี syntax แค่ใส่วงเล็บ แบบข้างล่างนี้