☰

bash またはシェルを使用した csv ファイルに対する SQL 操作

tag-icon • tag-icon bash shell-script text-processing sql
bash またはシェルを使用した csv ファイルに対する SQL 操作

これは私の入力ファイルです

0164318,001449,001452,001922  
0164318,001456,001457,001922  
0842179,002115,002118,001485  
0846354,001512,001513,001590  
0841422,001221,001224,001860  
0841422,001227,001228,001860

私は結果を

0164318,001449,001457,001922  
0842179,002115,002118,001485  
0846354,001512,001513,001590  
0841422,001221,001228,001860


col1 を使用してグループ化し、シェル スクリプトを使用してmin(col2) と max(col3) を見つけます。

答え1

使用csvkit、

$ csvsql -H --query 'SELECT a,min(b),max(c),d FROM file GROUP BY a' file.csv
a,min(b),max(c),d
164318,1449,1457,1922
841422,1221,1228,1860
842179,2115,2118,1485
846354,1512,1513,1590

これにより、CSV データが一時データベース (デフォルトでは SQLite だと思います) にロードされ、指定された SQL クエリが適用されます。テーブルはデフォルトで入力ファイルと同じ名前 (サフィックスなし) になり、データに列ヘッダーがないため、デフォルトのフィールド名はアルファベット順になります。

オプションは、データに列ヘッダーがないことを-H示します。csvsql

出力で生成されたヘッダーを削除するには、結果を のようなものにパイプしますsed '1d'。

ゼロ埋めされた整数を取得するには:

$ csvsql -H --query 'SELECT printf("%07d,%06d,%06d,%06d",a,min(b),max(c),d) FROM file GROUP BY a' file.csv
"printf(""%07d,%06d,%06d,%06d"",a,min(b),max(c),d)"
"0164318,001449,001457,001922"
"0841422,001221,001228,001860"
"0842179,002115,002118,001485"
"0846354,001512,001513,001590"

ここでは、各結果レコードに対して 1 つの出力フィールドのみを要求しているため (また、その出力フィールドはコンマを含むため)、行は引用符で囲まれます。別の方法としては、入力が少し増えますが、余分な二重引用符は生成されません。

$ csvsql -H --query 'SELECT printf("%07d",a),printf("%06d",min(b)),printf("%06d",max(c)),printf("%06d",d) FROM file GROUP BY a' file.csv
"printf(""%07d"",a)","printf(""%06d"",min(b))","printf(""%06d"",max(c))","printf(""%06d"",d)"
0164318,001449,001457,001922
0841422,001221,001228,001860
0842179,002115,002118,001485
0846354,001512,001513,001590

ここでも、結果をパイプすることで出力ヘッダーを削除できますsed '1d'。

答え2

使用csvkit:

csvsql -H --query "select a,min(b),max(c),d from file group by a,d" file.csv

先頭の 0 が切り捨てられることに注意してください。

出力：

a,min(b),max(c),d
164318,1449,1457,1922
841422,1221,1228,1860
842179,2115,2118,1485
846354,1512,1513,1590

答え3

ミラー氏（http://johnkerl.org/miller/doc）、を使用して

mlr --ocsv --quote-all --inidx --ifs , cat inputFile | \
mlr --ocsv --quote-none  --icsvlite stats1 -g '"1"' -a min,max,min -f '"2","3","4"' \
then cut -f '"1","2"_min,"3"_max,"4"_min' \
then label id,col2,col3,col4 | sed 's/"//g'

あなたが持っている

id,col2,col3,col4
0164318,001449,001457,001922
0842179,002115,002118,001485
0846354,001512,001513,001590
0841422,001221,001228,001860

答え4

SQL を基本的な手続き型操作に分解し、シェル スクリプトで複製することができます。

もちろん、これはあまり良い考えではありません。宣言型言語（SQLなど）の利点の1つは、開発者が手続き型実装の冗長性と複雑さを意識せずに済むため、データに集中できるからです。（最適化は宣言型言語の2番目の大きな利点ですが、手続き型プログラムで複製すると失われます）。
また、このアプローチには問題があります。シェルループ内でテキストを処理することは、通常は悪い習慣だと考えられています。。

ただし、多くのシステムにプリインストールされている標準ユーティリティを活用するシェル スクリプトの例を次に示します (配列構造は除きます。配列構造は POSIX では指定されていませんが、広く利用可能であり、質問されているので確実に利用できるはずですbash)。

#!/bin/bash

# The input file will be passed as the first argument
file="$1"

# For each input line:
# We take only the values of the first field, sort them, remove duplicates
for i in $(cut -d ',' -f 1 "$file" | sort -n -u); do

    # Resetting the array is not really needed; we do it for safety
    out=()

    # The first field of the output row is the key of the loop
    out[0]="$i"

    # We only consider the rows whose first field is equal
    # to the current key (grep) and...

    # ... we sort the values of the second field
    # in ascending order and take only the first one
    out[1]="$(grep "^${out[0]}" "$file" | cut -d ',' -f 2 | sort -n | head -n 1)"

    # ... we sort the values of the third field in
    # ascending order and take only the last one
    out[2]="$(grep "^${out[0]}" "$file" | cut -d ',' -f 3 | sort -n | tail -n 1)"

    # ... we sort the values of the fourth field in
    # ascending order and take only the first one
    out[3]="$(grep "^${out[0]}" "$file" | cut -d ',' -f 4 | sort -n | head -n 1)"

    # Finally we print out the output, separating fields with ','
    printf '%s,%s,%s,%s\n' "${out[@]}"

done

これは次のように呼び出される。

./script file

このスクリプトは、

SELECT col1, MIN(col2), MAX(col3), MIN(col4)
FROM text
GROUP BY col1
ORDER BY col1

関連情報