Snowflake SQL查询以筛选行，直到列值第一次出现为止

Question

希望你做得很好。。。我正试图输出这些行，直到值"0"第一次出现；编码完成点击"；在输入表中(列->event_type(。。我需要排除第一次出现值"0"后的行；编码完成点击"；按event_time列升序排列。。请查找输入表和输出表的DDL。。你能帮我一下吗。。

Input
Create table ##input
(event_time datetime2,
event_time varchar(1000),
user_id varchar(30),
ctextid int,
vbillid int)
insert into ##input values
('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
('1/12/2022 1:46:02 PM','Start Work Clicked','am31','10021','21'),
('1/12/2022 1:51:02 PM','Home Button Clicked','am31','10021','21'),
('1/13/2022 1:21:02 PM','VisitBill Button Clicked','am12','8921','31'),
('1/13/2022 1:31:02 PM','Home Button Clicked','am12','8921','31'),
('1/13/2022 1:51:02 PM','VisitBill Button Clicked','am13','8921','31'),
('1/13/2022 2:51:02 PM','Coding Complete Clicked','am13','8921','31'),
('1/13/2022 2:57:02 PM',' Icon Clicked','am14','8921','31'),
('1/13/2022 3:57:02 PM','VisitBill Button Clicked','am14','8921','31'),
('1/13/2022 4:10:02 PM','Coding Complete Clicked','am14','8921','31'),
('1/15/2022 4:10:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:18:02 PM','Coding Complete Clicked','am78','9213','34'),
('1/15/2022 4:38:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:50:02 PM','Coding Complete Clicked','am78','9213','34'),
('1/15/2022 4:57:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 5:20:02 PM','Coding Complete Clicked','am78','9213','34')
output
create table ##output
(event_time datetime2,
event_time varchar(1000),
user_id varchar(30),
ctextid int,
vbillid int)
insert into ##output values
('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
('1/13/2022 1:21:02 PM','VisitBill Button Clicked','am12','8921','31'),
('1/13/2022 1:31:02 PM','Home Button Clicked','am12','8921','31'),
('1/13/2022 1:51:02 PM','VisitBill Button Clicked','am13','8921','31'),
('1/13/2022 2:51:02 PM','Coding Complete Clicked','am13','8921','31'),
('1/15/2022 4:10:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:18:02 PM','Coding Complete Clicked','am78','9213','34')

Answer 1

你不能做一些类似的事情吗

INSERT INTO ##output
( event_time
, event_type
, user_id
, ctextid
, vbillid)
SELECT event_time
, event_type
, user_id
, ctextid
, vbillid
FROM ##input
WHERE event_time <
(SELECT TOP(1) event_time FROM ##input WHERE event_type = 'Coding Complete Clicked' order by event_time ASC)
ORDER BY event_time ASC