ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / mysql 8 Window Function
25 сообщений из 42, страница 1 из 2
  1  все
mysql 8 Window Function
    #39760189
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
вот тут
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value
написано
Код: plaintext
Returns the value of expr from the last row of the window frame.
но в примере не понятно что LAST_VALUE возвращает и почему
как получить последнее значение ?
и как получить количество в группе? при ....WINDOW w AS (PARTITION BY.....
...
Рейтинг: 0 / 0
16.01.2019, 11:39
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760198
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Что там непонятного? есть фрейм w с партиционированием по subject и сортировкой по time - вот в этом фрейме и получишь последнее с этой сортировкой значение с учётом указанных границ фрейма.
...
Рейтинг: 0 / 0
16.01.2019, 11:57
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760206
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
 SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

first отображает логично.
а last вроде как должна отображать 20 и 25
а по-факту просто дублирует val
...
Рейтинг: 0 / 0
16.01.2019, 12:11
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760272
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
А ты забыл про ROWS UNBOUNDED PRECEDING ?
...
Рейтинг: 0 / 0
16.01.2019, 14:05
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760306
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
 
создаём , заполняем
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
CREATE TABLE test1 (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  n int(11) DEFAULT NULL,
  m int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 0,
AVG_ROW_LENGTH = 1820,
CHARACTER SET utf8,
COLLATE utf8_general_ci;


INSERT INTO test1 VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 3, 1),
(5, 4, 1),
(6, 16, 2),
(7, 17, 2),
(8, 19, 2),
(9, 18, 2);




AkinaА ты забыл про ROWS UNBOUNDED PRECEDING ?

 
нет , не забыл
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT
  ROW_NUMBER() OVER W AS 'row_number',
  m,
  n,
  LAG(n, 1, 0) OVER w AS 'lag',
  LEAD(n, 1, 0) OVER w AS 'lead',
  NTH_VALUE(n, 1) OVER w AS 'NTH_VALUE',
  FIRST_VALUE(n) OVER w AS 'first',
  LAST_VALUE(n) OVER w AS 'LAST_VALUE'
FROM test1
WINDOW w AS (PARTITION BY m
ORDER BY n ROWS UNBOUNDED PRECEDING);



"row_number""m""n""lag""lead""NTH_VALUE""first""LAST_VALUE"1110211121213112313231134133411351430114121601716161622171618161617321817191616184219180161619


 
так работает
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
  ROW_NUMBER() OVER w AS 'row_number',
  m,
  n,
  LAG(n, 1, 0) OVER w AS 'lag',
  LEAD(n, 1, 0) OVER w AS 'lead',
  NTH_VALUE(n, 1) OVER w AS 'NTH_VALUE',
  FIRST_VALUE(n) OVER w AS 'first',
  FIRST_VALUE(n) OVER (w ORDER by n DESC ) AS 'LAST_VALUE'
FROM test1
WINDOW w AS ( PARTITION BY m) ORDER BY n;


"row_number""m""n""lag""lead""NTH_VALUE""first""LAST_VALUE"1110211421213114313231144133411451430114121601716161922171619161619421819016161932191718161619
...
Рейтинг: 0 / 0
16.01.2019, 15:02
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760331
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
вот ещё остался вопрос про количество в "подгруппах"
...
Рейтинг: 0 / 0
16.01.2019, 15:30
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760335
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
вариант нумерации
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT
  ROW_NUMBER() OVER v AS `row_number_all`,
  ROW_NUMBER() OVER w AS `row_number_groop`,
  m,
  n,
  LAG(n, 1, 0) OVER w AS `lag_`,
  LEAD(n, 1, 0) OVER w AS `lead`,
  NTH_VALUE(n, 1) OVER w AS `NTH_VALUE`,
  FIRST_VALUE(n) OVER w AS `first`,
  FIRST_VALUE(n) OVER (w ORDER by n DESC ) AS `LAST_VALUE`
FROM test1
WINDOW  w AS ( PARTITION BY m  ),  v AS (  ORDER BY n)ORDER BY n;



"row_number_all""row_number_groop""m""n""lag_""lead""NTH_VALUE""first""LAST_VALUE"1111021142212131143313231144413341145514301146121601716161972217161916161984218190161619932191718161619
...
Рейтинг: 0 / 0
16.01.2019, 15:33
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760339
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
вот только что-то не так с lag_ и lead ....
...
Рейтинг: 0 / 0
16.01.2019, 15:38
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760353
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
вот так надо
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT
  ROW_NUMBER() OVER v AS `row_number_all`,
  ROW_NUMBER() OVER (w ORDER by n  ) AS `row_number_groop`,
  m,
  n,
  LAG(n, 1, 0) OVER (w ORDER by m,n  ) AS `lag_`,
  LEAD(n, 1, 0) OVER (w ORDER by m,n  ) AS `lead`,
  NTH_VALUE(n, 1) OVER w AS `NTH_VALUE`,
  FIRST_VALUE(n) OVER w AS `first`,
  FIRST_VALUE(n) OVER (w ORDER by n DESC ) AS `LAST_VALUE`
FROM test1
WINDOW  w AS ( PARTITION BY m  ),  v AS (  ORDER BY n)ORDER BY n;;



"row_number_all""row_number_groop""m""n""lag_""lead""NTH_VALUE""first""LAST_VALUE"1111021142212131143313231144413341145514301146121601716161972217161816161983218171916161994219180161619


вот только как по скорости такое? новшество...
...
Рейтинг: 0 / 0
16.01.2019, 15:52
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760381
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадянет , не забылНе понял, что тебя не устраивает в результате запроса?
Скажем, берём первую запись.

Партиционирование по m , так что во фрейме с этой записью будут только записи с тем же m=1 .
Сортировка по n - так что записи сортируются в порядке возрастания n .
Граница ROWS UNBOUNDED PRECEDING - значит, во фрейм для ЭТОЙ записи попадают только те записи, которые идут не позже её в указанной сортировке.
Т.е. для данной записи фрейм состоит из неё одной. И соответственно LAST_VALUE(n) для этой единственной записи равно значению n у этой записи, т.е. 1 .

Возьмём вторую запись.

Партиционирование по m , так что во фрейме с этой записью будут только записи с тем же m=1 .
Сортировка по n - так что записи сортируются в порядке возрастания n .
Граница ROWS UNBOUNDED PRECEDING - значит, во фрейм для ЭТОЙ записи попадают она ( n=2 ), а также все предыдущие записи в указанной сортировке, т.е. ещё и запись с n=1 .
Т.е. для данной записи фрейм состоит из двух записей. И соответственно LAST_VALUE(n) среди них при сортировке по возрастанию n будет 2 .

И так далее...
...
Рейтинг: 0 / 0
16.01.2019, 16:16
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760389
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
AkinaНе понял, что тебя не устраивает в результате запроса?тут 21786797
LAST_VALUE не вяжется с FIRST_VALUE
...
Рейтинг: 0 / 0
16.01.2019, 16:23
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760392
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадя , укажи конкретную строку вывода и объясни, чем именно тебе не нравится выведенное там значение.
...
Рейтинг: 0 / 0
16.01.2019, 16:25
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760400
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
колонка FIRST_VALUE отображает первое значение по своей "подгруппе"
а колонка LAST_VALUE должна отображать последние значения по "подгруппе" - или я не правильно понял?
Код: plaintext
Returns the value of expr from the last row of the window frame.
...
Рейтинг: 0 / 0
16.01.2019, 16:30
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760522
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадяколонка LAST_VALUE должна отображать последние значения по "подгруппе" - или я не правильно понял?НЕТ!!!
По ФРЕЙМУ !!!
...
Рейтинг: 0 / 0
16.01.2019, 20:54
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760610
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
AkinaПо ФРЕЙМУ !!!
Код: plaintext
1.
2.
 FIRST_VALUE(expr) [null_treatment] over_clause

Returns the value of expr from the first row of the window frame.
тот же фрейм , но результаты разные
...
Рейтинг: 0 / 0
17.01.2019, 08:10
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760620
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Не-е-е...

Не путай раздел (PARTITION) и фрейм (PARTITION + BORDERS).
Фрейм всегда является подмножеством раздела (в т.ч. может с ним совпадать).
Если раздел одинаков для всех записей раздела, то фрейм может быть индивидуален для каждой записи.
...
Рейтинг: 0 / 0
17.01.2019, 09:15
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760680
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Код: plaintext
1.
2.
Returns the value of expr from the last row of the window frame.
Returns the value of expr from the first row of the window frame.
AkinaНе путай раздел (PARTITION) и фрейм (PARTITION + BORDERS).?????
...
Рейтинг: 0 / 0
17.01.2019, 10:39
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760739
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадя , фрейм включает три компонента.

Первый - partition_clause, секционирование (PARTITION BY). Определяет деление записей на группы по равенству выражения секционирования.
Второй - order_clause, сортировка (ORDER BY). Определяет порядок записей в секции.
Третий - frame_clause, границы (BORDERS). Определяет, какие из записей секции будут включены во фрейм текущей записи с учётом текущего порядка. Задаются как ROWS - по номеру записи в текущей секции при текущей сортировке, либо как RANGE - по значению выражения, вычисляемого для каждой записи текущей секции (допустимые типы - числовой, тогда выражение должно иметь числовой тип, и дата-время, тогда выражение должно иметь тип INTERVAL).

Изучи, наконец, Window Function Frame Specification полностью.
...
Рейтинг: 0 / 0
17.01.2019, 11:56
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760816
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
Akina,

ты хочешь сказать , что для last и first фрейм - это разные понятия?
тут 21786568 всё одинаково , кроме вывода в колонках last и first
...
Рейтинг: 0 / 0
17.01.2019, 13:13
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39760846
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадядля last и first фрейм - это разные понятия?Никто не мешает каждой из функций указать свой фрейм.

вадятут 21786568 всё одинаково , кроме вывода в колонках last и firstДа ты задолбал общими фразами. Чётко объясняй - запись такая-то, значение такое-то, считаю, что должно быть такое-то, потому что ...
...
Рейтинг: 0 / 0
17.01.2019, 13:43
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39761060
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
AkinaДа ты задолбал общими фразами. Чётко объясняй - запись такая-то, значение такое-то, считаю, что должно быть такое-то, потому что ...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+
обрати внимание на столбцы first и last
в столбце first все нормально -
для партиции subject st113 выводится первое значение 10 для всей партиции(первое значение в партиции)
для партиции subject xh458 выводится первое значение 0 для всей партиции(первое значение в партиции)

для колонки last всё совсем не так.....
вроде должно быть так
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |    10  |    10 |   20 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |   20 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   20 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |     0  |     0 |   25 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   25 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |   25 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   25 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+
...
Рейтинг: 0 / 0
17.01.2019, 17:12
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39761222
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
вадя,
блин, кажется даже я уже понял ))
last дублирует val, потому что это значение val и есть последнее в (каждом!) фрейме.
Еще раз: фрейм != партиция.
...
Рейтинг: 0 / 0
18.01.2019, 05:42
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39761229
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
paverвадя,
блин, кажется даже я уже понял ))
last дублирует val, потому что это значение val и есть последнее в (каждом!) фрейме.
Еще раз: фрейм != партиция.
тогда что отображает first?
...
Рейтинг: 0 / 0
18.01.2019, 07:13
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39761242
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
вадя , давай ещё раз.

