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

Откуда:
Сообщений: 1
Уважаемые форумчане, помогите разобраться с таким вопросом...

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

Нужно выбрать по конкретному пользователю данные за определённый промежуток времени (например за неделю) так, чтобы каждая запись была за конкретный день. И если данных в таблице на текущий день нет, то брать предыдущее значение. Всё это хозяйство нужно для вывода графика.

В таблице на запрос
declare @UserId int = 53652
declare @DF date = '20120426'
declare @DT date = '20120502'

select * from UserRating where UserId = @UserId and RatingDate between @DF and @DT

возвращает записи


Id UserId UserName Position PositionDelta Rating RatingDelta News NewsDelta Comments CommentsDelta Friends FriendsDelta LastVisitDate RatingDate SignupDate
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------- ----------- ----------- ----------- ----------- ----------- ------------- ----------- ------------ ----------------------- ----------------------- -----------------------
86725 53652 Трям 74 0 15734 5 15 0 3379 0 83 0 2012-04-27 09:51:00.000 2012-04-27 09:57:06.700 2008-03-25 00:00:00.000
89057 53652 Трям 74 0 15735 1 15 0 3382 0 83 0 2012-04-27 23:59:00.000 2012-04-28 02:15:02.080 2008-03-25 00:00:00.000
91164 53652 Трям 74 0 15736 1 15 0 3382 0 83 0 2012-04-28 21:39:00.000 2012-04-29 02:15:01.580 2008-03-25 00:00:00.000
94929 53652 Трям 74 0 15733 -3 15 0 3382 0 83 0 2012-04-30 18:42:00.000 2012-05-01 02:15:01.117 2008-03-25 00:00:00.000

(4 row(s) affected)



Я задачу решил, но не оптимально.

declare @MinDate date 
declare @MinDateLastNotNull date
declare @ur as table(
	[Id] [int]  NOT NULL,
	[UserId] [int] NOT NULL,
	[UserName] [nvarchar](250) NULL,
	[Position] [int] NULL,
	[PositionDelta] [int] NULL,
	[Rating] [int] NULL,
	[RatingDelta] [int] NULL,
	[News] [int] NULL,
	[NewsDelta] [int] NULL,
	[Comments] [int] NULL,
	[CommentsDelta] [int] NULL,
	[Friends] [int] NULL,
	[FriendsDelta] [int] NULL,
	[LastVisitDate] [datetime] NOT NULL,
	[RatingDate] [datetime] NULL,
	[SignupDate] [datetime] NULL
	)

-- собираю даты в цикле, добавляя к @DF по дню
while(@DF < @DT)
begin
	--получаю дату на которую есть рейтинг в данном периоде
	select @MinDate = MIN(RatingDate) 
	from dbo.UserRating 
	where UserId = @UserId and RatingDate between @DF and @DT
	
	--запоминаю дату, в случае, если рейтинг в этом периоде существует
	if(@MinDate is not null)
	begin
		set @MinDateLastNotNull = @MinDate;
	end
	
	if exists(select * from UserRating where UserId = @UserId and cast(RatingDate as date) = @DF)
		begin
			--на текущий день есть запись. Вставляю во временную таблицу 
			insert into @ur
			select * from UserRating where UserId = @UserId and cast(RatingDate as date) = @DF
		end
	else
		begin
			--за текущий день записей нет, вставляю предыдущую
			insert into @ur 
			select 
			[Id]
			,[UserId]
			,[UserName]
			,[Position]
			,[PositionDelta]
			,[Rating]
			,[RatingDelta]
			,[News]
			,[NewsDelta]
			,[Comments]
			,[CommentsDelta]
			,[Friends]
			,[FriendsDelta]
			,[LastVisitDate]
			,@DF as RatingDate
			,[SignupDate]
			 from UserRating where UserId = @UserId and cast(RatingDate as date) = @MinDateLastNotNull
		end
	--инкремент даты
	set @DF = (select dateadd(d, 1, @DF))
end
--финальная выборка
select * from @ur


Результат:

Id UserId UserName Position PositionDelta Rating RatingDelta News NewsDelta Comments CommentsDelta Friends FriendsDelta LastVisitDate RatingDate SignupDate
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------- ----------- ----------- ----------- ----------- ----------- ------------- ----------- ------------ ----------------------- ----------------------- -----------------------

