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

使用 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();

有关 SQL 时间序列聚合的常见问题

1. 目的是什么 LEAD() 和 LAG() 在 SQL 中？
2. 这 LEAD() 函数从下一行获取值，而 LAG() 检索上一行的值。它们用于识别行中的转换或更改，例如跟踪行中的更改 订单号。
3. 我该如何使用 GROUP BY 对于时间序列数据？
4. 您可以使用 GROUP BY 基于公共列聚合行，例如 订单号，同时应用聚合函数，例如 SUM() 或者 MAX() 整合整个团队的价值观。
5. 有什么好处 WITH 通用表表达式 (CTE)？
6. CTE 允许您定义易于读取和重用的临时结果集，从而简化查询。例如，CTE 可以在聚合之前识别组的开始和结束。
7. 我可以使用递归查询进行时间序列聚合吗？
8. 是的！递归查询对于链接相互依赖的数据行非常有用。例如，您可以“链接”具有重叠时间的行以进行更复杂的聚合。
9. 处理重叠时间范围时如何确保准确性？
10. 为了避免重复计算，请在查询中使用条件逻辑，例如过滤或设置边界。组合 CASE 带有窗口函数的语句可以帮助管理这些重叠。

总结 SQL 聚合见解

了解如何处理重复 订单号 时间序列数据中的值对于准确的数据处理至关重要。本文重点介绍了 CTE 和窗口函数等各种技术，以简化复杂的查询并确保有意义的结果。这些策略对于涉及重叠或碎片订单的场景至关重要。

无论您是构建生产仪表板还是分析时间敏感数据，这些 SQL 技能都将提升您的能力。将模块化查询设计与高级功能相结合可确保您的解决方案既高效又可维护。在您的项目中应用这些方法来释放时间序列数据分析的全部潜力！ 😊