为每组选择第一行和最后一行，并取MySQL中的列值差？

WITH RankedData AS (
    SELECT 
        id,
        group_id,
        value,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id) AS rn
    FROM data_table
)
SELECT 
    first_row.group_id,
    first_row.value AS first_value,
    last_row.value AS last_value,
    last_row.value - first_row.value AS value_difference
FROM (
    SELECT group_id, value
    FROM RankedData
    WHERE rn = 1
) AS first_row
JOIN (
    SELECT group_id, value
    FROM RankedData
    WHERE rn = (SELECT MAX(rn) FROM RankedData WHERE group_id = RankedData.group_id)
) AS last_row
ON first_row.group_id = last_row.group_id;