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

Откуда:
Сообщений: 274
Добрый день! Прошу помочь! есть 2 таблицы

create table tDealTransaction (DealTransactID numeric(15, 0) NOT NULL)
go
insert into tDealTransaction select '10004911562'
insert into tDealTransaction select '10004911218'
go
create table tPayment (PaymentID numeric(15, 0) IDENTITY(1000000001,1) NOT NULL,
DealTransactID numeric(15, 0) NOT NULL,
rCRC int NULL,
TimeStamp datetime NOT NULL
)
go
insert into tPayment (DealTransactID, rCRC,Timestamp ) select '10004911562','-811356808','2013-10-04 12:38:16.280' 
insert into tPayment (DealTransactID, rCRC,Timestamp ) select '10004911562','-811356807','2013-10-04 12:39:17.780' 
insert into tPayment (DealTransactID, rCRC,Timestamp ) select '10004911218','706163842','2013-10-04 12:39:59.577' 
go

Необходимо вынуть все поля, связав таблицы по полю DealTransactID, причем из таблицы tPayment нужно взять строку с более поздним timestamp.

Как это сделать лучше всего?
4 окт 13, 16:32    [14925226]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Евгенич,
с помощью cross apply (select top 1 .. from tPayment i where i.DealTransactID = o.DealTransactID order by Timestamp desc, PaymentID desc)
4 окт 13, 16:38    [14925262]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Мистер Хенки
Евгенич,
с помощью cross apply (select top 1 .. from tPayment i where i.DealTransactID = o.DealTransactID order by Timestamp desc, PaymentID desc)


Не очень понял, да.. и ... 2000 Server у меня
4 окт 13, 16:47    [14925302]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
prog882
Guest
Евгенич,
declare @tDealTransaction table (DealTransactID numeric(15, 0))

insert into @tDealTransaction
select '10004911562'

union all

select '10004911218'

declare @tPayment table (
	DealTransactID numeric(15, 0),
	rCRC int,
	timestamp datetime
	)

insert into @tPayment
select '10004911562',
	'-811356808',
	'2013-10-04 12:38:16.280'

union all

select '10004911562',
	'-811356807',
	'2013-10-04 12:39:17.780'

union all

select '10004911218',
	'706163842',
	'2013-10-04 12:39:59.577'

select *
from (
	select t2.DealTransactID,
		t2.rCRC,
		maxTimeStamp = MAX(t2.timestamp) over (partition by t2.DealTransactID),
		t2.timestamp
	from @tDealTransaction t1
	inner join @tPayment t2
		on t1.DealTransactID = t2.DealTransactID
	) t
where maxTimeStamp = timestamp
4 окт 13, 16:47    [14925303]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
prog882
Guest
Евгенич,
или
.....

select *
from @tDealTransaction t1
cross apply (
	select top 1 t2.DealTransactID,
		t2.rCRC,
		t2.timestamp
	from @tPayment t2
	where t1.DealTransactID = t2.DealTransactID
	order by t2.timestamp desc
	) t
4 окт 13, 16:51    [14925332]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
prog882
Евгенич,
или
.....

select *
from @tDealTransaction t1
cross apply (
	select top 1 t2.DealTransactID,
		t2.rCRC,
		t2.timestamp
	from @tPayment t2
	where t1.DealTransactID = t2.DealTransactID
	order by t2.timestamp desc
	) t


Насколько я понял это как минимум для 2005?
4 окт 13, 16:55    [14925360]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Уленшпигель
Member

Откуда:
Сообщений: 115
Евгенич,

SELECT a.* 
FROM tPayment a 
JOIN (SELECT DealTransactID, max(Timestamp) as maxtimestamp
         FROM tPayment
	 GROUP BY DealTransactID) b 
ON a.DealTransactID = b.DealTransactID and a.Timestamp = b.maxtimestamp
4 окт 13, 16:56    [14925362]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Уленшпигель
Евгенич,

SELECT a.* 
FROM tPayment a 
JOIN (SELECT DealTransactID, max(Timestamp) as maxtimestamp
         FROM tPayment
	 GROUP BY DealTransactID) b 
ON a.DealTransactID = b.DealTransactID and a.Timestamp = b.maxtimestamp



Да, так врооде работает, но не хотел группировкой, а пробовал так, но почему то не вышло, кто подскажет?
select p.DealTransactID,p.rCRC,p.TimeStamp from
tPayment p
where p.TimeStamp=(select max(TimeStamp)from tGisGmp_Payment p1 where p.DealTransactID=p1.DealTransactID )
4 окт 13, 17:03    [14925410]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Евгенич,
раз 2000, то тогда так
select tmp.DealTransactID
		,pp.*
from(
		select dt.DealTransactID
				,(	select top (1) PaymentID 
					from tPayment p 
					where p.DealTransactID = dt.DealTransactID 
					order by TimeStamp desc, PaymentId desc) as lastPayment
		from tDealTransaction dt) tmp
	inner join 	tPayment pp
	on pp.PaymentId = tmp.lastPayment 

и естественно

CREATE UNIQUE INDEX idx_dtid_ts_pid on tPayment(DealTransactID,TimeStamp,PaymentID)
4 окт 13, 17:33    [14925579]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Добрый Э - Эх
Guest
Евгенич,

Основные способы решения поставленной задачи: тынц
7 окт 13, 05:06    [14931787]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Вопрос усложнился : есть 2 таблицы

create table tPayment (PaymentID numeric(15, 0) IDENTITY(1000000001,1) NOT NULL,
DealTransactID numeric(15, 0) NOT NULL
)
go

insert into tPayment (DealTransactID  ) select '10004911562'
insert into tPayment (DealTransactID ) select '10004911562'
insert into tPayment (DealTransactID ) select '10004911562' 
insert into tPayment (DealTransactID ) select '10004911218'
go

CREATE TABLE tQuittance (QuittanceID numeric(15, 0) IDENTITY(1000000001,1) NOT NULL,
PaymentID numeric(15, 0) NOT NULL, Error smallint NOT NULL
)
go

insert into tQuittance (PaymentID,Error ) select '1000000001','1'
insert into tQuittance (PaymentID,Error ) select '1000000002','0'
insert into tQuittance (PaymentID,Error ) select '1000000003','1'
insert into tQuittance (PaymentID,Error ) select '1000000004','1'

go


Необходимо выбрать по каждому DealTransactID максимальный PaymentID где error=0, если такого нет то просто максимальный PaymentID (Таблицы связаны по полю PaymentID)
7 окт 13, 14:49    [14934186]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Евгенич,
ну и сделайте все то же самое только сначала для тех записей у которых в группе есть error=0, а потом для тех записей у которых в группе нет error=0 а результаты двух запросов обьедините через union all
7 окт 13, 14:54    [14934221]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
o-o
Guest
2000:

declare @tPayment table (PaymentID numeric(15, 0) IDENTITY(1000000001,1) NOT NULL,
                         DealTransactID numeric(15, 0) NOT NULL
)


insert into @tPayment (DealTransactID  ) select '10004911562'
insert into @tPayment (DealTransactID ) select '10004911562'
insert into @tPayment (DealTransactID ) select '10004911562' 
insert into @tPayment (DealTransactID ) select '10004911218'


declare @tQuittance table (QuittanceID numeric(15, 0) IDENTITY(1000000001,1) NOT NULL,
                           PaymentID numeric(15, 0) NOT NULL, Error smallint NOT NULL
)


insert into @tQuittance (PaymentID,Error ) select '1000000001','1'
insert into @tQuittance (PaymentID,Error ) select '1000000002','0'
insert into @tQuittance (PaymentID,Error ) select '1000000003','1'
insert into @tQuittance (PaymentID,Error ) select '1000000004','1'


select t_all.DealTransactID,
       isnull(t_0.max_id, t_all.max_id) as max_id
from 	
(select p.DealTransactID, 
	   MAX(p.PaymentID) as max_id       
from @tPayment p join @tQuittance q on p.PaymentID = q.PaymentID
group by p.DealTransactID) t_all

left join

(select p.DealTransactID, 
	   MAX(p.PaymentID) as max_id       
from @tPayment p join @tQuittance q on p.PaymentID = q.PaymentID
where q.Error = 0
group by p.DealTransactID) t_0

on t_all.DealTransactID = t_0.DealTransactID
7 окт 13, 15:01    [14934277]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Мистер Хенки
Евгенич,
ну и сделайте все то же самое только сначала для тех записей у которых в группе есть error=0, а потом для тех записей у которых в группе нет error=0 а результаты двух запросов обьедините через union all


а если предпочтение через exists?
7 окт 13, 15:05    [14934314]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Сделал через временную таблицу, но не нравиццо! хоть и работает.

select p.DealTransactID,q.PaymentID,q.error 
			into #test
			from
			tPayment p
			,tQuittance q
where p.PaymentID=q.PaymentID

select t1.DealTransactID,max(t1.PaymentID) from #test t1
where
not exists (select null
			  from #test t0
			where t1.dealtransactid=t0.dealtransactid
			and t1.error>t0.error 
			)
group by t1.DealTransactID


Может кто подскажет покомпактней?

PS. Вариант o-o тоже арбайтен!
7 окт 13, 15:49    [14934642]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
покомпактней

select p.DealTransactID, 
       isnull(max(case when q.Error = 0 then p.PaymentID else null end), max(p.PaymentID)) as max_id
from tPayment p join tQuittance q on p.PaymentID = q.PaymentID
group by p.DealTransactID

Правда, в нагрузку прилагается сообщение "Warning: Null value is eliminated by an aggregate or other SET operation". Ну это победите при необходимости и желании.
7 окт 13, 16:02    [14934760]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Гость333
Евгенич
покомпактней

select p.DealTransactID, 
       isnull(max(case when q.Error = 0 then p.PaymentID else null end), max(p.PaymentID)) as max_id
from tPayment p join tQuittance q on p.PaymentID = q.PaymentID
group by p.DealTransactID

