如何使用帶有條件的SQLLag()函式檢索行

我一直在嘗試解決這個問題，但未能得到預期的結果。請幫忙，因為我已經嘗試了幾天。

我有下表，其中 click_time 列是我需要的結果。

我需要在 unix_time_seconds 列中找到“單擊”操作和單擊之前的“獲取”之間的時間差，并且標志 = 2。

click_time 列僅顯示“click”行與 click 之前的第一行之間的差異，其中 flag = 2 和 event=fetch

我使用了以下陳述句，但我不確定如何倒退并找到第一行是“fetch”并且具有 value =“2”，然后取該行并從點擊中減去。我使用 lag() 函式來讀取前一行，但它卡在到達前一行 flag=2 和 event = fetch

 select time, unix_time_seconds,event,flag,
 case when event = "click"
   then unix_time_seconds - lag(unix_time_seconds, 1 ) over (order by flag desc)
 end as click_time

 from table_event

uj5u.com熱心網友回復：

只需添加partition by (case when event='click' or flag=2 then 1 end)您的視窗功能即可達到目的。此條件將忽略事件不是“點擊”或事件是“獲取”且標志不是 2 的所有行。

而不是所有 15 行lag()視窗函式將只考慮 11 行以下。

模式和插入陳述句：

 create table table_event(time datetime,    unix_time_seconds int,  event varchar(10), flag int);
 insert into table_event values('22/2/1 3:52:16',   1641095536, 'fetch',    2);
 insert into table_event values('22/2/1 3:52:19',   1641095539, 'click',    0);
 insert into table_event values('22/2/1 4:59:13',   1641099553, 'fetch',    2);
 insert into table_event values('22/2/1 4:59:21',   1641099561, 'fetch',    1);
 insert into table_event values('22/2/1 4:59:28',   1641099568, 'fetch',    1);     
 insert into table_event values('22/2/1 4:59:35',   1641099575, 'fetch',    1);
 insert into table_event values('22/2/1 6:51:42',   1641106302, 'fetch',    2);
 insert into table_event values('22/2/1 6:51:57',   1641106317, 'fetch',    1);
 insert into table_event values('22/2/1 6:51:59',   1641106319, 'click',    0);
 insert into table_event values('22/3/1 6:15:20',   1641190520, 'fetch',    2);
 insert into table_event values('22/7/1 8:12:15',   1641543135, 'fetch',    2); 
 insert into table_event values('22/10/1 1:09:56',  1641776996, 'fetch',    2); 
 insert into table_event values('22/10/1 1:09:57',  1641776997, 'click',    0);
 insert into table_event values('22/10/1 1:12:59',  1641777179, 'fetch',    2); 
 insert into table_event values('122/10/1 1:13:01', 1641777181, 'click',    0);

詢問：

選擇時間, unix_time_seconds,event,flag, case when event = 'click' then unix_time_seconds - lag(unix_time_seconds, 1 ) over (partition by (case when event='click' or(event='fetch' and flag=2) then 1 結束）按時間排序）以 click_time 結束

從 table_event 按 unix_time_seconds 排序

輸出：

db<>在這里擺弄

uj5u.com熱心網友回復：

這個問題有多種解決方案，這里是其中之一。

請注意使用值來創建臨時資料集。
另一種方法是使用stack函式 另請注意時間戳文字的使用，例如timestamp '2022-01-02 03:52:16'

外部 CASE 陳述句是為了僅顯示點擊事件的 click_time 值。

視窗函式按 unix_time_seconds 對記錄進行排序，并且對于每條記錄，在此記錄之前取最大 unix_time_seconds （order by在此背景關系中實際上是隱含的語法order by ... rows between unbounded preceding and current row）。
視窗函式中的 CASE 陳述句確保我們只查看帶有 2 標志的 fetch 事件。

with t (time, unix_time_seconds, event, flag)
as
(
  select   *
  from     values  (timestamp '2022-01-02 03:52:16', 1641095536, 'fetch', 2)
                  ,(timestamp '2022-01-02 03:52:19', 1641095539, 'click', 0)
                  ,(timestamp '2022-01-02 04:59:13', 1641099553, 'fetch', 2)
                  ,(timestamp '2022-01-02 04:59:21', 1641099561, 'fetch', 1)
                  ,(timestamp '2022-01-02 04:59:28', 1641099568, 'fetch', 1)
                  ,(timestamp '2022-01-02 04:59:35', 1641099575, 'fetch', 1)
                  ,(timestamp '2022-01-02 06:51:42', 1641106302, 'fetch', 2)
                  ,(timestamp '2022-01-02 06:51:57', 1641106317, 'fetch', 1)
                  ,(timestamp '2022-01-02 06:51:59', 1641106319, 'click', 0)
                  ,(timestamp '2022-01-03 06:15:20', 1641190520, 'fetch', 2)
                  ,(timestamp '2022-01-07 08:12:15', 1641543135, 'fetch', 2)
                  ,(timestamp '2022-01-10 01:09:56', 1641776996, 'fetch', 2)
                  ,(timestamp '2022-01-10 01:09:57', 1641776997, 'click', 0)
                  ,(timestamp '2022-01-10 01:12:59', 1641777179, 'fetch', 2)
                  ,(timestamp '2022-01-10 01:13:01', 1641777181, 'click', 0)
)
select    *
         ,case 
         
             when  event == 'click' 
             
             then  unix_time_seconds 
             
                 - max(case 
                           when event = 'fetch' and flag = 2 
                           then unix_time_seconds 
                       end
                       ) over (order by unix_time_seconds)
          end as click_time
          
from      t

此解決方案已在帶有 Apache Spark 3.2.0 的 Azure Databricks、RT 10.1 上進行了測驗

uj5u.com熱心網友回復：

這應該可以解決問題：

select time, unix_time_seconds,event,flag,
case when event = 'click' then 
    unix_time_seconds - (select top 1 unix_time_seconds from table_event y where event = 'fetch' and flag = 2 AND y.unix_time_seconds <= z.unix_time_seconds order by y.unix_time_seconds desc) 
end as click_time  from table_event z order by unix_time_seconds

標籤：sql 循环 apache-spark-sql 窗函数 落后

上一篇：NOTBETWEEN在使用金額范圍時會出錯。當沒有匹配的金額范圍時

下一篇：OracleSQL-使用另一個表中可用的日期范圍過濾表中的值