บทความ IT เจ๋งๆ

Design vector created by macrovector_official - www.freepik.com

ปัจจุบัน SQL กลายเป็นทักษะพื้นฐานที่เป็นที่ต้องการในแวดวงและเป็นหนึ่งในทักษะที่เห็นในประกาศในตำแหน่งงานต่าง ๆ มากมาย ดังนั้นคนที่ต้องทำงานกับ Data เป็นหลัก จำเป็นต้องมีทักษะในด้านนี้ และนี่ก็คือ 4 เคล็ดลับ SQL สำหรับ Data Scientists และ Data Analysts แต่ก็มีประโยชน์กับคนไอทีที่ต้องใช้งาน SQL ด้วยเช่นกัน

1. อย่าใช้ Avg() กับค่าเฉลี่ยเพราะมันให้ผลลัพธ์แตกต่างกัน

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

คำถามน่าสนใจก็คือ เหตุใดหาค่าเฉลี่ยของค่าเฉลี่ยจึงเป็นสิ่งไม่ควรทำทั้งในกรณีของ SQL และกรณีโดยทั่วไป เนื่องจากค่าเฉลี่ยอาจถูกบิดเบือนไปจากความเป็นจริงได้

ลองดูตัวอย่างในตารางด้านล่าง

จากตาราง เป็นค่าเฉลี่ยของ Cost Per Claim ของแต่ละ Country เราจะเห็นว่า ค่าเฉลี่ยของ Country หนึ่ง มาจากการ Claims ถึง 100 ครั้ง และอีก Country หนึ่ง มาจากการ Claims เพียง 2 ครั้ง โดยทั่วไป เป็นไปได้ว่าตารางนี้อาจไม่มี จำนวนการ Claims ทั้งหมด (Total Claims) แต่เรากำลังใช้มันเพื่อแสดงให้เห็นว่า มันง่ายแค่ไหนที่ค่าเฉลี่ยจะถูกบิดเบือนไปจากความเป็นจริง

จะเป็นอย่างไร หากเราต้องการหาค่าเฉลี่ยของทุก Country ซึ่งหากคุณลองหาค่าเฉลี่ยของค่าเฉลี่ย คุณจะได้ผลลัพธ์ที่ $525 ซึ่งดูเหมือนว่า จะเป็นผลลัพธ์ที่ไม่ถูกต้อง

หากการ Claims 100 ครั้ง มีค่าเฉลี่ยอยู่ที่ $50 และมีการ Claims 2 ครั้ง ที่มีค่าเฉลี่ย $1000 ดังนั้น เมื่อคิดค่าเฉลี่ยทั้งหมดแล้ว ผลลัพธ์ควรจะมีค่าที่ใกล้เคียงกับ $50 มากกว่า $500

ในความเป็นจริงแล้ว ค่าเฉลี่ยของการ Claims เหล่านี้อยู่ที่ประมาณ $68 ดังนั้น หากคุณหาค่าเฉลี่ยของค่าเฉลี่ย คุณก็จะได้ผลลัพธ์ที่ผิดไปจากความเป็นจริง

คำถามที่น่าสนใจอีกคำถามก็คือ ถ้าเราต้องการหาค่าเฉลี่ยของค่าเฉลี่ย จะทำได้ไหม

บางครั้งเราก็คิดว่า ค่าเฉลี่ยของค่าเฉลี่ย อาจมีค่าใกล้เคียงกับผลลัพธ์ที่ควรเป็น

ลองดูตัวอย่าง SQL ด้านล่างนี้

ในกรณีนี้ เราจะใช้ Table ที่มีค่าเฉลี่ยของ Cost Per Patient และค่าเฉลี่ยของ Visits Per Patient ตาม County และ Age

อย่างไรก็ตาม เราต้องการหาค่าเฉลี่ยของ Cost Per Patient และ Visits Per Patient ของแต่ละ Country 

เราสามารถหาค่าเฉลี่ยของค่าเฉลี่ยจาก Table นี้ โดยใช้ Query ด้านบนและจะได้ผลลัพธ์ตามด้านล่างนี้

คราวนี้ ถ้าเราต้องเขียน Query ให้ถูกต้องโดยมีการคำนวณค่าเฉลี่ยเสียใหม่ของแต่ละ Country ดังที่เห็นใน Query ด้านล่างนี้:

ทีนี้ลองมาเปรียบเทียบผลลัพธ์การ Query นี้กับผลลัพธ์ก่อนหน้านี้

คุณจะสังเกตเห็นว่า มีความแตกต่างเล็กน้อยในผลลัพธ์ของ County ที่ชื่อ King 

