Join语句时间约束中的SQL相关子查询

Question

我有一个关于Teradata SQL的特定问题，我相信它有一个与相关子查询有关的答案……

基本上，在我工作的呼叫中心，每个座席都必须为他们接听的每个呼叫创建一个案例(在公司CRM中)，并且每个案例都与一个帐户相关联。我需要获得一组代理为特定日期创建的所有案例，然后在接下来的3天内获得在这些帐户上创建的下一个案例(如果有)。基本上，我需要执行以下操作(请注意，无论创建者或帐号如何，所有案例都会被转储到同一个数据库中)：

SELECT
  CASES.CASE_ID, CASES.CREATION_TIMESTAMP, CASES.ACCOUNT_NUMBER, CASES.AGENT_ID
FROM
  CASES
WHERE CREATION_DATE = {d '2013-12-01'} AND AGENT_ID IN ('aaaaa','bbbbb','ccccc')

并以某种方式为每个帐户上创建的下一个案例的案例ID和创建时间戳添加2列：

SELECT
  INITIAL_CASES.CASE_ID, INITIAL_CASES.CREATION_TIMESTAMP,
  INITIAL_CASES.ACCOUNT_NUMBER,
  INITIAL_CASES.AGENT_ID,
  REPEAT.CASE_ID, REPEAT.CREATION_TIMESTAMP
FROM
  CASES AS INITIAL_CASES
LEFT JOIN

  (SELECT
    ACCOUNT_NUMBER, MIN(CREATION_TIMESTAMP) 
   FROM CASES 
   WHERE CREATION_TIMESTAMP > INITIAL_CASES.CREATION_TIMESTAMP 
   AND CREATION_DATE >= {d '2013-12-01'} 
   AND CREATION_DATE <= {d '2013-12-04'}) REPEAT

ON INITAL_CASES.ACCOUNT_NUMBER = REPEAT_CASES.ACCOUNT_NUMBER
WHERE AGENT_ID IN ('aaaaa','bbbbb','ccccc')

一些问题是: 1)在我的代理创建案例后，可能没有或超过1个案例在同一帐户上创建，因此我需要使用MIN( CREATION_TS )和WHERE CREATION_TS> INITIAL_CREATION_TS的组合，但是需要为我的代理案例的初始结果集中的每一行执行此查询，因为它们都具有不同的时间戳。

Answer 1

你可能不需要自连接，看起来你只是想要基于CREATION_TIMESTAMP的下一行数据(如果是在接下来的3天内)，这很容易通过窗口聚合实现：

SELECT
  CASES.CASE_ID, CASES.CREATION_TIMESTAMP, CASES.ACCOUNT_NUMBER, CASES.AGENT_ID,
  CASE
    WHEN MIN(CREATION_DATE)
         OVER (PARTITION BY ACCOUNT_NUMBER 
               ORDER BY CREATION_TIMESTAMP
               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) <= DATE '2013-12-04'
    THEN MIN(CASE_ID) 
         OVER (PARTITION BY ACCOUNT_NUMBER 
               ORDER BY CREATION_TIMESTAMP
               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_CASE_ID,
  CASE
    WHEN MIN(CREATION_DATE) 
         OVER (PARTITION BY ACCOUNT_NUMBER 
               ORDER BY CREATION_TIMESTAMP
               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) <= DATE '2013-12-04'
    THEN MIN(CREATION_TIMESTAMP) 
         OVER (PARTITION BY ACCOUNT_NUMBER 
               ORDER BY CREATION_TIMESTAMP
               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_CREATION_TIMESTAMP
FROM
  CASES
WHERE CREATION_DATE = {d '2013-12-01'} AND AGENT_ID IN ('aaaaa','bbbbb','ccccc')