У меня есть большой набор данных городов, основанный на координатах, где у каждого города есть оценка и набор координат, который указывает точную точку, где была взята оценка.
В наборе данных нет повторяющихся названий городов, так как я уже очистил данные, взяв только наивысший балл из каждого экземпляра строки города. Однако есть места, в которые включены несколько районов одного города, например. Бруклин, Квинс и т. д., а в некоторых местах по всему миру, таких как Дубай, есть сотни таких районов.
У меня есть код, который может вычислить разницу между двумя точками широты и долготы в метрах, который хорошо работает в SQL. Что мне нужно сделать, так это запустить его через мой набор данных и найти минимальное расстояние только для строк выше вычисляемой, поэтому, если в моей таблице выше для Торонто, будет найдено минимальное расстояние между Торонто и Нью-Йорком, Торонто и Сингапуром и затем, очевидно, верните результат Торонто/Нью-Йорк.
Затем я могу отфильтровать по этому умственному расстоянию (например, WHERE mindistance> 4000), чтобы предотвратить появление большинства этих нескольких городских районов и, следовательно, сделать мой набор данных полезным.
Поскольку меня интересует только наивысший балл в каждом городе, мне нужно только рассчитать это расстояние из строк выше в каждом случае, поскольку строки ниже всегда будут иметь более низкий балл, надеюсь, это имеет смысл.
Мне не удалось попробовать что-либо в соответствии с представленными выше строками, я пытался найти, как запустить что-то вроде цикла for в SQL, но не знаю, как заставить его анализировать только строки выше в таблице.
Привет, Ник, извини, я немного новичок в переполнении стека. Мы используем MS SQL Server. Города ранжируются по выставленным им баллам, я могу легко добавить поле идентификатора, поэтому в этом случае идентификатор Нью-Йорка = 1, Сингапур = 2, Торонто = 3. Я хотел бы сравнить каждую запись только с теми, у которых идентификатор меньше чем собственное.
Ну, если предположить, что "выше" означает меньший идентификатор, чем текущий, и если вам нужен новый столбец идентификатора ближайшего города,
SELECT C.Id,
C.City,
...,
(
SELECT TOP(1) C2.Id
FROM Cities AS C2
WHERE C2.Id < C.Id
ORDER BY ABS(C2.Latitude - C.Latitude) --> put distance formula here
) AS NearestCityId
FROM Cities AS C
ORDER BY C.Id
Это абсолютно идеально с очень небольшими корректировками, спасибо
Пример схемы и данных:
create table cities
(id int,
city varchar(100),
point geography);
insert into cities values (1, 'New York', 'POINT (-73.4 40.35)');
insert into cities values (2, 'Toronto', 'POINT (-79.34 43.65)');
insert into cities values (3, 'Singapore', 'POINT (103.85 1.29)');
insert into cities values (4, 'Tokyo', 'POINT (139.84 35.65)');
insert into cities values (5, 'Chicago', 'POINT (-87.62 41.88)');
insert into cities values (6, 'Milwaukee', 'POINT (-87.91 43.04)');
Запрос:
with cte as (
select
c1.city as city1,
c2.city as city2,
c1.point.STDistance(c2.point) as dist
from cities c1
inner join cities c2
on c1.id > c2.id
),
cte2 as (
select
row_number() over (partition by city1 order by dist asc) as row_number,
city1,
city2,
dist
from cte
)
select
city1 as city,
city2 as nearest_previous_city,
dist
from cte2
where row_number = 1
Шаги следующие:
cte
вычисляет расстояние между данным городом и всеми предыдущими городами (c1.id > c2.id
)cte2
упорядочивает соседние города для каждого города по расстоянию и присваивает им ранг (partition by city1 order by dist asc
))cte3
выбирает ближайший город (row_number = 1
)
Привет - каково ваше определение "выше"? Поскольку записи в таблице базы данных не имеют внутреннего порядка, как вы определяете для любой данной записи, с какими другими записями вы хотите ее сравнить? Кроме того, отметьте свой вопрос СУБД, которую вы используете (Oracle, MySQL, Postgres и т. д.), поскольку ответ, вероятно, должен быть специфичным для возможностей вашей конкретной СУБД.