Выбор следующего по глубине образца

Предположим, нам требуется сформировать выборку, в которой вместе с информацией об образце указывается глубина отбора следующего (по глубине) образца.

Построим запрос, который выполняет следующие действия.

• Выбирает все пары образцов одной скважины, связанных условием, что второй образец был отобран с большей глубины.
• Группирует выборку по первому образцу (группа сосотит из пар с одинаковым первым образцом).
• В каждой группе вычисляет минимальное значение глубины отбора второго образца.

Пары образцов

Все пары образцов можно получить используя соединение таблицы sample с самой собой. Например, с помощью CROSS JOIN:

  SELECT *
  FROM sample CROSS JOIN sample AS second_sample
  

Нас интересуют такие пары, которые были отобраны из одной скважины. Это условие может быть указано либо в выражении WHERE, либо в условии соединения [INNER] JOIN. Воспользуемся вторым вариантом. Заметим, что CROSS JOIN был заменен на JOIN с условием. Также нам нужно условие на сравненеие глубин: второй образец отобран с большей глубины. Будем выбирать из ьаблицы только идентификаторы и глубины отбора образцов. Получаем следующий запрос.

  SELECT sample.sample_id AS id,
         sample.sample_depth AS depth,
         second_sample.sample_id AS id2,
         second_sample.sample_depth AS depth2
  FROM sample JOIN sample AS second_sample ON sample.borehole_id = second_sample.borehole_id
  WHERE second_sample.sample_depth > sample.sample_depth
  

Таким образом мы получили пары образцов.

Нахождение глубины отбора следующего образца

Можно заметить, что для образца с id=1 в получившейся выборке есть несколько записей, по одной для каждого образца из этой скважины, но с большей глубиной отбора. Нам нужно оставить записи, соответсвующие "ближайшему" образцу.

Вместо удаления лишних записей мы будем вычислять нужное значение. Для этого разделим записи на группы по значению id1 и в каждой группе вычислим минимальное значение depth2.

Для наглядности, вместо усложнения предыдущего запроса или использования вложенных запросов, будем использовать выражение WITH, которое позволит обращаться к данным предыдущего запроса по имени deeper_sample. Также добавим в выборку идентификатор скважины и отсорируем результат по скважине и глубине отбора первого образца из пары.

    WITH deeper_sample AS (
      SELECT sample.sample_id AS id1,
             sample.sample_depth AS depth1,
             sample.borehole_id,
             second_sample.sample_id AS id2,
             second_sample.sample_depth AS depth2
      FROM sample JOIN sample AS second_sample ON sample.borehole_id = second_sample.borehole_id
      WHERE second_sample.sample_depth > sample.sample_depth
    )
    SELECT id1,
           MIN(depth1) AS depth1,
           MIN(depth2) AS next_deeper
    FROM deeper_sample
    GROUP BY id1
    ORDER BY borehole_id, depth1
    

Упражнение: перепишите приведённый запрос без использования конструкции WITH.

Проблема последней записи

Последняя версия нашего запроса содержит один изъян: образец с максимальной глубиной отбора отсутствует в выборке. Это связано с тем, что для него нет парной записи.

Для включения в выпорку пар образцов записей, соответствующих образцам с максимальной глубиной отбора, необходимо заменить JOIN на LEFT JOIN. Однако, если мы просто добавим ключевое слово LEFT результат выполнения запроса не изменится.

    SELECT sample.sample_id AS id,
           sample.sample_depth AS depth,
           second_sample.sample_id AS id2,
           second_sample.sample_depth AS depth2
    FROM sample
         LEFT JOIN   /* <-------- Замена JOIN на LEFT JOIN */
         sample AS second_sample ON sample.borehole_id = second_sample.borehole_id
    WHERE second_sample.sample_depth > sample.sample_depth
    

Проблема в том, что условие WHERE second_sample.sample_depth ... требует, чтобы запись в табице second_sample существовала.

Исправить сиуацию можно перемещением условия из выражения WHERE в выражение LEFT JOIN.

    SELECT sample.sample_id AS id,
           sample.sample_depth AS depth,
           second_sample.sample_id AS id2,
           second_sample.sample_depth AS depth2
    FROM sample
         LEFT JOIN
         sample AS second_sample
           ON sample.borehole_id = second_sample.borehole_id
              AND second_sample.sample_depth > sample.sample_depth
    

Итоговый запрос

    WITH deeper_sample AS (
      SELECT sample.sample_id AS id1,
             sample.sample_depth AS depth1,
             sample.borehole_id,
             second_sample.sample_id AS id2,
             second_sample.sample_depth AS depth2
      FROM sample
           LEFT JOIN
           sample AS second_sample
             ON sample.borehole_id = second_sample.borehole_id
                AND second_sample.sample_depth > sample.sample_depth
    )
    SELECT id1,
           MIN(depth1) AS depth1,
           MIN(depth2) AS next_deeper
    FROM deeper_sample
    GROUP BY id1
    --  HAVING MIN(depth2) IS NULL  /* Для показа только самых глубоких образцов */
    ORDER BY borehole_id, depth1
    

Чтобы увидеть записи с максимальной глубиной отбора первого образца можно либо использовать для поля depth1 сортировку по убыванию, либо раскомментировать выражение HAVING, которое отбирает только те группы, для которых глубина depth2 не определена.