去掉最大值和最小值然后求平均值

yestreenstars

又到了春暖花开、春雨绵绵的季节了，这么好的季节就应该头脑风暴一下，我设计了这样一道题（其实是从群里抄的 ），聪明的你要多久能做出来呢？

原文本：
Peter Blue Teacher 58.40 74.32
Anne Red Student 22.33 122.32
Anne Red Student 99.39 88.22
Sue Yellow Worker 76.38 88.47
Peter Blue Teacher 77.43 28.31
Ken Black Driver 84.67 47.33
Sue Yellow Worker 64.67 73.27
Peter Blue Teacher 70.49 12.77
Sue Yellow Worker 202.69 37.34
Peter Blue Teacher 105.48 88.42

目标文本：
Anne Red Student 60.86 105.27
Peter Blue Teacher 73.96 20.54
Sue Yellow Worker 76.38 88.47
Ken Black Driver 84.67 47.33

要求：
1.如果第一列只出现过一次（比如Ken）则直接打印；
2.如果第一列出现过两次（比如Anne）则对第四列和第五列各自求平均值然后打印；
3.如果第一列出现过三次（比如Sue）则去掉第三列的最大值（比如202.69）和最小值（比如64.67）的两行，然后打印处于中间的那行；
4.如果第一列出现过四次（比如Peter）或四次以上则去掉第三列的最大值和最小值的两行，然后对其余的行的第四列和第五列各自求平均值然后打印；
5.对最后打印的顺序无要求；
6.只能用awk进行处理，不借用管道和其他命令，不能先用sort排序。

reyleon

说明有问题呀! 应该都是第一列出现一次,两次,三次,四次吧

yestreenstars

回复 2# reyleon


    嘿嘿，纯属笔误，已修改。

jason680

回复 1# yestreenstars

Would you like this

Description: (mask them to see description)
  x ($1) for key name
  p[x] for full name
  c[x] for count
  p[x,1] for total $4
  p[x,2] for total $5
  p[x,3] for Max $4, and the same line $5 be p[x,4]
  p[x,5] for Min $4, and the same line $5 be p[x,6]


$ awk '{x=$1;p[x]=$1" "$2" "$3;if(!c[x]++)for(n=0;n++<2;)p[x,n]=p[x,n+2]=p[x,n+4]=$(n+3);else{p[x,1]+=$4;p[x,2]+=$5;if(p[x,3]<$4){p[x,3]=$4;p[x,4]=$5}if(p[x,5]>$4){p[x,5]=$4;p[x,6]=$5}}}END{for(x in c){if(c[x]<=2){x4=p[x,1]/c[x];x5=p[x,2]/c[x]}else{x4=(p[x,1]-p[x,3]-p[x,5])/(c[x]-2);x5=(p[x,2]-p[x,4]-p[x,6])/(c[x]-2)}print p[x],x4,x5}}' FILE
Ken Black Driver 84.67 47.33
Anne Red Student 60.86 105.27
Peter Blue Teacher 73.96 20.54
Sue Yellow Worker 76.38 88.47


---without x4 and x5 ----------------
$ awk '{x=$1;p[x]=$1" "$2" "$3;if(!c[x]++)for(n=0;n++<2;)p[x,n]=p[x,n+2]=p[x,n+4]=$(n+3);else{p[x,1]+=$4;p[x,2]+=$5;if(p[x,3]<$4){p[x,3]=$4;p[x,4]=$5}if(p[x,5]>$4){p[x,5]=$4;p[x,6]=$5}}}END{for(x in c){if(c[x]>=3){p[x,1]-=(p[x,3]+p[x,5]);p[x,2]-=(p[x,4]+p[x,6]);c[x]-=2}print p[x],p[x,1]/c[x],p[x,2]/c[x]}}' FILE

yestreenstars

回复 4# jason680


    先顶礼膜拜一下先 太强大了，我得好好看一下~

yjwan

awk本身就是一门语言   虽然很简练   但是用的已经很少了  而且可读性  一样望去  真是头大

对付这种稍微复杂的问题   不如用python或者perl   虽然写的多几句   但是可读性还是不错的

代号：军刀

awk写得太复杂看得真是头大

yestreenstars

@jason680
我自己也写了一个，应该比你那个好懂一些。

1. awk '{a[$1]=a[$1]!=""?a[$1]" "$4:$4;b[$1,$4]=$5;NF-=2;c[$1]=$0}END{for(i in a){k=split(a[i],d," ");asort(d,e);if(k==1)print c[i],e[1],b[i,e[1]];if(k==2){m=(e[1]+e[2])/2;n=(b[i,e[1]]+b[i,e[2]])/2;print c[i],m,n}if(k>=3){for(j=2;j<k;j++){m+=e[j];n+=b[i,e[j]]}print c[i],m/(k-2),n/(k-2)}m=n=0}}'

复制代码

jason680

回复 8# yestreenstars


That's good for understand, but not for run time

$ wc FILE
31002 155010 744102 FILE

$ time awk '{a[$1]++;b[$1]=b[$1]?b[$1]" "$4:$4;c[$1,$4]=$5;NF-=2;d[$1]=$0}END{for(i in a){k=split(b,e," ");asort(e,f);if(a==1)print d,f[1],c[i,f[1]];if(a==2){m=(f[1]+f[2])/2;n=(c[i,f[1]]+c[i,f[2]])/2;print d,m,n}if(a>=3){for(j=2;j<k;j++){m+=f[j];n+=c[i,f[j]]}print d,m/(k-2),n/(k-2)}m=n=0}}' FILE1
Ken Black Driver 84.67 47.33
Anne Red Student 15497.8 15497.5
Peter Blue Teacher 73.96 20.54
Sue Yellow Worker 76.38 88.47

real    0m30.264s
user    0m2.040s
sys    0m27.482s

$ time awk '{x=$1;p[x]=$1" "$2" "$3;if(!c[x]++)for(n=0;n++<2;)p[x,n]=p[x,n+2]=p[x,n+4]=$(n+3);else{p[x,1]+=$4;p[x,2]+=$5;if(p[x,3]<$4){p[x,3]=$4;p[x,4]=$5}if(p[x,5]>$4){p[x,5]=$4;p[x,6]=$5}}}END{for(x in c){if(c[x]>=3){p[x,1]-=(p[x,3]+p[x,5]);p[x,2]-=(p[x,4]+p[x,6]);c[x]-=2}print p[x],p[x,1]/c[x],p[x,2]/c[x]}}' FILE1
Ken Black Driver 84.67 47.33
Anne Red Student 15497.8 15497.5
Peter Blue Teacher 73.96 20.54
Sue Yellow Worker 76.38 88.47

real    0m0.229s
user    0m0.084s
sys    0m0.088s

davidbeckham921

我去天外有天啊