English 中文(简体)
I wish I could correlate an "inline view"
原标题:

I have a Patient table:

PatientId   Admitted
---------   ---------------
1           d/m/yy hh:mm:ss
2           d/m/yy hh:mm:ss
3           d/m/yy hh:mm:ss

I have a PatientMeasurement table (0 to many):

PatientId   MeasurementId   Recorded            Value
---------   -------------   ---------------     -----
1           A               d/h/yy hh:mm:ss     100
1           A               d/h/yy hh:mm:ss     200
1           A               d/h/yy hh:mm:ss     300
2           A               d/h/yy hh:mm:ss     10
2           A               d/h/yy hh:mm:ss     20
1           B               d/h/yy hh:mm:ss     1
1           B               d/h/yy hh:mm:ss     2

I am trying to create a result set that resembles:

PatientId   Numerator   Denominator
---------   --------    -----------
1           1           1
2           1           1
3           0           1       

Essentially, a patient will have a 1 in the numerator if the have at least one value for measurement A and one value for measurement B. In this example, patient 1 has 3 A measurements and 2 B measures, so the numerator is 1. Patient 2 has 2 A measurements, but no B measurements, so the numerator is 0. Patient has neither an A measurement nor a B measurement, so the numerator is 0.

My query thus far is:

SELECT  PatientId, CASE WHEN a.cnt+b.cnt>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM    patient p

LEFT OUTER JOIN (
    SELECT  PatientId, count(*) cnt
    FROM    PatientMeasurement pm
    WHERE   MeasurementId= A 
    --AND   Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY PatientId
) a ON p.PatientId=a.PatientId

LEFT OUTER JOIN (
    SELECT  PatientId, count(*) cnt
    FROM    PatientMeasurement pm
    WHERE   MeasurementId= B 
    --AND   Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY PatientId
) b ON p.PatientId=b.PatientId

