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

Откуда: Украина
Сообщений: 81
Всем доброго времени суток, вот столкнулся вроде с простой задачей, но как то пока не получается ее решить, ищу совета.
MSSQL Server 2008 R2.

Суть вопроса - есть 3 таблицы:
1) таблица 1 хранит информацию о человеке (о том что он начал проходить тест)
2) таблица 2 хранит результаты человека (несколько результатов в сыром формате) - но там не понятно, что это за результаты - мы только можем знать, что они относятся к конкретной записи таблицы 1
3) таблица 3 хранит распарсенные результаты = с именем метрики и датой их заполенения. Поскольку человек может пытаться пройти тест несколько раз - то результаты в 3 таблице могут содержать несколько метрик с одним кодом для одной записи из таблицы 1.

данных не сильно много пока - но на одну запись из таблицы 1 приходится порядка 1000 строк в таблице 3.

Стоит задача - для каждой записи таблицы 1 получить из таблицы 3 только по одной метрике каждого типа, отсортированные по дате создания (только последние считаются валидными), остальные не в счет.

Собственно уперся в запрос.

Пока написал запрос - который выведет все результаты из таблицы 3 для записи таблицы 1, отсортированные по имени метрики и по дате - как взять из этой таблицы только первые уникальные записи (по коду метрики - пока ума не приложу).

Запрос имеет след вид
-- 2 : Original Query (last active item is the first one)
USE [Test]
SELECT       
      , c1.ParticipantApplicationID -- Participant Id Relation
      , c3.MetricCode -- metric code item 
      , c3.MetricValue -- actual metric value
      , c3.CreatedDate as MetricDate
FROM 
      dbo.VersionConfigParticipantApp c1 
            inner join dbo.VersionConfigParticipantAppResponse c2 on (c1.Id = c2.VersionConfigParticipantAppID)
            inner join dbo.VersionConfigParticipantAppResponseMetric c3 on (c2.Id = c3.ResponseInfo_Id)  
 WHERE  
	c1.ParticipantApplicationID IN ('2121104') -- subset of the participants to get information related to
	AND c1.[AssessmentStatus] = 4 -- 4: started, 5: completed
ORDER BY 	
	c1.ParticipantApplicationID, c3.MetricCode, c3.CreatedDate DESC


пример результата запроса:
2121104	Assmnt_Simulation_A	1	2013-09-23 12:04:56.530
2121104	Assmnt_Simulation_A	1	2013-09-19 09:58:26.393
2121104	Assmnt_Simulation_A	0	2013-09-18 15:44:43.537
2121104	C7_Greet_A      	2122	        2013-09-23 12:05:00.507
2121104	C7_Greet_A 	2274	        2013-09-19 09:58:30.543
2121104	C7_Greet_A	        342272	2013-09-18 15:50:27.717
2121104	C7_Greet_B	        0	        2013-09-23 12:06:06.530
2121104	C7_Greet_B	        0	        2013-09-19 09:59:48.780
2121104	C7_Greet_B	        0	        2013-09-18 11:23:59.710


то есть в идеале мне нужны в результате записи 1, 4, 7 ....

Что думается сделать -
1) попытаться взять уникальные записи метрик из таблицы 3 и потом включить их в subquery для запроса - но там нужно быть уверенным что все метрики будут относиться к конкретной записи таблицы 1, то есть будет масло мясляное в выборке
2) пробежаться по выборке данного запроса и включить результаты в таблицу (временную возможно) - проверяя что в таблице нет еще метрики с таким именем (наверное напрашивается курсор)
... но чувствую, что можно сделать красивее и оптимальнее - вот только надо вникнуть.

Заранее спасибо за советы ).

будьте проще, будет легче :)
24 сен 13, 16:48    [14878622]     Ответить | Цитировать Сообщить модератору
 Re: выбор последней записи в таблице с одинковым именем.  [new]
хмхм
Guest
alexxUK,

чего ж проще то?

declare @t table(person nvarchar(100), modifiedon datetime, val1 int, val2 int)


insert into @t
select 'Вася', '2012-02-02 08:30:00', 10, 11
union 
select 'Вася', '2012-02-02 08:32:00', 11, 90
union
select 'Петя', '2012-02-04 09:30:00', 10, 89


select t.* from @t t 
inner join(
select person, max(modifiedon) as d from @t
group by person)results on results.person = t.person and results.d = t.modifiedon
24 сен 13, 17:33    [14878933]     Ответить | Цитировать Сообщить модератору
 Re: выбор последней записи в таблице с одинковым именем.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
alexxUK,

select top (1) with ties
..
order by row_number() over (partition by c1.ParticipantApplicationID order by c3.CreatedDate desc)


select
...
from
cross apply(select top (1) c3.CreatedDate from dbo.VersionConfigParticipantAppResponseMetric c3 where  c2.Id = c3.ResponseInfo_Id) metricDate(value)
24 сен 13, 17:46    [14879017]     Ответить | Цитировать Сообщить модератору
 Re: выбор последней записи в таблице с одинковым именем.  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
