sql - SQL查询：一个表/时间序列对另一个表的反应，对于每个用户

Question

假设我有表A和B，它们都有超过 1 亿行包含 8 年的数据。它们都有列row_id（=唯一的行标识符，例如行号）person_id，，dt（=日期时间）。中的一行A表示用户打开电子邮件的时间。中的一行B代表用户访问特定网站的时间。

我希望创建一个 table C，它恰好附加A了几个（比如说 10 个）列。C_i这些列C_i必须衡量，在用户打开一封电子邮件后（即在 A 中的一行中），i 他访问了该网站的次数（即在打开电子邮件后的几个小时内）（即在 A 中的几行计数B） . 我怎样才能有效地构建C？

我在 Oracle PL/SQL 中尝试仅添加一列C_1：

SELECT 
    any_value(A.id), 
    any_value(A.person_id), 
    any_value(A.dt), 
    count(1) response_1h 
FROM A, B 
WHERE 
    A.person_id = B.person_id AND 
    0 <= B.dt - A.dt AND B.dt-A.dt<=1/24 
GROUP BY A.row_id;

我收到一个错误（不是因为这个，而是因为类似的查询），我怀疑是因为加入的表太大：

ORA-01652：无法在表空间 TEMP_DEV 01652 中将临时段扩展 256。00000 -“无法在表空间 %s 中将临时段扩展 %s” *原因：无法为临时段分配所需块数的范围在指示的表空间中。*操作：使用 ALTER TABLESPACE ADD DATAFILE 语句将一个或多个文件添加到指定的表空间。

对 10 个新列执行此操作似乎远非最佳。有人愿意告诉我这是如何正确完成的吗？

标签： sqloracleperformance

Answer 1

您可以使用条件聚合来计算网站访问量。下面的示例查看每条消息后的十小时并有条件地计数。

select
  a.id, a.person_id, a.dt,
  count(case when b.dt <= a.dt + interval '1' hour then 1 end) as response_1h,
  count(case when b.dt <= a.dt + interval '2' hour then 1 end) as response_2h,
  count(case when b.dt <= a.dt + interval '3' hour then 1 end) as response_3h,
  count(case when b.dt <= a.dt + interval '4' hour then 1 end) as response_4h,
  count(case when b.dt <= a.dt + interval '5' hour then 1 end) as response_5h,
  count(case when b.dt <= a.dt + interval '6' hour then 1 end) as response_6h,
  count(case when b.dt <= a.dt + interval '7' hour then 1 end) as response_7h,
  count(case when b.dt <= a.dt + interval '8' hour then 1 end) as response_8h,
  count(case when b.dt <= a.dt + interval '9' hour then 1 end) as response_9h,
  count(b.dt) as response_10h
from a
left join b on b.person_id = a.person_id
            and b.dt > a.dt
            and b.dt <= a.dt + interval '10' hour
group by a.id, a.person_id, a.dt
order by a.id;

与横向连接相同：

select *
from a
outer apply 
(
  select 
    count(case when b.dt <= a.dt + interval '1' hour then 1 end) as response_1h,
    count(case when b.dt <= a.dt + interval '2' hour then 1 end) as response_2h,
    count(case when b.dt <= a.dt + interval '3' hour then 1 end) as response_3h,
    count(case when b.dt <= a.dt + interval '4' hour then 1 end) as response_4h,
    count(case when b.dt <= a.dt + interval '5' hour then 1 end) as response_5h,
    count(case when b.dt <= a.dt + interval '6' hour then 1 end) as response_6h,
    count(case when b.dt <= a.dt + interval '7' hour then 1 end) as response_7h,
    count(case when b.dt <= a.dt + interval '8' hour then 1 end) as response_8h,
    count(case when b.dt <= a.dt + interval '9' hour then 1 end) as response_9h,
    count(b.dt) as response_10h
  from b 
  where b.person_id = a.person_id
    and b.dt > a.dt
    and b.dt <= a.dt + interval '10' hour
)
order by a.id;

查看解释计划，看看一个是否应该比另一个运行得更快。

对于任一查询，您都需要表 b 上的复合索引，以便快速找到其匹配的行。由于索引中列的顺序很重要，我将添加两个可能的索引：

create index idx1 on b (person_id, dt);
create index idx2 on b (dt, person_id);

您始终可以删除未使用的索引。

至于 ORA-01652：我无法回答这个问题。也许您可以增加临时表空间的大小并解决问题。我不知道。