SQL subquery with average of 3 top values in Postgresql

Question

I need a little help to solve a query to count patients (ID) who have the average of the 3 last diastolic tension (TAD) < 90.

I've tried several type of nested subqueries with different errors.

This is my last version I've done:

SELECT CENTRO, COUNT ( DISTINCT ID )
FROM
(
    SELECT PAC.CENTRO, PAC.ID, T.TAD
    FROM IDDPAC PAC,
    (
        SELECT AVG(TA.TAD) TAD
        FROM
        (
            SELECT
            TEXT_TO_NUMBER ( PAG.TEXTO ) TAD
            FROM IDDPAG PAG, DATE D
            WHERE TRIM  ( PAG.DGP )='AH'
            AND PAG.ID=T.ID
            AND PAG.FECHA=D.OMI
            AND D.TIME_DATE::DATE BETWEEN DATE '2012-01-01'
            AND DATE '2012-12-31'
            ORDER BY PAG.FECHA DESC LIMIT 3
        ) TA
    ) T
    WHERE PAC.CENTRO='10040110' AND T.ID = PAC.ID
    GROUP BY PAC.CENTRO , PAC.ID
)
A
WHERE T.TAD < 90
GROUP BY CENTRO

And I get the following error:

ERROR:  falta una entrada para la tabla «t» en la cláusula FROM
LINE 31:             AND PAG.ID=T.ID
                                ^
********** Error **********

Translation:

ERROR:  missing an entry for the table «t» in the clause FROM
LINE 31:             AND PAG.ID=T.ID
                                ^
********** Error **********

. . Your non-working query doesn't really help understand the problem. Sample data and desired results would really help. — Gordon Linoff, Aug 22 '14 at 12:02
@GordonLinoff: The non-working query is perfectly sufficient for me to solve the problem. — Jonathan Hall, Aug 22 '14 at 12:05

score 0 · Answer 1 · answered Aug 22 '14 at 12:03

The problem is, exactly as the error indicates, that 'T' is not defined in the place it is requested. Your error is in the innermost subquery:

        SELECT
        TEXT_TO_NUMBER ( PAG.TEXTO ) TAD
        FROM IDDPAG PAG, DATE D
        WHERE TRIM  ( PAG.DGP )='AH'
        AND PAG.ID=T.ID
        AND PAG.FECHA=D.OMI
        AND D.TIME_DATE::DATE BETWEEN DATE '2012-01-01'
        AND DATE '2012-12-31'
        ORDER BY PAG.FECHA DESC LIMIT 3

But there is no T defined here to be used in the PAG.ID=T.ID portion of your WHERE clause. Did you mean to join on a table called T? Or did you mean to use D.ID instead?

@Antonio: I offered some suggestions. I don't know exactly what you're trying to do, so I can't tell you precisely how to fix it. But you're trying to compare to a table that doesn't exist. First, get your subquery working without everything else. Once that's working, plug it into your larger query. — Jonathan Hall, Aug 22 '14 at 12:06

score 0 · Accepted Answer · answered Aug 22 '14 at 12:07

To get the average of the last three values, use row_number() to enumerate the values. Then choose the last three and take the average. This gives you the patient level information:

    SELECT PAC.CENTRO, PAG.ID, AVG(TA.TAD) AS TAD
    FROM (SELECT PAG.ID, TEXT_TO_NUMBER ( PAG.TEXTO ) as TAD,
                 ROW_NUMBER() OVER (PARTITION BY PAG.ID ORDER BY D.TIME_DATE DESC) as seqnum
          FROM IDDPAG PAG JOIN
               DATE D
               ON PAG.FECHA = D.OMI JOIN
               IDDPAC PAC
               ON PAC.ID = PAG.ID
          WHERE TRIM  ( PAG.DGP )='AH' AND
                D.TIME_DATE::DATE BETWEEN DATE '2012-01-01' AND DATE '2012-12-31'
         ) TA
    WHERE SEQNUM <= 3
    GROUP BY PAC.CENTRO, PAD.ID
    HAVING AVG(TA.TAD) < 90;

The count by centro would just be:

  SELECT CENTRO, COUNT(*)
  FROM (SELECT PAC.CENTRO, PAG.ID, AVG(TA.TAD) AS TAD
        FROM (SELECT PAG.ID, TEXT_TO_NUMBER ( PAG.TEXTO ) as TAD,
                     ROW_NUMBER() OVER (PARTITION BY PAG.ID ORDER BY D.TIME_DATE DESC) as seqnum
              FROM IDDPAG PAG JOIN
                   DATE D
                   ON PAG.FECHA = D.OMI JOIN
                   IDDPAC PAC
                   ON PAC.ID = PAG.ID
              WHERE TRIM  ( PAG.DGP )='AH' AND
                    D.TIME_DATE::DATE BETWEEN DATE '2012-01-01' AND DATE '2012-12-31'
             ) TA
        WHERE SEQNUM <= 3
        GROUP BY PAC.CENTRO, PAD.ID
        HAVING AVG(TA.TAD) < 90
       ) TA
  GROUP BY CENTRO;

SQL subquery with average of 3 top values in Postgresql

2 Answers2