ฟังก์ชัน Analytic

ฟังก์ชันสำหรับจัดการข้อมูลใน SQL Server

Ana Voicu

Data Engineer

FIRST_VALUE()

FIRST_VALUE(numeric_expression) 
    OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

คืนค่าแรกในชุดข้อมูลที่เรียงลำดับแล้ว

ส่วนประกอบของ clause OVER

Component	Status	Description
PARTITION by column	optional	แบ่งชุดผลลัพธ์ออกเป็น partition
ORDER BY column	mandatory	เรียงลำดับชุดผลลัพธ์
ROW_or_RANGE frame	optional	กำหนดขอบเขตของ partition

LAST_VALUE()

LAST_VALUE(numeric_expression) 
    OVER ([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

คืนค่าสุดท้ายในชุดข้อมูลที่เรียงลำดับแล้ว

ขอบเขตของ partition

RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary

Boundary	Description
UNBOUNDED PRECEDING	แถวแรกใน partition
UNBOUNDED FOLLOWING	แถวสุดท้ายใน partition
CURRENT ROW	แถวปัจจุบัน
PRECEDING	แถวก่อนหน้า
FOLLOWING	แถวถัดไป

ตัวอย่าง FIRST_VALUE() และ LAST_VALUE()

SELECT
    first_name + ' ' + last_name AS name,
    gender,
    total_votes AS votes,    
    FIRST_VALUE(total_votes) 
    OVER (PARTITION BY gender ORDER BY total_votes) AS min_votes,
    LAST_VALUE(total_votes) 
        OVER (PARTITION BY gender ORDER BY total_votes 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_votes
FROM voters;

| name            | gender | votes | min_votes | max_votes |
|-----------------|--------|-------|-----------|-----------|
| Michele Suarez  | F      | 20    | 20        | 189       |
| ...             | ...    | ...   | 20        | 189       |
| Marcus Jenkins  | M      | 16    | 16        | 182       |
| Micheal Vazquez | M      | 18    | 16        | 182       |

LAG() และ LEAD()

LAG(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

เข้าถึงข้อมูลจากแถวก่อนหน้าในชุดผลลัพธ์เดียวกัน

LEAD(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

เข้าถึงข้อมูลจากแถวถัดไปในชุดผลลัพธ์เดียวกัน

ตัวอย่าง LAG() และ LEAD()

SELECT 
    broad_bean_origin AS bean_origin,
    rating,
    cocoa_percent,
    LAG(cocoa_percent) OVER(ORDER BY rating ) AS percent_lower_rating,
    LEAD(cocoa_percent) OVER(ORDER BY rating ) AS percent_higher_rating
FROM ratings
WHERE company = 'Felchlin'
ORDER BY rating ASC;

| bean_origin        | rating | cocoa_percent | percent_lower_rating | percent_higher_rating |
|--------------------|--------|---------------|----------------------|-----------------------|
| Grenada            | 3      | 0.58          | NULL                 | 0.62                  |
| Dominican Republic | 3.75   | 0.62          | 0.58                 | 0.64                  |
| Madagascar         | 3.75   | 0.64          | 0.74                 | 0.65                  |
| Venezuela          | 4      | 0.65          | 0.74                 | NULL                  |

มาฝึกกันเถอะ!

ฟังก์ชันสำหรับจัดการข้อมูลใน SQL Server