sql - 同一个表的两行值的差异

Question

我有一个实验室值表，如果其 >= 0.2，我想查询并获取最后一个值和最近值之间的差异。我该怎么做呢。这是我到目前为止的查询。任何帮助，将不胜感激。数据库是Oracle 10g。有关详细信息，请参阅屏幕截图。

SELECT DISTINCT VISITLAB.DATEOFENTRY, VISITLAB.LABVALUE, VISITLAB.CODE      
FROM(XCX.PATIENTVISIT PATIENTVISIT
       INNER JOIN
          XCX.MASTERPATIENT MASTERPATIENT
       ON (PATIENTVISIT.MASTERPATIENTID = MASTERPATIENT.MASTERPATIENTID))
   INNER JOIN
      XCX.VISITLAB VISITLAB
   ON (VISITLAB.MASTERPATIENTID = PATIENTVISIT.MASTERPATIENTID)
      AND (VISITLAB.VISITNUMBER = PATIENTVISIT.VISITNUMBER)  
WHERE     (MASTERPATIENT.MASTERPATIENTID = 'xxxxxxxx')
   AND (VISITLAB.CODE = 'UQN0')
   AND (PATIENTVISIT.DISCHARGEDATE IS NULL)
   AND (PATIENTVISIT.FACILITYCODE = 'x')

Answer 1

您应该使用 LAG 分析函数来检索前一行中的值。

SELECT * FROM (
SELECT DISTINCT VISITLAB.DATEOFENTRY, VISITLAB.LABVALUE, VISITLAB.CODE, 
    (LAG(VISITLAB.LABVALUE) over (order by VISITLAB.DATEOFENTRY) - VISITLAB.LABVALUE) as DIFF
FROM(XCX.PATIENTVISIT PATIENTVISIT
INNER JOIN
       XCX.MASTERPATIENT MASTERPATIENT
       ON (PATIENTVISIT.MASTERPATIENTID = MASTERPATIENT.MASTERPATIENTID))
INNER JOIN
       XCX.VISITLAB VISITLAB
       ON (VISITLAB.MASTERPATIENTID = PATIENTVISIT.MASTERPATIENTID)
       AND (VISITLAB.VISITNUMBER = PATIENTVISIT.VISITNUMBER)
WHERE (MASTERPATIENT.MASTERPATIENTID = 'xxxxxxxx')
AND (VISITLAB.CODE = 'UQN0')
AND (PATIENTVISIT.DISCHARGEDATE IS NULL)
AND (PATIENTVISIT.FACILITYCODE = 'x')
) INLINEVIEW
WHERE DIFF < .2

这不一定是您正在寻找的答案，但它演示了我如何根据前一行中的值计算列的派生值。