按分区在最近的日期加入

Question

我有两个表，点击：

user    date
a   01/10/2021
a   02/10/2021
a   03/10/2021
b   27/09/2021
c   08/10/2021
c   13/10/2021

和分段：

user    date    segment
a   26/09/2021  1
a   27/09/2021  1
a   03/10/2021  2
c   01/10/2021  5
c   10/10/2021  6

我想加入点击片段，以确定每个用户在给定点击日期的最新片段。因此，正确的输出将如下所示：

user    date    segment
a   01/10/2021  1
a   02/10/2021  1
a   03/10/2021  2
b   27/09/2021  
c   08/10/2021  5
c   13/10/2021  6

我尝试了以下操作，但得到错误"由于内部错误，不支持这种类型的相关子查询模式">

select *,
(select top 1 segment
from b
where
b.date <= a.date
ORDER BY ROW_NUMBER() OVER(PARTITION BY b.id ORDER BY b.date DESC)) as segment_lookup
from a;

非常感谢您的帮助，找到最好的方法。提前感谢

Answer 1

您没有提到数据库，所以我假设它是SQL Server。

另外，第一个表似乎没有键，所以下面的查询使用(user，date(作为键。

你可以做：

select *
from (
select
c.*, s.segment,
row_number() over(partition by c.u, c.d order by s.d desc) as rn
from clicks c
left join segment s on s.u = c.u and c.d >= s.d
) x
where rn = 1

结果：

u  d           segment  rn 
-- ----------- -------- -- 
a  2021-10-01  1        1  
a  2021-10-02  1        1  
a  2021-10-03  2        1  
b  2021-09-27           1  
c  2021-10-08  5        1  
c  2021-10-13  6        1  

请参阅db<gt；小提琴1。

或者，可以使用理论上性能更好的横向连接。

编辑：根据请求添加横向连接

带有横向连接的查询可以采用以下形式：

select
c.*, s.segment
from clicks c
outer apply (
select top 1 * from segment s where s.u = c.u and c.d >= s.d
order by s.d desc
) s

结果：

u  d           segment 
-- ----------- ------- 
a  2021-10-01  1       
a  2021-10-02  1       
a  2021-10-03  2       
b  2021-09-27          
c  2021-10-08  5       
c  2021-10-13  6       

请参阅db<gt；小提琴2。

如果索引segment (u, d)被创建，则横向连接可能是相当有性能的。