sql - 加入并计算 2 个日期之间的不同值

Question

我有一个左表:

CREATE TABLE tab (
  code int
, max  date
, min  date
);

还有一个包含用户事件的右表(每个用户每天一行):

CREATE TABLE activity (
  date   date
, "user" text
);

示例数据:

表 tab(添加了我正在尝试计算的列):

Code  Min          Max            (Expected output)
201   2019-1-8    2019-1-10           3
202   2019-1-8    2019-1-11           3
203   2019-1-11   2019-1-12           2

表事件:

Date              User
2019-1-8         ABCD001
2019-1-8         ABCD002
2019-1-9         ABCD001
2019-1-9         ABCD003
2019-1-10        ABCD001
2019-1-11        ABCD002
2019-1-12        ABCD003

我想将 tab 的列作为输出，使用该日期范围内不同用户 activity 的计数进行扩展

所以我需要以某种方式加入 min 和 max 之间的日期范围。

试验 1:

select code, min, max, count(b.distinct user)
from tab a
left join activity b on b.date between a.min and a.max
group by 1,2,3 

试验 2:

select code, min, max, count(b.distinct user)
from tab a
left join activity b on b.date <= a.min and b.date >=a.max and b.date = a.min
group by 1,2,3

我运行了上述代码的不同版本，但它要么运行很长时间，要么失败。

Answer 1

我建议LEFT JOIN LATERAL:

SELECT a.code, a.min, a.max, b.ct
FROM   tab a
LEFT   JOIN LATERAL (
   SELECT count(DISTINCT b."user") AS ct
   FROM   activity b
   WHERE  b.date BETWEEN a.min AND a.max
   ) b ON true;

如果范围不是非常小，这样的查询可能会很昂贵。左边的每一行都涉及右边的一系列行。对于大范围，DISTINCT 计数在它之上是昂贵的。但这应该是最快的查询。

在 activity(date, "user") 上用 index 支持它。如果您从中获得仅索引扫描，则仅添加第二个索引列。

用户 是 reserved word ， 顺便提一句。不要将其用作标识符。
而且我也不会使用 date、min 或 max。基本数据类型和函数的名称作为标识符可能会导致混淆错误。

相关:

• What is the difference between LATERAL and a subquery in PostgreSQL?