In Linux, awk counts the number of IP accesses __linux

Now there is a file, the amount of data in the 200多万条 record, want to use the shell of awk to do statistics, the file format is as follows
#关键字 #url#ip Address #
Test|123|1
Test|123|1
Test|123|2
Test2|12|1
Test2|123|1
Test2|123|2
Now want to count the result is: see the same keyword and URL the total number of visits, as well as how many different IP, output to a file
SQL implementation is very simple select keyword, url, count (1), COUNT (distinct IP) GROUP by keyword, URL, but the amount of data is too large, the report can not run out, want to be implemented under the shell, But my shell is not proficient, do not know how to fast implementation, especially the distinct that
The ideal result is:
#关键字 #url# different ip# search times
Test 123 2 3
Test2 123 1 2
Test2 12 1 1




Awk-f "|" ' {a[$1 ' "$2]++;b[$1" "$" "$3]++}" (b[$1 "$" "$3]==1) {++c[$1" "$2]}end{for (i in a) print I,c[i],a[i]} ' file
Test2 123 2 2
Test2 12 1 1
Test 123 2 3