Попытка найти второе по величине значение в столбце (postgres sql)

Ответ 1

Вот еще одно концептуально простое решение, которое выполнялось для меня в .1 миллисекундах на столе в 21 миллион строк, в соответствии с EXPLAIN ANALYZE. Он ничего не возвращает в случае, когда имеется только одно значение.

SELECT a.name, 
(SELECT word FROM apple ap WHERE ap.name=a.name ORDER BY word ASC OFFSET 1 LIMIT 1) 
FROM apple a

Обратите внимание, что в моей таблице уже есть существующие индексы по имени, слову и (имя, слово), что позволяет мне использовать ORDER BY таким образом.

Ответ 2

Самый простой, хотя и неэффективный (массив может исчерпывать память):

select student, (array_agg(grade order by grade desc))[2]
from 
student_grades
group by student

Эффективный:

create aggregate two_elements(anyelement)
(
sfunc = array_limit_two,
stype = anyarray,
initcond = '{}'
);

create or replace function array_limit_two(anyarray, anyelement) returns anyarray
as 
$$
begin
    if array_upper($1,1) = 2 then
        return $1;
    else
        return array_append($1, $2);
    end if;
end;
$$ language 'plpgsql';

Данные теста:

create table student_grades
(
student text,
grade int
);



insert into student_grades values 
('john',70),
('john',80),
('john',90),
('john',100);


insert into student_grades values
('paul',20),
('paul',10),
('paul',50),
('paul',30);


insert into student_grades values
('george',40);

Тестовый код:

-- second largest
select student, coalesce( (two_elements(grade order by grade desc))[2], max(grade) /* min would do too, since it one element only */ )
from 
student_grades
group by student


-- second smallest
select student, coalesce( (two_elements(grade order by grade))[2], max(grade) /* min would do too, since it one element only */ )
from 
student_grades
group by student

Вывод:

q_and_a=# -- second largest
q_and_a=# select student, coalesce( (two_elements(grade order by grade desc))[2], max(grade) /* min would do too, since it one element only */ )
q_and_a-# from
q_and_a-# student_grades
q_and_a-# group by student;
 student | coalesce
---------+----------
 george  |       40
 john    |       90
 paul    |       30
(3 rows)


q_and_a=#
q_and_a=# -- second smallest
q_and_a=# select student, coalesce( (two_elements(grade order by grade))[2], max(grade) /* min would do too, since it one element only */ )
q_and_a-# from
q_and_a-# student_grades
q_and_a-# group by student;
 student | coalesce
---------+----------
 george  |       40
 john    |       80
 paul    |       20
(3 rows)

ИЗМЕНИТЬ @diesel Самый простой (и эффективный):

-- second largest
select student, array_min(two_elements(grade order by grade desc))
from 
student_grades
group by student;

-- second smallest
select student, array_max(two_elements(grade order by grade))
from 
student_grades
group by student;

Функция array_max:

create or replace function array_min(anyarray) returns anyelement
as
$$
select min(unnested) from( select unnest($1) unnested ) as x
$$ language sql;

create or replace function array_max(anyarray) returns anyelement
as
$$
select max(unnested) from( select unnest($1) unnested ) as x
$$ language sql;

ИЗМЕНИТЬ

Может быть самым простым и эффективным из всех, если только Postgresql сделает array_max встроенной функцией и облегчит предложение LIMIT при агрегации:-) Предложение LIMIT для агрегации - это функция моей мечты на Postgresql

select student, array_max( array_agg(grade order by grade limit 2) )
from 
student_grades
group by student;

Пока этот LIMIT для агрегации еще недоступен, используйте это:

-- second largest
select student, 

    array_min
    (

        array ( 
               select grade from student_grades 
               where student = x.student order by grade desc limit 2 )

    )

from 
student_grades x
group by student;


-- second smallest
select student, 

    array_max
    (

        array ( 
               select grade from student_grades 
               where student = x.student order by grade limit 2 )

    )

from 
student_grades x
group by student;

Ответ 3

Это также грубая сила, но гарантируется, что она будет проходить только ровно и только один раз:

select name,word
  from (
         select name,word
              , row_number() over (partition by name 
                                       order by word desc)
                as rowNum
           from apple
       ) x
 where rowNum = 2

Эта версия ниже может работать лучше, если у вас есть индекс покрытия (имя, слово), и есть высокое количество значений слов для имени:

with recursive myCte as
(
 select name,max(word) as word
      , 1 as rowNum
   from apple
  group by name
  union all
 select par.name
      , (select max(word) as word
           from apple 
          where name = par.name
            AND word < par.word
        ) as word
      , 2 as rowNum
   from myCte par
  where par.rowNum = 1
)
select * from myCte where rownum = 2

Ответ 4

SELECT *
FROM (
  SELEC name, 
        dense_rank() over (partition by name order by word desc) as word_rank,
        count(*) over (partition by name) as name_count
  FROM apple
) t
WHERE (word_rank = 2 OR name_count = 1)

Edit:
name_count = 1 заботится о тех случаях, когда для определенного имени присутствует только одна строка.

Используя dense_rank() вместо rank(), убедитесь, что есть строка с word_rank = 2, поскольку dense_rank гарантирует отсутствие пробелов

Ответ 5

Очень грубый запрос, но он работает

select a.name, a.word
from apple a
where (select count(distinct b.word) from apple b
    where b.word > a.word) = 1

Ответ 6

Другой подход, используйте RANK:

with ranking as
(
    select student, grade, rank() over(partition by student order by grade desc) as place
    from 
    student_grades
)
select * 
from
ranking
where 
    (student, place) 
    in 
    (
        select student, max(place)
        from ranking
        where place <= 2
        group by student
    )

В секундах от MIN:

with ranking as
(
    select student, grade, 
        rank() 
        -- just change DESC to ASC
        over(partition by student order by grade ASC ) as place
    from 
    student_grades
)
select * 
from
ranking
where 
    (student, place) 
    in 
    (                
        select student, max(place) -- still max
        from ranking
        where place <= 2
        group by student
    )

Ответ 7

Умм, вы не просто имеете в виду:

select a.name, max(a.word) as word
from apple a
where a.word < (select max(b.word) from apple b WHERE a.name = b.name)
group by a.name;

ты? Одна строка для имени возвращает второе наибольшее значение для имени (или нет строки, если нет второго наивысшего значения).

Если это то, что вы хотите, в вашем запросе просто отсутствует ограничение, хотя я подозреваю, что это, вероятно, два сканирования таблицы, если PostgreSQL имеет смысл преобразовать его в JOIN.