一、需求背景

基于保密要求，不放原始表，新建測(cè)試表用來(lái)演示

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

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

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

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

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

二、問(wèn)題解決

? ? ? ?為了便于自己理清思路，分步驟寫(xiě)出sql語(yǔ)句。

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

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

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

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

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

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

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

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

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

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

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