Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
Скачал, начал щупать, и натолкнулся на такое:
if object_id('tempdb..#t') is not null drop table #t
go
create table #t(usr int, dt datetime, t int)
insert #t values
(1, '20120308 08:00:00',	 0),
(1, '20120308 09:00:00',	 1),
(1, '20120308 10:00:00',	 0),
(1, '20120308 17:00:00',	 1),
(1, '20120308 17:02:00',	 1),
(2, '20120308 08:00:00',	 1),
(2, '20120308 17:00:00',	 1),
(2, '20120308 17:01:00',	 1),
(2, '20120308 17:03:00',	 0)

;with cte as(
	select *
		, FIRST_VALUE(t) over(order by usr) first_dt1
		, FIRST_VALUE(t) over(partition by usr order by dt) first_dt2
		, LAST_VALUE(t) over(order by usr) last_dt1
		, LAST_VALUE(t) over(partition by usr order by dt) last_dt2
		, LAST_VALUE(t) over(partition by usr order by dt range current row) last_dt3
		, LAST_VALUE(t) over(partition by usr order by dt range between
					unbounded preceding and unbounded following) last_dt4
	from #t
	
)
select * from cte

результат:
usrdttfirst_dt1first_dt2last_dt1last_dt2last_dt3last_dt4
12012-03-08 08:00:00.0000001001
12012-03-08 09:00:00.0001001111
12012-03-08 10:00:00.0000001001
12012-03-08 17:00:00.0001001111
12012-03-08 17:02:00.0001001111
22012-03-08 08:00:00.0001010110
22012-03-08 17:00:00.0001010110
22012-03-08 17:01:00.0001010110
22012-03-08 17:03:00.0000010000

Люди, гляньте на колонки last_dt1 и 2 и скажите, это я дурак, или все-таки лыжи не едут?
12 мар 12, 20:05    [12234871]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy, ну дык сортировка по dt не определена, соответственно видимо равносильно взятию любого, то же самое для FIRST_VALUE, если я правильно все понимаю, т.к. MSDN по ним прочитал только 5 минут назад))))
12 мар 12, 20:20    [12234943]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP
iljy, ну дык сортировка по dt не определена, соответственно видимо равносильно взятию любого, то же самое для FIRST_VALUE, если я правильно все понимаю, т.к. MSDN по ним прочитал только 5 минут назад))))

Здраст приехали, чей-то не определена-то? ORDER BY dt стоит для 2, 3 и 4. А проблема, как мне показалось, в том, что по умолчанию RANGE берется какой-то странный, не вся группа.
12 мар 12, 20:31    [12234989]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
iljy, а что не так с last_dt2,3,4?
last_dt1 - сортировка по неуникальному usr - какое повезло, такое и взялось LAST_VALUE среди равных по сортировке, нет?
13 мар 12, 00:29    [12235799]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
step_ks,

с 3 и 4 все так, а вот с 1 и 2 - по меньшей мере странно. Читаем:
BOL
Returns the last value in an ordered set of values in SQL Server 2012.
....
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

Т.е. если нет PARTITION BY, то все строки идут как одна группа и для них всех функция должна вернуть одно значение. При указании строки разбиваются на группы, но внутри группы функция все равно обязана возвращать одно значение. На примере FIRST_VALUE мы ровно это и видим. А вот для LAST_VALUE - увы. Читаем дальше:
BOL
rows_range_clause further limits the rows within the partition by specifying start and end points.

Т.е. с помощью RANGE я могу сузить группу. Но если я указываю группу от начала до конца (т.е. по идее то, что должно быть по умолчанию), я получаю правильный результат - last_dt4.
Мне кажется, произошло вот что: для этих они решили оптимизировать и сузить интервал по умолчанию, сделать его как RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW для FIRST. В общем-то логично - какой смысл ждать хвоста, если значение уже известно? Вот только на LAST они эту оптимизацию распространили зря, там окно должно быть другим: RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
13 мар 12, 01:46    [12235973]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
Про last_dt1 понимаю так:
LAST_VALUE(t) over(order by usr) last_dt1 - партишен по всей выборке, с usr = 2 несколько строк с разыми t и , вроде как, ничего удивильного, что может вернуться t из той, из которой повезет. Ну типа как update 1:M. Да, может было бы красивее, если бы возвращало одинаковое значение на каждую строку, но опять же - какое?

Теперь про last_dt2.
http://msdn.microsoft.com/en-us/library/ms189461%28v=sql.110%29.aspx
"OVER Clause (Transact-SQL)"
If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame.
В описании LAST_VALUE (http://msdn.microsoft.com/ru-ru/library/hh231517%28v=sql.110%29.aspx) есть пример, где тоже видно, что LAST_VALUE по-дефолту считает с начала до текущего значения. Вот оно как, оказывается.
13 мар 12, 09:16    [12236295]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
step_ks
Про last_dt1 понимаю так:
LAST_VALUE(t) over(order by usr) last_dt1 - партишен по всей выборке, с usr = 2 несколько строк с разыми t и , вроде как, ничего удивильного, что может вернуться t из той, из которой повезет. Ну типа как update 1:M. Да, может было бы красивее, если бы возвращало одинаковое значение на каждую строку, но опять же - какое?


Вернее так (в свете сказанного про last_dt2):
LAST_VALUE(t) over(order by usr) last_dt1 = партишен по всей выборке + RANGE UNBOUNDED PRECEDING AND CURRENT ROW. А среди UNBOUNDED PRECEDING как уж повезет среди нескольких строк с одинаковой сортировкой
13 мар 12, 09:24    [12236328]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy
kDnZP
iljy, ну дык сортировка по dt не определена, соответственно видимо равносильно взятию любого, то же самое для FIRST_VALUE, если я правильно все понимаю, т.к. MSDN по ним прочитал только 5 минут назад))))

Здраст приехали, чей-то не определена-то? ORDER BY dt стоит для 2, 3 и 4. А проблема, как мне показалось, в том, что по умолчанию RANGE берется какой-то странный, не вся группа.

step_ks
Про last_dt1 понимаю так:
LAST_VALUE(t) over(order by usr) last_dt1 - партишен по всей выборке, с usr = 2 несколько строк с разыми t и , вроде как, ничего удивильного, что может вернуться t из той, из которой повезет. Ну типа как update 1:M. Да, может было бы красивее, если бы возвращало одинаковое значение на каждую строку, но опять же - какое?

step_ks, спасибо, вы объяснили мою мысль человеческим языком. А то я вчера прямо после своей мысли спать завалился и проснулся поздней ночью.

iljy, я так понимаю вы ожидали поведения от LAST_VALUE() аналогичное
, FIRST_VALUE(t) over(order by usr, dt DESC) first_dt1
, FIRST_VALUE(t) over(partition by usr order by dt DESC) first_dt2

? Хотя проверить не могу, 2012 остался на домашнем компьютере.
13 мар 12, 09:37    [12236382]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
SQL Server 2012 RTM Sample Databases are available!!!
Adventure Works for SQL Server 2012
13 мар 12, 10:31    [12236708]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
step_ks,

да при чем тут одинаковые записи в порядке сортировки?? Оно по условию должно выдавать ОДИНАКОВОЕ ЗНАЧЕНИЕ ДЛЯ ВСЕЙ ГРУППЫ, а разбиение на группы задается предложением PARTITION BY, а никак не количеством одинаковых строк (понятно, что из них может быть взято произвольное значение, проблема-то не в том, что оно произвольное, а в том, что оно разное для разных строк!). Более того, когда я явно указываю, что для определения значения надо использовать всю группу, поведение получается вполне себе ожидаемое.

kDnZP,

а вы как-то по другому трактуете понятие "первая" и "последняя запись в группе"? Повторюсь - проблема не в произвольности выбора среди равных.
13 мар 12, 13:08    [12238245]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
kDnZP
, FIRST_VALUE(t) over(order by usr, dt DESC) first_dt1


Только здесь dt не нужно, просто order by usr desc.
13 мар 12, 13:11    [12238279]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

я так понимаю, ответ тут:

http://connect.microsoft.com/SQLServer/feedback/details/679668/last-value-does-not-return-expected-values#details

Posted via ActualForum NNTP Server 1.5

13 мар 12, 13:18    [12238353]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
Кстати,

Discontinued Database Engine Functionality in SQL Server 2012

CategoryDiscontinued featureReplacement
MetadataDATABASEPROPERTYDATABASEPROPERTYEX


Однако ж


select @@version
select DATABASEPROPERTY('tempdb','IsAnsiNullsEnabled')


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 row(s) affected)


-----------
0

(1 row(s) affected)
13 мар 12, 13:43    [12238654]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy
kDnZP
, FIRST_VALUE(t) over(order by usr, dt DESC) first_dt1


Только здесь dt не нужно, просто order by usr desc.

Не уверен. Но проверить, пока не дойду домой возможности нет. Так что погляжу только вечером.
13 мар 12, 13:50    [12238754]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
iljy
step_ks,

да при чем тут одинаковые записи в порядке сортировки??

При том, что по-дефолту - RANGE

iljy
Оно по условию должно выдавать ОДИНАКОВОЕ ЗНАЧЕНИЕ ДЛЯ ВСЕЙ ГРУППЫ, а разбиение на группы задается предложением PARTITION BY, а никак не количеством одинаковых строк (понятно, что из них может быть взято произвольное значение, проблема-то не в том, что оно произвольное, а в том, что оно разное для разных строк!). Более того, когда я явно указываю, что для определения значения надо использовать всю группу, поведение получается вполне себе ожидаемое.

kDnZP,

а вы как-то по другому трактуете понятие "первая" и "последняя запись в группе"? Повторюсь - проблема не в произвольности выбора среди равных.

Может, я чего-то так и не понял. Какой, по вашему представлению, был бы правильный результат в last_dt1? все 1 или все 0, чтоли?
13 мар 12, 13:51    [12238767]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Если я понял основную мысль от iljy.

step_ks, я тоже думаю что это вводит в заблуждение.
Понятно, когда RANGE для агрегатных функций (Sum). А так получается в 95% случае мы должны дописывать пару лишних слов, чтобы прекрыть неудобные Default параметры/настройки.

Разве что не понял где это именно это проблема стандарта (ANSI SQL). В том что там так определено или наоборот не оговорено.

Если надо получить относительно текущей последовательности (а не всего блока), скорее будут пользоваться LAG/LEAD чем First/Last.
Тут и так функции дублируют друг друга. Я бы понял если бы было удобно использовать их Default-ы.
13 мар 12, 14:12    [12238967]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
step_ks
iljy
step_ks,

да при чем тут одинаковые записи в порядке сортировки??

При том, что по-дефолту - RANGE

Что - приборы?

step_ks
iljy
Оно по условию должно выдавать ОДИНАКОВОЕ ЗНАЧЕНИЕ ДЛЯ ВСЕЙ ГРУППЫ, а разбиение на группы задается предложением PARTITION BY, а никак не количеством одинаковых строк (понятно, что из них может быть взято произвольное значение, проблема-то не в том, что оно произвольное, а в том, что оно разное для разных строк!). Более того, когда я явно указываю, что для определения значения надо использовать всю группу, поведение получается вполне себе ожидаемое.

kDnZP,

а вы как-то по другому трактуете понятие "первая" и "последняя запись в группе"? Повторюсь - проблема не в произвольности выбора среди равных.

Может, я чего-то так и не понял. Какой, по вашему представлению, был бы правильный результат в last_dt1? все 1 или все 0, чтоли?

Естественно. Группа-то одна, с какой стати значения разные?
13 мар 12, 14:21    [12239085]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iljy, я понял. Мы о разных вещах говорим))). Т.е. поведение LAST_VALUE меня не удивило, а вот ваша уверенность, что от dt не зависит результат - вызвало протест, т.к. что-то подсказывает, что будут побочные эффекты... Вот тот скрипт, что буду проверять:

if object_id('tempdb..#t') is not null drop table #t
go
create table #t(usr int, dt datetime, t int)
insert #t values
(1, '20120308 10:00:00',	 1),
(1, '20120308 08:00:00',	 2),
(1, '20120308 09:00:00',	 3),
(1, '20120308 17:00:00',	 4),
(1, '20120308 17:02:00',	 5),
(2, '20120308 08:00:00',	 6),
(2, '20120308 17:00:00',	 7),
(2, '20120308 17:01:00',	 8),
(2, '20120308 17:03:00',	 9)

;with cte as(
	select *
		, FIRST_VALUE(t) over(order by usr) first_dt1
		, LAST_VALUE(t) over(order by usr) last_dt1
		--, ROW_NUMBER() over(order by usr,dt DESC)
	from #t
	
)
select * from cte
13 мар 12, 14:29    [12239207]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Разве что не понял где это именно это проблема стандарта (ANSI SQL). В том что там так определено или наоборот не оговорено.

я так понимаю, что именно что так определено:

If WD has no window framing clause, then
Case:
i) If the window ordering clause of WD is not present, then WF is the window partition of R.
ii) Otherwise, WF consists of all rows of the partition of R that precede R or are peers of R in the
window ordering of the window partition defined by the window ordering clause.


Posted via ActualForum NNTP Server 1.5

13 мар 12, 14:35    [12239272]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
iljy
Естественно. Группа-то одна, с какой стати значения разные?
А если бы были одинаковые, то никто бы не спросил, почему именно такие, а не другие из существующих?
13 мар 12, 14:36    [12239293]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior
Если я понял основную мысль от iljy.

step_ks, я тоже думаю что это вводит в заблуждение.
Понятно, когда RANGE для агрегатных функций (Sum). А так получается в 95% случае мы должны дописывать пару лишних слов, чтобы прекрыть неудобные Default параметры/настройки.

Разве что не понял где это именно это проблема стандарта (ANSI SQL). В том что там так определено или наоборот не оговорено.

Если надо получить относительно текущей последовательности (а не всего блока), скорее будут пользоваться LAG/LEAD чем First/Last.
Тут и так функции дублируют друг друга. Я бы понял если бы было удобно использовать их Default-ы.

По умолчанию для всех этих функций должна использоваться вся группа. Я уже цитировал
БОЛ
rows_range_clause further limits the rows within the partition by specifying start and end points.

И хотя нигде явно не указано, что применяется по умолчанию, по всем логическим законам по умолчанию группа никак не ограничивается.
Далее, для функции FIRST_VALUE совершенно очевидно, что использование UNBOUNDED FOLLOWING бессмысленно, так что вполне допустимо использовать CURRENT ROW в качестве верхней границы, что, видимо, и сделано. А вот для LAST_VALUE это не годится, но, видимо, тоже было сделано. Интересно проверить для других функций
Что касается заменяемости функци FIRST/LAST и LAG/LEAD, то разница все-таки есть:
if object_id('tempdb..#t') is not null drop table #t
go
create table #t(usr int, dt datetime, t int)
insert #t values
(1, '20120308 08:00:00',	 0),
(1, '20120308 09:00:00',	 1),
(1, '20120308 10:00:00',	 0),
(1, '20120308 17:00:00',	 1),
(1, '20120308 17:02:00',	 1),
(2, '20120308 08:00:00',	 1),
(2, '20120308 17:00:00',	 1),
(2, '20120308 17:01:00',	 1),
(2, '20120308 17:03:00',	 0)

select *
	, FIRST_VALUE(t) over(partition by usr order by dt rows 2 preceding) first_dt
	, LAG(t,2) over(partition by usr order by dt) lag_dt
from #t


usrdttfirst_dt2first_dt2
12012-03-08 08:00:00.00000NULL
12012-03-08 09:00:00.00010NULL
12012-03-08 10:00:00.000000
12012-03-08 17:00:00.000111
12012-03-08 17:02:00.000100
22012-03-08 08:00:00.00011NULL
22012-03-08 17:00:00.00011NULL
22012-03-08 17:01:00.000111
22012-03-08 17:03:00.000011
13 мар 12, 14:45    [12239419]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
step_ks
iljy
Естественно. Группа-то одна, с какой стати значения разные?
А если бы были одинаковые, то никто бы не спросил, почему именно такие, а не другие из существующих?

я бы не спросил. Ни у кого же не вызывает удивления, что TOP 1 с неуникальными значеними в порядке может что угодно вернуть.
13 мар 12, 14:49    [12239471]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

вот у "соседей" еще нашел:

http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions001.htm#i81407
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.


логично, или не логично, но исключений для конкретно LAST_VALUE нет, вроде как.

Posted via ActualForum NNTP Server 1.5

13 мар 12, 14:51    [12239493]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior,

хотя не, вру! по идее как раз по умолчанию RANGE BETWEEN UNBOUNDED PRECIDING AND CURRENT ROW, точно! Только для LAST_VALUE это смысла не имеет...
13 мар 12, 14:53    [12239522]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2012 RTM  [new]
step_ks
Member

Откуда:
Сообщений: 936
iljy
step_ks
пропущено...
А если бы были одинаковые, то никто бы не спросил, почему именно такие, а не другие из существующих?

я бы не спросил. Ни у кого же не вызывает удивления, что TOP 1 с неуникальными значеними в порядке может что угодно вернуть.

Согласен, было бы красивее. Но пользы было бы особо не больше. Как от update 1:M с разными значениями в М.
13 мар 12, 14:57    [12239560]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить