基于其他行的SQL去除重复行

Question

我有两个表格（一个显示发送给客户的所有方案记录，另一个显示用户采取行动的所有方案记录）。我的最终目标是计算向用户发送的这些方案中，有多少已被执行。我现在有一种方法可以做到这一点，但存在一个问题。这些方案可能会在一天内多次发送给用户（我将使用20作为例子）。如果用户在24小时内对该方案采取行动，则不希望将其视为19个未执行和1个已执行的方案。我希望它被视为1个已执行的方案，没有失败的方案。

需要过滤包含发送给客户的方案的表格，以使每个发送给客户的方案每24小时只显示一次。

我的解决方案是有一个“窗口”类型的东西，因此一旦将某个方案发送给客户，就会为该客户“锁定”24小时，因此它不会在我的“发送给客户的方案”查询中多次出现。

我有一个像这样的表格：

Customer    Time    Program
-----------------------------------
1           8:05    a
1           10:30   a
1           11:30   a
1           12:30   b
1           1:25    a
2           9:38    b
2           10:38   c
2           1:36    c
2           2:40    c
2           3:41    b
.
.
.

我希望能够得到一个表格（查询，而非删除），该表格可以在特定时间范围内按客户去重程序（有点混乱！我知道）

以下是我的要求（以3小时为例）：

Customer    Time    Program
-----------------------------------
1           8:05    a
1           11:30   a
1           12:30   b
2           9:38    b
2           10:38   c
2           2:40    c
2           3:41    b
.
.
.

- John Verrone

---

你使用的是哪种关系型数据库管理系统？ - Taryn

@hockeyplayer0623 - 在你的示例中，程序A中哪一个是重复的？你如何判断？为什么10:30是重复的，但11:30不是？看起来你只想根据时间删除记录，而不是删除重复记录。 - Art

请定义一下“滚动窗口”的含义。 - APC

如果在“关闭”同一程序的实例后的3小时内出现重复，则将其视为重复。因此，8:05程序已经发生，现在从8:05开始的3小时内的任何内容都不计入重复。一旦到达11:05，该程序再次开放。因此，现在程序A的11:30实例已经出现，因此现在关闭了下一个3小时的“窗口”。这就是我所追求的。 - John Verrone

如果没有任何答案解决了你的问题，请在此澄清，不要发布一个新的完全相同的问题。很抱歉如果这与你得到的其他建议相矛盾，但是新问题意味着你有一个新的问题，至少与已经解决的问题有些不同。 - ypercubeᵀᴹ

Answer 1

试试这个：

select *
from t
where not exists (select 1 from t t2
                  where t2.customer = t.customer and
                        t2.program = t.program and
                        t2.time - t.time < 3.0/24 and
                        t2.time > t.time
                 )

日期时间算术运算取决于数据库，但这对许多数据库都适用。

- Gordon Linoff

---

这是朝着正确的方向，但它不会显示想要的结果。 - ypercubeᵀᴹ

@ypercube……我不是那样理解这个问题的。而且，期望输出中“a”的第二行支持我的问题理解。 - Gordon Linoff

我将把这个标记为正确答案，因为它非常接近我想要的。只有使用递归函数才能实现的“移动窗口”不会发生，因为我运行递归函数大约一个小时后变得不耐烦，所以放弃了。谢谢 @ypercube gordon - John Verrone

@hockeyplayer0623 . . . 请再发布一个问题，并更好地描述滚动时间范围（我错过了评论部分）。我认为可能有解决方案，但我想看看它的样本数据。 - Gordon Linoff

抱歉造成困惑，Gordon。我真的需要滚动或这种技术，但我不确定哪个更好，但我相信这将更简单。 - John Verrone

Answer 2

我认为你所说的可以通过递归查询来解决。以下是一种解决方案:

WITH cte AS
  ( SELECT 
        customer, program, time,
        ROW_NUMBER() 
            OVER (PARTITION BY customer, program
                  ORDER BY time)
          AS rn, 
        MIN(time) 
            OVER (PARTITION BY customer, program
                  ORDER BY time 
                  RANGE BETWEEN 3.0/24 FOLLOWING 
                            AND UNBOUNDED FOLLOWING)
          AS next_time
    FROM a
  )

SELECT 
    customer, time, program
FROM 
    cte
START WITH rn = 1
CONNECT BY PRIOR customer = customer
       AND PRIOR program = program
       AND PRIOR next_time = time
ORDER BY 
    customer, time, program ;

你也可以将上面的MIN(time)替换为FIRST_VALUE(time)，并获得相同的结果。这可能更有效率。

在SQL-Fiddle上进行了测试。

在大表上的效率可能不会很好。您可以尝试使用较小的数据集运行查询。

并且您至少应该添加此索引，以便进行索引扫描：

CREATE INDEX ix                -- choose a name for the index
  ON tableX                    -- the table name
  (customer, program, time) ;

你也可以跳过最终排序，或者将其更改为更类似于使用的索引：

ORDER BY 
    customer, program, time ;

- ypercubeᵀᴹ