如何在时间序列表中添加具有重复订单号的列

使用 SQL 聚合重复订单号的时间序列数据

该解决方案使用 SQL 创建模块化查询，通过时间序列聚合处理非唯一订单号。

-- Define a Common Table Expression (CTE) to track transitions between order IDs
WITH order_transitions AS (
    SELECT
        *,
        LAG(order_id) OVER (ORDER BY start) AS prev_id,
        LEAD(order_id) OVER (ORDER BY start) AS next_id
    FROM production
)
-- Create a query to handle gaps and the first line issue
SELECT
    order_id,
    MIN(start) AS start,
    MAX(end) AS end,
    SUM(count) AS total_count
FROM (
    SELECT
        order_id,
        start,
        end,
        count,
        CASE
            WHEN prev_id != order_id OR prev_id IS  THEN ROW_NUMBER() OVER (ORDER BY start)
            ELSE 
        END AS grouping_flag
    FROM order_transitions
) t
GROUP BY order_id, grouping_flag
ORDER BY start;

使用过程 SQL 和 PL/pgSQL 进行自定义聚合

这种方法使用 PostgreSQL 中的 PL/pgSQL 进行动态和迭代的逐行处理。

DO $$
DECLARE
    curr_order_id INTEGER;
    curr_start TIMESTAMP;
    curr_end TIMESTAMP;
    curr_count INTEGER;
BEGIN
    -- Create a temp table to hold results
    CREATE TEMP TABLE aggregated_data (
        order_id INTEGER,
        start TIMESTAMP,
        end TIMESTAMP,
        count INTEGER
    );
    -- Loop through each row in production
    FOR row IN SELECT * FROM production ORDER BY start LOOP
        IF curr_order_id IS DISTINCT FROM row.order_id THEN
            -- Insert previous aggregated row
            INSERT INTO aggregated_data VALUES (curr_order_id, curr_start, curr_end, curr_count);
            -- Reset for new group
            curr_order_id := row.order_id;
            curr_start := row.start;
            curr_end := row.end;
            curr_count := row.count;
        ELSE
            -- Aggregate within the same group
            curr_end := row.end;
            curr_count := curr_count + row.count;
        END IF;
    END LOOP;
END $$;

具有 Node.js 和 SQL 集成的 JavaScript 后端解决方案

该后端解决方案使用 Node.js 动态处理 SQL 数据，结合错误处理和模块化功能。

const { Client } = require('pg'); // PostgreSQL client
const aggregateData = async () => {
    const client = new Client({
        user: 'user',
        host: 'localhost',
        database: 'production_db',
        password: 'password',
        port: 5432
    });
    try {
        await client.connect();
        const query = `WITH lp AS (
            SELECT *, LEAD(order_id) OVER (ORDER BY start) AS next_id FROM production
        )
        SELECT order_id, MIN(start) AS start, MAX(end) AS end, SUM(count) AS count
        FROM lp
        GROUP BY order_id
        ORDER BY MIN(start);`;
        const result = await client.query(query);
        console.log(result.rows);
    } catch (err) {
        console.error('Error executing query:', err);
    } finally {
        await client.end();
    }
};
aggregateData();

1. 内容灵感来自 PostgreSQL 官方文档中的 SQL 窗口函数和聚合示例。欲了解更多详情，请访问 PostgreSQL 窗口函数文档 。
2. 改编自数据库设计和分析指南的真实用例 SQL 小屋 ，SQL 见解的优秀资源。
3. 处理时间序列数据的最佳实践源自以下教程 极客们的极客们 ，一个编程和 SQL 基础知识的平台。