mysql - SQL - 显示分组值从一组到另一组的变化(取决于时间)

Question

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

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

但是，该查询将永远运行。如何实现显示从一个时间间隔到下一个时间间隔的 sum_amount 变化？

谢谢。

编辑:删除了 previous_group 表并使用 LAG 窗口函数解决了它 - 感谢 Rick James。

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

Answer 1

在VIEW中，不需要使用WITH和第二个SELECT。只需执行第一个SELECT即可。

对于“更改”，不要使用 WITH，而是使用 LAG。