使用滞后函数的SQL [英] SQL using lag function

问题描述

我要执行的操作是使用以下规则创建"ID_DESLIG"列:

What i want to do is to create a column 'ID_DESLIG' with the following rules:

• 对于同一面板，当STATE为"LIGADO"时，ID_DESLIG将为先前STATE的ID为"DESLIGADO"；

• when STATE is 'LIGADO' then ID_DESLIG will be the id of the previous STATE is 'DESLIGADO', for the same panel;

当STATE为'DESLIGADO'时，ID_DESLIG将为当前行的ID；

when STATE is 'DESLIGADO' then ID_DESLIG will be the id of the current row;

我想要的例子

预先感谢您的帮助

推荐答案

我认为您想要:

select t.*,
       (case when state = 'DESLIGADO' then id
             else max(case when state = 'DESLIGADO' then id end) over (order by id)
        end) as desligado_id
from t;

反过来，这可以简化为:

In turn, this can be simplified to:

max(case when state = 'DESLIGADO' then id end) over (order by id)

您可以使用lag()对此进行表述，但前提是您知道状态始终是交错的.

You could phrase this using lag() but only if you know that the states are always interleaved.

在标准SQL(和某些数据库)中，这也可以表示为:

In standard SQL (and some databases), this could also be expressed as:

max(id) filter (where state = 'DESLIGADO') over (order by id)

这篇关于使用滞后函数的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！