SQL operation on csv file using bash or shell

Question

This is my input file

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

I want my result as

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

group by using col1 and to find min(col2) and max(col3)
through shell script.

Answer 1

Using 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

This would load the CSV data into a temporary database (SQLite by default I believe), and then apply the given SQL query to it. The table will by default have the same name as the input file (sans suffix) and since the data lacks column headers, the default field names will be alphabetical.

The -H options tells csvsql that the data has no column headers.

To delete the generated header in the output, pipe the result through something like sed '1d'.

To get zero-filled integers:

$ 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"

Here, the lines gets quoted since we're actually only requesting a single output field for each result record (and it contains commas). Another way to do it, which involves a bit more typing, but does not generate extra double quotes:

$ 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

Again, the output header can be removed by piping the result through sed '1d'.

Answer 2

Using csvkit:

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

Note, that this will truncate the leading 0.

Output:

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

Answer 3

With Miller (http://johnkerl.org/miller/doc), using

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'

you have

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

Answer 4

You can break down your SQL into basic procedural operations and replicate them in a shell script.

This is of course not a great idea, since one of the advantages of declarative languages (as SQL) is that they hide the verbosity and complexity of the procedural implementation to developers, allowing them to concentrate on data. (Optimization is a second great advantage of declarative languages that is lost if you replicate them with a procedural program).
Also, this approach is problematic because processing text in shell loops is usually considered bad practice.

However, here is an example of shell script that leverages standard utilities that you will find pre-installed on many systems (except for the array construct — not specified in POSIX, but widely available, and surely available to you since you are asking about 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

It is meant to be invoked as

./script file

This script is equivalent to

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