基于事件时间和活动列计算列的结束时间

下面是一个示例表,其中包含数据库中单个拆分测试对象的事件。拆分测试可以打开和关闭,如中所述 is_active

我正在尝试转换表,以便每次运行测试时,我们都能为每个分支获得一行,并且 started_at 和 ended_at 价值观而不是 event_time 。如果测试尚未关闭,则 结束于 应该是 null .

这就是我想要的结果:

我在努力拉 结束于 基于 你是活跃的吗 .我试过这样的方法

select 
    split_test_id, 
    branch, 
    event_time as started_at,
    last_value(started_at) over (partition by split_test_id, branch, is_active order by event_time) as ended_at
from example
where is_active = true

但我知道它们在逻辑上有缺陷。

SQL来创建上面的示例表:

drop table if exists example;

create table example (
    event_id varchar, -- unique
    split_test_id varchar, 
    branch varchar,
    is_active boolean,
    started_at timestamp_ntz
);
 
insert into example values
    ('1', '595aa50', 'a', true, '2021-11-11 22:53:08.360'),
    ('2', '595aa50', 'b', true, '2021-11-11 22:53:08.360'),
    ('3', '595aa50', 'a', false, '2021-11-11 22:34:39.235'),
    ('4', '595aa50', 'b', false, '2021-11-11 22:34:39.235'),
    ('5', '595aa50', 'a', true, '2021-11-02 23:40:27.001'),
    ('6', '595aa50', 'b', true, '2021-11-02 23:40:27.001'),
    ('7', '595aa50', 'a', false, '2021-11-02 20:54:29.620'),
    ('8', '595aa50', 'b', false, '2021-11-02 20:54:29.620'),
    ('9', '595aa50', 'a', true, '2021-11-02 20:31:08.297'),
    ('10', '595aa50', 'b', true, '2021-11-02 20:31:08.297'),
    ('11', '595aa50', 'c', false, '2021-10-05 20:33:36.394'),
    ('12', '595aa50', 'b', false, '2021-10-05 20:33:36.394'),
    ('13', '595aa50', 'c', true, '2021-09-15 21:33:58.856'),
    ('14', '595aa50', 'b', true, '2021-09-15 21:33:58.856'),
    ('15', '595aa50', 'c', false, '2021-09-08 18:42:35.728'),
    ('16', '595aa50', 'b', false, '2021-09-08 18:42:35.728'),
    ('17', '595aa50', 'c', true, '2021-09-01 23:09:15.596'),
    ('18', '595aa50', 'b', true, '2021-09-01 23:09:15.596');

select 
    *
from example;