要处理带有子组的表的SQL查询。

Question

我有一个大型Oracle DB表，它包含近2亿行。它只有三列:订阅者id字段、日期字段和提供id字段。

对于该表中的每一行，我需要查找此行在表中是否有相应的行，以便：

1) --它们属于同一个用户(相同的订阅者id)

2) --它们过去与当前行有一定距离(例如，如果我们的当前行是A，则具有相同订户id的B行应该具有A.date > B.date >= A.date - 30(days))。

3)除了2)我们还必须查询特定的报价id：(A.date > B.date >= A.date - 30和B.offerid == some_id)

我知道函数滞后和领先，我计划为此目的使用它们。根据某些给定的字段，这些函数返回排序表中当前行以上或下面字段的值。令人不安的是，具有相同订阅者id字段的行数最多可达84。当我用滞后函数使用ORDER语句on ( SUBSCRIBER_ID，DATE)时，对于每一行，我需要检查当前行上面的84行，以确保上面的行与我的当前行共享相同的SUBSCRIBER_ID。由于某些订阅者id子组只有3-4行左右的条目，因此这种不必要的行访问量是浪费的。

我如何才能完成这项工作，而不需要每次检查84行？Oracle是否支持任何只对由组通过语句生成的子组工作的方法？

Answer 1

实际上，Oracle中的解析函数计数(*)为我做了必要的工作。我使用了以下结构

SELECT
SUBSCRIBER_ID,
SEGMENTATION_DATE,
OFFER_ID,
COUNT(*) OVER (PARTITION BY SUBSCRIBER_ID ORDER BY SEGMENTATION_DATE RANGE BETWEEN UNBOUNDED     PRECEDING AND 1 PRECEDING) AS SENDEVER,
COUNT(*) OVER (PARTITION BY SUBSCRIBER_ID ORDER BY SEGMENTATION_DATE RANGE BETWEEN 30 PRECEDING AND 1 
COUNT(CASE WHEN (OFFER_ID =580169) THEN 1 ELSE NULL END ) OVER (PARTITION BY SUBSCRIBER_ID ORDER    BY SEGMENTATION_DATE RANGE BETWEEN 180 PRECEDING AND 1 PRECEDING) AS SEND6M580169
FROM myTable

根据SUBSCRIBER_ID字段对表进行分组，并在每个组的行上使用适当的语句之间的范围，我只在所需的时间间隔内选择具有适当日期的语句。

通过在OFFER_ID字段上使用CASE WHEN语句，我将进一步过滤当前SUBSCRIBER_ID组中的行，并使用无效的提供者id抛出所有行。

好的是这里不需要自连接，操作的顺序降低了一个数量级。

Answer 2

一种选择是使用像这样的自连接：

SELECT t1.*, NVL2(t2.subscriber_id, 'Yes', 'No') as match_found
FROM
  myTable t1 LEFT JOIN 
  myTable t2 ON t1.subscriber_id = t2.subscriber_id 
              AND t1.date > t2.date AND t2.date >= t1.date - 30
              AND t2.offerid = <filter_offer_id>