COOL IT IDEAS AND STUFF

See the original english version Click here!

 

ในชีวิตการทำงานของ Programmer คงต้องเคยผ่านการเขียน SQL Query กันมาแล้วทุกคน แต่เชื่อว่าหนึ่งในสิ่งที่คุณอยากรู้ก็คือ จะเขียน SELECT Query ที่มีความซับซ้อนได้อย่างไร และจะเริ่มต้นจากตรงไหน เป็นต้น ดังนั้น ในบทความนี้จะมาบอกถึงเบื้องหลัง วิธีเขียน SELECT Query ที่มีความซับซ้อน เพื่อเป็นแนวทางให้คนที่ต้องเขียน SQL 

The Model

เราจะเริ่มต้นด้วย Data Model ที่จะใช้ ก่อนที่คุณจะเริ่มเขียน Query (ที่ซับซ้อน) คุณควรเข้าใจก่อนว่า อะไรอยู่ที่ไหนบ้าง เช่น Table ใดเก็บ Data อะไรบ้าง นอกจากนี้คุณควรเข้าใจธรรมชาติของความสัมพันธ์ระหว่าง Table เหล่านั้น

หากคุณยังไม่มี 2 สิ่งนี้ที่ต้องจัดการ คุณก็มี 3 ทางเลือก คือ:

1. สอบถามคนที่สร้าง Model เกี่ยวกับ Document (ถ้าสามารถสอบถามบุคคลนั้นได้) เพื่อที่คุณจะได้เข้าใจ Business Logic ที่อยู่เบื้องหลัง Data
2. สร้าง Document ด้วยตัวคุณเอง สิ่งนี้อาจต้องใช้เวลา แต่มันมีประโยชน์มากโดยเฉพาะอย่างยิ่ง หากคุณบังเอิญเข้ามาในช่วงระว่างกลางของ Project ที่ยังไม่มี Document
3. คุณสามารถทำได้ตลอดเวลาโดยไม่ต้องมี Document แต่คุณควรแน่ใจก่อนว่า คุณต้องรู้ตัวว่ากำลังทำอะไรอยู่ เช่น ไม่แนะนำให้คุณขับรถในขณะที่เบรกยังซ่อมไม่เสร็จ นั่นหมายความว่า คุณสามารถลองได้ แต่...

เคล็ดลับเหล่านี้สามารถใช้ได้ไม่ว่าคุณกำลังทำอะไรกับ Database ของคุณ การที่เรามีภาพรวมจะช่วยให้คุณประหยัดเวลาได้มากในระยะยาว ดังนั้น คุณควรลงทุนด้านเวลาพอสมควรในขณะที่คุณกำลังเริ่มต้น

มาเริ่มต้นกันด้วย Query ที่ซับซ้อน

ตอนนี้เราต้องกลับมาที่คำถามแรกที่เราเคยตั้งคำถามไว้ คือ “เราจะเขียน SELECT Query ที่ซับซ้อนได้อย่างไร” เรามาเริ่มด้วย Query ที่ซับซ้อนกัน

และนี่คือสิ่งที่ได้จากการ Query:

 

คุณจะเห็นว่า เรามี Query ที่ซับซ้อน และมีผลลัพธ์ที่แสดงออกมา 2 Rows ตามด้านบน และเราก็คงจะพูดได้ยากว่า Query เหล่านี้ ใช้ทำอะไร และทำงานอย่างไร ดังนั้น เราลองมาเปลี่ยนมันกัน

จะเขียน SELECT Query ที่ซับซ้อนได้อย่างไร และจะเอา Data มาจากที่ไหน

เรากลับไปที่คำถามเดิมกัน ตอนนี้เราจะตอบคำถามนี้ไปทีละขั้นตอน โดยเราจะมาดูกันว่า ผลลัพธ์ที่ต้องการจาก Query คืออะไร (Assignment ที่คุณได้รับ)

“ให้ Return ทุกประเทศพร้อมกับ จำนวนการโทรที่เกี่ยวข้อง และระยะเวลาการโทรโดยเฉลี่ยเป็นวินาที ในผลลัพธ์จะแสดงเฉพาะ ประเทศที่มีระยะเวลาการโทรโดยเฉลี่ย มากกว่า ระยะเวลาการโทรโดยเฉลี่ยของการโทรทั้งหมด”

สิ่งแรกที่เราต้องทำก็คือ พิจารณาว่าเราจะใช้ Table ใดในกระบวนการนี้ ซึ่งคุณจะเห็นว่าใน Data Model ได้มีการใส่สีให้กับ Table ที่เราจำเป็นต้องใช้

แล้วเราจะตัดสินใจได้อย่างไรว่า ควรจะเป็น Table ใด? คำตอบมี 2 ส่วน คือ:

