转换表以平滑一列中的记录

Question

这种情况（现实生活中的情况）是：

我有一个带有“log”记录的表，为了简单起见，我们假设只有列NAME，TIME，STATE（实际上没有更多）。共有100万行，其中有100个名称（计算机）和10个状态（生产、空闲、自动、维护…） 这可以解释为：*在t0计算机1处于空闲状态，在t1计算机1处于生产状态。。。等等。你知道吗

I ask your help with replacing IDLE states with their preceding state, if there are less then 5 consecutive idles. Every other state resets the "idle counter", if there are more then 5 idles, keep the 6th and subsequent.

这最好用一个例子来描述，因此对于下面的灰色假设：

• 列STATE是内联写的
• 只有一个名字
• 由字母ABCD...描述的状态，I是空闲状态
• 数据按时间排序，左边是最早的

STATE column I HAVE: AABBCIICAABAIIIIIIIIIAAAIIIBIIAACC...
STATE column I NEED: AABBCCCCAABAAAAAAIIIIAAAAAABBBAACC...

你可以很容易地看到，不到5个空闲状态被前一个状态“运行”了。你知道吗

我们决定，这些短暂的空闲时间是假阳性的，应该被平滑处理（更长的延迟时间，如代码示例中所示）。我有241M记录表，我需要“重新计算”到这个新的格式。 我不是要找一个简单的select，我需要执行一个更新，所以表保持这个新的格式，我以后可以像现在一样使用它。你知道吗

我不是一个完全的sql初学者，到目前为止，我怀疑方法是使用OVER子句，并在分区和前面和后面，但这是我遇到的问题。你知道吗

如果需要的话，我可以使用一种方法做*创建表作为选择。。。*把旧桌子扔了。 我对python也相当精通，但我看不到一种有效地获取、处理和运行这么多更新的方法。你知道吗

非常感谢你的建议。你知道吗

Answer 1

这对你有用吗（我用计数器代替时间戳）：

    create table tsovf (id varchar2(10), rn number, cond varchar2(1));

    insert into tsovf 
    select 'comp1', rownum, substr('AABBCIICAABAIIIIIIIIIAAAIIIBIIAACC',rownum,1)
    from dual
    connect by level<length('AABBCIICAABAIIIIIIIIIAAAIIIBIIAACC');

    insert into tsovf 
    select 'comp2', rownum, substr('AABBCIICAABAIIIIIIIIIAAAIIIBIIAACC',rownum,1)
    from dual
    connect by level<length('AABBCIICAABAIIIIIIIIIAAAIIIBIIAACC');

    with fq as (select id, rn, cond, 
    lag( cond,1) over (partition by id order by rn) prv1,
    lag( cond,2) over (partition by id order by rn) prv2,
    lag( cond,3) over (partition by id order by rn) prv3,
    lag( cond,4) over (partition by id order by rn) prv4,
    lag( cond,5) over (partition by id order by rn) prv5
    from tsovf),
    sq as (
    select id,rn, 
    case 
    when cond='I' and prv1!='I' then prv1
    when cond='I' and prv2!='I' then prv2
    when cond='I' and prv3!='I' then prv3
    when cond='I' and prv4!='I' then prv4
    when cond='I' and prv5!='I' then prv5
    else cond
    end cond
    from fq)
    select * 
    from sq
    order by id,rn;

输出为：

    comp1   1   A
    comp1   2   A
    comp1   3   B
    comp1   4   B
    comp1   5   C
    comp1   6   C
    comp1   7   C
    comp1   8   C
    comp1   9   A
    comp1   10  A
    comp1   11  B
    comp1   12  A
    comp1   13  A
    comp1   14  A
    comp1   15  A
    comp1   16  A
    comp1   17  A
    comp1   18  I
    comp1   19  I
    comp1   20  I
    comp1   21  I
    comp1   22  A
    comp1   23  A
    comp1   24  A
    comp1   25  A
    comp1   26  A
    comp1   27  A
    comp1   28  B
    comp1   29  B
    comp1   30  B
    comp1   31  A
    comp1   32  A
    comp1   33  C
    comp2   1   A
    comp2   2   A
    comp2   3   B
    comp2   4   B
    comp2   5   C
    comp2   6   C
    comp2   7   C
    comp2   8   C
    comp2   9   A
    comp2   10  A
    comp2   11  B
    comp2   12  A
    comp2   13  A
    comp2   14  A
    comp2   15  A
    comp2   16  A
    comp2   17  A
    comp2   18  I
    comp2   19  I
    comp2   20  I
    comp2   21  I
    comp2   22  A
    comp2   23  A
    comp2   24  A
    comp2   25  A
    comp2   26  A
    comp2   27  A
    comp2   28  B
    comp2   29  B
    comp2   30  B
    comp2   31  A
    comp2   32  A
    comp2   33  C