Вы слышали правильно. До сих пор потрясающие оконные функции были функцией, уникально зарезервированной для SQL. Кажется, даже этим сложным функциональным языкам программирования все еще не хватает этой прекрасной функциональности (поправьте меня, если я ошибаюсь, ребята из Haskell).
Мы написали тонны постов в блоге об оконных функциях, проповедуя их нашей аудитории, в таких статьях, как:
- Вероятно, самая крутая особенность SQL: оконные функции
- Используйте этот аккуратный трюк с функцией окна для расчета разницы во времени во временном ряду
- Как найти самую длинную последовательность событий в SQL
- Не упустите возможности SQL Power с FIRST_VALUE (), LAST_VALUE (), LEAD () и LAG ()
- Разница между ROW_NUMBER (), RANK () и DENSE_RANK ()
Один из моих любимых примеров использования оконных функций — промежуточный итог . Т.е. получить из следующей таблицы транзакций банковского счета:
|
1
2
3
4
5
6
7
|
| ID | VALUE_DATE | AMOUNT ||------|------------|--------|| 9997 | 2014-03-18 | 99.17 || 9981 | 2014-03-16 | 71.44 || 9979 | 2014-03-16 | -94.60 || 9977 | 2014-03-16 | -6.96 || 9971 | 2014-03-15 | -65.95 | |
… к этому, с расчетным балансом:
|
1
2
3
4
5
6
7
|
| ID | VALUE_DATE | AMOUNT | BALANCE ||------|------------|--------|----------|| 9997 | 2014-03-18 | 99.17 | 19985.81 || 9981 | 2014-03-16 | 71.44 | 19886.64 || 9979 | 2014-03-16 | -94.60 | 19815.20 || 9977 | 2014-03-16 | -6.96 | 19909.80 || 9971 | 2014-03-15 | -65.95 | 19916.76 | |
С SQL это просто кусок пирога. Обратите внимание на использование SUM(t.amount) OVER(...) :
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
SELECT t.*, t.current_balance - NVL( SUM(t.amount) OVER ( PARTITION BY t.account_id ORDER BY t.value_date DESC, t.id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ), 0) AS balanceFROM v_transactions tWHERE t.account_id = 1ORDER BY t.value_date DESC, t.id DESC |
Как работают оконные функции?
(не забудьте забронировать наш мастер-класс по SQL, чтобы узнать об оконных функциях и многом другом!)
Несмотря на иногда немного пугающий синтаксис, оконные функции действительно очень легко понять. Окна являются «представлениями» данных, полученных в ваших предложениях FROM / WHERE / GROUP BY / HAVING . Они позволяют вам получить доступ ко всем другим строкам относительно текущей строки, в то время как вы вычисляете что-то в своем предложении SELECT (или редко в своем предложении ORDER BY ). То, что на самом деле делает приведенное выше утверждение, таково:
|
1
2
3
4
5
6
7
|
| ID | VALUE_DATE | AMOUNT | BALANCE ||------|------------|---------|----------|| 9997 | 2014-03-18 | -(99.17)|+19985.81 || 9981 | 2014-03-16 | -(71.44)| 19886.64 || 9979 | 2014-03-16 |-(-94.60)| 19815.20 || 9977 | 2014-03-16 | -6.96 |=19909.80 || 9971 | 2014-03-15 | -65.95 | 19916.76 | |
Т.е. для любого данного баланса вычтите из текущего баланса SUM() « OVER() » окно всех строк, которые находятся в том же разделе, что и текущая строка (тот же банковский счет), и которые строго «выше» текущий ряд
Или подробно:
-
PARTITION BYуказывает «OVER()», который строит строки окна -
ORDER BYуказывает порядок окна -
ROWSуказывает, какие упорядоченные индексы строк следует учитывать
Можем ли мы сделать это с коллекциями Java?
Да мы можем! Если вы используете jOOλ : полностью бесплатную лицензированную библиотеку Apache 2.0 с открытым исходным кодом, которую мы разработали, потому что мы думали, что API-интерфейсы JDK 8 Stream и Collector просто не делают этого.
Когда была разработана Java 8, много внимания уделялось поддержке параллельных потоков. Это хорошо, но, конечно, не единственная полезная область, где можно применять функциональное программирование. Мы создали jOOλ, чтобы заполнить этот пробел — без реализации полностью нового, альтернативного API коллекций, такого как Javaslang или функциональный java .
jOOλ уже обеспечивает:
- Типы кортежей
- Более полезные вещи для упорядоченных, только последовательных потоков
С недавно выпущенным jOOλ 0.9.9 мы добавили две основные новые функции:
- Тонны новых коллекционеров
- Оконные функции
Много пропавших коллекционеров в JDK
JDK поставляется с парой сборщиков, но они кажутся неуклюжими и многословными, и никто действительно не ценит написание сборщиков, подобных тем, которые описаны в этом вопросе о переполнении стека (и многим другим).
Но случай использования, раскрытый в связанном вопросе, является очень допустимым. Вы хотите объединить несколько вещей из списка людей:
|
1
2
3
4
5
6
7
|
public class Person { private String firstName; private String lastName; private int age; private double height; private double weight; // getters / setters |
Если у вас есть этот список:
|
1
2
3
4
5
|
List<Person> personsList = new ArrayList<Person>();personsList.add(new Person("John", "Doe", 25, 1.80, 80));personsList.add(new Person("Jane", "Doe", 30, 1.69, 60));personsList.add(new Person("John", "Smith", 35, 174, 70)); |
Теперь вы хотите получить следующие агрегаты:
- Число людей
- Макс возраст
- Мин высота
- Средний вес
Это нелепая проблема для любого, кто привык писать SQL:
|
1
2
|
SELECT count(*), max(age), min(height), avg(weight)FROM person |
Готово. Насколько сложно это может быть в Java? Оказывается, нужно много кода для клея с использованием vanilla JDK 8 API. Рассмотрим сложные ответы, данные
С jOOλ 0.9.9 решение этой проблемы снова становится смехотворно тривиальным, и это выглядит почти как SQL :
|
01
02
03
04
05
06
07
08
09
10
|
Tuple result =Seq.seq(personsList) .collect( count(), max(Person::getAge), min(Person::getHeight), avg(Person::getWeight) );System.out.println(result); |
И результат дает:
|
1
|
(3, Optional[35], Optional[1.69], Optional[70.0]) |
Обратите внимание, что этот запрос не выполняется к базе данных SQL (для этого и нужен jOOQ ). Мы выполняем этот «запрос» к коллекции Java в памяти.
ОК, хорошо, это уже круто. А что насчет оконных функций?
Правильно, название этой статьи не обещало ничего сложного. Это обещало потрясающие оконные функции.
Тем не менее, оконные функции — это не что иное, как агрегаты (или ранжирование) в подмножестве вашего потока данных. Вместо объединения всего потока (или таблицы) в одну запись вы хотите сохранить исходные записи и обеспечить агрегацию для каждой отдельной записи напрямую.
Хорошим вводным примером для оконных функций является пример, представленный в этой статье, который объясняет разницу между ROW_NUMBER (), RANK () и DENSE_RANK () . Рассмотрим следующий запрос PostgreSQL:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT v, ROW_NUMBER() OVER(w), RANK() OVER(w), DENSE_RANK() OVER(w)FROM ( VALUES('a'),('a'),('a'),('b'), ('c'),('c'),('d'),('e')) t(v)WINDOW w AS (ORDER BY v); |
Это дает:
|
01
02
03
04
05
06
07
08
09
10
|
| V | ROW_NUMBER | RANK | DENSE_RANK ||---|------------|------|------------|| a | 1 | 1 | 1 || a | 2 | 1 | 1 || a | 3 | 1 | 1 || b | 4 | 4 | 2 || c | 5 | 5 | 3 || c | 6 | 5 | 3 || d | 7 | 7 | 4 || e | 8 | 8 | 5 | |
То же самое можно сделать в Java 8, используя jOOλ 0.9.9
|
01
02
03
04
05
06
07
08
09
10
11
|
System.out.println( Seq.of("a", "a", "a", "b", "c", "c", "d", "e") .window(naturalOrder()) .map(w -> tuple( w.value(), w.rowNumber(), w.rank(), w.denseRank() )) .format()); |
Уступая …
|
01
02
03
04
05
06
07
08
09
10
11
12
|
+----+----+----+----+| v0 | v1 | v2 | v3 |+----+----+----+----+| a | 0 | 0 | 0 || a | 1 | 0 | 0 || a | 2 | 0 | 0 || b | 3 | 3 | 1 || c | 4 | 4 | 2 || c | 5 | 4 | 2 || d | 6 | 6 | 3 || e | 7 | 7 | 4 |+----+----+----+----+ |
Опять же, обратите внимание, что мы не выполняем никаких запросов к базе данных. Все сделано в памяти.
Обратите внимание на две вещи:
- Оконные функции jOOλ возвращают ранги, основанные на 0, как ожидается для API Java, в отличие от SQL, который основан на 1.
- В Java невозможно создать специальные записи с именованными столбцами. Это прискорбно, и я надеюсь, что в будущем Java будет поддерживать такие возможности языка.
Давайте рассмотрим, что происходит именно в коде:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
System.out.println( // This is just enumerating our values Seq.of("a", "a", "a", "b", "c", "c", "d", "e") // Here, we specify a single window to be // ordered by the value T in the stream, in // natural order .window(naturalOrder()) // The above window clause produces a Window<T> // object (the w here), which exposes... .map(w -> tuple( // ... the current value itself, of type String... w.value(), // ... or various rankings or aggregations on // the above window. w.rowNumber(), w.rank(), w.denseRank() )) // Just some nice formatting to produce the table .format()); |
Это оно! Легко, не правда ли?
Мы можем сделать больше! Проверь это:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
System.out.println( Seq.of("a", "a", "a", "b", "c", "c", "d", "e") .window(naturalOrder()) .map(w -> tuple( w.value(), // v0 w.count(), // v1 w.median(), // v2 w.lead(), // v3 w.lag(), // v4 w.toString() // v5 )) .format()); |
Что дает вышеупомянутое?
|
01
02
03
04
05
06
07
08
09
10
11
12
|
+----+----+----+---------+---------+----------+| v0 | v1 | v2 | v3 | v4 | v5 |+----+----+----+---------+---------+----------+| a | 1 | a | a | {empty} | a || a | 2 | a | a | a | aa || a | 3 | a | b | a | aaa || b | 4 | a | c | a | aaab || c | 5 | a | c | b | aaabc || c | 6 | a | d | c | aaabcc || d | 7 | b | e | c | aaabccd || e | 8 | b | {empty} | d | aaabccde |+----+----+----+---------+---------+----------+ |
Ваше сердце аналитики должно подпрыгнуть, сейчас.
Подожди секунду. Можем ли мы сделать кадры тоже, как в SQL? Да мы можем. Как и в SQL, когда мы опускаем предложение frame в определении окна (но мы указываем предложение ORDER BY ), то по умолчанию применяется следующее:
|
1
2
|
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
Мы сделали это в предыдущих примерах. Это можно увидеть в столбце v5, где мы агрегируем строку от самого первого значения до текущего значения. Итак, давайте уточним кадр:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
System.out.println( Seq.of("a", "a", "a", "b", "c", "c", "d", "e") .window(naturalOrder(), -1, 1) // frame here .map(w -> tuple( w.value(), // v0 w.count(), // v1 w.median(), // v2 w.lead(), // v3 w.lag(), // v4 w.toString() // v5 )) .format()); |
И результат, тривиально:
|
01
02
03
04
05
06
07
08
09
10
11
12
|
+----+----+----+---------+---------+-----+| v0 | v1 | v2 | v3 | v4 | v5 |+----+----+----+---------+---------+-----+| a | 2 | a | a | {empty} | aa || a | 3 | a | a | a | aaa || a | 3 | a | b | a | aab || b | 3 | b | c | a | abc || c | 3 | c | c | b | bcc || c | 3 | c | d | c | ccd || d | 3 | d | e | c | cde || e | 2 | d | {empty} | d | de |+----+----+----+---------+---------+-----+ |
Как и ожидалось, на lead() и lag() это не влияет, в отличие от count() , median() и toString()
Потрясающие! Теперь давайте рассмотрим промежуточный итог.
Часто вы не вычисляете оконные функции по скалярному значению самого потока, так как это значение обычно не скалярное значение, а кортеж (или POJO на языке Java). Вместо этого вы извлекаете значения из кортежа (или POJO) и выполняете агрегирование для этого. Итак, опять же, при расчете BALANCE , нам нужно сначала извлечь AMOUNT .
|
1
2
3
4
5
6
7
|
| ID | VALUE_DATE | AMOUNT | BALANCE ||------|------------|---------|----------|| 9997 | 2014-03-18 | -(99.17)|+19985.81 || 9981 | 2014-03-16 | -(71.44)| 19886.64 || 9979 | 2014-03-16 |-(-94.60)| 19815.20 || 9977 | 2014-03-16 | -6.96 |=19909.80 || 9971 | 2014-03-15 | -65.95 | 19916.76 | |
Вот как бы вы написали промежуточную сумму с помощью Java 8 и jOOλ 0,9,9
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
BigDecimal currentBalance = new BigDecimal("19985.81");Seq.of( tuple(9997, "2014-03-18", new BigDecimal("99.17")), tuple(9981, "2014-03-16", new BigDecimal("71.44")), tuple(9979, "2014-03-16", new BigDecimal("-94.60")), tuple(9977, "2014-03-16", new BigDecimal("-6.96")), tuple(9971, "2014-03-15", new BigDecimal("-65.95"))).window(Comparator .comparing((Tuple3<Integer, String, BigDecimal> t) -> t.v1, reverseOrder()) .thenComparing(t -> t.v2), Long.MIN_VALUE, -1).map(w -> w.value().concat( currentBalance.subtract(w.sum(t -> t.v3) .orElse(BigDecimal.ZERO)))); |
Уступая
|
1
2
3
4
5
6
7
8
9
|
+------+------------+--------+----------+| v0 | v1 | v2 | v3 |+------+------------+--------+----------+| 9997 | 2014-03-18 | 99.17 | 19985.81 || 9981 | 2014-03-16 | 71.44 | 19886.64 || 9979 | 2014-03-16 | -94.60 | 19815.20 || 9977 | 2014-03-16 | -6.96 | 19909.80 || 9971 | 2014-03-15 | -65.95 | 19916.76 |+------+------------+--------+----------+ |
Несколько вещей изменились здесь:
- Компаратор теперь учитывает два сравнения. К сожалению, JEP-101 не был полностью реализован , поэтому нам нужно помочь компилятору с выводом типа здесь.
-
Window.value()теперь является кортежем, а не одним значением. Поэтому нам нужно извлечь из него интересный столбецAMOUNT(черезt -> t.v3). С другой стороны, мы можем простоconcat()что дополнительное значение к кортежу
Но это уже все. Помимо многословности компаратора (о котором мы обязательно расскажем в будущей версии jOOλ), написание оконной функции — это очень просто.
Что еще мы можем сделать?
Эта статья не является полным описанием всего, что мы можем сделать с помощью нового API. Мы скоро напишем пост в блоге с дополнительными примерами. Например:
- Раздел по пунктам не описан, но также доступен
- Вы можете указать гораздо больше окон, чем одно окно, представленное здесь, каждое с индивидуальными
PARTITION BY,ORDER BYи спецификациями кадров
Кроме того, текущая реализация является довольно канонической, то есть она (пока) не кеширует агрегации:
- Для неупорядоченных / неструктурированных окон (одинаковое значение для всех разделов)
- Окна со строго восходящей рамкой (агрегация может основываться на предыдущем значении для ассоциативных коллекторов, таких как
SUM()илиtoString())
Это все с нашей стороны. Скачайте jOOλ, поиграйте с ним и наслаждайтесь тем, что самая потрясающая функция SQL теперь доступна для всех вас, разработчиков Java 8!
| Ссылка: | 2016 будет годом, который вспомнили, когда в Java наконец появились оконные функции! от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ . |