SQL наибольший-n-на-группу с разрозненными данными по нескольким таблицам

У меня есть проблема с наибольшей группой, которую я пытаюсь решить. Я читал существующие решения, но, похоже, ни одно из них не соответствует причудам, с которыми я имею дело.

Сценарий. Допустим, есть нефтяная компания, у которой есть несколько нефтяных скважин. Каждая скважина имеет несколько нефтяных резервуаров. Каждый день кто-то считывает показания каждой скважины. Иногда они также снимают показания резервуаров, однако показания резервуаров проводятся гораздо реже и могут быть распределены в течение нескольких дней.

Все показания скважин и резервуаров записываются в базу данных, упорядоченную по дате.

CREATE TABLE "wellReadings" (
    "id" INT PRIMARY AUTO_INCREMENT,
    "date" DATETIME,
    "wellName" VARCHAR(10),
    ...
);

CREATE TABLE "tankReadings" (
    "id" INT PRIMARY AUTO_INCREMENT,
    "date" DATETIME,
    "well" INT NOT NULL,
    "tankName" VARCHAR(10),
    ...
);

Проблема: Для любого заданного показания скважины (в таблице wellReadings) на любую дату я хочу найти показания резервуара (в таблице tankReadings) для всех резервуаров, связанных с этой скважиной, снятых на та самая дата. Если для конкретного резервуара нет показаний на эту дату, мне нужны самые последние показания до этой даты.

До сих пор я пытался использовать соединения и подзапросы, но не смог сузить результаты до самых последних показаний резервуара (мои тестовые запросы продолжают давать мне ВСЕ показания резервуаров, которые происходят на дату чтения скважины или до нее). ). Коррелированный подзапрос может работать, но моя БД их не поддерживает (SQLite).


sql greatest-n-per-group
person Nairou    schedule 27.07.2011    source источник
comment
Я не совсем понимаю, как показатели tankReadings соотносятся с wellReadings, учитывая столбцы, которые вы предоставили.   -  person Neil    schedule 28.07.2011

Ответы (1)


arrow_upward
1
arrow_downward

Вы всегда можете попробовать что-то вроде:

SELECT * FROM wellReadings AS well, tankReadings AS tank
 WHERE tank.well = well.id 
   AND tank.date =
       (SELECT MAX(tank2.date) FROM tankReadings AS tank2
         WHERE tank2.id = tank.id
           AND tank2.date <= well.date)

Это может быть не самый эффективный способ сделать это, но он должен работать.

Пс. Если возможно, что некоторые скважины могут вообще не иметь прошлых показаний резервуара, вы можете вместо этого использовать левое соединение:

SELECT * FROM wellReadings AS well
  LEFT JOIN tankReadings AS tank
    ON tank.well = well.id 
   AND tank.date =
       (SELECT MAX(tank2.date) FROM tankReadings AS tank2
         WHERE tank2.id = tank.id
           AND tank2.date <= well.date)
person Ilmari Karonen    schedule 27.07.2011
comment
К сожалению, в своем первом ответе я предположил, что в каждом резервуаре несколько колодцев, а не наоборот. Исправлено. - person Ilmari Karonen; 28.07.2011