[sql] Выбрать ближайщие точки
Для одной точки из первой таблицы делается легко.
Для всех - думается мне надо уже писать скрипт.
Для всех - думается мне надо уже писать скрипт.
именно для всех
Если в MSAccess есть нечто типа ROWID, то в принципе я себе этот запрос представляю.
Наверное я его представлю и в том случае если ROWID нет, но там будет такое извращение, что лучше скрипт написать.
Наверное я его представлю и в том случае если ROWID нет, но там будет такое извращение, что лучше скрипт написать.
Если в MSAccess есть нечто типа ROWID, то в принципе я себе этот запрос представляю.вот не знаю
Наверное я его представлю и в том случае если ROWID нет, но там будет такое извращение, что лучше скрипт написать.
но что-то мне подсказывает, что нетуа что делает ROWID (Oracle?)
Я неправ, нужен ROWNUM на самом деле.
принцип запроса можешь описать кратко?)
Здесь была фигня, подумаю еще 
даже не успел почитать)
Примерно так.
select t1.id, t2.id from t1, t2
where t2.id in
(select t2.id from t2 as s2
where rownum=1
order by ( (t1.x-s2.x)*(t1.x-s2.x) + (t1.y-s2.y)*(t1.y-s2.y) )
)
union all
<то же самое для rownum=2>
union all
и т.д.
Если эту хрень сделать подзапросом, то ее можно еще и отсортировать по t1.id.
Если у таблиц нет уникального ключа, можно использовать вместо <что-то>.id - (<что-то>.x, <что-то>.y)
select t1.id, t2.id from t1, t2
where t2.id in
(select t2.id from t2 as s2
where rownum=1
order by ( (t1.x-s2.x)*(t1.x-s2.x) + (t1.y-s2.y)*(t1.y-s2.y) )
)
union all
<то же самое для rownum=2>
union all
и т.д.
Если эту хрень сделать подзапросом, то ее можно еще и отсортировать по t1.id.
Если у таблиц нет уникального ключа, можно использовать вместо <что-то>.id - (<что-то>.x, <что-то>.y)
Не прокатит. ROWNUM`ы до сортировки назначаются.
Оракл сжуёт это:
select x1,y1,x2,y2 from (
select a.x x1, a.y y1, b.x x2, b.y y2, row_number over (partition by a.x, a.y order by a.x-b.x)*(a.x-b.x)+(a.y-b.y)*(a.y-b.y rn from a, b
) where rn <=5
Оракл сжуёт это:
select x1,y1,x2,y2 from (
select a.x x1, a.y y1, b.x x2, b.y y2, row_number over (partition by a.x, a.y order by a.x-b.x)*(a.x-b.x)+(a.y-b.y)*(a.y-b.y rn from a, b
) where rn <=5
Нехилый в оракле row_number 
Можно ли средствами SQL составить запросда можно. Причем с использованием только обычных операторов, которые есть в любом SQL-е. Но это будет очень тяжелый запрос. Если еще актульно, то я могу выписать запрос.
>то я могу выписать запрос.
Пиши, интересно посмотреть.
Пиши, интересно посмотреть.
Давай!
select a1.x, a1.y, b1.x, b1.y from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select mina2.x-b2.x)*(a2.x-b2.x)+(a2.y-b2.y)*(a2.y-b2.y r from a a2,b b2
where a2.x=a1.x and a2.y=a1.y
group by a2.x,a2.y
)
union all
select a1.x, a1.y, b1.x, b1.y from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select minx1-x2)*(x1-x2)+(y1-y2)*(y1-y2 r from (
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
minus
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select mina2.x-b2.x)*(a2.x-b2.x)+(a2.y-b2.y)*(a2.y-b2.y r from a a2,b b2
where a2.x=a1.x and a2.y=a1.y
group by a2.x,a2.y)
)
where x1=a1.x and y1=a1.y
group by x1,y1
)
union all
select a1.x, a1.y, b1.x, b1.y from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select minx1-x2)*(x1-x2)+(y1-y2)*(y1-y2 r from (
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
minus
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select mina2.x-b2.x)*(a2.x-b2.x)+(a2.y-b2.y)*(a2.y-b2.y r from a a2,b b2
where a2.x=a1.x and a2.y=a1.y
group by a2.x,a2.y)
minus
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select minx1-x2)*(x1-x2)+(y1-y2)*(y1-y2 r from (
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
minus
select a1.x x1, a1.y y1, b1.x x2, b1.y y2 from a a1,b b1
where (a1.x-b1.x)*(a1.x-b1.x)+(a1.y-b1.y)*(a1.y-b1.y) = (
select mina2.x-b2.x)*(a2.x-b2.x)+(a2.y-b2.y)*(a2.y-b2.y r from a a2,b b2
where a2.x=a1.x and a2.y=a1.y
group by a2.x,a2.y)
)
where x1=a1.x and y1=a1.y
group by x1,y1
)
)
where x1=a1.x and y1=a1.y
group by x1,y1
)
и т.д.
Типа того, только он может для каждой точки больше 5 выдать, если несколько точек на одном расстоянии находятся...
minus не везде есть
а где он есть вообще?
На where not exists заменить можно... Access вроде должен прожевать...
Яндекс говорит, что в оракле есть минус, более того
The MINUS operator is equivalent to the EXCEPT operator in the ANSI/ISO SQL standard.
точняк, есть такое
CREATE TABLE T1(
x1 float NOT NULL,
y1 float NOT NULL,
CONSTRAINT [PK_T1] PRIMARY KEY
(
x1,
y1
)
)
CREATE TABLE T2(
x2 float NOT NULL,
y2 float NOT NULL,
CONSTRAINT [PK_T2] PRIMARY KEY
(
x2,
y2
)
)
Сам запрос:
SELECT x1, y1, x2, y2
FROM
(
SELECT T1.*, T2.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
) V1
WHERE
(
SELECT COUNT (*)
FROM
(
SELECT T1.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
) V2
WHERE V1.x1 = V2.x1
AND V1.y1 = V2.y1
AND V1.r <= V2.r
) <= 5
опять будет глюк, если точки равноудалены, но средствами сыкуля, боюсь, этого никак не избежать 
Можно 
CREATE VIEW V1
AS
SELECT T1.*, T2.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
---------------------------------------------------------
CREATE VIEW V2
AS
SELECT V1.*,
(
SELECT COUNT (*)
FROM V1 _V2
WHERE V1.x1 = _V2.x1
AND V1.y1 = _V2.y1
AND V1.r > _V2.r
) AS InCircleN,
(
SELECT COUNT (*)
FROM V1 _V2
WHERE V1.x1 = _V2.x1
AND V1.y1 = _V2.y1
AND V1.r = _V2.r
) AS OnCircleN
FROM V1
---------------------------------------------------------
CREATE VIEW V3
AS
SELECT *
FROM V2
WHERE InCircleN < 5 AND InCircleN + OnCircleN > 5
---------------------------------------------------------
SELECT *
FROM V2
WHERE InCircleN + OnCircleN <= 5
UNION ALL
SELECT *
FROM V3
WHERE
(
SELECT COUNT(*)
FROM V3 _V3
WHERE V3.x1 = _V3.x1
AND V3.y1 = _V3.y1
AND V3.r = _V3.r
AND (V3.x2 < _V3.x2 OR (V3.x2 = _V3.x2 AND V3.y2 < _V3.y2
) < 5 - InCircleN
маньяк!
но можно проще
кстати, не хорошо навешивать PK на x,y - в реальности будет другой PK, да и их уникальности требовать не очень хорошо. Но работать будет, согласен
не хорошо навешивать PK на x,y - в реальности будет другой PKиз соображений производительности — да.
да и их уникальности требовать не очень хорошо
если точки идентифицируются своими координатами, то условие уникальности вполне логично, например, места в кинотеатре. Тут надо более детально исследовать предметную область. В первом посте говорилось только о координатах точек, и я не стал выяснять есть ли скрытый идентификатор точки и взял координаты в качестве идентификатора.
скрытый идентификатор, конечно есть, но все точки с одинаковыми наборами равнозначны и должны выводиться
правильный запрос
конечно есть, но все точки с одинаковыми наборами равнозначны и должны выводитьсянаборы это координаты?
что значит равнозначны? в таблице могут быть записи с одинаковыми координатами?
Запрос легко переписывается для любого случая.
кстати, хорошая задачка для собеседования
наборы это координаты?Наборы из двух координат, уникальными не являются
что значит равнозначны? в таблице могут быть записи с одинаковыми координатами?
Запрос легко переписывается для любого случая.
Равнозначны - значит, что если есть 10 точек на одинаковом расстоянии, то они все должны попадать в результат запроса.
10 точек на одинаковом расстоянии, то они все должны попадать в результат запросаэто противоречит изначальной формулировки запроса
запрос, который сможет выдать к каждой записи (точке) из первой таблицы, по 5 ближайших точек из второй
Сформулируй точно какой запрос нужен.
Запрос, выдающий все точки по 5 кратчайшим расстояниям
Возможно, ты говришь о таком запросе
SELECT *
FROM
(
SELECT *,
(
SELECT COUNT (*)
FROM
(
SELECT T1.*, T2.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
) _V2
WHERE V1.x1 = _V2.x1
AND V1.y1 = _V2.y1
AND V1.r > _V2.r
) AS InCircleN,
(
SELECT COUNT (*)
FROM
(
SELECT T1.*, T2.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
) _V2
WHERE V1.x1 = _V2.x1
AND V1.y1 = _V2.y1
AND V1.r >= _V2.r
) AS InOnCircleN
FROM
(
SELECT T1.*, T2.*, (x1-x2)*(x1-x2)+(y1-y2)*(y1-y2) AS r
FROM T1, T2
) V1
) V2
WHERE InOnCircleN <= 5 OR (InOnCircleN > 5 AND InCircleN < 5)
см. выше
но можно прощенапиши свой вариант запроса
по-моему connect by тебе поможет
Это ж вроде чисто Оракловая фича...
напиши свой вариант запроса
SELECT
*
FROM T1, T2 p1
WHERE
(
SELECT COUNT(*)
FROM T2 p2
WHERE
p1.x2-t1.x1)*(p1.x2-t1.x1)+(p1.y2-t1.y1)*(p1.y2-t1.y1) < (p2.x2-t1.x1)*(p2.x2-t1.x1)+(p2.y2-t1.y1)*(p2.y2-t1.y1
OR p1.x2-t1.x1)*(p1.x2-t1.x1)+(p1.y2-t1.y1)*(p1.y2-t1.y1) = (p2.x2-t1.x1)*(p2.x2-t1.x1)+(p2.y2-t1.y1)*(p2.y2-t1.y1)
AND (p1.x2 < p2.x2 OR (p1.x2 = p2.x2 AND (p1.y2 <= p2.y2
) <= 5
я думал над подобным расширением условного выражения после WHERE, но это не работает:
Для пары точек (0,0) (0,0.5) COUNT дает 3, это не то что нам нужно.
Для пары точек (0,0) (0,0.5) COUNT дает 3, это не то что нам нужно.
Прошу прощения, я не потестил. В моем выражении перепутаны знаки < и >
надо просто поменять все < на > или поменять алиасы p1 и p2 местами.
В данной реализации он возвращает 5 наиболее удаленных точек
надо просто поменять все < на > или поменять алиасы p1 и p2 местами.
В данной реализации он возвращает 5 наиболее удаленных точек
поменять алиасы p1 и p2 местамитоже не работает:
Прошу прощения, я не потестил.
а ты потести, и подумай
а я, прежде че запостить второй раз, потестил
подобные запросы уже встечались в моей практике, и подход отработан
X1 Y1 X2 Y2
0 0 0 0.5
0 0 1 -1
1 1 0 0.5
1 1 1 1
подобные запросы уже встечались в моей практике, и подход отработан
запостить второй раззапости, плиз, правильный запрос
запрос, который я выполняю для двух точек:
SELECT
*
FROM T1, T2 p2
WHERE
(
SELECT COUNT(*)
FROM T2 p1
WHERE
p1.x2-t1.x1)*(p1.x2-t1.x1)+(p1.y2-t1.y1)*(p1.y2-t1.y1) < (p2.x2-t1.x1)*(p2.x2-t1.x1)+(p2.y2-t1.y1)*(p2.y2-t1.y1
OR p1.x2-t1.x1)*(p1.x2-t1.x1)+(p1.y2-t1.y1)*(p1.y2-t1.y1) = (p2.x2-t1.x1)*(p2.x2-t1.x1)+(p2.y2-t1.y1)*(p2.y2-t1.y1)
AND (p1.x2 < p2.x2 OR (p1.x2 = p2.x2 AND (p1.y2 <= p2.y2
) <= 2
встечались в моей практике, и подход отработанно тогда почему ты писал
опять будет глюк, если точки равноудалены, но средствами сыкуля, боюсь, этого никак не избежать
это я сдуру написал
плохо подумал
плохо подумал
а потом хорошо подумал и понял, что подобные задачи я уже решал, хотя и в другом контексте
да, извиняюсь, я немного запутался. Твой запрос правильный, и более эффективен
Оставить комментарий
deniz666
Есть аксессовская база с двумя таблицами, в обеих содержатся координаты точек на карте.Можно ли средствами SQL составить запрос, который сможет выдать к каждой записи (точке) из первой таблицы, по 5 ближайших точек из второй? (в смысле x^2+y^2)