1. ใช้ Table ทั้งหมดที่มี Data ที่คุณต้องการ เพื่อแสดงในผลลัพธ์ของคุณ ในกรณีของเรา Table ที่เป็นปัญหาก็คือ ประเทศ (Country) (เนื่องจากเราต้องการชื่อประเทศ) และการโทร (Call) (เนื่องจากเราต้องการ start_time และ end_time เพื่อที่จะสามารถคำนวณระยะเวลาการโทรโดยเฉลี่ยได้)
2. หาก Table จาก Bullet Point ก่อนหน้า ไม่เกี่ยวข้องกันโดยตรง คุณจะต้องใช้ Table เหล่านั้นทั้งหมด (ซึ่งในกรณีของเรา ก็คือ วิธีที่เราจะเชื่อมโยงจาก country Table เพื่อไปยัง call Table)

หลังจากการวิเคราะห์นี้ เรารู้ว่าเราต้องใช้ Table ดังต่อไปนี้: country, city, customer และ call ซึ่งหากเราต้องการใช้พวกมันอย่างถูกต้อง เราจำเป็นต้อง Join Table เหล่านี้โดย Foreign Keys หากเรายังไม่ต้องนึกถึง Query สุดท้าย ตอนนี้เราได้รู้แล้วว่าจะมีส่วนต่าง ๆ ดังนี้:

 

เราสามารถทำสิ่งหนึ่งได้ ซึ่งนั่นคือ การทดสอบว่า Query แบบนี้จะ Return ผลลัพธ์อะไรกลับมาให้:

ในที่นี้จะขอไม่แสดงรูปภาพของผลลัพธ์ทั้งหมด เนื่องจากมันคงจะมี Column ออกมาเป็นจำนวนมาก แต่คุณก็สามารถตรวจสอบเองได้ ขอแนะนำให้คุณทดสอบ Query ของคุณในบางส่วน แม้ว่าจะไม่ปรากฏในผลลัพธ์สุดท้าย แต่เราสามารถใช้มันเป็นเพื่อดูเป็นตัวอย่างของผลลัพธ์ได้ ด้วยการทดสอบส่วนของ Query ดังกล่าว คุณจะได้ทราบถึง สิ่งที่เกิดขึ้นเบื้องหลังและพอจะสามารถสรุปได้ว่า ผลลัพธ์สุดท้ายควรจะออกมาเป็นอย่างไร แต่อย่างไรก็ตาม เราก็ต้องตอบให้ได้ว่า “เราจะเขียน SELECT Query ที่ซับซ้อนได้อย่างไร”

วิธีเขียน SELECT Query ที่มีความซับซ้อน – เขียนบางส่วนของ Query ขึ้นมา

เราได้เขียนส่วนของ Query ขึ้นมาแล้ว และนั่นเป็นแนวทางปฏิบัติที่ดี ซึ่งจะช่วยให้คุณสร้าง Query ที่ซับซ้อนจาก “กลุ่มของ Query” ได้ง่ายขึ้น แต่ขณะเดียวกันคุณจะทดสอบ Query ของคุณไปพร้อม ๆ กันด้วย เพราะคุณจะตรวจสอบแต่ละส่วนของพวกมันไปด้วย รวมทั้งตรวจสอบการทำงานของ Query ที่ถูก Execute หรือเพิ่มเข้าไป

เราจะเริ่มต้นกันด้วยส่วนนี้ก่อน “ประเทศที่มีระยะเวลาการโทรโดยเฉลี่ย มากกว่า ระยะเวลาการโทรโดยเฉลี่ยของการโทรทั้งหมด” ซึ่งเห็นได้ชัดว่า เราต้องคำนวณระยะเวลาโดยเฉลี่ยจากการโทรทั้งหมด (เป็นวินาที) ดังนั้น เรามาเริ่มทำกัน

ในที่นี้จะขอให้ Aggregate Function เข้ามาช่วย โดยที่ DATEDIFF Function จะช่วยคำนวณถึงความแตกต่างในหน่วยของช่วงเวลาที่กำหนด ระหว่างเวลาเริ่มต้น และ เวลาสิ้นสุด ซึ่งผลลัพธ์ที่ได้ ก็แสดงให้เห็นว่า ระยะเวลาการโทรโดยเฉลี่ย ก็คือ 354 วินาที

ตอนนี้เราจะเขียน Query ซึ่งจะ Return ค่ารวมสำหรับทุกประเทศ

จากด้านบน จะขอชี้ให้เห็น 2 สิ่งต่อไปนี้:

1. SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) : ในส่วนนี้จะทำการรวมผล เฉพาะการโทรที่มีอยู่เท่านั้น เนื่องจากเราใช้ LEFT JOIN ดังนั้นเราจะ JOIN ประเทศต่าง ๆ โดยไม่ต้องมีการโทรเข้าไปด้วย ในกรณีที่เราใช้ COUNT เราจะได้ค่า 1 ที่ถูก Return กลับมาสำหรับประเทศที่ไม่มีการโทรใด ๆ แต่เราต้องการค่า 0 ที่นั่น (เราต้องการเห็นข้อมูลนั้น)
2. AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) : AVG ก็เป็นการหาค่าเฉลี่ย แต่สิ่งที่เพิ่มเข้ามาคือการใช้ ISNULL(…,0) ซึ่งวิธีการนี้จะช่วยทดสอบว่า ค่าที่คำนวณได้เป็น IS NULL หรือไม่ หากเป็นเช่นนั้นก็ให้แทนที่ด้วย 0 ซึ่งค่าที่คำนวณ อาจเป็น NULL ได้ หากไม่มี Data อยู่ (เราใช้ LEFT JOIN)