หากเราเปรียบเทียบ ค่าเฉลี่ยของการ Visit พวกมันดูเหมือนจะมีค่าที่ใกล้เคียงกันระหว่าง 2.4 กับ 2.6 นี่คือเหตุผลที่เราเชื่อว่า ทำไมบางคนถึงหลงเชื่อในการหาค่าเฉลี่ยของค่าเฉลี่ยอยู่ บางครั้งพวกมันก็ได้ผลลัพธ์ที่ใกล้เคียงกับความเป็นจริง จึงทำให้หลายคนใช้วิธีการนี้

อย่างไรก็ตาม หากเราดูไปที่ค่าเฉลี่ยของ Cost Per Claim เราจะสังเกตเห็นว่า มีค่าแตกต่างกันประมาณ $58 ระหว่าง $564.33 กับ $622.6 ซึ่งนั่นก็เกือบ 10% และหากพูดถึงเรื่องการประหยัด Cost แล้ว นี่ถือว่ามีความแตกต่างอย่างมาก

และแม้ระหว่าง 2.4 เทียบกับ 2.6 ดูเหมือนว่าจะแตกต่างกันเพียงเล็กน้อย แต่มันก็สามารถนำไปสู่ความแตกต่างที่มากขึ้นได้ ดังนั้น จงอย่าหาค่าเฉลี่ยของค่าเฉลี่ย

2. คุณสามารถใช้ Case Statement ภายใน Sum

เคล็ดลับอีกประการหนึ่งในการเขียน SQL ก็คือ การเรียนรู้วิธีใช้ Case Statement ใน Sum Clause ของคุณ สิ่งนี้มีประโยชน์มากเมื่อคุณพยายามเขียน Metrics ด้วย อัตราส่วนหรือตัวเศษ

ตัวอย่างเช่น ลองดู Query ที่ด้านล่าง คุณจะเห็นว่า เราต้องใช้งาน Table Claims ถึง 2 ครั้ง เพื่อรับจำนวนค่าที่เราพยายามจะ Filter รวมทั้งจำนวน Rows ทั้งหมด อย่างไรก็ตามเราสามารถลดการเขียน Query ให้สั้นลงได้

เราสามารถเขียน Case Statement เพื่อนับค่าทั้งหมดที่มีเงื่อนไขเป็น True จากนั้นก็หารด้วยจำนวนที่นับได่ทั้งหมด ดัง Query ด้านล่างนี้

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

จากประสบการณ์โดยทั่วไป เคล็ดลับนี้มักจะถูกนำมาใช้โดย SQL Developers ส่วนใหญ่ในช่วงปีแรกหรือปีที่ 2 ของการใช้ SQL

มันมีประโยชน์อย่างยิ่งในการเขียน Code ที่นับเปอร์เซ็นต์ของ Nulls ใน Row หรือเพื่อคำนวณ Metrics สำหรับ Dashboards แต่ในทางกลับกัน นี่คือเหตุผลที่ว่าทำไม Analysts และ Data Engineers หลายคนถึงได้คุ้นเคยกับเคล็ดลับนี้ตราบเท่าที่พวกเขาต้องเขียน SQL ในปริมาณที่เหมาะสมและไม่ใช้เพียงแค่ Drag & Drop Solutions

3. ทำความเข้าใจกับ Array และวิธีการจัดการพวกมัน

การมี Arrays และ Maps ภายใน Database Tables ของคุณ ถือเป็นสิ่งที่ไม่ปกติ อย่างไรก็ตามเราสังเกตเห็นว่ามีทีมจำนวนมากที่ใช้ Data ที่ไม่มีโครงสร้าง ซึ่งสามารถนำไปใช้ประโยชน์กับ Data Structures อย่าง Arrays และ Array Functions

เนื่องจาก Database อย่าง Postgres และ SQL Engine อย่าง Presto อนุญาตให้คุณสามารถจัดการกับ Arrays ใน Query ของคุณ

แม้ว่า Arrays และ Maps จะไม่ใช่ Concept ใหม่ แต่มันก็เป็น Concept ใหม่สำหรับ Analysts และ Data Scientists บางคนที่ยังไม่คุ้นเคยกับการเขียน Program มากนัก

ซึ่งหมายความว่า คุณอาจต้องเรียนรู้ Array และ Map Functions เป็นครั้งคราวเพื่อใช้ในการ Extract Data 

เริ่มต้นด้วยการเรียนรู้วิธีการ Unnest Map ใน Presto ซึ่ง Map ก็คือ Data Structure ที่จัดเตรียมความสัมพันธ์ของ Key:Value Pairs ไว้ให้ ซึ่งหมายความว่า คุณสามารถจัดเตรียม Unique Key อย่าง Description ที่ต้องการ เกี่ยวกับ Value เช่น "first_name":"George" นอกจากนี้ Map ยังสามารถมี Key Value Pairs ได้หลายคู่ ดังแสดงในรูปภาพด้านล่างนี้

