unbounded - sum over sql server 2008

¿Cómo combinar GROUP BY y ROW_NUMBER?

Question

Espero que el siguiente código de muestra se explique por sí mismo:

declare @t1 table (ID int,Price money, Name varchar(10))
declare @t2 table (ID int,Orders int, Name varchar(10))
declare @relation table (t1ID int,t2ID int)
insert into @t1 values(1, 200, ''AAA'');
insert into @t1 values(2, 150, ''BBB'');
insert into @t1 values(3, 100, ''CCC'');
insert into @t2 values(1,25,''aaa'');
insert into @t2 values(2,35,''bbb'');
insert into @relation values(1,1);
insert into @relation values(2,1);
insert into @relation values(3,2);
select T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,T1Sum.Price
FROM @t2 T2
INNER JOIN (
SELECT Rel.t2ID
,MAX(Rel.t1ID)AS t1ID
-- the MAX returns an arbitrary ID, what i need is:
-- ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
,SUM(Price)AS Price
FROM @t1 T1
INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
GROUP BY Rel.t2ID
)AS T1Sum ON T1Sum.t2ID = T2.ID
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID

Resultado:

T2ID T2Name Orders T1ID T1Name Price
1 aaa 25 2 BBB 350,00
2 bbb 35 3 CCC 100,00

Lo que necesito está comentado anteriormente, una forma de obtener el ROW_NUMBER pero también a Group By en primer lugar. Así que necesito la sum de todos los precios T1 agrupados por T2.ID en la tabla de relación y en la consulta externa el t1ID con el precio más alto.

En otras palabras: ¿Cómo cambiar MAX(Rel.t1ID)AS t1ID para devolver un poco el ID con el precio más alto?

Entonces, el resultado deseado es (observe que el primer T1ID cambió de 2 a 1 porque tiene el precio más alto):

T2ID T2Name Orders T1ID T1Name Price
1 aaa 25 1 AAA 350,00
2 bbb 35 3 CCC 100,00

Nota : en caso de que se esté preguntando por qué no multiplico los Orders con el precio: no están relacionados (por lo que debería haber omitido esta columna, ya que es un poco ambiguo, ignórelo, lo he agregado para hacer todo menos abstracto). En realidad, las Orders deben permanecer sin cambios, esa es la razón por la cual el enfoque de subconsulta para unir ambas y la razón por la que debo agruparlas en primer lugar.

Conclusión : obviamente, el núcleo de mi pregunta puede ser respondido por la cláusula OVER que se puede aplicar a cualquier función agregada como SUM (vea la respuesta de Damien ) lo que era nuevo para mí. Gracias a todos por sus enfoques de trabajo.

Answer 1

Indudablemente esto puede simplificarse pero los resultados coinciden con sus expectativas.

La esencia de esto es

• Calcule el precio máximo en un CTE separado para cada t2ID
• Calcule el precio total en un CTE separado para cada t2ID
• Combina los resultados de ambos CTE ''s.

Declaración SQL

;WITH MaxPrice AS (
SELECT t2ID
, t1ID
FROM (
SELECT t2.ID AS t2ID
, t1.ID AS t1ID
, rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.Price DESC)
FROM @t1 t1
INNER JOIN @relation r ON r.t1ID = t1.ID
INNER JOIN @t2 t2 ON t2.ID = r.t2ID
) maxt1
WHERE maxt1.rn = 1
)
, SumPrice AS (
SELECT t2ID = t2.ID
, Price = SUM(Price)
FROM @t1 t1
INNER JOIN @relation r ON r.t1ID = t1.ID
INNER JOIN @t2 t2 ON t2.ID = r.t2ID
GROUP BY
t2.ID
)
SELECT t2.ID
, t2.Name
, t2.Orders
, mp.t1ID
, t1.ID
, t1.Name
, sp.Price
FROM @t2 t2
INNER JOIN MaxPrice mp ON mp.t2ID = t2.ID
INNER JOIN SumPrice sp ON sp.t2ID = t2.ID
INNER JOIN @t1 t1 ON t1.ID = mp.t1ID

Answer 2

La deduplicación (para seleccionar el T1 máximo) y la agregación deben realizarse como pasos distintos. He usado un CTE ya que creo que esto lo hace más claro:

;WITH sumCTE
AS
(
SELECT Rel.t2ID, SUM(Price) price
FROM @t1 AS T1
JOIN @relation AS Rel
ON Rel.t1ID=T1.ID
GROUP
BY Rel.t2ID
)
,maxCTE
AS
(
SELECT Rel.t2ID, Rel.t1ID,
ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
FROM @t1 AS T1
JOIN @relation AS Rel
ON Rel.t1ID=T1.ID
)
SELECT T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,sumT1.Price
FROM @t2 AS T2
JOIN sumCTE AS sumT1
ON sumT1.t2ID = t2.ID
JOIN maxCTE AS maxT1
ON maxT1.t2ID = t2.ID
JOIN @t1 AS T1
ON T1.ID = maxT1.t1ID
WHERE maxT1.PriceList = 1

Answer 3

Wow, las otras respuestas parecen complejas, así que espero que no me haya perdido algo obvio.

Puede usar OVER / PARTITION BY contra agregados, y luego ellos agruparán / agregarán sin una cláusula GROUP BY . Así que acabo de modificar su consulta a:

select T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,T1Sum.Price
FROM @t2 T2
INNER JOIN (
SELECT Rel.t2ID
,Rel.t1ID
-- ,MAX(Rel.t1ID)AS t1ID
-- the MAX returns an arbitrary ID, what i need is:
,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
,SUM(Price)OVER(PARTITION BY Rel.t2ID) AS Price
FROM @t1 T1
INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
-- GROUP BY Rel.t2ID
)AS T1Sum ON T1Sum.t2ID = T2.ID
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
where t1Sum.PriceList = 1

Lo que da el conjunto de resultados solicitado.

Answer 4

;with C as
(
select Rel.t2ID,
Rel.t1ID,
t1.Price,
row_number() over(partition by Rel.t2ID order by t1.Price desc) as rn
from @t1 as T1
inner join @relation as Rel
on T1.ID = Rel.t1ID
)
select T2.ID as T2ID,
T2.Name as T2Name,
T2.Orders,
T1.ID as T1ID,
T1.Name as T1Name,
T1Sum.Price
from @t2 as T2
inner join (
select C1.t2ID,
sum(C1.Price) as Price,
C2.t1ID
from C as C1
inner join C as C2
on C1.t2ID = C2.t2ID and
C2.rn = 1
group by C1.t2ID, C2.t1ID
) as T1Sum
on T2.ID = T1Sum.t2ID
inner join @t1 as T1
on T1.ID = T1Sum.t1ID