ฟังก์ชั่นหน้าต่าง SQL ห้าอันดับแรกที่คุณควรรู้สำหรับการสัมภาษณ์ด้านวิทยาศาสตร์ข้อมูล

SQL เป็นภาษาสากลในโลกของข้อมูลและเป็นทักษะที่สำคัญที่สุดในการเป็นผู้เชี่ยวชาญด้านข้อมูล

เหตุผลที่ SQL มีความสำคัญมากเพราะเป็นทักษะหลักที่จำเป็นในระหว่างขั้นตอนการโต้แย้งข้อมูล การสำรวจข้อมูล การจัดการข้อมูล การพัฒนาไปป์ไลน์ และการสร้างแดชบอร์ดจำนวนมากทำได้ผ่าน SQL

สิ่งที่แยกนักวิทยาศาสตร์ด้านข้อมูลที่ยอดเยี่ยมออกจากนักวิทยาศาสตร์ด้านข้อมูลที่ดีก็คือนักวิทยาศาสตร์ด้านข้อมูลที่ยอดเยี่ยมสามารถโต้แย้งข้อมูลได้มากเท่าที่ความสามารถของ SQL อนุญาต ส่วนใหญ่ของความสามารถในการใช้ SQL ทั้งหมดที่มีให้อย่างเต็มที่คือการรู้วิธีใช้ฟังก์ชันหน้าต่าง

อย่างที่บอก มาดำดิ่งกัน!

1. เดลต้าที่มี LEAD() และ LAG()

 
LEAD() และ LAG() ส่วนใหญ่จะใช้ในการเปรียบเทียบช่วงเวลาหนึ่งกับช่วงเวลาก่อนหน้าสำหรับเมตริกที่กำหนด เพื่อให้ตัวอย่างบางส่วน ...

• คุณสามารถรับส่วนต่างระหว่างยอดขายในแต่ละปีและยอดขายของปีที่แล้ว
• คุณสามารถรับส่วนต่างในจำนวนการลงชื่อสมัครใช้/Conversion/การเข้าชมเว็บไซต์แบบเดือนต่อเดือน
• คุณสามารถเปรียบเทียบผู้ใช้ปั่นเป็นรายเดือน

ตัวอย่าง:

แบบสอบถามต่อไปนี้แสดงวิธีที่คุณสามารถสอบถามการเปลี่ยนแปลงร้อยละรายเดือนในค่าใช้จ่าย

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. ผลรวมสะสมที่มี SUM() หรือ COUNT()

 
การคำนวณยอดรวมสามารถทำได้ง่ายๆ ผ่านฟังก์ชัน windows ที่ขึ้นต้นด้วย SUM() หรือ COUNT() นี่เป็นเครื่องมือที่มีประสิทธิภาพเมื่อคุณต้องการแสดงการเติบโตของเมตริกหนึ่งๆ เมื่อเวลาผ่านไป โดยเฉพาะอย่างยิ่ง จะมีประโยชน์ในสถานการณ์ต่อไปนี้:

• รับรายได้และค่าใช้จ่ายทั้งหมดตามช่วงเวลา
• รับเวลาที่ใช้กับแอพทั้งหมดต่อผู้ใช้
• รับ Conversion ทั้งหมดตามช่วงเวลา

ตัวอย่าง:

ตัวอย่างต่อไปนี้แสดงวิธีที่คุณสามารถรวมคอลัมน์ผลรวมของค่าใช้จ่ายรายเดือน:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. เส้นค่าเฉลี่ยเคลื่อนที่ด้วย AVG()

 
AVG() มีประสิทธิภาพมากในฟังก์ชัน windows เนื่องจากช่วยให้คุณสามารถคำนวณได้ ค่าเฉลี่ยการเคลื่อนที่ ล่วงเวลา.

