Обрабатываем статистические данные при помощи sqlite3-shell

Преамбула

На сайте журнала "xakep" ведется рейтинг лучших репозиториев на GitHub по итогам недели. В последнем рейтинге меня заинтересовала утилита TextQL занявшая первое место. По описанию она позволяет выполнять sql запросы на текстовых данных описанных в формате csv. Мне показалось это интересным и я открыл демо:

Демонстрация работы утилиты TextQL

в демонстрации меня удивил момент когда пользователь перешел в режим sqlite-консоли. Я полез в исходник и увидел то что в принципе и ожидал. Утилита представляла собой обертку над базой sqlite3 с возможностью импорта/экспорта данных в csv формат. Все бы ничего, но эти  возможности встроены в штатный шел базы по умолчанию. О том как использовать их к своей пользе я и хочу рассказать.


Установка.

Если вы пользователь Linux вам будет достаточно установить последнюю версию базы из репозитория вашего дистрибутива. Пользователи Windows могут вполне легально скачать последнюю версию с официального сайта.

Формат данных

CVS - один из самых старых стандартов описания данных. Представляет из себя данные таблицы записанные построчно в текстовом виде. Колонки строк разделены специальными символами (чаще всего "," или ";"). Пример:

D:\home\samples>type test.csv                               
a;b;c                                                       
1;2;3                                                       
4;5;6                                                       
7;8;9   

Файл с тестовыми данными

Используем shell

Следующие действия предполагают знание языка запросов sql.

D:\home\samples>sqlite3                                     
SQLite version 3.8.3 2014-02-03 14:04:11                    
Enter ".help" for instructions                              
Enter SQL statements terminated with a ";"                  

Выставляет режим роботы с csv-файлами.

sqlite> .mode csv                                           

Устанавливаем символ разделитель, для файлов созданных MS Office это ";".

sqlite> .separator ;                                        

Включаем вывод заголовков таблиц.

sqlite> .header ON                                          

Импортируем данные из файла test.csv в таблицу my_table

sqlite> .import test.csv my_table                           

Проверяем результат

sqlite> select * from my_table;                                                   
a;b;c                                                       
1;2;3                                                       
4;5;6                                                       
7;8;9       

sqlite> select a from my_table;                             
a                                                           
1                                                           
4                                                           
7                                                           

sqlite> select "My statistic" as "Result", max(a) as "Max of A", min(b) as "Min of B", sum(c) as "Sum of C" from my_table; 

Result;"Max of A";"Min of B";"Sum of C"                                                                                    
"My statistic";7;2;18                                                                                                      

Теперь данные находятся в во временной базе данных и вы можете произвольно манипулировать ими. Сохранить результат выборки в виде таблицы пригодной для Excel-я можно следующим образом:

sqlite> .output statistic.csv                                                                                              
sqlite> select "My statistic" as "Result", max(a) as "Max of A", min(b) as "Min of B", sum(c) as "Sum of C" from my_table; 
sqlite> .exit                                                                                                              
                                                                                                                           
D:\home\samples>type statistic.csv                                                                                         
Result;"Max of A";"Min of B";"Sum of C"                                                                                    
"My statistic";7;2;18                                                                                                      

Где statistic.csv это имя выходного файла. Ниже приведен пример отображения в Excel:

Пример содержимого файла с результатами выполнения запроса к базе

Итог

Всегда изучайте доступные инструменты перед тем как делать свой велосипед.