Repeat the aggregation function

Analytics
Highlighted
Yan
Enthusiast

Repeat the aggregation function

Hi there,

I am learning my way in Teradata, so please forgive me for asking a lot of questions. Thanks in advance for those who take time helping me out!

 

I have a data set with two grouping variable ("Order_No", “Line_No”) and two value variable ("Location”, “Days"):

Order_No   Line_No     Location    Days

A                 1                  Loc1           12

A                 1                  Loc2           10

A                 1                  Loc4           3

A                 2                  Loc1           12

A                 2                  Loc3           5

A                 2                  Loc4           3

A                 3                  Loc1           12

A                 3                  Loc4           3

A                 4                  Loc1           12

A                 4                  Loc4           3    

A                 4                  Loc5           5

 

 

B                 1                  Loc1           12

B                 1                  Loc2           10

B                 2                  Loc1           12

B                 2                  Loc3           5

 

C                 1                  Loc4           3

C                 2                  Loc1           12

C                 2                  Loc2           9

C                 3                  Loc1           12

 

D                 2                  Loc4           3    

D                 2                  Loc5           5

 

With Waldar's help, I was able to identify the single order output, but now I need to extract, from +5000 lines, the location touching every single Line_No with the least average days value PER Order_No.

 

Below is the desired output:

 

Order_No   Location    AvgDay

A                 Loc4           3

B                 Loc1           12

C                 n/a              n/a

D                 Loc4           3

 

How can I repeat the aggregated function for each order?

 

Thanks!


Accepted Solutions
Junior Contributor

Re: Repeat the aggregation function

You need two counts on different levels:

 

SELECT ord.OrderNum, ordLoc.loc, ordLoc.Days
FROM 
 ( -- number of lines per order
   SELECT OrderNum, Count(DISTINCT line) AS Cnt
   FROM mvt_datas AS t
   GROUP BY OrderNum
 ) AS ord
LEFT JOIN
 ( -- number of lines per location/order
   SELECT loc, OrderNum, Count(*) AS Cnt, Avg(Days) AS Days
   FROM mvt_datas AS t
   GROUP BY Loc, OrderNum
 ) AS ordLoc
ON ordLoc.OrderNum = ord.OrderNum -- same order
AND ordLoc.Cnt = ord.Cnt          -- location covers all lines of an order 
QUALIFY Row_Number() Over (PARTITION BY Ord.OrderNum ORDER BY ordLoc.Days DESC) = 1 -- get the location with the lowest average.

 

Is the base data a table or result of a join?

 

Windowed Aggregates don't support DISTINCT, otherwise this could be a single access to the base table.

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Repeat the aggregation function

You need two counts on different levels:

 

SELECT ord.OrderNum, ordLoc.loc, ordLoc.Days
FROM 
 ( -- number of lines per order
   SELECT OrderNum, Count(DISTINCT line) AS Cnt
   FROM mvt_datas AS t
   GROUP BY OrderNum
 ) AS ord
LEFT JOIN
 ( -- number of lines per location/order
   SELECT loc, OrderNum, Count(*) AS Cnt, Avg(Days) AS Days
   FROM mvt_datas AS t
   GROUP BY Loc, OrderNum
 ) AS ordLoc
ON ordLoc.OrderNum = ord.OrderNum -- same order
AND ordLoc.Cnt = ord.Cnt          -- location covers all lines of an order 
QUALIFY Row_Number() Over (PARTITION BY Ord.OrderNum ORDER BY ordLoc.Days DESC) = 1 -- get the location with the lowest average.

 

Is the base data a table or result of a join?

 

Windowed Aggregates don't support DISTINCT, otherwise this could be a single access to the base table.

Yan
Enthusiast

Re: Repeat the aggregation function

Hi Dnoeth,

Thanks for your response. I am so impressed with the prompt and thorough reply from you guys, especially during Thanksgiving weekend!

Your solution worked well, only that I need to change the DESC to ASC to get the least value.

Again, thanks a lot!

Yan