This works as expected as long as I don t include the correlated, date restriction (Recorded < dateadd(hh, 12, Admitted). Unfortunately, correlating an inline view in this manner is not syntactically valid.

This has forced me to re-write the SQL to:

SELECT  PatientId, CASE WHEN v.a+v.b>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM    (

    SELECT  PatientId,
    (
        SELECT  PatientId, count(*) cnt
        FROM    PatientMeasurement pm
        WHERE   PatientId=p.PatientId
        AND MeasurementId= A 
        AND Recorded <= dateadd(hh, 12, Admitted)
        GROUP BY PatientId
    ) a,
    (
        SELECT  PatientId, count(*) cnt
        FROM    PatientMeasurement pm
        WHERE   PatientId=p.PatientId
        AND MeasurementId= B 
        AND Recorded <= dateadd(hh, 12, Admitted)
        GROUP BY PatientId
    ) b
    FROM    Patient p
) v

My question: Is there a better, more-efficient way to do this?

Thanks for your time.

最佳回答

Try this :

WITH GroupPatients AS 
    (SELECT MeasurementID, PatientId, Count(*) AS cnt
    FROM PatientMeasurement AS pm
    INNER JOIN Patient p ON pm.PatientID = p.PatientID
    WHERE
        MeasurementId IN ( A ,  B )
    AND
        Recorded <= dateadd(hh, 12, Admitted)
    GROUP BY MeasureMentID, PatientId)

SELECT p.PatientID, Case
    When IsNull(GPA.cnt, 0) > 0 AND IsNull(GPB.cnt, 0) > 0 Then 1
    Else 0
End AS Numerator, 1 AS Denominator
FROM Patient p
LEFT JOIN GroupPatientsA AS GPA ON p.PatientID = GPA.PatientID AND GPA.MeasurementID =  A 
LEFT JOIN GroupPatientsB AS GPB ON p.PatientID = GPB.PatientID AND GPB.MeasurementID =  B 

I ve made one tweak to the business logic too - your spec says Numerator should be one if a patient has both A and B measurements - however, your clause of a.cnt+b.cnt>2 will erroneously return one if either a.cnt or b.cnt are 3 or more and the other is zero.

问题回答

Another solution can be close to your original attempt using OUTER APPLY:

SELECT  PatientId, CASE WHEN a.cnt+b.cnt>2 THEN 1 ELSE 0 END Numerator, 1 Denominator 
FROM    patient p 
OUTER APPLY ( 
    SELECT      count(*) cnt 
    FROM        PatientMeasurement pm 
    WHERE       MeasurementId= A  
    AND       Recorded <= dateadd(hh, 12, p.Admitted) 
    AND pm.PatientId = p.PatientId
) AS a(cnt)     
OUTER APPLY ( 
    SELECT      count(*) cnt 
    FROM        PatientMeasurement pm 
    WHERE       MeasurementId= B  
    AND       Recorded <= dateadd(hh, 12, p.Admitted) 
    AND pm.PatientId = p.PatientId
) AS b(cnt)
SELECT  p.*, 
        CASE WHEN
        EXISTS
        (
        SELECT  NULL
        FROM    PatientMeasurement pm
        WHERE   pm.PatientID = p.ID
                AND pm.Type =  A 
                AND pm.Recorded <= DATEADD(hh, 12, p.Admitted)
        ) AND EXISTS (
        SELECT  NULL
        FROM    PatientMeasurement pm
        WHERE   pm.PatientID = p.ID
                AND pm.Type =  B 
                AND pm.Recorded <= DATEADD(hh, 12, p.Admitted)
        ) THEN 1 ELSE 0 END
FROM    Patient p

Assuming you are using Sql 2005 or 2008, the entire query can be simplified using some window functions and a pivot:

with pData as
(
    select  count(*) over(partition by PatientId, MeasurementId) as cnt,
            PatientId, MeasurementId
    from    PatientMeasurement pm
    where   MeasurementId in( A , B )
    and     Recorded <= dateadd(hh, 12, Admitted)
)
select  PatientId, coalesce([A],0) as cntA, coalesce([B],0) as cntB,
        case when coalesce([A],0) + coalesce([B],0) > 2 then 1 else 0 end as Numerator,
        1 as Denominator
from    pData
pivot   (max(cnt) for MeasurementId in([A],[B])) pvt
DECLARE @TimeSlot int;
SET @TimeSlot = 12;

WITH 
pt AS (
    SELECT p.PatientID, p.Admitted, m.MeasurementID, m.Recorded,
        CASE 
          WHEN m.Recorded <= dateadd(hh, @TimeSlot, p.Admitted) THEN 1 
          ELSE 0 
        END AS "InTimeSlot"
    FROM Patient AS p
    LEFT JOIN PatientMeasurement AS m ON p.PatientID = m.PatientID
),
cntA AS (
    SELECT PatientID, count(*) AS "A_count"
    FROM pt WHERE MeasurementID= A  AND InTimeSlot = 1
    GROUP BY PatientID
),
cntB AS (
    SELECT PatientID, count(*) AS "B_count"
    FROM pt WHERE MeasurementID= B  AND InTimeSlot = 1
    GROUP BY PatientID
),
cntAB AS (
    SELECT p.PatientID
          ,coalesce(a.A_count, 0) AS "A_cnt"
          ,coalesce(b.B_count, 0) AS "B_cnt"
    FROM Patient as p
    LEFT JOIN  cntA  AS a ON p.PatientID = a.PatientID
    LEFT JOIN  cntB  AS b ON p.PatientID = b.PatientID
),
cntN AS (
    SELECT PatientID,
        CASE WHEN A_cnt > 0 AND B_cnt > 0 THEN 1 ELSE 0 END AS Numerator
    FROM cntAB 
)
SELECT PatientID, Numerator, 1 AS Denominator FROM cntN




相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...

热门标签