เรามาดูกันว่า Query นี้จะ Return อะไรกลับมาให้:

“เราจะเขียน SELECT Query ที่ซับซ้อนได้อย่างไร” -> ตอนนี้เราใกล้ที่จะได้ Query ที่สมบูรณ์ของเราแล้ว

ดังนั้น ผลลัพธ์ที่ได้จะประกอบไปด้วย ทุกประเทศพร้อมกับจำนวนการโทร และระยะเวลาการโทรโดยเฉลี่ย จากผลลัพธ์นี้เราสนใจเฉพาะกลุ่มที่มีระยะเวลาการโทรโดยเฉลี่ย มากกว่า ระยะเวลาการโทรโดยเฉลี่ยของการโทรทั้งหมด นั่นคือ Query ดั้งเดิมของเรา แต่มีการเพิ่ม Comments เข้าไป

คุณสามารถดูผลลัพธ์การ Query ได้จากรูปภาพด้านล่างนี้:

เมื่อเปรียบเทียบกับ Query ก่อนหน้านี้ เราเพิ่งเพิ่มส่วนของ HAVING เข้าไป ขณะที่ในส่วน WHERE ของ Query เราจะใช้ทดสอบค่า "ปกติ" (Regular Value) แต่ในส่วน HAVING ของ Query จะถูกใช้เพื่อทดสอบค่ารวม(Aggregated Values) นั่นหมายถึง เรากำลังใช้มันเพื่อเปรียบเทียบค่า AVG

โดยทั่วไปแล้ว Comments เป็นสิ่งสำคัญ ไม่เฉพาะกับ Database เท่านั้น แต่สำคัญกับการเขียน Program ด้วย การเพิ่มComments เข้าไป 3 บรรทัดจะช่วยทำให้ Query ของคุณสามารถเข้าใจได้ง่ายมากขึ้น แม้แต่คนที่ดู Query นี้เป็นครั้งแรกก็จะเห็นว่าคุณทำอะไรและทำไปเพื่ออะไร มันมีประโยชน์กับคนที่จะต้องมาดูแลต่อหรือแม้แต่ตัวคุณเองที่กลับมาดูมันในอนาคต แม้ว่าจะต้องใช้เวลาพอสมควร ในการเขียน Comments เหล่านี้ แต่ขอแนะนำให้คุณทำมัน ซึ่งมันจะช่วยประหยัดเวลาคุณได้อย่างมากเมื่อคุณต้องกลับมาดูมันอีกครั้งในอนาคต

สรุป

จากคำถามที่ว่า “เราจะเขียน SELECT Query ที่ซับซ้อนได้อย่างไร” แม้จะไม่ใช่เรื่องง่ายที่จะตอบคำถามนี้ แต่ขอแนะนำให้คุณลองทำตามขั้นตอนต่อไปนี้:

• ให้คิดว่ามันเป็นตัวต่อ LEGO และให้สร้าง Query ในลักษณะนั้น ให้คิดว่า ส่วนที่มีความซับซ้อนเป็นเหมือนกล่องดำ – พวกมันจะ Return สิ่งที่ต้องการ และคุณจะเขียนมัน (และรวมมันไว้ใน Query หลัก) ในภายหลัง

• ระบุ Table ทั้งหมดที่คุณต้องการใช้ใน Query

• ทำการ Join Tables ที่มี Data ที่คุณต้องการจะแสดงมันออกมา หรือ Data ที่จะใช้ในส่วน WHERE ของ Query

• แสดง Data ทั้งหมดเพื่อตรวจสอบว่าคุณ Join ทุกอย่างถูกต้องหรือไม่ และเพื่อดูผลลัพธ์ของ Query ดังกล่าว

• สร้าง Query ย่อย ๆ ที่แยกจากกันทั้งหมด แล้วทดสอบดูว่า พวกมัน Return สิ่งที่ควรจะเป็นหรือไม่ จากนั้นให้เพิ่มพวกมันลงใน Query หลัก

• ทำการทดสอบทุกสิ่งทุกอย่าง

• เพิ่ม Comments เข้าไป

ที่มา:  https://www.sqlshack.com/

 

 

รับตำแหน่งงานไอทีใหม่ๆ ด้วยบริการ IT Job Alert

 

อัพเดทบทความจากคนวงในสายไอทีทาง LINE ก่อนใคร
อย่าลืมแอดไลน์ @techstarth เป็นเพื่อนนะคะ

 

บทความล่าสุด

• 6 AI และ Music APIs ที่แนะนำให้ลองทำ ใน Project ถัดไปของคุณ 
• IT & Digital Events in September 2020 
• 10 Productivity Tips การใช้ Code Editor ใน Visual Studio ให้รวดเร็วดั่งนินจา