Автор оригинала: Vlad Mihalcea.
Я нашел этот вопрос на форуме Hibernate , и это очень хорошая возможность показать, почему освоение функций Windows является очень важным навыком для любого разработчика серверного программного обеспечения.
Давайте предположим, что у нас есть следующая таблица записей в нашей базе данных:
| 2000 | a | 1 | x | 0 | 1 |
| 2000 | a | 1 | y | 0 | 2 |
| 2000 | a | 1 | z | 0 | 3 |
| 2000 | a | 2 | z | 0 | 4 |
| 2000 | a | 2 | x | 0 | 5 |
| 2000 | b | 1 | x | 0 | 6 |
| 2000 | b | 1 | y | 0 | 7 |
| 2000 | b | 1 | z | 0 | 8 |
| 2000 | b | 2 | z | 0 | 9 |
| 2001 | a | 1 | x | 0 | 10 |
| 2001 | a | 1 | y | 0 | 11 |
| 2001 | a | 1 | z | 0 | 12 |
| 2001 | a | 2 | z | 0 | 13 |
| 2001 | a | 2 | x | 0 | 14 |
| 2001 | a | 2 | y | 0 | 15 |
| 2001 | a | 2 | w | 0 | 16 |
| 2001 | a | 3 | y | 0 | 17 |
| 2001 | a | 3 | w | 0 | 18 |
| 2001 | b | 1 | x | 0 | 19 |
| 2001 | b | 1 | y | 0 | 20 |
| 2001 | b | 2 | x | 0 | 21 |
| 2001 | b | 2 | z | 0 | 22 |
Как указано в вопросе, цель пользователя состоит в том, чтобы:
Я хочу обновить столбец c5 (до 1) каждой группы на основе столбцов c1, c2, c3, где c3 максимален в той же группе c1, c2.
Проще простого!
Как я уже объяснял, SQL-это волшебная палочка . Не только то, что SQL был движущей силой широкого внедрения СУБД, но даже базы данных NewSQL (Google Spanner, CockroachDB ) или фреймворки потоковой передачи данных, такие как Кафка , приняли SQL.
Итак, вот как вы можете решить эту проблему:
int updateCount = entityManager.createNativeQuery(
"update entries set c5 = 1 " +
"where id in " +
"( " +
" select id " +
" from ( " +
" select *, MAX (c3) OVER (PARTITION BY c1, c2) as max_c3 " +
" from entries " +
" ) t " +
" where t.c3 = t.max_c3 " +
") ")
.executeUpdate();
assertEquals( 7, updateCount );
Поскольку оконные функции позволяют агрегировать значения, не нарушая результирующий набор возвращаемой таблицы, мы можем легко найти соответствующие идентификаторы, которые могут быть переданы в инструкцию UPDATE.
План выполнения приведенной выше инструкции UPDATE выглядит следующим образом:
explain analyze
update entries set c5 = 1
where id in
(
select id
from (
select *, MAX (c3) OVER (PARTITION BY c1, c2) as max_c3
from entries
) t
where t.c3 = t.max_c3
)
Update on entries
(cost=15.27..23.30 rows=1 width=2134)
(actual time=0.154..0.154 rows=0 loops=1)
-> Nested Loop
(cost=15.27..23.30 rows=1 width=2134)
(actual time=0.094..0.104 rows=7 loops=1)
-> HashAggregate
(cost=15.12..15.13 rows=1 width=1084)
(actual time=0.083..0.085 rows=7 loops=1)
Group Key: t.id
-> Subquery Scan on t
(cost=12.85..15.12 rows=1 width=1084)
(actual time=0.063..0.080 rows=7 loops=1)
Filter: (t.c3 = t.max_c3)
Rows Removed by Filter: 15
-> WindowAgg
(cost=12.85..14.25 rows=70 width=1056)
(actual time=0.053..0.065 rows=22 loops=1)
-> Sort
(cost=12.85..13.02 rows=70 width=1052)
(actual time=0.044..0.045 rows=22 loops=1)
Sort Key: entries_1.c1, entries_1.c2
Sort Method: quicksort Memory: 26kB
-> Seq Scan on entries entries_1
(cost=0.00..10.70 rows=70 width=1052)
(actual time=0.009..0.011 rows=22 loops=1)
-> Index Scan using entries_pkey on entries
(cost=0.14..8.16 rows=1 width=1054)
(actual time=0.002..0.002 rows=1 loops=7)
Index Cond: (id = t.id)
Planning time: 0.201 ms
Execution time: 0.230 ms
В настоящее время все основные базы данных поддерживают оконные функции, MySQL 8.0 является одной из последних крупных СУБД, присоединившихся к клубу. Oracle, PostgreSQL и SQL Server поддерживают оконные функции уже довольно давно.
Однако, предполагая, что вы застряли с каким-то старым экземпляром MySQL, вы все равно можете решить эту проблему, используя следующий SQL-запрос:
int updateCount = entityManager.createNativeQuery(
"update entries set c5 = 1 " +
"where id in " +
"( " +
" select e.id " +
" from entries e " +
" inner join ( " +
" select c1, c2, max(c3) as max_c3 " +
" from entries " +
" group by c1, c2 " +
" ) t " +
" on e.c1 = t.c1 and e.c2 = t.c2 and e.c3 = t.max_c3 " +
") " )
.executeUpdate();
assertEquals( 7, updateCount );
План выполнения приведенной выше инструкции UPDATE выглядит следующим образом:
explain analyze
update entries set c5 = 1
where id in
(
select e.id
from entries e
inner join (
select c1, c2, max(c3) as max_c3
from entries
group by c1, c2
) t
on e.c1 = t.c1 and e.c2 = t.c2 and e.c3 = t.max_c3
)
Update on entries
(cost=25.49..26.22 rows=1 width=1612)
(actual time=0.112..0.112 rows=0 loops=1)
-> Nested Loop
(cost=25.49..26.22 rows=1 width=1612)
(actual time=0.081..0.090 rows=7 loops=1)
-> HashAggregate
(cost=25.35..25.36 rows=1 width=562)
(actual time=0.074..0.075 rows=7 loops=1)
Group Key: e.id
-> Hash Join
(cost=13.85..25.35 rows=1 width=562)
(actual time=0.067..0.070 rows=7 loops=1)
Hash Cond: ((e.c1 = t.c1) AND
((e.c2)::text = (t.c2)::text) AND
(e.c3 = t.max_c3))
-> Seq Scan on entries e
(cost=0.00..10.70 rows=70 width=538)
(actual time=0.016..0.019 rows=22 loops=1)
-> Hash
(cost=12.62..12.62 rows=70 width=1072)
(actual time=0.029..0.029 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on t
(cost=11.23..12.62 rows=70 width=1072)
(actual time=0.021..0.023 rows=4 loops=1)
-> HashAggregate
(cost=11.23..11.92 rows=70 width=524)
(actual time=0.017..0.018 rows=4 loops=1)
Group Key: entries_1.c1, entries_1.c2
-> Seq Scan on entries entries_1
(cost=0.00..10.70 rows=70 width=524)
(actual time=0.004..0.005 rows=22 loops=1)
-> Index Scan using entries_pkey on entries
(cost=0.14..0.85 rows=1 width=1054)
(actual time=0.001..0.002 rows=1 loops=7)
Index Cond: (id = e.id)
Planning time: 0.293 ms
Execution time: 0.219 ms
Если вы сравните оба плана выполнения, вы увидите, что запрос функции Windows дает лучшую стоимость, чем другой.
Таким образом, не только то, что запрос намного легче читать, но, скорее всего, он также будет более эффективным.
Как я уже объяснял, пришло время освободиться от мышления, основанного на SQL-92 .
SQL имеет множество функций, таких как оконные функции, Общие табличные выражения, СВОДНЫЕ , производные таблицы и операции набора, которые вы можете использовать для поиска правильного ответа на ваши вопросы об обработке данных. Для получения дополнительной информации о новых функциях SQL посетите веб-сайт Markus Winand Modern SQL .