how to get min of an average

Analytics
Yan
Enthusiast

how to get min of an average

Hi there,

 

I am new to the Teradata world, and need some help to get the following resolved:

 

I need to find the location that touches all order lines with the least days. The desired result for the following should be A04, which showed up for every single line (i.e. count (distinct Line) =5), with the least average days of of 2 (instead of A05 that has 3).

How do we do min of an average?

 

Thanks a lot!

OrderNum/Line/Qty/Value/Loc/Days

1111110123A042
1111110125A053
11111203273A042
11111203176A053
1111120378A063
1111130117A042
111113014A053
111113012A071
1111140415A042
111114046A053
1111140410AT0999
111114046A071
111115024777A042
111115024181A053
1111150244ASJ999
11111502433A071
1111150242AW0999

Accepted Solutions
Highlighted
Teradata Employee

Re: how to get min of an average

Hi Yan,

 

There are some informations missing to provide a full answer but this should set you on the right track.

Your datas :

create multiset volatile table mvt_datas
( OrderNum  integer
, Line      integer
, Qty       integer
, Val       integer
, Loc       char(3)
, Days      integer
)
primary index (OrderNum)
on commit preserve rows;

insert into mvt_datas values (11111, 10,  1,  23,  'A04',   2);
insert into mvt_datas values (11111, 10,  1,  25,  'A05',   3);
insert into mvt_datas values (11111, 20,  3, 273,  'A04',   2);
insert into mvt_datas values (11111, 20,  3, 176,  'A05',   3);
insert into mvt_datas values (11111, 20,  3,  78,  'A06',   3);
insert into mvt_datas values (11111, 30,  1,  17,  'A04',   2);
insert into mvt_datas values (11111, 30,  1,   4,  'A05',   3);
insert into mvt_datas values (11111, 30,  1,   2,  'A07',   1);
insert into mvt_datas values (11111, 40,  4,  15,  'A04',   2);
insert into mvt_datas values (11111, 40,  4,   6,  'A05',   3);
insert into mvt_datas values (11111, 40,  4,  10,  'AT0', 999);
insert into mvt_datas values (11111, 40,  4,   6,  'A07',   1);
insert into mvt_datas values (11111, 50, 24, 777,  'A04',   2);
insert into mvt_datas values (11111, 50, 24, 181,  'A05',   3);
insert into mvt_datas values (11111, 50, 24,   4,  'ASJ', 999);
insert into mvt_datas values (11111, 50, 24,  33,  'A07',  1 );
insert into mvt_datas values (11111, 50, 24,   2,  'AW0', 999);

collect statistics column (OrderNum) on mvt_datas;

Note it's easier for anyone who's willing to help to provide your data in a script like this one.

 

I see two methods :

  select Loc
    from mvt_datas
   where OrderNum = 11111
group by Loc
  having count(*) = (select count(distinct Line) from mvt_datas where OrderNum = 11111)
 qualify row_number() over(order by avg(Days) asc) = 1;

The having part is to get only the location that shows up for every line.

It assumes (OrderNum, Line, Loc) is unique. If not, modify the count(*) by count(distinct Line).

The qualify is for getting the min Days part. I used avg here, but min / max / sum would have worked because it seems the Days column is only related to Loc column.

 

The other one may be easier to understand but gives the Location the more present (not necessarely on every Line) with the least days :

  select top 1 Loc
    from mvt_datas
   where OrderNum = 11111
group by Loc
order by count(*) desc
       , avg(Days) asc;
1 ACCEPTED SOLUTION
2 REPLIES
Highlighted
Teradata Employee

Re: how to get min of an average

Hi Yan,

 

There are some informations missing to provide a full answer but this should set you on the right track.

Your datas :

create multiset volatile table mvt_datas
( OrderNum  integer
, Line      integer
, Qty       integer
, Val       integer
, Loc       char(3)
, Days      integer
)
primary index (OrderNum)
on commit preserve rows;

insert into mvt_datas values (11111, 10,  1,  23,  'A04',   2);
insert into mvt_datas values (11111, 10,  1,  25,  'A05',   3);
insert into mvt_datas values (11111, 20,  3, 273,  'A04',   2);
insert into mvt_datas values (11111, 20,  3, 176,  'A05',   3);
insert into mvt_datas values (11111, 20,  3,  78,  'A06',   3);
insert into mvt_datas values (11111, 30,  1,  17,  'A04',   2);
insert into mvt_datas values (11111, 30,  1,   4,  'A05',   3);
insert into mvt_datas values (11111, 30,  1,   2,  'A07',   1);
insert into mvt_datas values (11111, 40,  4,  15,  'A04',   2);
insert into mvt_datas values (11111, 40,  4,   6,  'A05',   3);
insert into mvt_datas values (11111, 40,  4,  10,  'AT0', 999);
insert into mvt_datas values (11111, 40,  4,   6,  'A07',   1);
insert into mvt_datas values (11111, 50, 24, 777,  'A04',   2);
insert into mvt_datas values (11111, 50, 24, 181,  'A05',   3);
insert into mvt_datas values (11111, 50, 24,   4,  'ASJ', 999);
insert into mvt_datas values (11111, 50, 24,  33,  'A07',  1 );
insert into mvt_datas values (11111, 50, 24,   2,  'AW0', 999);

collect statistics column (OrderNum) on mvt_datas;

Note it's easier for anyone who's willing to help to provide your data in a script like this one.

 

I see two methods :

  select Loc
    from mvt_datas
   where OrderNum = 11111
group by Loc
  having count(*) = (select count(distinct Line) from mvt_datas where OrderNum = 11111)
 qualify row_number() over(order by avg(Days) asc) = 1;

The having part is to get only the location that shows up for every line.

It assumes (OrderNum, Line, Loc) is unique. If not, modify the count(*) by count(distinct Line).

The qualify is for getting the min Days part. I used avg here, but min / max / sum would have worked because it seems the Days column is only related to Loc column.

 

The other one may be easier to understand but gives the Location the more present (not necessarely on every Line) with the least days :

  select top 1 Loc
    from mvt_datas
   where OrderNum = 11111
group by Loc
order by count(*) desc
       , avg(Days) asc;
Yan
Enthusiast

Re: how to get min of an average

Hi Waldar,

Thanks a lot for the detailed explanation!!! Option 1 was exactly what I was looking for! All notes taken!

Yan