1.需求

根據(jù)身份證號(hào)分組聚合求第一次(開(kāi)始時(shí)間最小)出現(xiàn)的基站及對(duì)應(yīng)時(shí)間和最后一次(結(jié)束時(shí)間最大)出現(xiàn)的基站，ip4，ip6及對(duì)應(yīng)時(shí)間

2.應(yīng)用場(chǎng)景

安全行業(yè)，上網(wǎng)流量日志監(jiān)控行業(yè)，如追擊罪犯，要查看第一次出現(xiàn)的位置和最后一次出現(xiàn)的位置及最后一次聯(lián)系人等相關(guān)信息，從而可以和最后一次聯(lián)系人及相關(guān)信息從而分析罪犯去向等信息

3.示例數(shù)據(jù)

with a as 
(select 1 as identitycode,'st_001' as stationid,'ip4_001' as ip4,'' as ip6, 1 as isipv4,20240615150101 as starttime,20240615150601 as endtime
union all 
select 1 as identitycode,'st_002' as stationid,'' as ip4,'ip6_001' as ip6, 0 as isipv4,20240615150601 as starttime,20240615150901 as endtime
union all 
select 1 as identitycode,'st_003' as stationid,'ip4_02' as ip4,'' as ip6, 1 as isipv4,20240615151301 as starttime,20240615151701 as endtime
union all
select 2 as identitycode,'st_004' as stationid,'' as ip4,'ip6_004' as ip6, 0 as isipv4,20240618150101 as starttime,20240618150601 as endtime
union all 
select 2 as identitycode,'st_005' as stationid,'' as ip4,'ip6_001' as ip6, 0 as isipv4,20240618150601 as starttime,20240618150901 as endtime
union all 
select 2 as identitycode,'st_006' as stationid,'ip4_07' as ip4,'' as ip6, 1 as isipv4,20240618151301 as starttime,20240618151701 as endtime)

4.sql實(shí)現(xiàn)

hivesql實(shí)現(xiàn)，在倉(cāng)內(nèi)實(shí)現(xiàn)結(jié)果直接應(yīng)用

with a as 
(select 1 as identitycode,'st_001' as stationid,'ip4_001' as ip4,'' as ip6, 1 as isipv4,20240615150101 as starttime,20240615150601 as endtime
union all 
select 1 as identitycode,'st_002' as stationid,'' as ip4,'ip6_001' as ip6, 0 as isipv4,20240615150601 as starttime,20240615150901 as endtime
union all 
select 1 as identitycode,'st_003' as stationid,'ip4_02' as ip4,'' as ip6, 1 as isipv4,20240615151301 as starttime,20240615151701 as endtime
union all
select 2 as identitycode,'st_004' as stationid,'' as ip4,'ip6_004' as ip6, 0 as isipv4,20240618150101 as starttime,20240618150601 as endtime
union all 
select 2 as identitycode,'st_005' as stationid,'' as ip4,'ip6_001' as ip6, 0 as isipv4,20240618150601 as starttime,20240618150901 as endtime
union all 
select 2 as identitycode,'st_006' as stationid,'ip4_07' as ip4,'' as ip6, 1 as isipv4,20240618151301 as starttime,20240618151701 as endtime)

select b.identitycode,b.starttime as starttime,b.stationid as first_stationid,c.endtime as lasttime,c.stationid as last_stationid,c.ip4,c.ip6,c.isipv4 from 
(select identitycode,stationid,starttime,row_number() over(PARTITION by identitycode order by starttime) srn from a) b 
left join 
(select identitycode,stationid,ip4,ip6,isipv4,endtime,row_number() over(PARTITION by identitycode order by endtime desc) lrn from a) c
on b.identitycode=c.identitycode where b.srn=1 and c.lrn=1
;

輸出結(jié)果

olap引擎如clickhouse實(shí)現(xiàn)

select 
cur.identitycode AS identitycode,
toUInt64(cur.first.2) AS firsttime,
toUInt64(cur.last.5) AS lasttime,
cur.first.1 AS firststationid,
cur.last.1 AS laststationid,
toUInt64(cur.last.2) AS ip4,
cur.last.3 AS ip6,
toUInt8(cur.last.4) AS isipv4
from
(SELECT 
identitycode,
any(account) as account,
any(accounttype) as accounttype,
argMin(tuple(stationid ,starttime),starttime) AS first,
argMax(tuple(stationid,ip4,ip6,isipv4,endtime),endtime) AS last
FROM dwd_identity_base_cur_hour cur