86725 53652 Трям 74 0 15734 5 15 0 3379 0 83 0 2012-04-27 09:51:00.000 2012-04-26 00:00:00.000 2008-03-25 00:00:00.000

86725 53652 Трям 74 0 15734 5 15 0 3379 0 83 0 2012-04-27 09:51:00.000 2012-04-27 09:57:06.700 2008-03-25 00:00:00.000
89057 53652 Трям 74 0 15735 1 15 0 3382 0 83 0 2012-04-27 23:59:00.000 2012-04-28 02:15:02.080 2008-03-25 00:00:00.000
91164 53652 Трям 74 0 15736 1 15 0 3382 0 83 0 2012-04-28 21:39:00.000 2012-04-29 02:15:01.580 2008-03-25 00:00:00.000

94929 53652 Трям 74 0 15733 -3 15 0 3382 0 83 0 2012-04-30 18:42:00.000 2012-04-30 00:00:00.000 2008-03-25 00:00:00.000

94929 53652 Трям 74 0 15733 -3 15 0 3382 0 83 0 2012-04-30 18:42:00.000 2012-05-01 02:15:01.117 2008-03-25 00:00:00.000

(6 row(s) affected)


Есть ли возможность избавиться от временной таблицы и insert-ов? Можно ли решить эту задачу select-oм? Динамическим sql-ем решать бы не хотелось. Есть мысли?
3 май 12, 10:58    [12499446]     Ответить | Цитировать Сообщить модератору
 Re: Ежедневный рейтинг  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Таблица-календарь + outer apply.
3 май 12, 11:17    [12499575]     Ответить | Цитировать Сообщить модератору
 Re: Ежедневный рейтинг  [new]
Lavrinenko
Member

Откуда: Москва
Сообщений: 7
invm,

Календарь прикрутил, дело хорошее, а outer apply выдаёт нулл, там где должна быть запись. т.е.

declare @UserId int = 53652
declare @DF date = '20120426'
declare @DT date = '20120502'

select * from 
Calendar c outer apply (
							select * 
							from UserRating r 
							where UserId=@UserId and cast(RatingDate as date) = c.RatingDate
						) ap
where c.RatingDate between @DF and @DT


выдаёт


RatingDate Id UserId UserName Position PositionDelta Rating RatingDelta News NewsDelta Comments CommentsDelta Friends FriendsDelta LastVisitDate RatingDate SignupDate
---------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------- ----------- ----------- ----------- ----------- ----------- ------------- ----------- ------------ ----------------------- ----------------------- -----------------------
2012-04-26 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2012-04-27 86725 53652 Трям 74 0 15734 5 15 0 3379 0 83 0 2012-04-27 09:51:00.000 2012-04-27 09:57:06.700 2008-03-25 00:00:00.000
2012-04-28 89057 53652 Трям 74 0 15735 1 15 0 3382 0 83 0 2012-04-27 23:59:00.000 2012-04-28 02:15:02.080 2008-03-25 00:00:00.000
2012-04-29 91164 53652 Трям 74 0 15736 1 15 0 3382 0 83 0 2012-04-28 21:39:00.000 2012-04-29 02:15:01.580 2008-03-25 00:00:00.000
2012-04-30 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2012-05-01 94929 53652 Трям 74 0 15733 -3 15 0 3382 0 83 0 2012-04-30 18:42:00.000 2012-05-01 02:15:01.117 2008-03-25 00:00:00.000
2012-05-02 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

(7 row(s) affected)

а это не то, что нужно получить.

В результирующей выборке вместо нула должна стоять последняя запись

Попробовал поставить условие в outer apply ( if exists... - ругается. Incorrect syntax near the keyword 'if'.
3 май 12, 12:14    [12500093]     Ответить | Цитировать Сообщить модератору
 Re: Ежедневный рейтинг  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
declare @UserId int = 53652
declare @DF date = '20120426'
declare @DT date = '20120502'

select * from 
Calendar c outer apply (
							select top (1) r.* 
							from UserRating r 
							where r.UserId=@UserId and r.RatingDate <= c.RatingDate
							order by r.RatingDate desc
						) ap
where c.RatingDate between @DF and @DT
3 май 12, 12:29    [12500262]     Ответить | Цитировать Сообщить модератору
 Re: Ежедневный рейтинг  [new]
Lavrinenko
Member

Откуда: Москва
Сообщений: 7
invm,

Да, так гораздо лучше, спасибо за совет.

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

Спасибо.
3 май 12, 14:16    [12501345]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить