Лучший способ выбрать строку с самой последней меткой времени, которая соответствует критерию

Это то, что появляется так часто, я почти переставал думать об этом, но я почти уверен, что я не делаю этого наилучшим образом.

Вопрос: предположим, что у вас есть следующая таблица

CREATE TABLE TEST_TABLE
(
  ID          INTEGER,
  TEST_VALUE  NUMBER,
  UPDATED     DATE,
  FOREIGN_KEY INTEGER
);

Каков наилучший способ выбрать TEST_VALUE, связанный с последней обновленной строкой, где FOREIGN_KEY = 10?

EDIT:. Сделайте это более интересным, так как ответы ниже просто идут с моим методом сортировки и затем выбирают верхнюю строку. Неплохо, но для больших возвратов заказ убьет производительность. Итак, бонусные очки: как сделать это масштабируемым образом (т.е. Без лишнего порядка).

Ответы

Ответ 1

Аналитические функции - ваши друзья

SQL> select * from test_table;

        ID TEST_VALUE UPDATED   FOREIGN_KEY
---------- ---------- --------- -----------
         1         10 12-NOV-08          10
         2         20 11-NOV-08          10

SQL> ed
Wrote file afiedt.buf

  1* select * from test_table
SQL> ed
Wrote file afiedt.buf

  1  select max( test_value ) keep (dense_rank last order by updated)
  2  from test_table
  3* where foreign_key = 10
SQL> /

MAX(TEST_VALUE)KEEP(DENSE_RANKLASTORDERBYUPDATED)
-------------------------------------------------
                                               10

Вы также можете расширить это, чтобы получить информацию для всей строки

SQL> ed
Wrote file afiedt.buf

  1  select max( id ) keep (dense_rank last order by updated) id,
  2         max( test_value ) keep (dense_rank last order by updated) test_value
,
  3         max( updated) keep (dense_rank last order by updated) updated
  4  from test_table
  5* where foreign_key = 10
SQL> /

        ID TEST_VALUE UPDATED
---------- ---------- ---------
         1         10 12-NOV-08

И аналитические подходы обычно довольно эффективны.

Следует также отметить, что аналитические функции относительно новы, поэтому, если вы находитесь на чем-то раньше 9.0.1, это может не сработать. Это уже не огромное население, но на старых версиях всегда есть несколько людей.

Ответ 2

Либо используйте подзапрос

WHERE updated = (SELECT MAX(updated) ...)

или выберите ТОП-1 запись с помощью

ORDER BY updated DESC

В синтаксисе Oracle это будет:

SELECT 
  * 
FROM 
(
  SELECT * FROM test_table
  ORDER BY updated DESC
)
WHERE 
  ROWNUM = 1

Ответ 3

Во-первых, вам всегда нужно посмотреть на все строки с этим внешним ключом и найти тот, у которого самое высокое значение UPDATED... что означает MAX или ORDER BY. Эффективность сравнения частично зависит от оптимизатора, поэтому будет зависеть от вашей версии Oracle. Однако ваши структуры данных могут оказывать большее влияние на фактическую производительность. Индекс FOREIGN_KEY, UPDATED DESC, TEST_VALUE, вероятно, предоставит наиболее масштабируемое решение для запросов, поскольку Oracle, как правило, сможет дать ответ, просто обращаясь к одному листовому блоку. Это может негативно сказаться на вставках, поскольку в эту структуру должны быть вставлены новые записи.

Ответ 4

Вероятно, более низкий способ, которым я сейчас занимаюсь, делает что-то вроде этого

SELECT TEST_VALUE
FROM TEST_TABLE
WHERE ID = (
  SELECT ID
  FROM (
    SELECT ID
    FROM TEST_TABLE
    WHERE FOREIGN_KEY = 10
    ORDER BY UPDATED DESC
  )
  WHERE ROWNUM = 1
)

но, пожалуйста, StackOverflow Geniuses, научите меня некоторым трюкам

Ответ 5

SELECT TEST_VALUE
  FROM TEST_TABLE
 WHERE UPDATED      = ( SELECT MAX(UPDATED)
                          FROM TEST_TABLE
                         WHERE FOREIGN_KEY = 10 )
   AND FOREIGN-KEY  = 10
   AND ROWNUM       = 1  -- Just in case records have the same UPDATED date

Скорее это первая запись, вы можете сломать галстук с самым высоким ID или, возможно, наименьшим/большим TEST_VALUE.

Индекс FOREIGN_KEY, UPDATED поможет запросить performace.

Ответ 6

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

WITH test_table_ranked AS (
    SELECT
        test_table.*,
        ROW_NUMBER() OVER (
            PARTITION BY foreign_key ORDER BY updated DESC
        ) AS most_recent
    FROM
        test_table
)
SELECT *
FROM test_table_ranked
WHERE most_recent = 1
-- AND foreign_key = 10

Этот запрос находит самые последние обновления для каждого внешнего ключа в таблице. Хотя ответ Justin быстрее, когда ключ известен, этот запрос также работает в SQL Server.

Ответ 7

Производительность будет зависеть от индексации. Вот метод.

WITH 
ten AS
(
    SELECT *
    FROM TEST_TABLE
    WHERE FOREIGH_KEY = 10
)
SELECT TEST_VALUE 
FROM ten
WHERE UPDATED = 
(
    SELECT MAX(DATE)
    FROM ten
)

Ответ 9

select test_value
from
(
  select test_value 
  from test_table
  where foreign_key=10
  order by updated desc
)
where rownum = 1

Oracle достаточно умен, чтобы понять, что ему требуется только одна строка из внутреннего выбора, и он будет делать это эффективно.

Ответ 10

не будет работать:

SELECT TOP 1 ID
FROM test_table
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESC

нет необходимости в подзапросе...