[求助]awk能否實(shí)現(xiàn)類似excel數(shù)據(jù)透視表功能

hgx126

一文本如下:

1. 
   
2. 機(jī)構(gòu)編號(hào),商戶號(hào),姓名,金額,等級(jí)
   
3. 5411,54117648,張斌,67,100
   
4. 5412,60519607,胡蓉,57,60
   
5. 5412,54119444,羅鑫,17,30
   
6. 5412,54119445,陳延德,38,60
   
7. 5411,54119477,楊曉榮,13,30
   
8. 5411,54119476,鄭元兵,58,60
   
9. 5411,54119476,鄭元兵,98,100
   
10. 5416,54119471,羅小平,21,30
    
11. 5416,54119470,何燕,30,60
    
12. 5416,54119470,何燕,30,60
    
13. 5416,54119468,羅國(guó)江,74,100
    

復(fù)制代碼

按機(jī)構(gòu)號(hào)分為行,等級(jí)為列,統(tǒng)計(jì)商戶號(hào)數(shù)量,金額求和,類似excel數(shù)據(jù)透視表功能，結(jié)果如下:

1. 機(jī)構(gòu)編號(hào),30,30,60,60,100,100,合計(jì),合計(jì)
   
2. 機(jī)構(gòu)編號(hào),商戶號(hào)數(shù)量,金額,商戶號(hào)數(shù)量,金額,商戶號(hào)數(shù)量,金額,商戶號(hào)數(shù)量,金額
   
3. 5411,1,13,1,58,2,165,4,236
   
4. 5412,1,17,2,95,0,0,3,112
   
5. 5416,1,21,2,60,1,74,4,155
   
6. 總計(jì),3,51,5,213,3,239,11,503
   

復(fù)制代碼

cao627

第2個(gè)表和第一個(gè)表之間的邏輯關(guān)系真心看不懂！@hgx126

rubyish

回復(fù) 1# hgx126

awk ~ bd
shishi perl:

perl toss.pl file

toss.pl

1. #!/usr/bin/perl
   
2. use 5.010;
   
3. 
   
4. open my $DATA, shift or die $!;
   
5. readline $DATA;
   
6. 
   
7. my ( $O, $o ) = ( [ 0, 0 ], ',' );
   
8. my ( %G, %X, %H, %V, $V );
   
9. 
   
10. while (<$DATA>) {
    
11.     chomp;
    
12.     my ( $n, undef, undef, $m, $g ) = split $o;
    
13.     $G{$g} ||= 1;
    
14.     $_->[0]++, $_->[1] += $m for $X{$n}{$g}, $H{$n}, $V{$g}, $V;
    
15. }
    
16. 
    
17. my @G = sort { $a <=> $b } keys %G;
    
18. 
    
19. say join $o, '機(jī)構(gòu)編號(hào)', map { ($_) x 2 } @G, '合計(jì)';
    
20. say join $o, '機(jī)構(gòu)編號(hào)', ( '商戶號(hào)數(shù)量', '金額' ) x ( @G + 1 );
    
21. 
    
22. for my $n ( sort { $a <=> $b } keys %X ) {
    
23.     say join $o, $n, map( @{ $X{$n}{$_} || $O }, @G ), @{ $H{$n} };
    
24. }
    
25. 
    
26. say join $o, '總計(jì)', map( @{ $V{$_} }, @G ), @$V;
    

復(fù)制代碼

jcdiy0601

awk -F, '{a[$1" "$2]+=$4;b[$1" "$2]++}END{for(i in a)print i,a[i],b[i]}' file|awk '{print $1,$4,$3}' |awk '{a[$1]=a[$1]","$2","$3}END{for(i in a)print i,a[i]}'
5416 ,2,60,1,21,1,74
5411 ,1,67,2,156,1,13
5412 ,1,17,1,38,1,57