postgresql - 计算不同记录之间的时间差

Question

我有一个如下所示的数据集:

对于每个 OwnerID，我想计算当前记录和下一条记录在 creationtime 列中的差异(对于相同的 ownerID)，以新列 TimeDiff 的形式。我相信这里需要自连接，但我不确定如何使用自连接来计算当前记录和下一条记录之间的差异。

在执行此操作时，任何 ownerID 的最后一条记录可以具有默认值“NA”，因为不会有下一条记录(对于相同的 ownerID) 来计算差异。

这是我用来获取此数据集的查询:

    SELECT DISTINCT ga.ownerid,
         mr.name,
         SPLIT_PART(SPLIT_PART(ga.activitydata,' ',2),',',1) AS Assignmentid,
         EXTRACT(YEAR FROM ga.creationtime) AS YEAR,
         EXTRACT(MONTH FROM ga.creationtime) AS MONTH,
         EXTRACT(DAY FROM ga.creationtime) AS DAY,
         EXTRACT(DOW FROM ga.creationtime) AS DOW,
         ga.creationtime,
         a.encodedid,
         a.name
  FROM flx2.groupactivities ga
    JOIN flx2.memberstudytrackitemstatus mstis ON SPLIT_PART (SPLIT_PART (ga.activitydata,' ',2),',',1) = mstis.assignmentid
    JOIN flx2.artifacts a ON mstis.studytrackitemid = a.id
    JOIN auth.memberhasroles mhr ON mhr.memberid = ga.ownerid
    JOIN flx2.memberroles mr ON mr.id = mhr.roleid
  WHERE ga.activitytype = 'assign'
  AND   ga.ownerid NOT IN (SELECT memberid FROM auth.memberhasroles WHERE roleid = 25)
  AND   a.artifacttypeid = 54
  AND   a.encodedid IS NOT NULL
  ORDER BY ga.ownerid,
           ga.creationtime,
           a.encodedid

我正在使用 Amazon Redshift 获取此数据。

如有任何帮助，我们将不胜感激。

TIA!

更新:

我使用了@systemjack 建议的方法。以下是我得到的结果:

我们可以在这里清楚地注意到 encodedid 列对于相同的 assignmentID(MAT.PRB.410，如中突出显示的那样)上图)，但事实并非如此。在上面提到的查询中，如果没有 LEAD 函数，就不会发生这种情况。这是我正在使用的更新后的查询(只有一个额外的 LEAD 函数):

SELECT DISTINCT ga.ownerid,
       mr.name,
       SPLIT_PART(SPLIT_PART(ga.activitydata,' ',2),',',1) AS Assignmentid,
       EXTRACT(YEAR FROM ga.creationtime) AS YEAR,
       EXTRACT(MONTH FROM ga.creationtime) AS MONTH,
       EXTRACT(DAY FROM ga.creationtime) AS DAY,
       EXTRACT(DOW FROM ga.creationtime) AS DOW,
       ga.creationtime,
       LEAD(ga.creationtime,1) OVER (PARTITION BY ga.ownerid ORDER BY ga.creationtime) AS nexttime,
       a.encodedid,
       a.name
FROM flx2.groupactivities ga
  JOIN flx2.memberstudytrackitemstatus mstis ON SPLIT_PART (SPLIT_PART (ga.activitydata,' ',2),',',1) = mstis.assignmentid
  JOIN flx2.artifacts a ON mstis.studytrackitemid = a.id
  JOIN auth.memberhasroles mhr ON mhr.memberid = ga.ownerid
  JOIN flx2.memberroles mr ON mr.id = mhr.roleid
WHERE ga.activitytype = 'assign'
AND   ga.ownerid NOT IN (SELECT memberid FROM auth.memberhasroles WHERE roleid = 25)
AND   a.artifacttypeid = 54
AND   a.encodedid IS NOT NULL
ORDER BY ga.ownerid,
         ga.creationtime,
         a.encodedid LIMIT 1000

nexttime 列中的值似乎也被抬高了。它似乎在 creationtime 列中取下一个值。例如:在第二条记录中，nexttime 列的值应该是 2013-09-18 06:14:59 而不是 2014-01- 18 12:16:49

为什么我们得到的记录比预期的多？我该如何解决这些问题？

Answer 1

更新:这样看起来更好吗？

with dataset as (
    SELECT DISTINCT ga.ownerid,
        mr.name,
        SPLIT_PART(SPLIT_PART(ga.activitydata,' ',2),',',1) AS Assignmentid,
        EXTRACT(YEAR FROM ga.creationtime) AS YEAR,
        EXTRACT(MONTH FROM ga.creationtime) AS MONTH,
        EXTRACT(DAY FROM ga.creationtime) AS DAY,
        EXTRACT(DOW FROM ga.creationtime) AS DOW,
        ga.creationtime,
        a.encodedid,
        a.name
    FROM flx2.groupactivities ga
    JOIN flx2.memberstudytrackitemstatus mstis ON SPLIT_PART (SPLIT_PART (ga.activitydata,' ',2),',',1) = mstis.assignmentid
    JOIN flx2.artifacts a ON mstis.studytrackitemid = a.id
    JOIN auth.memberhasroles mhr ON mhr.memberid = ga.ownerid
    JOIN flx2.memberroles mr ON mr.id = mhr.roleid
    WHERE ga.activitytype = 'assign'
        AND   ga.ownerid NOT IN (SELECT memberid FROM auth.memberhasroles WHERE roleid = 25)
        AND   a.artifacttypeid = 54
        AND   a.encodedid IS NOT NULL
)
select d.*,
    LEAD(creationtime,1) OVER (PARTITION BY ownerid ORDER BY creationtime) AS nexttime
from dataset d
ORDER BY ownerid, creationtime, encodedid, nextime
LIMIT 1000

---

像这样的东西(未经测试的代码)可能会起作用。想法是使用 LEAD window function为每个所有者获取以下记录的 creationtime，如果它是最后一条记录，它将为 null，然后使用常规 DATEDIFF得到你想要的单位。 CASE外部查询中的语句处理最后一条记录边缘情况，您可以调整它以获得您想要的结果。

select ownerid, creationtime,
    case when nextime is not null
        then datediff('second', creationtime, nextime)
        else datediff('second', creationtime, sysdate)
        end as timediff
from (
    select distinct ownerid, creationtime,
        lead(creationtime,1) over (partition by ownerid order by creationtime) as nexttime
    from yourdata
)