时间戳

首页 > 解决方案 > 随时间更新列

sql - 随时间更新列

问题描述

我有下表：

    id  Prod_id Year    Quartar Start_flag  End_flag    status
1   A001    2015    1   0   0   0
2   A001    2015    2   1   0   0
3   A001    2015    3   0   0   0
4   A001    2015    4   0   0   0
5   A001    2016    1   0   0   0
6   A001    2016    2   0   0   0
7   A001    2016    3   0   1   0
8   A001    2016    4   0   0   0
9   B002    2015    1   0   0   0
10  B002    2015    2   0   0   0
11  B002    2015    3   0   0   0
12  B002    2015    4   1   0   0
13  B002    2016    1   0   1   0
14  B002    2016    2   0   0   0
15  B002    2016    3   0   0   0
16  B002    2016    4   0   0   0
17  c003    2015    1   0   1   0
18  c003    2015    2   0   0   0
19  c003    2015    3   0   1   0
20  c003    2015    4   1   0   0
21  c003    2016    1   0   0   0
22  c003    2016    2   0   0   0
23  c003    2016    3   0   0   0
24  c003    2016    4   0   0   0

该表具有使用 (Prod_id,Year,Quarter) 的唯一索引和聚集在 (Prod_id,Year,Quarter) 上的索引。换句话说，该表按我的表排序，具有以下内容：

我想通过时间从 start_flag 更新状态列 = 1 到 start_flag 之后的第一个 end_flag 之前的四分之一。如果没有结束标志，则更新将持续到选定产品的最后一条记录。

执行更新后，更新后的表应该是这样的：

 id Prod_id Year    Quartar Start_flag  End_flag    status
1   A001    2015    1   0   0   0
2   A001    2015    2   1   0   1
3   A001    2015    3   0   0   1
4   A001    2015    4   0   0   1
5   A001    2016    1   0   0   1
6   A001    2016    2   0   0   1
7   A001    2016    3   0   1   0
8   A001    2016    4   0   0   0
9   B002    2015    1   0   0   0
10  B002    2015    2   0   0   0
11  B002    2015    3   0   0   0
12  B002    2015    4   1   0   1
13  B002    2016    1   0   1   0
14  B002    2016    2   0   0   0
15  B002    2016    3   0   0   0
16  B002    2016    4   0   0   0
17  c003    2015    1   0   1   0
18  c003    2015    2   0   0   0
19  c003    2015    3   0   1   0
20  c003    2015    4   1   0   1
21  c003    2016    1   0   0   1
22  c003    2016    2   0   0   1
23  c003    2016    3   0   0   1
24  c003    2016    4   0   0   1

我已经使用游标植入了使用 pl/pgsql 的解决方案。但是使用游标的解决方案在我的情况下需要 30 多个小时才能更新此列。是否有其他方法可以在不使用游标的情况下进行此更新？我正在使用 PostgreSQL 10.6。请指教&谢谢。

标签： sqlpostgresql

解决方案

免责声明：我并不真正了解您的季度用例以及start_flag随着时间的推移范围应该如何工作。所以下面的解决方案是不看时间部分的。如果您可以稍微解释一下您的时间示例，我相信以下解决方案只需要稍作调整（例如PARTITION子句）即可为您工作。

演示：db<>小提琴

我的样本数据：

id   start_flag   end_flag
1    0            0
2    1            0
3    0            0
4    0            0
5    0            1
6    0            0
7    1            0
8    0            1
9    0            0
10   0            1
11   0            0
12   0            1
13   1            0
14   0            0
16   0            0

查询：

SELECT
    *,
    GREATEST(
         first_value(start_flag - end_flag) OVER (PARTITION BY sum ORDER BY id), 
         0
    ) as status
FROM (
    SELECT 
        *,
        SUM(start_flag + end_flag) OVER (ORDER BY id)
    FROM mytable
) s

结果：

id   start_flag   end_flag   status
1    0            0          0
2    1            0          1
3    0            0          1
4    0            0          1
5    0            1          0
6    0            0          0
7    1            0          1
8    0            1          0
9    0            0          0
10   0            1          0
11   0            0          0
12   0            1          0
13   1            0          1
14   0            0          1
16   0            0          1

解释：

使用窗口函数：

  1. SUM每个潜在“状态变化”（start_flag或end_flag）的累积。这会创建组。在每个组中都存在相同的状态。（见SUM小提琴列）
  2. first_value窗口函数：为每个组检查状态更改是由start_flag（正）还是end_flag（负）完成
  3. GREATEST()将负值归一化为零。

编辑：添加prod_id和year/quarters

总而言之，整个用例非常相似——仅使用窗口函数：

演示：db<>小提琴

  1. id我不是按顺序订购，而是按顺序订购year, quarter
  2. 我没有查看整个数据集，而是使用prod_id窗口函数的分区（在 上）：

扩展查询：

SELECT
    *, 
    GREATEST(
         first_value(start_flag - end_flag) OVER (PARTITION BY prod_id, sum ORDER BY year, quarter), 
         0
    ) as status
FROM (
    SELECT 
        *,
        SUM(start_flag + end_flag) OVER (PARTITION BY prod_id ORDER BY year, quarter)
    FROM mytable
) s

推荐阅读