时间戳

首页 > 解决方案 > Redshift AWS - 在子查询和 cte 中使用 lag() 更新表

sql-update - Redshift AWS - 在子查询和 cte 中使用 lag() 更新表

问题描述

我有一个包含以下条目的 Redshift 数据库：表名 = 订阅者

time_at calc_subscribers calc_unsubscribers current_subscribers
2021-07-02 07:30:00 0 0 0
2021-07-02 07:45:00 39 8 0
2021-07-02 08:00:00 69 17 0
2021-07-02 08:15:00 67 21 0
2021-07-02 08:30:00 48 23 0

目标是用之前的值计算 current_subscribers。

current_subscribers = calc_subscribers - calc_unsubscribers + previous_current_subscribers

我执行以下操作：

UPDATE subscribers sa
  SET current_subscribers = COALESCE( sa.calc_subscribers - sa.calc_unsubscribers + sub.previous_current_subscribers,0)
  FROM (
    SELECT
      time_at,
      LAG(current_subscribers, 1) OVER
      (ORDER BY time_at desc) previous_current_subscribers
      FROM subscribers
  ) sub
  WHERE sa.time_at = sub.time_at

问题是在子查询“sub”中生成了一个基于表中当前值的表，因此previous_current_subscribers 始终为0。而不是逐行遍历这个。所以结果是： current_subscribers = calc_subscribers - calc_unsubscribers + 0 我也已经用 CTE 试过了，不幸的是没有成功：

结果应如下所示：

time_at calc_subscribers calc_unsubscribers current_subscribers
2021-07-02 07:30:00 0 0 0
2021-07-02 07:45:00 39 8 31
2021-07-02 08:00:00 69 17 83
2021-07-02 08:15:00 67 21 129
2021-07-02 08:30:00 48 95 82

我很感激任何想法。

标签： sql-updatesubqueryamazon-redshiftcommon-table-expressionwindow-functions

解决方案

您遇到的问题是您想在计算当前行时使用一行的结果。这是递归的，我认为在这种情况下你可以这样做，但很昂贵。

您要查找的结果是该行和之前行的所有 calc_subscribers 的总和减去该行和之前行的所有 calc_unsubscribers 的总和。这是 2 个窗口函数之间的区别 - 求和。

sum(calc_subscribers) over (order by time_at desc rows unbounded preceding) - sum(calc_unsubscribers) over (order by time_at desc rows unbounded preceding) as current_subscribers

推荐阅读