SQL - расчет минимальной разницы между текущим значением строки и ТОЛЬКО строками выше

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

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

Город Счет широта Долгота Нью-Йорк 100 Икс у Сингапур 98,5 Икс у Торонто 96,7 Икс у ...

У меня есть код, который может вычислить разницу между двумя точками широты и долготы в метрах, который хорошо работает в SQL. Что мне нужно сделать, так это запустить его через мой набор данных и найти минимальное расстояние только для строк выше вычисляемой, поэтому, если в моей таблице выше для Торонто, будет найдено минимальное расстояние между Торонто и Нью-Йорком, Торонто и Сингапуром и затем, очевидно, верните результат Торонто/Нью-Йорк.

Затем я могу отфильтровать по этому умственному расстоянию (например, WHERE mindistance> 4000), чтобы предотвратить появление большинства этих нескольких городских районов и, следовательно, сделать мой набор данных полезным.

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

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

Привет - каково ваше определение "выше"? Поскольку записи в таблице базы данных не имеют внутреннего порядка, как вы определяете для любой данной записи, с какими другими записями вы хотите ее сравнить? Кроме того, отметьте свой вопрос СУБД, которую вы используете (Oracle, MySQL, Postgres и т. д.), поскольку ответ, вероятно, должен быть специфичным для возможностей вашей конкретной СУБД.

NickW 02.02.2023 13:07

Привет, Ник, извини, я немного новичок в переполнении стека. Мы используем MS SQL Server. Города ранжируются по выставленным им баллам, я могу легко добавить поле идентификатора, поэтому в этом случае идентификатор Нью-Йорка = 1, Сингапур = 2, Торонто = 3. Я хотел бы сравнить каждую запись только с теми, у которых идентификатор меньше чем собственное.

T BBB 02.02.2023 13:16
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
51
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Ну, если предположить, что "выше" означает меньший идентификатор, чем текущий, и если вам нужен новый столбец идентификатора ближайшего города,

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

Это абсолютно идеально с очень небольшими корректировками, спасибо

T BBB 02.02.2023 15:21

Пример схемы и данных:

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)

Другие вопросы по теме