基于时间的SQL聚合

创建一些数据来处理.

-- Simulate the input data into a table called NodeAddresses.
DROP TABLE IF EXISTS NodeAddresses;
CREATE TABLE NodeAddresses(area_date DATETIME NOT NULL
                                , area_name CHAR(5) NOT NULL
                                , node_address BIGINT NOT NULL
                                );
                                
INSERT INTO NodeAddresses(area_date
                        , area_name
                        , node_address
                        )
VALUES ('2024-01-01 08:15:00', 'area1', 1)
    , ('2024-01-01 08:20:00', 'area1', 1)
    , ('2024-01-01 08:25:00', 'area1', 1)
    , ('2024-01-01 08:30:00', 'area1', 1)
    , ('2024-01-01 08:45:00', 'area1', 1)
    , ('2024-01-01 09:00:00', 'area1', 1)
    , ('2024-01-01 08:15:00', 'area2', 2)
    , ('2024-01-01 08:20:00', 'area2', 2)
    , ('2024-01-01 09:00:00', 'area2', 1)
    , ('2024-01-01 09:05:00', 'area2', 2)
    , ('2024-01-01 09:10:00', 'area2', 2);

查找每个 node 地址和区域的上一个日期和下一个日期.使用该值计算该 node 地址在该区域中的时间差.根据您的10分钟间隔条件调整时差.确定每个区域的时间总和.

SELECT subq3.area_name
    , SUM(subq3.minutes_spent_in_area) AS total_minutes_in_area
FROM(
    SELECT CASE 
            WHEN (minutes_since_previous >= 10 OR minutes_since_previous IS NULL) AND (minutes_till_next >= 10 OR minutes_till_next IS NULL)
                THEN 5
            WHEN minutes_since_previous >= 10
                THEN 0
            ELSE minutes_since_previous
        END AS minutes_spent_in_area
        , area_name
    FROM(
        SELECT TIMESTAMPDIFF(MINUTE, previous_area_date, area_date) AS minutes_since_previous
            , TIMESTAMPDIFF(MINUTE, area_date, next_area_date) AS minutes_till_next
            , area_name
        FROM(
            SELECT area_date
                , LAG(area_date) OVER(PARTITION BY node_address, area_name ORDER BY area_date ASC) AS previous_area_date
                , LEAD(area_date) OVER(PARTITION BY node_address, area_name ORDER BY area_date ASC) AS next_area_date
                , area_name
                , node_address
            FROM NodeAddresses
            ) subq
        ) subq2
    ) subq3
GROUP BY subq3.area_name;

自己试试:db<>fiddle