sql-statements-feature-image
sql-statements-feature-image

หลังจากหายหน้าหายตาไปซักพักกับงานยุ่งๆๆ คราวนี้เรามาเรียนรู้กับการใช้งานสิ่งสําคัญอีกอย่างนึงของ 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 ที่เราจะเอามาใช้เล่นวันนี้กันก่อน

sql-table-for-statement-tutorial
sql-table-for-statement-tutorial

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

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

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

sql-fiddle-example
sql-fiddle-example

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

  • Join
  • Count & Distinct
  • With

Join คืออะไร?

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

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

example-calling-to-user-a-mobile-phone-with-order
example-calling-to-user-a-mobile-phone-with-order

ทีนี้ เราจะเขียนดื้อๆอย่าง 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’

sql-inner-join-example
sql-inner-join-example

จะเห็นว่ามันออกมาเป็น 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 เท่านั้นน่ะ มันมีหลากหลายแบบมากๆเลย โดยสามารถดูได้จากรูปข้างล่างนี้

sql-join-example
sql-join-example (http://www.sql-join.com/sql-join-types)

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

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

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

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

      sql-right-join
      sql-right-join
  • 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 ที่เราระบุลงไปนั้นเอง เช่น

sql-table-for-statement-tutorial
sql-table-for-statement-tutorial

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

sql-count-customer-name
sql-count-customer-name

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

sql-count-order
sql-count-order

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

sql-distinct-count-example
sql-distinct-count-example

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

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

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

sql-disctinct-*-technique
sql-disctinct-*-technique

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>]

sql-with-clause-subquery-example
sql-with-clause-subquery-example (http://www.dba-oracle.com/t_with_clause.htm)

จะเห็นว่าตัวหลังจากสร้าง 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 แค่ใส่วงเล็บ แบบข้างล่างนี้

    example-subquery (http://www.dofactory.com/sql/subquery)
    example-subquery (http://www.dofactory.com/sql/subquery)

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.