ในกรณีนี้เรามี 2 Keys คือ dob และ friend_ids ที่เราต้องการ Access

คำถามคือ แล้วเราจะเข้าถึง Data เหล่านั้นได้อย่างไร ลองดู Query จากด้านล่างนี้ 

จากที่คุณเห็น คุณสามารถกำหนด Row สำหรับทั้ง Key และ Value ได้ ดังนั้น เมื่อเราดึง Data ออกมา คุณก็จะได้ Data Types ของ Data ซึ่งผลลัพธ์จะมีลักษณะดังภาพด้านล่างนี้

คุณยังสามารถตรวจสอบความยาวของ Arrays, ค้นหา Key ที่ต้องการและอื่น ๆ อีกมากมาย (อ่านเพิ่มเติมเกี่ยวกับ Presto Array ได้จากที่นี่) ซึ่งขอแนะนำว่า อย่าใช้ Arrays และ Maps เพียงเพื่อให้พวกมันทำหน้าที่แทน Data Modeling ที่ดี อย่างไรก็ตาม มันอาจมีประโยชน์กับคุณเมื่อต้องทำงานกับ Data ที่คุณไม่ได้ต้องการ Schema ที่เฉพาะเจาะจงมากนัก

4. Lead และ Lag เพื่อหลีกเลี่ยง Self Joins

เมื่อคุณมีการวิเคราะห์สิ่งใดก็ตาม คุณมักจะเปรียบเทียบผลลัพธ์ของ 2 เหตุการณ์ หรือคำนวณระยะเวลาระหว่าง 2 เหตุการณ์

วิธีหนึ่งที่คุณสามารถทำได้ก็คือ การ Self-Join Table ของพวกมันเอง และทำการเชื่อมต่อระหว่าง 2 Rows เข้าด้วยกัน อย่างไรก็ตาม SQL Function ที่มีประโยชน์อีกอย่างหนึ่ง ก็คือ Lag และ Lead Functions

สิ่งเหล่านี้จะช่วยให้ User สามารถอ้างอิง Lagging หรือ Leading Value ที่ต้องการได้ อีกทั้งคุณยังสามารถระบุรายละเอียด Lagging และ Leading Values ที่คุณต้องการได้อีกด้วย

ตัวอย่างเช่น ใน Query ด้านล่างนี้ เรากำลังแบ่ง Lagging และ Leading Values ด้วย patient_id ซึ่งหมายความว่า เรากำลังพิจารณาเฉพาะ Lagging และ Lead claim_dates และ claim_costs ของ Patient 

ผลลัพธ์ของ Query นี้ จะมีลักษณะดังตารางด้านล่างนี้

คุณจะสังเกตเห็นว่า ในวันแรกของ Patient ทุกรายนั้น Lagging Claim_date และ Cost มีค่าเป็น Null  นั่นเป็นเพราะ ไม่มี Cost หรือ Claim Date ก่อนหน้านี้

โดยรวมแล้ว Lag และ Lead Functions สามารถทำให้ชีวิตของ SQL Developers ง่ายขึ้นมาก

รายละเอียด เป็นสิ่งที่มีความสำคัญกับ SQL

SQL ยังคงเป็นภาษาของ Data และการได้รู้เคล็ดลับเหล่านี้ สามารถช่วยให้แน่ใจว่า Dashboard หรือการวิเคราะห์ ในครั้งถัดไปของคุณจะดีขึ้นกว่าเดิม ไม่ว่าคุณจะหลีกเลี่ยงการหาค่าเฉลี่ยของค่าเฉลี่ย หรือการเขียนเพื่อตรวจสอบคุณภาพ Data ก็ตาม การปรับปรุงเล็ก ๆ น้อย ๆ เหล่านี้ สามารถสร้างความแตกต่างได้อย่างมาก ปัญหาเหล่านี้บางส่วน สามารถก่อให้เกิดปัญหาใหญ่กับธุรกิจหรือบริษัทได้ ดังนั้น สิ่งนี้จะช่วยให้การทำงานของคุณมีความรวดเร็วขึ้น

นอกจากนี้ การวิเคราะห์ Data ของคุณจะแม่นยำยิ่งขึ้นและคุณสามารถมั่นใจได้มากขึ้นกับตัวเลขที่คุณได้รับหากคุณทำตามเคล็ดลับเหล่านี้

ที่มา:  https://dev.to/

 

 

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

 

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

 

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

• 6 ตัวอย่าง JavaScript Code สำหรับใช้แก้ปัญหาที่มักเจอกันบ่อย ๆ
• Top 10 Libraries ที่ Java Developer ทุกคนควรรู้จักไว้
• 4 Python Features ที่มีประโยชน์สุด ๆ