基于hitsTime的映射类型

Question

我有A桌：

hitsTime              type_             session_id
2022-09-23 19:18:19   lego_banner       1152969
2022-09-23 19:22:43   icon-Terdekat     1152969
2022-09-23 19:22:53   lego_banner       1152969
2022-09-23 19:45:34   lego_banner       1152969 
2022-09-23 19:45:39   lego_banner       1152969
2022-09-23 19:47:18   lego_banner       1152969
2022-09-23 19:47:51   lego_banner       1152969
2022-09-23 19:52:18   lego_banner       1152969
2022-09-23 19:58:00   lego_banner       1152969

表b：

hitsTime             session_id cart_id 
2022-09-23 19:47:02  1152969    378299
2022-09-23 19:20:35  1152969    304438
2022-09-23 19:27:25  1152969    304470  
2022-09-23 19:27:25  1152969    304495

流程是，如果我们单击type_，那么我们就可以添加cart_id。我想知道cart_id是哪种类型的。其思想是，如果hitsTime类型小于cart_id，cart id将进入特定类型。例如，在表b cart_id 304438中，hitsTime 2022-09-23 19:20:35，如果我们查看表a，类型是lego_banner，因为hitsTime 2022-09-23 19:18:19。cart_id不是从图标-Terdekat引起的hitsTime更多的是cart_id表b hitsTime。

预期产出：

session_id  cart_id  type_
1152969     304438   lego_banner
1152969     378299   lego_banner
1152969     304470   lego_banner
1152969     304495   lego_banner

Answer 1

这个查询适用于我：

with t1 as (
  select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:18:19') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:22:43') as hitsTime, 'icon-Terdekat' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:22:53') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:45:34') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:45:39') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:47:18') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:47:51') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:52:18') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:58:00') as hitsTime, 'lego_banner' as type_, 1152969 as session_id
),
t2 as (
  select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:47:02') as hitsTime, 1152969 as session_id, 378299 as cart_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:20:35') as hitsTime, 1152969 as session_id, 304438 as cart_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:27:25') as hitsTime, 1152969 as session_id, 304470 as cart_id
  union all select parse_datetime('%Y-%m-%d %H:%M:%S','2022-09-23 19:27:25') as hitsTime, 1152969 as session_id, 304495 as cart_id
),

t3 as
(select t1.hitsTime, min(t1.hitsTime) over (partition by t1.session_id, t2.cart_id) as hitmin ,t1.session_id,t2.cart_id, t1.type_
from t1 inner join  t2 on t2.hitsTime > t1.hitsTime
and t1.session_id = t2.session_id)

select session_id, cart_id, type_
from t3
where t3.hitsTime = hitmin

输出：

​