Возьмём определённую запись исходной таблицы. Например, седьмую.
Определяем фрейм, который используется для обработки этой записи.
1) PARTITION BY subject
Следовательно, в секцию этой записи входят записи с тем же значением subject, что и у текущей записи. Это записи с пятой по девятую.
2) ORDER BY time
Следовательно, записи в секции сортируются по полю времени. И их порядок таков: пятая, шестая, седьмая, восьмая, девятая.
3) ROWS UNBOUNDED PRECEDING
Следовательно, фрейм текущей записи начинается с начала секции (т.е. с пятой записи), и завершается текущей записью. Т.е. фрейм текущей записи включает следующие записи в следующем порядке: пятая, шестая, седьмая.

Соответственно FIRST(val) вернёт значение val из первой записи фрейма, она же пятая запись. LAST(val) вернёт val из последней записи фрейма, т.е. седьмой.

==================

Вот теперь ТОЧНО ТАК ЖЕ распиши всё для той записи и того поля, значение которого тебе не нравится. Только тогда можно будет увидеть, в какой именно точке ты косячишь.
...
Рейтинг: 0 / 0
18.01.2019, 07:56
| Ответить | Цитировать | Написать  
mysql 8 Window Function
    #39761256
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
вадяpaverвадя,
блин, кажется даже я уже понял ))
last дублирует val, потому что это значение val и есть последнее в (каждом!) фрейме.
Еще раз: фрейм != партиция.
тогда что отображает first?
Значение val первой записи фрейма (которая по условиям твоего BORDERS совпадает и с первой записью секции).
...
Рейтинг: 0 / 0
18.01.2019, 09:35
| Ответить | Цитировать | Написать  
25 сообщений из 42, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / mysql 8 Window Function
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=41&tid=1829358]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 43ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 64ms
get tp. blocked users:
 1ms
others: 304ms
total:  455ms
созд. / загр.: 455ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]