一、需求背景

基于保密要求，不放原始表，新建測試表用來演示

insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('張三', to_date('21-02-2024 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), '2024001', '0001');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('張三', to_date('21-02-2024 12:01:00', 'dd-mm-yyyy hh24:mi:ss'), '2024002', '0001');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('張三', to_date('21-02-2024 12:02:00', 'dd-mm-yyyy hh24:mi:ss'), '2024003', '0001');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('張三', to_date('21-02-2024 14:00:00', 'dd-mm-yyyy hh24:mi:ss'), '2024004', '0001');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:05:00', 'dd-mm-yyyy hh24:mi:ss'), '2024005', '0002');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:06:00', 'dd-mm-yyyy hh24:mi:ss'), '2024006', '0002');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:07:00', 'dd-mm-yyyy hh24:mi:ss'), '2024007', '0002');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:08:00', 'dd-mm-yyyy hh24:mi:ss'), '2024008', '0003');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:09:00', 'dd-mm-yyyy hh24:mi:ss'), '2024009', '0003');insert into TEST0221 (采血人, 采血時間, 條碼號, 病人ID)
values ('李四', to_date('21-02-2024 12:10:00', 'dd-mm-yyyy hh24:mi:ss'), '2024010', '0003');

? ? ? ? ?最近工作上接到一個需求，統(tǒng)計采血工作量，按人員統(tǒng)計采血人次，本來很好統(tǒng)計，業(yè)務上一個人可能一次采血可能會采集多管，但是只能算作采血人員只采集了一次，那么就按人次來計算，而不是按試管來計算，sql語句如下：

select 采血人,count(distinct 病人ID) as 采血人次 from TEST0221
group by 采血人;

? ? ? ? 但是很快需求方提出問題，張三的工作量少了1個，因為雖然他只采集了這一個人，但是他最后一次采集與前一次相差了2小時左右，很明顯不是一次采集完的，經(jīng)過一番溝通，確定了統(tǒng)計口徑為：如果同一個人的采集時間與上一次間隔超過了10分鐘，那么就應該把這次也算作一次工作量。

? ? ? ?這樣統(tǒng)計思路很清晰，但是SQL語句好像不太好寫，或許有其他方式，但最終決定嘗試用開窗函數(shù)解決。

二、問題解決

? ? ? ?為了便于自己理清思路，分步驟寫出sql語句。

首先我需要知道同一個采樣人對同一個患者，每次采樣時間與上一次采樣的間隔，也就是需要在兩行數(shù)據(jù)之間做計算，把同一個患者同一個采樣人上一次的采樣時間獲取到，那么使用開窗函數(shù)lead來實現(xiàn)。

LEAD函數(shù)和?LAG函數(shù)主要用于查詢當前字段的上一個值或下一個值，若向上取值或向下取值沒有數(shù)據(jù)的時候顯示為NULL

• LEAD: 向后偏移
• LAG: 向前偏移

?關于開窗函數(shù)網(wǎng)上資料很多，可以自行了解。?

select t.采血人,t.采血時間,t.條碼號,t.病人id， lead(采血時間, 1) OVER(partition by 采血人, 病人id ORDER BY 采血時間 desc) as 前一次采血時間,(t.采血時間 - lead(采血時間, 1)OVER(partition by 采血人, 病人id ORDER BY 采血時間 desc))*24*60 as 間隔分鐘數(shù)from TEST0221 t;

此處解釋下?lead(采血時間, 1) OVER(partition by 采血人, 病人id ORDER BY 采血時間 desc)?

其中?lead(采血時間, 1) 的1是偏移量，采血時間是要取的字段

其中 partition by 采血人, 病人id? 是在窗口中根據(jù)采血人和病人ID分組

ORDER BY 采血時間 desc 則是根據(jù)采血時間排序

? 到了這一步之后，思路就很清晰了，只需要在上面的基礎上加上10分鐘的判斷并計數(shù)即可

select s.采血人,count(distinct s.計數(shù)項 ) as 采血人次 from (
select t.采血人,t.采血時間,t.條碼號,case when    (t.采血時間 - lead(采血時間, 1)OVER(partition by 采血人, 病人id ORDER BY 采血時間 desc))*24*60>10 then  病人id||'-'||to_char(采血時間,'yyyy-mm-dd hh24:mi:ss') else病人id end as 計數(shù)項from TEST0221 t) s  group by s.采血人;