在DB2数据库中，基于多列分组并利用时间极值差计算数值平均变化率的SQL实现方法

我完全理解你的需求——按NAME和SLOT分组后，用每组内时间最早和最晚对应的AMOUNT差值，除以这两个时间点的差，得到AMOUNT的平均变化率，这本质上就是均值定理里的平均变化率计算对吧？

针对DB2数据库，我给你提供两种简洁的实现方案，都能准确得到你想要的结果：

方法一：利用ROW_NUMBER标记首尾记录并自连接 #

这种方法通过给每组的记录按时间排序编号，找到最早和最晚的那条记录，再通过自连接获取对应的值进行计算：

WITH DUMMY_TABLE ("ID", "NAME", "UNIX_TIMESTAMP", "SLOT", "AMOUNT") AS (
VALUES
(1, 'dani', 1686311907, 0, 22.0),
(2, 'dani', 1686311988, 0, 25.0),
(3, 'dani', 1686320086, 1, 13.0),
(4, 'dani', 1686320095, 1, 14.0),
(5, 'kane', 1686311032, 0, 8.0),
(6, 'kane', 1686311099, 0, 27.0),
(7, 'kane', 1686322000, 1, 12.0),
(8, 'kane', 1686322045, 1, 17.0)
),
ranked_data AS (
    SELECT 
        NAME,
        SLOT,
        AMOUNT,
        UNIX_TIMESTAMP,
        -- 按时间升序编号，1是组内最早的记录
        ROW_NUMBER() OVER (PARTITION BY NAME, SLOT ORDER BY UNIX_TIMESTAMP ASC) AS rn_asc,
        -- 按时间降序编号，1是组内最晚的记录
        ROW_NUMBER() OVER (PARTITION BY NAME, SLOT ORDER BY UNIX_TIMESTAMP DESC) AS rn_desc
    FROM DUMMY_TABLE
)
SELECT
    r1.NAME,
    r1.SLOT,
    -- 计算平均变化率：(末值-初值)/(末时间-初时间)
    (r2.AMOUNT - r1.AMOUNT) / (r2.UNIX_TIMESTAMP - r1.UNIX_TIMESTAMP) AS avg_rate_of_change
FROM ranked_data r1
JOIN ranked_data r2
    ON r1.NAME = r2.NAME
    AND r1.SLOT = r2.SLOT
    AND r1.rn_asc = 1
    AND r2.rn_desc = 1;

方法二：利用FIRST_VALUE/LAST_VALUE窗口函数直接获取极值 #

这种方法更直观，直接通过窗口函数提取每组的首尾AMOUNT和时间极值，再去重得到结果：

WITH DUMMY_TABLE ("ID", "NAME", "UNIX_TIMESTAMP", "SLOT", "AMOUNT") AS (
VALUES
(1, 'dani', 1686311907, 0, 22.0),
(2, 'dani', 1686311988, 0, 25.0),
(3, 'dani', 1686320086, 1, 13.0),
(4, 'dani', 1686320095, 1, 14.0),
(5, 'kane', 1686311032, 0, 8.0),
(6, 'kane', 1686311099, 0, 27.0),
(7, 'kane', 1686322000, 1, 12.0),
(8, 'kane', 1686322045, 1, 17.0)
),
grouped_extremes AS (
    SELECT
        NAME,
        SLOT,
        -- 获取组内时间最早的AMOUNT
        FIRST_VALUE(AMOUNT) OVER (PARTITION BY NAME, SLOT ORDER BY UNIX_TIMESTAMP ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amount,
        -- 获取组内时间最晚的AMOUNT
        LAST_VALUE(AMOUNT) OVER (PARTITION BY NAME, SLOT ORDER BY UNIX_TIMESTAMP ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount,
        -- 获取组内最小时间戳
        MIN(UNIX_TIMESTAMP) OVER (PARTITION BY NAME, SLOT) AS min_ts,
        -- 获取组内最大时间戳
        MAX(UNIX_TIMESTAMP) OVER (PARTITION BY NAME, SLOT) AS max_ts
    FROM DUMMY_TABLE
)
SELECT DISTINCT
    NAME,
    SLOT,
    (last_amount - first_amount) / (max_ts - min_ts) AS avg_rate_of_change
FROM grouped_extremes;

这两种方法都能完美实现你的需求，你可以根据自己的习惯选择其中一种。需要注意的是，如果组内只有一条记录，时间差会为0导致除法错误，你可以根据实际情况添加HAVING MAX(UNIX_TIMESTAMP) != MIN(UNIX_TIMESTAMP)来过滤这类分组。

备注：内容来源于stack exchange，提问作者Dani