Я пытаюсь найти второе наибольшее значение в столбце и только второе по величине значение.
select a.name, max(a.word) as word
from apple a
where a.word < (select max(a.word) from apple a)
group by a.name;
По какой-то причине то, что у меня теперь, возвращает второе наибольшее значение, а все более низкие значения также, но, к счастью, избегают наибольшего значения.
Есть ли способ исправить это?
Вот еще одно концептуально простое решение, которое выполнялось для меня в .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 таким образом.
Самый простой, хотя и неэффективный (массив может исчерпывать память):
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;
Это также грубая сила, но гарантируется, что она будет проходить только ровно и только один раз:
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
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 гарантирует отсутствие пробелов
Очень грубый запрос, но он работает
select a.name, a.word
from apple a
where (select count(distinct b.word) from apple b
where b.word > a.word) = 1
Другой подход, используйте 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
)
Умм, вы не просто имеете в виду:
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.