动态子查询上的Postgres连接 [英] Postgres Join on Dynamic Subquery

问题描述

背景

我有2个数据表。

对于tableA中的每一行，我要查找tableB中日期最接近的行，并将这些值联接到tableA中的行。

示例表：

表A：

p_id	类别	l_date
1	CATA	2005-01-05
1	catB	2005-06-10
2	CATC	2000-01-10

tableB：

p_id	e_id	e_date
1	22	2005-01-01
1	23	2005-01-06
1	24	2005-01-06
1	28	2005-01-10
2	29	2010-08-10

预期结果：

p_id	类别	l_date	e_id	e_date
1	CATA	2005-01-05	23	2005-01-06
1	CATA	2005-01-05	24	2005-01-06
1	catB	2005-06-10	28	2005-01-10
2	CATC	2000-01-10	29	2010-08-10

已尝试

此查询不起作用，但我认为这是我应该走的方向。

select a.p_id, a.category, a.l_date, c.e_id, c.e_date from tableA a 
left join lateral
(
  select top 1 p_id, e_id, e_date from tableB b
  where a.pid = b.pid 
  order by abs(datediff(days, a.l_date, b.e_date))
) c on True;

tableA和tableB的行很大，分别为17m和150m。

这听起来像是正确的方法吗？

使用RedShift集群，运行Postgres 8.x

推荐答案

相关子查询方法或完全交叉连接方法都将执行将一个表中的每一行与另一个表中的每一行进行比较(以某种方式)的任务。当表变大时，比较(连接)所有这些行变得令人望而却步。在这些情况下，需要不同的方法。

暴力强制不会很快(即使它完成了)，所以我们需要在这方面更有效率一些。我告诉客户想一想，如果我给他们成堆的索引卡，他们将如何(手动)执行此查询。一个人珍惜他们的时间，所以他们不会通过做所有可能的组合来做这件事，他们会想出一种更有效的方式，他们可以快速完成任务，回到他们的生活中来。在您描述的这种情况下，您需要找到更有效的方法。我很乐意与您详细讨论如何构建这些类型的查询。

利用您的数据(并为一些更有趣的情况美化一下)，我创建了一个如何做到这一点的示例。(是的，您可以交叉联接小的表，并使用更简单的SQL来实现这一点，但这不会进行扩展。)

数据设置：

create table tableA (p_id int, category varchar(64), l_date date);
insert into tableA values
(1,'catA','2005-01-05'),
(1,'catB','2005-06-10'),
(2,'catC','2000-01-10');

create table tableB (p_id int, e_id int, e_date date);
insert into tableB values
(1,22,'2005-01-01'),
(1,23,'2005-01-06'),
(1,24,'2005-06-01'),
(1,28,'2005-06-15'),
(2,29,'2010-08-10');

查询如下：

with combined as 
(
  select
    *,
    coalesce(max(l_date) OVER (partition by p_id order by
      dt rows between unbounded preceding and 1 preceding), '1970-01-01'::date) cb,
    coalesce(min(l_date) OVER (partition by p_id order by
      dt desc rows between unbounded preceding and 1 preceding), '2100-01-01'::date) ca 
  from
    (
      select
        p_id,
        category,
        l_date,
        NULL as e_id,
        NULL as e_date,
        l_date dt 
      from
        tableA 
      union all
      select
        p_id,
        NULL as category,
        NULL as l_date,
        e_id,
        e_date,
        e_date dt 
      from
        tableB 
    ) c
)
,
closest as 
(
  select
    p_id,
    e_id,
    e_date,
    cb,
    ca,
    case
      when
        coalesce(e_date - cb, 0) > (ca - e_date) 
      then ca 
      else cb 
    end closest 
  from
    combined 
  where
    e_date is not NULL
)
select
  c.p_id,
  a.category,
  a.l_date,
  c.e_id,
  c.e_date 
from
  closest c 
  left join tableA a 
  on c.closest = a.l_date and c.p_id = a.p_id 
order by
  c.p_id,
  c.e_id ;

虽然这看起来可能很多，但并不是那么复杂。首先，CTE查找最接近的l_date早于e_date(Cb)和最接近的l_date晚于e_date(Ca)。它对统一的数据集执行此操作，以允许窗口化。第二个CTE只确定哪个更近，ca或cb，并将其生成为"；最接近"；。它还去掉了UNION添加的所有tableB信息(不再需要)。最后，此"；最近"；日期提供生成最终结果所需信息的联接。

现在，该查询没有考虑到可能发生的许多实际数据问题，因此请以此为起点。我还根据测试数据对您的数据做了一些假设(比方说，表A中没有两行具有相同的l_date和P_id)。因此，请以此为起点。

关于性能的最后一句话-虽然窗口函数并不便宜，而且随着数据表大小的增加会做更多的工作，但它们的性能比交叉连接的大数据表高出几个数量级。您要做的事情很复杂，因此需要一些时间，但这是我发现的执行这些复杂操作的最快方法，这些操作通常会出现大量循环问题。

这篇关于动态子查询上的Postgres连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！