Задачка на SQL

Есть такая вот табличка:

  id   | student_id | school_id | school_year_id | grade
-------+------------+-----------+----------------+-------
 25553 |       6144 |         8 |              1 |     9
  3518 |       6144 |        48 |              1 |     9
  3519 |       6145 |        48 |              1 |     9
  3520 |       6146 |        48 |              1 |     9
  3524 |       6150 |        48 |              1 |     9

Нужно выбрать записи с уникальными student_id. Если есть несколько строк с одним student_id нужно выбрать ту, для которой id максимальный.

Для этого случая должно получиться:

  id   | student_id | school_id | school_year_id | grade
-------+------------+-----------+----------------+-------
 25553 |       6144 |         8 |              1 |     9
  3519 |       6145 |        48 |              1 |     9
  3520 |       6146 |        48 |              1 |     9
  3524 |       6150 |        48 |              1 |     9
👍ПодобаєтьсяСподобалось0
До обраногоВ обраному0
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Это не кусок плана запроса, а весь план. Судя по всему иднекс не применяется.

Это правда, план запросов зависит от многих факторов — например от количества записей в таблицах. Но мы то ведь спорим не об этом, а о том будет ли СУБД применять индекс при джойне с подзапросом. А из вашего куска плана выполнения, я к примеру этого понять не могу...

2 crypto5:
вы правы.
провел следственный эксперимент:

создал таблицу

id  | student_id | school_id | school_year_id | grade
------------±--------------±--------------±---------------------±------
1 |       1 |       0 |           0 |   0
2 |       1 |       0 |           0 |   0
3 |       2 |       0 |           0 |   0
...
1000000 |   500000 |       0 |           0 |   0

Выполнял оба запроса — один с Inner Join, второй с IN
и, как оказалось, время выполнения получилось одинаковое (разница в тысячные секунды можно списать на погрешность).

НО! Все это благодаря тому, что получившийся план выполнения запроса в обоих случаях практически идентичен, для ясности приведу оба:

случай с Join:
Clustered Index Seek -> Hash Match (Aggregate) -> Hash Match (Inner Join) -> Select

Clustered Index Scan------------------------------------------^

случай с IN:
Clustered Index Seek -> Hash Match (Aggregate) -> Hash Match (Right Semi Join) -> Select

Clustered Index Scan------------------------------------------^

как вы видите, в случае с IN создается Right Join — и делает это не программист!, а оптимизатор MS Sql Server...

Так что, поправьте меня если я не прав, но в других СУБД никто не гарантирует, что производительность этих двух запросов будет идентичная.

> 2 crypto5: пруфлинка нет. Но вы можете это проверить на Northwind or AdventureWorks базах (в MS Management Studio есть performance statistics для этих целей)

Я не Microsoft guy, и что бы проверить это мне потребуются значительные усилия, но мои познания в области алгоритмов баз данных, говорят мне что вы не правы. Из под запроса у вас прихосят модифицированные данные, и индекс я думаю к ним не применится.

2 crypto5: пруфлинка нет. Но вы можете это проверить на Northwind or AdventureWorks базах (в MS Management Studio есть performance statistics для этих целей)

Anatoliy_2: через CTE нельзя, т.к. Макс юзает PostgreSQL

Можно через CTE
WITH MaxStudentID (max_id, student_id) AS
(
SELECT MAX (id), student_id
FROM students
GROUP BY student_id)
SELECT students.*
FROM students

JOIN MaxStudentID ON students.id = MaxStudentID.max_id

> Михрософт рекомендует использовать join вместо in, т.к. в случае с join получается индексированный по id список, следовательно поиск будет осуществляться быстрее

Это очевидно будет верно если джойнить таблицу с индексом. А вот если джойнить подзапрос, можно пруфлинк?

Эксперт подсказал решение проще:

SELECT DISTINCT ON (student_id) * FROM ....тут идет мой запрос .... ORDER BY student_id, id DESC

Без временной таблицы.

хех, аналогичная задача возникла сегодня

как то так:
select max (t.id) over (partition by t.student_id),
t.student_id,
t.school_id,
t.school_year_id,
t.grade

from table1 t

>> Нужно выбрать записи с уникальными student_id. Если есть несколько строк с одним student_id нужно выбрать ту, для которой id максимальный.

В Харькове такую задачу спрашивают практически на каждом собеседовании:)

Точно! Что-то я торможу. Проблема только в том что таблица которую я показал, не существует. Это результат запроса. Есть ли способ сделать эту фильтрацию в рамках запроса, не используя временные таблицы? Через subquery у меня не получилось (Postgres 8.3):


=# select * from (select attendances.id, student_id, school_id, school_year_id, grade
FROM students JOIN attendances ON students.id = attendances.student_id
JOIN school_years ON school_years.id = attendances.school_year_id
WHERE students.year_graduated_in is null AND attendances.grade < 12 AND
attendances.school_year_id = 1) qq where id in (select max(id) from qq group by student_id);
ERROR:  relation "qq" does not exist

Не суть важно конечно, я могу и временную табличку сделать. Но интересно.

2 bonifatio:

Михрософт рекомендует использовать join вместо in, т.к. в случае с join получается индексированный по id список, следовательно поиск будет осуществляться быстрее

SELECT * FROM Table WHERE id IN (SELECT MAX (id) FROM Table GROUP BY student_id)

SELECT t1.id, t1.student_id, t1.school_id, t1.school_year_id, t1.grade FROM... AS t1
INNER JOIN
(
SELECT MAX (id) id, student_id FROM...
GROUP BY student_id) AS t2

ON t1.id=t2.id

Підписатись на коментарі