postgresql - 在 PostgreSQL 的记录中查找最后出现的值

Question

我不是 SQL 的新手，但我是 PostgreSQL 的新手，我真的很难在不同的环境中调整我当前的知识。

我正在尝试创建一个变量来捕获某人是否在 0/1 时间内保持活跃、跳过或流失系列变量。例如，在下面的数据中，我的数据集将包含变量 id、time 和 voted，我将创建变量“skipped ":

id     time     voted     skipped
1      1        1         active
1      2        0         skipped
1      3        1         active
2      1        1         active
2      2        0         churned
2      3        0         churned
3      1        1         active
3      2        1         active
3      3        0         churned

编码“skipped”的规则非常简单:如果 1 是最后一条记录，则此人是“活跃的”，任何零都算作“skipped”，但如果 0 是最后一条记录，则此人是“流失的” .

id = 1 的记录是一个跳过，因为 id 在时间 2 为 0 后在时间 3 非零。另外两种情况，0 是最终值，因此它们被“搅动”。谁能帮忙？我整天都在胡思乱想，结果碰壁了。

Answer 1

这不是特别优雅，但它应该能满足您的需求:

with votes as (
  select
    id, time, voted,
    max(time) over (partition by id) as max_time
  from voter_data
)
select
  v1.id, v1.time, v1.voted,
  case
    when v1.voted = 1 then 'active'
    when v2.voted = 1 then 'skipped'
    else 'churned'
  end as skipped
from
  votes v1
  join votes v2 on
    v1.id = v2.id and
    v1.max_time = v2.time

简而言之，我们首先找出每个选民 ID 的最后一条记录，然后我们对结果表进行自连接以仅隔离最后一条 ID。

这有可能产生多个结果——如果可以同时对同一 ID 投票两次。如果是这种情况，您需要 row_number() 而不是 max()。

您的数据结果:

1   1   1   'active'
1   2   0   'skipped'
1   3   1   'active'
2   1   1   'active'
2   2   0   'churned'
2   3   0   'churned'
3   1   1   'active'
3   2   1   'active'
3   3   0   'churned'