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

Откуда:
Сообщений: 311
Добрый день, ALL!
Есть таблица со значениями температур в разных городах на определенную дату. за день показания температуры могут меняться, что отражается новой строчкой в таблице. нужно найти последнюю температуру для _каждого_ города из таблицы к определенному моменту времени.

скрипты
if OBJECT_ID('tempdb..#t') is not null
	drop table #t
create table #t(townID varchar(10), dat datetime, temp decimal(10,2))
create clustered index ix on #t (dat,townID)

insert into #t(townID,dat,temp)
select *
from 
(   values
('msk','20110501 10:05',16),
('msk','20110502 11:05',18),
('spb','20110505 10:35',13),
('spb','20110501 12:05',13),
('nsb','20110501 10:05',15),
('nsb','20110506 17:15',11),
('msk','20110501 12:05',10),
('nov','20110507 13:05',6)
)  t(townID,dat,temp)


результат который хотелось бы иметь для @dat = '20110505' (то есть окончание 4го мая)
townID temp
msk 18.00
nov NULL
nsb 15.00
spb 13.00


вариант для sql server 2005 и далее у меня назрел такой
 declare @dat datetime = '20110505'
select distinct t1.townID,t2.temp
from #t t1
left join (
select top 1 with ties *
from #t t
where t.dat<@dat
order by ROW_NUMBER() over(partition by t.townID order by t.dat desc )) t2 on t2.townID=t1.townID

а вот для 2000 сервера приходит в голову только коррелированный подзапрос
 declare @dat datetime = '20110505'
select distinct t.townID,(select top 1 t1.temp from #t t1 where t1.townID=t.townID and t1.dat<@dat)
from #t t

но при больших количествах данных он мне очень не нравится (правильно ли я понимаю что подзапрос выполняется для каждой строки исходной таблицы?)

прошу помощи в реализации данной задачи для 2000 сервера, ну и более красивые варианты для старших серверов приветствуются!


+ вариант тестового наполнения с бОльшим количеством данных:
if OBJECT_ID('tempdb..#t') is not null
	drop table #t
create table #t(townID varchar(10), dat datetime, temp decimal(10,2))
create clustered index ix on #t (dat,townID)

insert into #t(townID,dat,temp)
select 
	case 
		when RAND(CHECKSUM(NEWID()))*10 < 3 then 'msk' 
		when RAND(CHECKSUM(NEWID()))*10 between 3 and 7 then 'spb' 
		when RAND(CHECKSUM(NEWID()))*10 between 7 and 10 then 'nsb' 
		else 'nov' 
	end		
	,DATEADD(MI,s.number*3,'20110501') 
	,
	RAND(CHECKSUM(NEWID()))*50
from master..spt_values  s ,master..spt_values
where s.type='p'
11 окт 11, 13:02    [11419220]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
iljy
Member

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

я не понял, у вас индекс неуникален чтоли? С уникальным делается примерно так:
select t1.townID,t1.temp
from #t t1
join (
select townID, MAX(dat) dat
from #t t
where dat<@dat
group by townID) t2 on t2.townID=t1.townID and t2.dat = t1.dat
11 окт 11, 13:18    [11419433]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
declare @dat datetime = '20110505'
select
	t.townID
	, t.dat
	, t.temp
from	
	#t t
inner join (
	select
		t1.townID
		, MAX(t1.dat) maxdate
	from 
		#t t1
	where
		t1.dat < @dat
	group by
		t1.townID
) md on md.townID = t.townID and md.maxdate = t.dat
11 окт 11, 13:21    [11419476]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
bacalavr
Member

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

индекс уникален

в вашем варианте не покажется строка с
townID temp
'nov' NULL


так как данные с этим городом не попадают под фильтр с датой

а нужно отобразить данные по _всем_ городам
11 окт 11, 13:29    [11419571]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
bacalavr
Member

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

аналогично ответу выше
11 окт 11, 13:29    [11419580]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
iljy
Member

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

ну так замените INNER JOIN на OUTER.
11 окт 11, 13:30    [11419587]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
bacalavr
Member

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

так как строчек на один день и для одного города может быть много то я не вижу способа отличить строки которые после джойна дали нулл просто потому что данных нет и потому что не подходят по дате
%)
возможно, просто пятница на дворе и я туплю (
в общем, если не сложно, подскажите , как "ну так замените INNER JOIN на OUTER."
11 окт 11, 13:38    [11419662]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
iljy
Member

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

ну да, еще надо условие внутрь MAX убрать.
select t2.townID, t1.temp
from #t t1
right join (
select townID, MAX(case when dat<@dat then dat end) dat
from #t t
group by all townID) t2 on t2.townID=t1.townID and t2.dat = t1.dat
11 окт 11, 13:40    [11419672]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
iljy
Member

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

но вообще для таких случаев полезно иметь таблицу городов отдельную, сильно может эффективность поднять. Да и кореллированный подзапрос в этом случае отнюдь не так страшен.
11 окт 11, 13:41    [11419682]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
bacalavr
Member

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

Спасибо за идею убрать условие в агрегат!

будь таблица городов в наличии, не было бы этого топика)

а варианты для старших серверов более красивые можете предложить?
11 окт 11, 13:44    [11419706]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
iljy
Member

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

так создайте, она вам в любом случае пригодится, некошерно это - символьные строки ключами делать.

Для старших серверов можно что-то такого типа
select top 1 with ties townID, temp
from (
	select townID, case when dat < @dat then dat end dat,
		case when dat < @dat then temp end temp
	from #t
)t
order by ROW_NUMBER() over(partition by townID order by dat desc)
, но это опять же не слишком эффективно.
11 окт 11, 13:56    [11419774]     Ответить | Цитировать Сообщить модератору
 Re: найти последние данные на определенную дату, sql 2000  [new]
bacalavr
Member

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

Спасибо!
11 окт 11, 14:55    [11420413]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить