SQL-显示从一个组到另一个组的分组值更改(取决于时间)

我创建了一个按1分钟间隔分组的SQL视图:

CREATE VIEW `summary` AS
    WITH shops AS (    
        SELECT    
            shop,
            timestamp DIV 1000000 DIV 60 AS timestamp,
            SUM(amount) AS sum_amount
        FROM 
            products
        GROUP BY timestamp DIV 1000000 DIV 60, shop
    )    

    SELECT * FROM shops

现在我还想有一个字段,它可以给我 sum_amount 来自上一个分组行/间隔。“前一组”指具有下一个较低时间戳的组。

我试着再加一个 WITH 声明并从两个子查询中选择:

    previous_group AS
    (    
        SELECT 
            s2.sum_amount AS previous_sum_amount
        FROM
            shops s1, shops s2
        WHERE
            s2.timestamp + (1000000 * 60) = s1.timestamp
    )

    SELECT
        shop,
        timestamp,
        sum_amount,
        sum_amount / previous_sum_amount AS change
    FROM
        shops, previous_group

但是,此查询将永远运行。如何实现在 总额 从一个时间间隔到下一个时间间隔?

谢谢您。

编辑: 删除了 previous_group 然后用 LAG 窗户功能-感谢里克·詹姆斯。

SELECT
    shop,
    timestamp,
    sum_amount,
    sum_amount / LAG(sum_amount, 1) OVER (PARTITION BY shop ORDER BY timestamp DESC) AS change
FROM
    shops, previous_group