Правда, в нагрузку прилагается сообщение "Warning: Null value is eliminated by an aggregate or other SET operation". Ну это победите при необходимости и желании.



Супер, спасибо! А как победить и на что это влияет?
7 окт 13, 16:32    [14935025]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
А как победить и на что это влияет?

Может повлиять на программу-клиента. Если она не в курсе, что из MSSQL могут приходить такие сообщения, то может выпасть exception.
В большинстве случаев с этим сообщением можно никак не бороться.
7 окт 13, 16:56    [14935255]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Гость333
Евгенич
А как победить и на что это влияет?

Может повлиять на программу-клиента. Если она не в курсе, что из MSSQL могут приходить такие сообщения, то может выпасть exception.
В большинстве случаев с этим сообщением можно никак не бороться.


Уважаемый, еще раз спасибо за предложенный вариант, но все никак не разберу логики получения
isnull(max(case when q.Error = 0 then p.PaymentID else null end), max(p.PaymentID)) as max_id.
Вам не трудно "на пальцах" для "чайника"?
7 окт 13, 17:15    [14935408]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Евгенич
Гость333
пропущено...

Может повлиять на программу-клиента. Если она не в курсе, что из MSSQL могут приходить такие сообщения, то может выпасть exception.
В большинстве случаев с этим сообщением можно никак не бороться.


Уважаемый, еще раз спасибо за предложенный вариант, но все никак не разберу логики получения
isnull(max(case when q.Error = 0 then p.PaymentID else null end), max(p.PaymentID)) as max_id.
Вам не трудно "на пальцах" для "чайника"?


На первый взгляд кажется, что всегда выбирается p.PaymentID и можно заменить все выражение на max(p.PaymentID) - однако это не даст желаемый результат, т.к. будет аггрегироваться весь запрос, а в предложенном варианте p.PaymentID со значением error=0 и "иначе" аггрегируются отдельно? Я все правильно понял?
7 окт 13, 22:09    [14936460]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Уленшпигель
Member

Откуда:
Сообщений: 115
Евгенич, первый агрегат - это все платежи с error = 0. У платежей с error = 1 идентификатор убирается. Функция max пропускает такие идентификаторы и выбирает только из тех, у которых error = 0. Но если у всех платежей транзакции error = 1, то max выдает null (поскольку все идентификаторы равны null). И тогда берется второй агрегат - просто максимальный идентификатор платежа для транзакции.
8 окт 13, 09:40    [14937586]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Евгенич
Вам не трудно "на пальцах" для "чайника"?

Чтобы проще было понимать подобные запросы, можно их раскладывать на составляющие, повторяя этапы обработки данных.

В данном случае в запросе присутствуют следующие элементы:
— p.DealTransactID
— max(case when q.Error = 0 then p.PaymentID else null end)
— max(p.PaymentID).

Из "max(case when q.Error = 0 then p.PaymentID else null end)" выделим ещё такие элементы:
— q.Error;
— case when q.Error = 0 then p.PaymentID else null end, назовём это выражение Pay0.

Составим запрос без агрегации:
select p.DealTransactID,
       q.Error,
       p.PaymentID,
       case when q.Error = 0 then p.PaymentID else null end as Pay0
from tPayment p join tQuittance q on p.PaymentID = q.PaymentID

Результат выполнения запроса:
DealTransactID Error PaymentID Pay0
10004911562 1 1000000001 NULL
10004911562 0 1000000002 1000000002
10004911562 1 1000000003 NULL
10004911218 1 1000000004 NULL

Как видно, в столбец Pay0 попадают значения PaymentID, но только если Error = 0. Если же Error <> 0, то в Pay0 попадает NULL.

Дальше сервер выполняет группировку по столбцу DealTransactID (группы значений были выделены синим и зелёным), выбирая максимальные значения из столбцов PaymentID и Pay0 (значения были выделены красным для первой группы и оранжевым — для второй):
DealTransactID MAX(PaymentID) MAX(Pay0)
10004911562 1000000003 1000000002
10004911218 1000000004 NULL

Здесь используется тот факт, что агрегатная функция MAX (как и любая другая агрегатная функция) игнорирует значения NULL. Т.е. MAX(NULL, 1000000002, NULL) = 1000000002.

Далее к двум полученным агрегатам применяется функция ISNULL (заменил запятую на точку с запятой, иначе расползается форматирование таблицы):
DealTransactID ISNULL(MAX(Pay0); MAX(PaymentID))
10004911562 1000000002
10004911218 1000000004

Всё, результат запроса получен.

То есть в столбце MAX(Pay0) был "максимальный PaymentID где error=0", а с помощью ISNULL обеспечили условие "если такого нет то просто максимальный PaymentID".
8 окт 13, 11:27    [14938234]     Ответить | Цитировать Сообщить модератору
 Re: максимум по группе  [new]
Евгенич
Member

Откуда:
Сообщений: 274
Огромное спасибо за бесценный опыт!
9 окт 13, 14:11    [14944776]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить