Linux 来自 shell 的 GROUP BY/SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10286522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GROUP BY/SUM from shell
提问by Legend
I have a large file containing data like this:
我有一个包含如下数据的大文件：
a 23
b 8
a 22
b 1
I want to be able to get this:
我希望能够得到这个：
a 45
b 9
I can first sort this file and then do it in Python by scanning the file once. What is a good direct command-line way of doing this?
我可以先对这个文件进行排序，然后通过扫描一次文件在 Python 中进行排序。这样做的一个好的直接命令行方式是什么？
回答by shellter
Edit: The modern (GNU/Linux) solution, as mentioned in comments years ago ;-) .
编辑：现代（GNU/Linux）解决方案，如多年前评论中所述;-)。
awk '{
arr[]+=
}
END {
for (key in arr) printf("%s\t%s\n", key, arr[key])
}' file \
| sort -k1,1
The originally posted solution, based on old Unix sortoptions:
最初发布的解决方案，基于旧的 Unixsort选项：
awk '{
arr[]+=
}
END {
for (key in arr) printf("%s\t%s\n", key, arr[key])
}' file \
| sort +0n -1
I hope this helps.
我希望这有帮助。
回答by Charles Duffy
No need for awk here, or even sort -- if you have Bash 4.0, you can use associative arrays:
这里不需要 awk，甚至不需要排序——如果你有 Bash 4.0，你可以使用关联数组：
#!/bin/bash
declare -A values
while read key value; do
values["$key"]=$(( $value + ${values[$key]:-0} ))
done
for key in "${!values[@]}"; do
printf "%s %s\n" "$key" "${values[$key]}"
done
...or, if you sort the file first (which will be more memory-efficient; GNU sort is able to do tricks to sort files larger than memory, which a naive script -- whether in awk, python or shell -- typically won't), you can do this in a way which will work in older versions (I expect the following to work through bash 2.0):
...或者，如果您首先对文件进行排序（这将更节省内存；GNU sort 能够做一些技巧来对大于内存的文件进行排序，这是一个幼稚的脚本——无论是在 awk、python 还是 shell 中——通常不会），您可以以在旧版本中工作的方式执行此操作（我希望以下内容可以通过 bash 2.0 工作）：
#!/bin/bash
read cur_key cur_value
while read key value; do
if [[ $key = "$cur_key" ]] ; then
cur_value=$(( cur_value + value ))
else
printf "%s %s\n" "$cur_key" "$cur_value"
cur_key="$key"
cur_value="$value"
fi
done
printf "%s %s\n" "$cur_key" "$cur_value"
回答by Birei
One way using perl:
一种使用方式perl：
perl -ane '
next unless @F == 2;
$h{ $F[0] } += $F[1];
END {
printf qq[%s %d\n], $_, $h{ $_ } for sort keys %h;
}
' infile
Content of infile:
内容infile：
a 23
b 8
a 22
b 1
Output:
输出：
a 45
b 9
回答by Dimitre Radoulov
With GNU awk(versions less than 4):
使用GNU awk（版本小于 4）：
WHINY_USERS= awk 'END {
for (E in a)
print E, a[E]
}
{ a[] += }' infile
With GNU awk>= 4:
使用GNU awk>= 4：
awk 'END {
PROCINFO["sorted_in"] = "@ind_str_asc"
for (E in a)
print E, a[E]
}
{ a[] += }' infile
回答by Paused until further notice.
This Perl one-liner seems to do the job:
这个 Perl one-liner 似乎可以完成这项工作：
perl -nle '($k, $v) = split; $s{$k} += $v; END {$, = " "; foreach $k (sort keys %s) {print $k, $s{$k}}}' inputfile
回答by saaj
This can be easily achieved with the following single-liner:
这可以通过以下单行轻松实现：
cat /path/to/file | termsql "SELECT col0, SUM(col1) FROM tbl GROUP BY col0"
Or.
或者。
termsql -i /path/to/file "SELECT col0, SUM(col1) FROM tbl GROUP BY col0"
Here a Python package, termsql, is used, which is a wrapper around SQLite. Note, that currently it's not upload to PyPI, and also can only be installed system-wide (setup.pyis a little broken), like:
这里使用了 Python 包termsql，它是 SQLite 的包装器。请注意，目前它不能上传到PyPI，也只能在系统范围内安装（setup.py有点坏），例如：
sudo pip install https://github.com/tobimensch/termsql/archive/master.zip