ค่าเฉลี่ยเคลื่อนที่เป็นวิธีการคาดการณ์มูลค่าระยะสั้นที่เรียบง่ายแต่มีประสิทธิภาพ พวกเขายังมีประโยชน์อย่างมากในการทำให้เส้นโค้งผันผวนบนกราฟเรียบขึ้น โดยทั่วไป ค่าเฉลี่ยเคลื่อนที่จะใช้เพื่อวัดทิศทางทั่วไปของตำแหน่งที่สิ่งต่างๆ กำลังเคลื่อนที่

โดยเฉพาะอย่างยิ่ง…

• สามารถใช้เพื่อดูแนวโน้มทั่วไปของยอดขายรายสัปดาห์ (ค่าเฉลี่ยจะเพิ่มขึ้นเมื่อเวลาผ่านไปหรือไม่) นี่จะบ่งบอกถึงการเติบโตในฐานะบริษัท
• นอกจากนี้ยังสามารถใช้เพื่อดูแนวโน้มทั่วไปของ Conversion รายสัปดาห์หรือการเข้าชมเว็บไซต์ได้อีกด้วย

ตัวอย่าง:

ข้อความค้นหาต่อไปนี้เป็นตัวอย่างของการหาค่าเฉลี่ยเคลื่อนที่ 10 วันสำหรับ Conversion

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() มีประโยชน์อย่างยิ่งเมื่อคุณต้องการรับบันทึกแรกหรือบันทึกสุดท้าย ตัวอย่างเช่น หากคุณมีตารางเวลาที่สมาชิกโรงยิมมาที่โรงยิม และคุณต้องการรับวันที่ในวันแรกที่พวกเขามาที่โรงยิม คุณสามารถ PARTITION BY ลูกค้า (ชื่อ/id) และ ORDER BY วันที่ซื้อ จากนั้น เพื่อให้ได้แถวแรก คุณสามารถกรองแถวที่มี rowNumber เท่ากับหนึ่งได้

ตัวอย่าง:

ตัวอย่างนี้แสดงวิธีที่คุณสามารถใช้ ROW_NUMBER() เพื่อรับวันแรกที่สมาชิกแต่ละคน (ผู้ใช้) เข้าเยี่ยมชม

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

สรุปได้ว่า หากคุณต้องการบันทึกรายการแรกหรือรายการสุดท้าย ROW_NUMBER() เป็นวิธีที่ยอดเยี่ยมในการบรรลุเป้าหมายนั้น

5. บันทึกอันดับด้วย DENSE_RANK()

 
DENSE_RANK() คล้ายกับ ROW_NUMBER() ยกเว้นว่าจะส่งกลับอันดับเดียวกันสำหรับค่าที่เท่ากัน การจัดอันดับอย่างหนาแน่นนั้นค่อนข้างมีประโยชน์ในการดึงข้อมูลเรกคอร์ดยอดนิยม เช่น:

• หากคุณต้องการดึง 10 อันดับแรกของ Netflix ที่มีผู้ชมมากที่สุดในสัปดาห์นี้
• หากคุณต้องการได้ผู้ใช้ 100 อันดับแรกโดยพิจารณาจากเงินที่ใช้ไป
• หากคุณต้องการดูพฤติกรรมของผู้ใช้ที่ใช้งานน้อยที่สุด 1000 ราย

ตัวอย่าง:

หากคุณต้องการจัดอันดับลูกค้าอันดับต้นๆ ของคุณตามยอดขายทั้งหมด DENSE_RANK() จะเป็นฟังก์ชันที่เหมาะสมในการใช้งาน

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

ขอบคุณที่อ่าน!

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

และเช่นเคย ฉันขอให้คุณประสบความส�าเร็จในการเรียนรู้ของคุณ!

 
 
เทอเรนซ์ ชิน เป็นผู้คลั่งไคล้ข้อมูลที่มีประสบการณ์ 3 ปีขึ้นไปใน SQL และ 2 ปีขึ้นไปใน Python และบล็อกเกอร์เกี่ยวกับ Towards Data Science และ KDnuggets

Original. โพสต์ใหม่โดยได้รับอนุญาต

ที่มา: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html