Home > awk, bash, MySQL > bash – Mimic MySQL GROUP BY function with awk

bash – Mimic MySQL GROUP BY function with awk

Problem:
I needed to get GROUP BY results and I did not have the time to wait to restore the mysqldump and run the commands. I had like over 360 zipped files to work with. Restoring each database and running the query even in a script was just too long a process. I opted to just extract the data and use awk to mimic GROUP BY.

Solution:
I used sed to extract the desired results from the mysql dump files. Then took the relevant date fields and used awk to do the group count.

Here is the SELECT…GROUP BY using mysql

$ mysql -u <user> -p<password> 
-e"SELECT date_format(timestamp,'%Y-%m-%d') as 'Date',count(distinct(<field>)) FROM <table>
WHERE <search criteria>
GROUP BY date_format(timestamp,'%Y-%m-%d')
ORDER BY date_format(timestamp,'%Y-%m-%d')" | sed 's/\t/,/g'
2012-10-10,4
2012-10-11,21
2012-10-12,19
2012-10-13,8
2012-10-14,22
2012-10-15,7
2012-10-16,13

Here is the same but now using awk. After extracting the data from the dumps I end up with the following

$ head -n 3 <file-with-extracted-content>
2012-10-01 06:08:54|377772327281
2012-10-01 06:08:55|371202225977
2012-10-01 06:08:55|061712877272
$ 
$ awk -v FS=" |\|" '{print $1, $3}' <file-with-extracted-content> | 
sort -u |  
awk -v OFS=, '{a[$1]+=1}END{for (i in a) print i,a[i]}' | 
sort -t, -k1
2012-10-10,4
2012-10-11,21
2012-10-12,19
2012-10-13,8
2012-10-14,22
2012-10-15,7

Advertisements
Categories: awk, bash, MySQL
  1. November 22, 2012 at 5:28 am

    Thanks for following 🙂
    I was using the same theme earlier, it looks good.

    Rahul
    http://linuxexplore.com

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: