Найдите строку с ближайшим ближайшим рейтингом (баллом) для каждой строки в таблице.

Я - пользователь-любитель SQL и хочу создать довольно продвинутый SQL-запрос для обнаружения наиболее похожих объектов в базе данных в Access

Точнее, моя база данных имеет следующую структуру

                        FID     PC1       PC2      PC3        PC4
                         1      0.765     0.043    -0.0023    -0.0009
                         5      0.223     -0.09    -0.0045    -0.0002  
                        300
                        ...
                        ...
                        ...

В этой структуре я хотел бы определить для каждого объекта (FID), какой другой (FID) наиболее похож на этот, рассматривая все 4 значения атрибутов ПК (PC1,PC2,PC3,PC4).

Я могу придумать математическое решение этой проблемы, но я не уверен, как реализовать это в запросе SQL. Может кто-нибудь посоветовать мне это?

заранее спасибо

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Точный запрос, который я выполняю, выглядит следующим образом:

 SELECT 
     Merged_Info.TARGET_FID, 
     Merged_Info.PC1_MEAN, 
     Merged_Info.PC2_Mean, 
     Merged_Info.PC3_MEAN, 
     Merged_Info.PC4_MEAN, 
     ([PC1_MEAN]+0.7*[PC2_Mean]+0.4*[PC3_MEAN]+0.2*[PC4_MEAN])/4 AS Rating
 FROM Merged_Info


SELECT
  q1.TARGET_FID,
  (
      SELECT TOP 1 q2.TARGET_FID 
      FROM myRatings  AS q2 
      WHERE q2.TARGET_FID<>q1.TARGET_FID 
      ORDER BY Abs(q2.Rating-q1.Rating), q2.TARGET_FID
  ) AS Closest_TARGET_FID
FROM myRatings  AS q1
Источник

1 ответ

Решение

Скажем, у нас есть примеры данных в таблице с именем [myData]:

FID  PC1  PC2  PC3  PC4
---  ---  ---  ---  ---
  1    1    3    5    2
  2    4    4    4    0
  3    5    3    1    1
  4    9    9    8    7

Мы используем некоторую формулу, чтобы дать каждой строке "оценку" (или "оценку") на основе значений от [PC1] до [PC4]. Для простоты мы будем использовать среднее значение. Поэтому мы создаем сохраненный запрос в Access с именем [myRatings]...

SELECT 
    myData.FID, 
    myData.PC1, 
    myData.PC2, 
    myData.PC3, 
    myData.PC4, 
    ([PC1]+[PC2]+[PC3]+[PC4])/4 AS Rating
FROM myData

... который возвращается...

FID  PC1  PC2  PC3  PC4  Rating
---  ---  ---  ---  ---  ------
  1    1    3    5    2    2.75
  2    4    4    4    0       3
  3    5    3    1    1     2.5
  4    9    9    8    7    8.25

Теперь мы можем использовать этот запрос в качестве основы для другого запроса, который находит FID с ближайшим рейтингом

SELECT 
    q1.FID,
    (
        SELECT TOP 1 q2.FID 
        FROM myRatings AS q2 
        WHERE q2.FID<>q1.FID 
        ORDER BY Abs(q2.Rating-q1.Rating), q2.FID
    ) AS Closest_FID
FROM myRatings AS q1

который возвращается

FID  Closest_FID
---  -----------
  1            2
  2            1
  3            1
  4            2

Обратите внимание, что предложение ORDER BY подзапроса включает q2.FID в качестве "прерывателя связи". Без этого запрос не будет выполнен, если он обработал строку с FID=1, потому что FID=2 и FID=3 имеют рейтинги, которые на 0,25 отличаются от рейтинга FID=1. В этом случае подзапрос вернет 2 строки, несмотря на TOP 1 пункт. (Это "вещь доступа".) Добавление второго условия ORDER BY гарантирует, что подзапрос всегда будет возвращать только одну строку.

Источник
Другие вопросы по тегам