Tuning Multi-Level Rows-to-Cols Query

Question

In the answer to a previous post (Tuning Rows-to-Cols Query), I learned how to more efficiently construct a row-to-cols query which allows for filtering by date. However, I now need to take this one level further.

The schema for the query below is as follows:
SAMPLE (1-to-many) TEST (1-to-many) RESULT (1-to-MANY)
Each sample has one or more tests, and each test has one or more results.

Question: How can I rewrite this view more efficiently, still allowing fast filtering by "Date Sampled?"

Concern: The points for MAX(tst.created_on) should be the set of unique tests with test_id (set 1) not for the set of unique results with test_id (set 2):

set 1: {1,    2,    76,     77,     135,      136}
set 2: {1, 1, 2, 2, 76, 76, 77, 77, 135, 135, 136, 136}

CREATE OR REPLACE VIEW V_TITRATION_SAMPLES as
     SELECT sam.sampled_on "Date Sampled",
            MAX(CASE WHEN res.result_tmpl_id = 4 THEN result END) "titrator", 
            MAX(CASE WHEN res.result_tmpl_id = 3 THEN result END) "factor",
            MAX(tst.created_on) "Last Test Creation"
       FROM lims.sample sam
       JOIN lims.test tst ON sam.sample_id = tst.sample_id
       JOIN lims.result res ON tst.test_id = res.test_id
      WHERE sam.sample_tmpl_id = 4
   GROUP BY sample_id, sam.sampled_on

Before GROUP BY:

   SAMPLE COLUMNS      |    TEST COLUMNS     | RESULT COLUMNS
   id  tmp sampled_on  | *id tmp created_on  | *id tmp result
    1   4  09-20 21:50 |   1  7  09-20 22:20 |   1  1     5
    1   4  09-20 21:50 |   1  7  09-20 22:20 |   2  3    2.1
    1   4  09-20 21:50 |   2  9  09-20 22:23 |   3  4     6
    1   4  09-20 21:50 |   2  9  09-20 22:23 |   4  6    123

   25   4  09-21 08:26 |  76  7  09-21 08:53 |  96  1     4
   25   4  09-21 08:26 |  76  7  09-21 08:53 |  97  3    1.6
   25   4  09-21 08:26 |  77  9  09-21 08:52 |  98  4     4
   25   4  09-21 08:26 |  77  9  09-21 08:52 |  99  6    103

  102   4  09-21 09:54 | 135  7  09-21 10:34 | 185  1     1
  102   4  09-21 09:54 | 135  7  09-21 10:34 | 186  3    1.8
  102   4  09-21 09:54 | 136  9  09-21 10:05 | 187  4     5
  102   4  09-21 09:54 | 136  9  09-21 10:05 | 188  6    110

* Shortened TABLE_id and TABLE_template_id to id and tmp, 
  respectively to keep this data grid narrow.

Results:

   "Date Sampled"  titrator   factor   "Last Test Creation"
   09-20 21:50        6         2.1    09-20 22:23
   09-21 08:26        4         1.6    09-21 08:53
   09-21 09:54        5         1.8    09-21 10:34

Answer 1

Give this a shot:

WITH titrate AS (
     SELECT r.test_id,
            MAX(r.result) 'titrator'
       FROM LIMS.RESULT r
      WHERE r.result_tmpl_id = 4
   GROUP BY r.test_id),
    factor AS (
     SELECT r.test_id,
            MAX(r.result) 'factor'
       FROM LIMS.RESULT r
      WHERE r.result_tmpl_id = 3
   GROUP BY r.test_id),
     created AS (
     SELECT t.sample_id,
            MAX(t.created_on) 'created'
       FROM LIMS.TEST t
   GROUP BY t.sample_id)
   SELECT s.sampled_on,
          ti.titrator,
          f.factor,
          t.created
     FROM LIMS.SAMPLE s
     JOIN created t ON t.sample_id = s.sample_id
LEFT JOIN titrate ti ON ti.test_id = t.test_id
LEFT JOIN factor f ON f.test_id = t.test_id

I changed out the CASE statements using subquery factoring - the LEFT JOIN may not be necessary.

The key part is the inline view/subquery factoring for the LIMS.TEST table, where you want to get the max created_on date for a sample_id - separating it from the rest of the query will give you more control to return what you want specifically from that table.

The equivalent alternative using inline views - they'll perform the same, just that subquery factoring is only supported since 9i:

   SELECT s.sampled_on,
          ti.titrator,
          f.factor,
          t.created
     FROM LIMS.SAMPLE s
     JOIN (SELECT t.sample_id,
                  MAX(t.created_on) 'created'
             FROM LIMS.TEST t
         GROUP BY t.sample_id) t ON t.sample_id = s.sample_id
LEFT JOIN (SELECT r.test_id,
                  MAX(r.result) 'titrator'
             FROM LIMS.RESULT r
            WHERE r.result_tmpl_id = 4
         GROUP BY r.test_id) ti ON ti.test_id = t.test_id
LEFT JOIN (SELECT r.test_id,
                  MAX(r.result) 'factor'
             FROM LIMS.RESULT r
            WHERE r.result_tmpl_id = 3
         GROUP BY r.test_id) f ON f.test_id = t.test_id