SQL - min() получает наименьшее значение, max() - самое высокое, что, если мне нужно 2-е (или 5-е, или n-е) самое низкое значение?

Проблема, которую я пытаюсь решить, заключается в том, что у меня есть такая таблица:

a и b относятся к точке в другой таблице. расстояние - это расстояние между точками.

| id | a_id | b_id | distance | delete |
| 1  |  1   |   1  |  1       |   0    |
| 2  |  1   |   2  |  0.2345  |   0    |
| 3  |  1   |   3  |  100     |   0    |
| 4  |  2   |   1  |  1343.2  |   0    |
| 5  |  2   |   2  |  0.45    |   0    |
| 6  |  2   |   3  |  110     |   0    |
....

Важным столбцом, который я ищу, является a_id. Если бы я хотел сохранить шкаф b для каждого a, я мог бы сделать что-то вроде этого:

update mytable set delete = 1 from (select a_id, min(distance) as dist from table group by a_id) as x where a_gid = a_gid and distance > dist;
delete from mytable where delete = 1;

Что даст мне такую ​​таблицу результатов:

| id | a_id | b_id | distance | delete |
| 1  |  1   |   1  |  1       |   0    |
| 5  |  2   |   2  |  0.45    |   0    |
....

т. е. мне нужна одна строка для каждого значения a_id, и эта строка должна иметь наименьшее значение расстояния для каждого a_id.

Однако я хочу сохранить 10 ближайших точек для каждого a_gid. Я мог бы сделать это с помощью функции plpgsql, но мне любопытно, есть ли более SQL-способ.

min() и max() возвращают наименьшее и наибольшее, если бы существовала агрегатная функция, такая как nth(), которая возвращала бы n-е наибольшее/наименьшее значение, тогда я мог бы сделать это аналогично тому, как описано выше.

Я использую PostgeSQL.


postgresql sql
person Rory    schedule 02.02.2009    source источник

Ответы (4)


arrow_upward
4
arrow_downward

Попробуй это:

SELECT  *
FROM    (
    SELECT  a_id, (
        SELECT b_id
        FROM mytable mib
        WHERE mib.a_id = ma.a_id
        ORDER BY
            dist DESC
        LIMIT 1 OFFSET s
        ) AS b_id
    FROM    (
        SELECT DISTINCT a_id
        FROM mytable mia
        ) ma, generate_series (1, 10) s
    ) ab
WHERE   b_id IS NOT NULL

Проверено на PostgreSQL 8.3

person Quassnoi    schedule 02.02.2009

arrow_upward
4
arrow_downward

Я люблю postgres, поэтому я воспринял это как вызов, когда увидел этот вопрос.

Итак, для таблицы:

    Table "pg_temp_29.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 value  | integer |

Со значениями:

 SELECT value FROM foo ORDER BY value;
 value 
-------
     0
     1
     2
     3
     4
     5
     6
     7
     8
     9
    14
    20
    32
(13 rows)

Вы можете сделать:

SELECT value FROM foo ORDER BY value DESC LIMIT 1 OFFSET X

Где X = 0 для самого высокого значения, 1 для второго по величине значения, 2... И так далее.

Это может быть дополнительно встроено в подзапрос для получения необходимого значения. Итак, чтобы использовать набор данных, предоставленный в исходном вопросе, мы можем получить a_ids с десятью самыми низкими расстояниями, выполнив:

SELECT a_id, distance FROM mytable
 WHERE id IN
  (SELECT id FROM mytable WHERE t1.a_id = t2.a_id
   ORDER BY distance LIMIT 10);
 ORDER BY a_id, distance;

 a_id | distance 
------+----------
    1 |   0.2345
    1 |        1
    1 |      100
    2 |     0.45
    2 |      110
    2 |   1342.2
person Elijah    schedule 02.02.2009
comment
Черт, я должен был привести вам пример из предоставленного вами набора данных. - person Elijah; 02.02.2009
comment
Это не сработает, потому что мне нужно наименьшее значение для каждого значения a_id. - person Rory; 02.02.2009
comment
Если вы удалите DESC (для убывания), вы получите список самых низких значений: SELECT value FROM foo ORDER BY value LIMIT 1 OFFSET 1; В любом случае, я надеялся продемонстрировать принцип на более простом наборе данных. - person Elijah; 02.02.2009

arrow_upward
0
arrow_downward

Есть ли в PostgreSQL аналитическая функция rank()? Если это так, попробуйте:

select a_id, b_id, distance
from
( select a_id, b_id, distance, rank() over (partition by a_id order by distance) rnk
  from mytable
) where rnk <= 10;
person Tony Andrews    schedule 02.02.2009
comment
Postgres не имеет этой функции, но вы на правильном пути, и ваше предложение помогает мне найти ответ. - person Rory; 02.02.2009
comment
Теперь, в PostgreSQL 9.1, в оконных функциях есть функция rank(). См. postgresql.org/docs/9.1/static/tutorial-window.html. - person Stefan; 10.06.2012

arrow_upward
0
arrow_downward

Этот SQL должен найти вам N-ю самую низкую зарплату, которая должна работать в SQL Server, MySQL, DB2, Oracle, Teradata и почти в любой другой СУБД: (примечание: низкая производительность из-за подзапроса)

SELECT * /*This is the outer query part */
FROM mytable tbl1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(tbl2.distance))
FROM mytable tbl2
WHERE tbl2.distance < tbl1.distance)

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

Чтобы найти N-е наименьшее значение, мы просто находим значение, которое имеет ровно на N-1 значение ниже, чем оно само.

person AsyncMoksha    schedule 05.02.2014