спасибо всем откликнувшимся, попробовал варианты пердложенные, или я не понял синтаксис, или еще чего - но мне возврашало по одной записи только :( , в итоге пока сделал через курсор (не оптимально возможно, но вроде работает)

вот что получилось итого на данный момент (потом перенесу в функцию которая по id + статуcу вернет значения)
SET NOCOUNT ON;
-- params
declare @participantAppValue bigint, 
	@assessmentStatus smallint
	
set @participantAppValue = 2121104
set @assessmentStatus = 4

-- output 
declare @participantAppId bigint, 
		@metricCode nvarchar(30),
		@metricValue nvarchar(30),
		@dateCreated datetime	
		
-- result table		
declare @t table(participantAppId bigint, metricCode nvarchar(30), metricValue nvarchar(30), dateCreated datetime)

-- cursor declaration
DECLARE metric_cursor CURSOR FOR 
SELECT
      c1.ParticipantApplicationID -- Participant Id Relation            
      , c3.MetricCode -- metric code item 
      , c3.MetricValue -- actual metric value
      , c3.CreatedDate as MetricDate -- date of metric
FROM 
      dbo.VersionConfigParticipantApp c1 
            inner join dbo.VersionConfigParticipantAppResponse c2 on (c1.Id = c2.VersionConfigParticipantAppID)
            inner join dbo.VersionConfigParticipantAppResponseMetric c3 on (c2.Id = c3.ResponseInfo_Id)  
 WHERE  
	c1.ParticipantApplicationID = @participantAppValue -- participant to get information related to
	AND c1.AssessmentStatus = @assessmentStatus -- expected assessment status
ORDER BY 	
	c1.ParticipantApplicationID, c3.MetricCode, c3.CreatedDate DESC

-- cursor flow
OPEN metric_cursor

FETCH NEXT FROM metric_cursor 
INTO @participantAppId, @metricCode, @metricValue, @dateCreated

WHILE @@FETCH_STATUS = 0
BEGIN
	if not exists(select MetricCode from @t where metricCode = @metricCode) 
	BEGIN
	
		Insert into @t(participantAppId, metricCode, metricValue, dateCreated)
		values(@participantAppId, @metricCode, @metricValue, @dateCreated)
    
    END
    
    FETCH NEXT FROM metric_cursor 
    INTO @participantAppId, @metricCode, @metricValue, @dateCreated
END 
CLOSE metric_cursor;
DEALLOCATE metric_cursor;

Select * from @t


в итоге вывод для человека составил:
2121104	Assmnt_Simulation_A	0	2013-09-24 13:17:19.937
2121104	C7_Greet_A	376370	2013-09-24 13:23:38.313
2121104	C7_Greet_B	0	2013-09-24 13:44:38.553
2121104	C7_Greet_C	1380029036611	2013-09-24 13:44:38.600
2121104	C7_Greet_D	2	2013-09-24 13:44:38.600
2121104	C7_Greet_E	521070	2013-09-24 13:44:38.617
2121104	C7_Greet_F	1208657	2013-09-24 13:44:38.630
2121104	C7_Notate_A	30621	2013-09-12 16:15:54.167
2121104	C7_Notate_B	86917	2013-09-12 16:15:54.200
2121104	C7_Notate_C	19601	2013-09-12 16:15:54.217
2121104	C7_Present_A	1939	2013-09-12 16:04:44.297
2121104	C7_Present_B	5494	2013-09-12 16:04:44.313
2121104	C7_Present_C	4	2013-09-12 16:04:44.313
...
24 сен 13, 18:59    [14879410]     Ответить | Цитировать Сообщить модератору
 Re: выбор последней записи в таблице с одинковым именем.  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
или как вариант вылез вот такой запрос с вложенным позапросом (на себя же) - выводит все метрики для всех людей (вывод данных аналогиченн курсорному запросу)

SELECT 
      c1.ParticipantApplicationID -- Participant Id (supplied in QueryString)               
      , c3.MetricCode -- metric code item (as described in document)
      , c3.MetricValue -- actual metric value
      , c3.CreatedDate as MetricDate
FROM dbo.VersionConfigParticipantApp c1 
            inner join dbo.VersionConfigParticipantAppResponse c2 on (c1.Id = c2.VersionConfigParticipantAppID)
            inner join dbo.VersionConfigParticipantAppResponseMetric c3 on (c2.Id = c3.ResponseInfo_Id)
            inner join(
				SELECT c4.ParticipantApplicationID, c6.MetricCode, MAX(c6.CreatedDate) as MaxMetricDate
				FROM dbo.VersionConfigParticipantApp c4
					inner join dbo.VersionConfigParticipantAppResponse c5 on (c4.Id = c5.VersionConfigParticipantAppID)
					inner join dbo.VersionConfigParticipantAppResponseMetric c6 on (c5.Id = c6.ResponseInfo_Id)
				WHERE	c4.AssessmentStatus = 4 -- 4: started, 5: completed
				GROUP BY c4.ParticipantApplicationID, c6.MetricCode
            ) c7 on c7.MetricCode = c3.MetricCode AND c1.ParticipantApplicationID = c7.ParticipantApplicationID AND c3.CreatedDate = c7.MaxMetricDate
 WHERE  
	c1.AssessmentStatus = 4 -- 4: started, 5: completed  
ORDER BY c1.ParticipantApplicationID, c3.MetricCode, c3.CreatedDate DESC
24 сен 13, 19:10    [14879448]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить