Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Доброго дня.

Есть таблица:
declare @t table (id int IDENTITY (1,1), [date] date, status bit)

insert into @t (date, status)
	values ('20100101',1),
		('20120101',0),
		('20130101',1),
		('20140101',0)


Необходимо получить все записи из этой таблицы, и "пометить" актуальную на сегодняшний день запись в отдельном поле.
Актуальной считается запись, имеющаяя МАКСИМАЛЬНУЮ дату, МЕНЬШЕ текущей.

Реализовал вот такой вариант:
select id, date, status,
cast(case when Date<=cast(getdate() as date) and (min(Date) over (order by Date rows between 1 FOLLOWING and 1 FOLLOWING) > cast (getdate() as date) or min(Date) over (order by Date rows between 1 FOLLOWING and 1 FOLLOWING) is null )
				then 1
				else 0
			end as bit) as Current_Status
	from @t


Подскажите, насколько оптимален данный вариант и есть ли варианты более оптимальные?
28 мар 13, 09:01    [14105319]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
declare @now date=getdate()
declare @date date
select @date=max(date) from @t where date<@now
select *, case date when @date then 1 else 0 end curr
  from @t
28 мар 13, 09:10    [14105347]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
with x as
(
 select
  *,
  row_number() over (order by case when [date] <= getdate() then datediff(s, [date], getdate()) else 2147483647 end) as rn
 from
  @t
)
select
 id, [date], status,
 case when rn = 1 then 1 else 0 end as flag
from
 x;

set statistics io off;
28 мар 13, 09:48    [14105470]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
invm

Только, коли уж тип данных date то не секунду брать для разности а часы, а то переполнение легко словить.
28 мар 13, 09:59    [14105524]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Ну или дни
28 мар 13, 10:00    [14105537]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
ambarka_max
Ну или дни
Ну да, дни надо. Не обратил внимания на тип.
28 мар 13, 10:33    [14105730]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

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

Ваш запрос не корректно обрабатывает ситуацию, когда есть записи с датой ПОСЛЕ текущей и нет с дато ДО. т.е. Актуальной записи НЕТ. А у вас есть:

declare @t table (id int IDENTITY (1,1), [date] date, status bit)

insert into @t (date, status)
--	values ('20100101',1), ('20120101', 0), ('20130101',1), 
values ('20140101',0)

Ожидаю:

id date status Current_Status
1 2014-01-01 0 0


имею:
id date status flag
1 2014-01-01 0 1
28 мар 13, 11:07    [14105960]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Galyamov Rinat
есть ли варианты более оптимальные?


К сообщению приложен файл. Размер - 6Kb
28 мар 13, 11:21    [14106056]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Galyamov Rinat,

Эта беда легко поправима:
with x as
(
 select
  *,
  row_number() over (order by case when [date] <= getdate() then datediff(day, [date], getdate()) else 2147483647 end) -
  case when [date] <= getdate() then 1 else 0 end as rn
 from
  @t
)
select
 id, [date], status,
 case when rn = 0 then 1 else 0 end as flag
from
 x;
28 мар 13, 11:23    [14106070]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Cygapb-007,

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


PS Чем вытащил такую стату? Как правильно сравнивать запросы по производительности?
28 мар 13, 11:30    [14106124]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
SQL Sentury Plan Explorer
28 мар 13, 11:33    [14106139]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Galyamov Rinat
PS Чем вытащил такую стату? Как правильно сравнивать запросы по производительности?

SQL Profiler тулза называется,идет в комплекте поставки с самими SQL Server
28 мар 13, 11:34    [14106150]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Galyamov Rinat,

В вашем варианте всегда будет Window spool. В варианте Cygapb-007 два прохода по таблице.
В моем один проход.

Все это видно в планах (set statistics xml on) и статистике IO (set statistics io on).
28 мар 13, 11:36    [14106163]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
invm, при все уважении, аналогичные запросы на большом тестовом примере:
declare @val int=1051
declare @current int

select @current=MAX(number) from master..spt_values where number<@val
select *,case number when @current then 1 else 0 end from master..spt_values

;with x as
(
 select
  *,
  row_number() over (order by case when number <= @val then @val-number else 2147483647 end) -
  case when number <= @val then 1 else 0 end as rn
 from
  master..spt_values
)
select
 *,
 case when rn = 0 then 1 else 0 end as flag
from
 x


К сообщению приложен файл. Размер - 5Kb
28 мар 13, 11:47    [14106226]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Cygapb-007,

Эта таблица с попугаями ни о чем не говорит. Тем более, что показываете вы в ней ожидаемые значения, а не реальные. Вот результаты statistics io
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2515 row(s) affected)
Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2515 row(s) affected)
Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Первые два результата -- ваш запрос, последний -- мой. У вас присутствует лишний скан таблицы.
28 мар 13, 11:55    [14106268]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Ну и в добавок, автор не указал какой должен быть результат, если будут несколько значений, удовлетворяющих условию.
28 мар 13, 12:05    [14106327]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
invm
Cygapb-007,

Эта таблица с попугаями ни о чем не говорит. Тем более, что показываете вы в ней ожидаемые значения, а не реальные. Вот результаты statistics io
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2515 row(s) affected)
Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2515 row(s) affected)
Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Первые два результата -- ваш запрос, последний -- мой. У вас присутствует лишний скан таблицы.
Ыыы... Речь о скорости выполнения или о количестве считываний?:) С датчиками скорости получаем:
+ То же, с добавлением таймера
declare @val int=1051

declare @Timing table (id int identity, timer datetime)
set statistics xml on
set statistics io on

insert @Timing values (GETDATE())
declare @current int
select @current=MAX(number) from master..spt_values where number<@val
select *,case number when @current then 1 else 0 end from master..spt_values

insert @Timing values (GETDATE())

;with x as
(
 select
  *,
  row_number() over (order by case when number <= @val then @val-number else 2147483647 end) -
  case when number <= @val then 1 else 0 end as rn
 from
  master..spt_values
)
select
 *,
 case when rn = 0 then 1 else 0 end as flag
from
 x

insert @Timing values (GETDATE())

set statistics io off
set statistics xml off


select t.id, DATEDIFF(MILLISECOND, t.timer, n.timer)ms
from @Timing t
join @Timing n on n.id=t.id+1
idms
136
2530
Хотя да, считываний таблицы у меня действительно 2...
28 мар 13, 12:10    [14106360]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

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

В данном конкретном случае имеется уникальный индекс. Таким образом двух одинаковых дат быть не может.

При выполнении вашего однопроходного и Cygapb-007 "двухпроходного" запросов в одном пакете - ваш запрос имеет бОльшую стоимость по отношению к пакету, чем два запроса...
28 мар 13, 12:17    [14106410]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Galyamov Rinat,
При наличии индекса по [date], вариант от Cygapb-007 будет быстрее.
28 мар 13, 12:48    [14106611]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
zozozozozo
Guest
а так?

select top(1) *
from @t m
where m.[date] <= getdate()
order by [date] desc;
28 мар 13, 14:46    [14107553]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
zozozozozo
Guest
пардон. не проникся заданием как следует
28 мар 13, 14:55    [14107639]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Cygapb-007,

В итоге, чисто для красоты, объединил два запроса в один:
Вместо
declare @now date=getdate()
declare @date date
select @date=max(date) from @t where date<@now
select *, case date when @date then 1 else 0 end curr
  from @t


Сделал:
declare @now date=getdate()

select *, case date when t1.Cur_Date then 1 else 0 end curr
  from @t join (select max(date) as Cur_Date from @t where date<@now) t1


По ресурсам и производительности, вроде бы, показывают одинаковые результаты.
29 мар 13, 11:31    [14111689]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
там наверное CROSS JOIN...
29 мар 13, 11:41    [14111751]     Ответить | Цитировать Сообщить модератору
 Re: Выбрать все записи и пометить актуальную по дате запись  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Cygapb-007,

Да, конечно.
29 мар 13, 11:53    [14111837]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить