Возникла необходимость выбрать новый тарифный план для сотового. Провозившить минут 30 с excel и google-docs стало понятно, что ничего толкового из этого не выйдет и без db тут не обойтись.
Чуть подумав рука сама набрала «q», так как это было единственное доступное на компьютере здесь и сейчас. Что про него знал: что первый и последний раз запускал год назад, минут на 30, для простой задачки по разбору и поиску по файлу.
Дальше будет много q, а именно ascii последователя подмножества языков APL'а и языка Scheme, а именно k и его расширения k-sql, переродившихся в продукт с именем Q — тесной связке языка и встроенной в него базы данных.
Немного лирики: качаю отчёт с сайта оператора в csv и чуть поправляю заголовок:
Дальше читаем мануал, чтение текстового файла 0:, паралелльно можно разделить файл на поля указав в левой части формат колонок и разделитель, если разделитель список, то колонки таблицы будут именованы из первой строки. Выбираем только нужные поля, раз два и таблица готова.
Кому скучно читать про подговку данных — можно прыгнуть сразу к анализу данных.
Так как q — векторная база данных, то по сути clog — это словарь, имя колонки — список значений.
Чуть подготовлю данные. Видно, что money не в числовом формате, надо бы преобразовать в число: ssr — это oracle replace. Термин $ (cast) занимается различными конвертациями и преобразованиями типов, в данном случае читает число из строки:
each — это map
Ну и запишем это всё в таблицу, используя update. Тут есть небольшая особенность. Если использовать имя таблицы clog, то результатом выполнения функции update будет новая таблица с обновлёнными значениями. но можно указать имя таблицы как `clog, тогда изменения будут сохранены. Телефон тоже сделаем строкой, изначально «S» — это не строка а символьный тип.
Почти все слова в данном q-sql — это обычные функции с небольшой порцией синтаксического сахара. Их можно использовать отдельно, например, where просто преобразовывает битовую строку в список индексов.
сравнение работает со списками. результат — битовая строка, из которой where извлекает индексы, ну а select по этим индексам извлекает соответствующие элементы списков из таблицы.
В файле есть как исходящие так входящие звонки и оплаты разных сервисов, выбираю строки где списывали деньги и есть номер телефона и присваиваю этому старое имя:
Определяем коды, которые используются, чтобы впоследствии классифицировать по операторам:
уже чуть позже я понял что надо вытянуть извлечение кода в функцию.
тут более sql-подобную запись с exec. exec — это тот же select, но который не возвращает словарь таблицы, а возвращает значения или значение результата запроса или таблицы.
Далее переходим к словарям, описываются они просто <ключи>! <значения>. Создаю словарь код<>оператор.
Многие тарифы округляют минуту до полной, ввожу поле для удобства, которое будет просто целым количеством минут. Я не сохраняю его в таблицу, просто получаю результат, так как позже создам view с этим полем. Время в миллисикундах, так что делю на 1000.
Создаю view с оператором и целыми минутами, если бы написал t:, то создал бы таблицу t. Напоминаю, что update сохраняет исходные колонки.
всё необходимое набрал, сохраняю результат t в файл на всякий случай, правильнее конечно было бы сохранить clog и описание view `t, но лень:
Всё что выше — просто подготовка данных, теперь чуть интереснее: разбор.
Посмотрим кому звонил больше всего, тут начинает группировка. Группировка — параметр функции select, которая создаёт списки для каждого вхождения ключа:
после чего выполняем функции с параметром в виде этого списка, desc — функция обратной сортировки, она сортирует как обычный список так и таблицу, умолчательно сортируется по последней колонке.
Заметив, что много звонков на один номер, я добавил колонку «любимый номер», чуть позже я решил просто обозначить это в поле оператора, назначил старой view новое имя, а «t» теперь это новая view на основе старой:
Теперь пора задуматься о деньгах, конкретно о тарифах мегафона.
Какой-то там по 3 копейки, описать функцией просто:
посмотрим что там с деньгами по каждому оператору:
Нужно вводить опции тарифа, если номер `lub, то делим цену на два и прибавляем 30р.
Собственно всё, функция для подсчёта будет следующая, тут для lub использует карринг:
К сожалению, я не нашёл как передать ключ и значение результата «by» в функцию, так что оформляю это как подзапрос. Так как op и time это не два значения какой-то строки из таблицы как в обычной db, то в функцию будут передавать целые списки (в данном случае список и список списков), но функция, описанная мной выше, ожидает только два параметра: оператор и список времен, так что проходится использовать функцию eachboth, которая обозначается как ' (кавычка) по сути это zipWith, но без ограничения количества списков. Запрос, в отличие от обычной db, при этом усложняется только на ':
Суммирую, тут можно написать как exec sum так и sum exec — просуммируется результат exec или exec просуммирует результат — роли не играет:
Понятно сколько бы я потратил, перейдя на данный тариф. Теперь подсчитаем другой, где минута округляется, a дальше посекундно. Считать приходится для каждого указанного времени, что я и делаю используя each:
Полный код:
Оформить этот текст было значительно сложнее чем написать эти 14 строк. Понятное дело, что тут нет неподъёмных для любой другой базы вещей, но написать это меня сподвигла простота использования и очевидность написания некоторых конструкций. В начале было чуть сложно переключиться с обычного sql, но после понимания того, что таблица тут хранит данные в списках, а функции, как правило работают практически с любыми встроенными типами данных, стало значительно понятнее. Именно идиоматическая простота и простота реализации этой db, а по сути это помесь scheme и APL, позволяет использовать этот инструмент эффективно. Впечатления от этого — это APL и функциональщина, сдвинутая в сторону sql и баз данных.
Вся база состоит из одного файла q.exe, размером ~400kb. Скептики улыбнутся после этого, но тогда посмотрите на список заказчиков данного продукта http://kx.com/end-user-customers.php.
Поиграть с этим можно скачав тут http://kx.com/software-download.php
Чуть подумав рука сама набрала «q», так как это было единственное доступное на компьютере здесь и сейчас. Что про него знал: что первый и последний раз запускал год назад, минут на 30, для простой задачки по разбору и поиску по файлу.
Дальше будет много q, а именно ascii последователя подмножества языков APL'а и языка Scheme, а именно k и его расширения k-sql, переродившихся в продукт с именем Q — тесной связке языка и встроенной в него базы данных.
C:\Users\unknown\Dropbox\j>q
KDB+ 3.0 2013.02.06 Copyright (C) 1993-2013 Kx Systems
w32/ 2()core 2972MB unknown win-d2om7les24v 192.168.1.2 PLAY 2013.05.07Немного лирики: качаю отчёт с сайта оператора в csv и чуть поправляю заголовок:
Сервис;Дата звонка;tel;time;Длит-ть;Баланс до;money;Баланс после;
Входящий звонок внутри группы;22.02.2013 20:38:14;79064014328;00:00:13;0;114,9175;0,0000;114,9175;
Входящий звонок;22.02.2013 20:03:49;79094445182;00:12:05;0;114,9175;0,0000;114,9175;
Исходящий звонок внутри группы;22.02.2013 17:04:39;79064014328;00:01:15;0;115,8175;-0,9000;114,9175;
Исходящий звонок внутри группы;22.02.2013 13:18:22;79064014328;00:01:36;0;116,7175;-0,9000;115,8175;
Списание за услугу Сообщники;22.02.2013 01:35:00;;00:00:00;0;119,3675;-2,6500;116,7175;
Запрос информации;21.02.2013 23:40:42;*102;00:00:01;0;119,3675;0,0000;119,3675;Дальше читаем мануал, чтение текстового файла 0:, паралелльно можно разделить файл на поля указав в левой части формат колонок и разделитель, если разделитель список, то колонки таблицы будут именованы из первой строки. Выбираем только нужные поля, раз два и таблица готова.
Кому скучно читать про подговку данных — можно прыгнуть сразу к анализу данных.
q)clog:select tel,time,money from ("SSSTSSSS";enlist ";") 0: `:tel.txt
q)clog
tel time money
---------------------------------
79064014328 00:00:31.000 0,0000
79263883922 00:02:06.000 0,0000
79064014328 00:01:15.000 -0,9000
79064014328 00:01:36.000 -0,9000
00:00:00.000 -2,6500
*102 00:00:01.000 0,0000
..Так как q — векторная база данных, то по сути clog — это словарь, имя колонки — список значений.
q)clog.money
`0,0000`0,0000`-0,9000`-0,9000`-2,6500`0,0000`0,0000`0,0000`0,0000`0,0000`-0,..
Чуть подготовлю данные. Видно, что money не в числовом формате, надо бы преобразовать в число: ssr — это oracle replace. Термин $ (cast) занимается различными конвертациями и преобразованиями типов, в данном случае читает число из строки:
each — это map
{"F"$ssr[string x;",";"."]} each clog.money
Ну и запишем это всё в таблицу, используя update. Тут есть небольшая особенность. Если использовать имя таблицы clog, то результатом выполнения функции update будет новая таблица с обновлёнными значениями. но можно указать имя таблицы как `clog, тогда изменения будут сохранены. Телефон тоже сделаем строкой, изначально «S» — это не строка а символьный тип.
q)update string tel, {"F"$ssr[string x;",";"."]} each money from `clog
`clog
Почти все слова в данном q-sql — это обычные функции с небольшой порцией синтаксического сахара. Их можно использовать отдельно, например, where просто преобразовывает битовую строку в список индексов.
сравнение работает со списками. результат — битовая строка, из которой where извлекает индексы, ну а select по этим индексам извлекает соответствующие элементы списков из таблицы.
q)15<40 10 20 30
1011b
q)where 15<40 10 20 30
0 2 3
В файле есть как исходящие так входящие звонки и оплаты разных сервисов, выбираю строки где списывали деньги и есть номер телефона и присваиваю этому старое имя:
q)clog:select from clog where money<0,not tel like ""
q)clog
tel time money
--------------------------------
"79064014328" 00:01:15.000 -0.9
"79064014328" 00:01:36.000 -0.9
"79064014328" 00:01:33.000 -0.9
"79104652109" 00:01:23.000 -11.9
"79265996349" 00:00:12.000 -5.95
..
Определяем коды, которые используются, чтобы впоследствии классифицировать по операторам:
уже чуть позже я понял что надо вытянуть извлечение кода в функцию.
q)gcode:1_ 4# / get code from tel
q)gcode each clog.tel
"906"
"906"
"906"
"910"
"926"
..
q)distinct gcode each clog.tel
"906"
"910"
"926"
..
тут более sql-подобную запись с exec. exec — это тот же select, но который не возвращает словарь таблицы, а возвращает значения или значение результата запроса или таблицы.
q)codes:exec distinct gcode each tel from clog
q)codes
"906"
"910"
"926"
..
Далее переходим к словарям, описываются они просто <ключи>! <значения>. Создаю словарь код<>оператор.
q)ops:codes ! `beeline`mts`megafon`beeline`mts`beeline`beeline`mts`moscow
q)ops
"906"| beeline
"910"| mts
"926"| megafon
"909"| beeline
"495"| moscow
..
Многие тарифы округляют минуту до полной, ввожу поле для удобства, которое будет просто целым количеством минут. Я не сохраняю его в таблицу, просто получаю результат, так как позже создам view с этим полем. Время в миллисикундах, так что делю на 1000.
q)update ctime:ceiling (time%1000)%60 from clog
tel time money ctime
--------------------------------------
"79064014328" 00:01:15.000 -0.9 2
"79064014328" 00:01:36.000 -0.9 2
"79064014328" 00:01:33.000 -0.9 2
"79104652109" 00:01:23.000 -11.9 2
"79265996349" 00:00:12.000 -5.95 1
..
Создаю view с оператором и целыми минутами, если бы написал t:, то создал бы таблицу t. Напоминаю, что update сохраняет исходные колонки.
q)t::update op:ops@gcode each tel, ctime:ceiling (time%1000)%60 from clog
q)t
tel time money op ctime
----------------------------------------------
"79064014328" 00:01:15.000 -0.9 beeline 2
"79064014328" 00:01:36.000 -0.9 beeline 2
"79064014328" 00:01:33.000 -0.9 beeline 2
"79104652109" 00:01:23.000 -11.9 mts 2
"79265996349" 00:00:12.000 -5.95 megafon 1
..
всё необходимое набрал, сохраняю результат t в файл на всякий случай, правильнее конечно было бы сохранить clog и описание view `t, но лень:
q)save `:t
`:t
Всё что выше — просто подготовка данных, теперь чуть интереснее: разбор.
Посмотрим кому звонил больше всего, тут начинает группировка. Группировка — параметр функции select, которая создаёт списки для каждого вхождения ключа:
q)select ctime by tel from t
tel | ctime ..
-------------| --------------------------------------------------------------..
"74956471602"| ,1 ..
"79031398210"| 7 3 ..
"7903X" | ,2 ..
"79064014328"| 2 2 2 2 1 2 1 1 1 3 1 2 2 3 1 1 1 1 2 2 3 3 3 1 3 2 1 1 0 2 1 ..
..
после чего выполняем функции с параметром в виде этого списка, desc — функция обратной сортировки, она сортирует как обычный список так и таблицу, умолчательно сортируется по последней колонке.
q)desc select sum ctime by tel from t
tel | ctime
-------------| -----
"79064014328"| 126
"79094445182"| 36
"79652650530"| 30
..
Заметив, что много звонков на один номер, я добавил колонку «любимый номер», чуть позже я решил просто обозначить это в поле оператора, назначил старой view новое имя, а «t» теперь это новая view на основе старой:
q)t2::update op:ops@gcode each tel, ctime:ceiling (time%1000)%60 from clog
q)t::update op:`lub from t2 where tel like "79064014328"
q)t
tel time money op ctime
----------------------------------------------
"79064014328" 00:01:15.000 -0.9 lub 2
"79064014328" 00:01:36.000 -0.9 lub 2
"79064014328" 00:01:33.000 -0.9 lub 2
"79104652109" 00:01:23.000 -11.9 mts 2
"79265996349" 00:00:12.000 -5.95 megafon 1
..
Теперь пора задуматься о деньгах, конкретно о тарифах мегафона.
Какой-то там по 3 копейки, описать функцией просто:
q)meg3:{0.03*sum x}
посмотрим что там с деньгами по каждому оператору:
q)select meg3 time%1000 by op from t
op | time
-------| ------
beeline| 111.93
lub | 148.05
megafon| 29.1
moscow | 0.93
mts | 24.45
Нужно вводить опции тарифа, если номер `lub, то делим цену на два и прибавляем 30р.
q)lub:{$[x=`lub;30+y%2;y]} / [op;time]
Собственно всё, функция для подсчёта будет следующая, тут для lub использует карринг:
q){lub[x] meg3[y]}
К сожалению, я не нашёл как передать ключ и значение результата «by» в функцию, так что оформляю это как подзапрос. Так как op и time это не два значения какой-то строки из таблицы как в обычной db, то в функцию будут передавать целые списки (в данном случае список и список списков), но функция, описанная мной выше, ожидает только два параметра: оператор и список времен, так что проходится использовать функцию eachboth, которая обозначается как ' (кавычка) по сути это zipWith, но без ограничения количества списков. Запрос, в отличие от обычной db, при этом усложняется только на ':
q)select money:{lub[x] meg3[y]}'[op;time] from select time%1000 by op from t
money
------
259.98
29.1
0.93
24.45
Суммирую, тут можно написать как exec sum так и sum exec — просуммируется результат exec или exec просуммирует результат — роли не играет:
q)exec sum {lub[x] meg3[y]}'[op;time] from select time%1000 by op from t
314.46
Понятно сколько бы я потратил, перейдя на данный тариф. Теперь подсчитаем другой, где минута округляется, a дальше посекундно. Считать приходится для каждого указанного времени, что я и делаю используя each:
q)mego:sum {1.20+$[x<=60;0;1.20*(x-60)%60]} each
q)exec sum {lub[x] mego[y]}'[op;time] from select time%1000 by op from t
258.06
Полный код:
clog:select tel,time,money from ("SSSTSSSS";enlist ";") 0: `:tel.txt
{"F"$ssr[string x;",";"."]} each clog.money
update string tel, {"F"$ssr[string x;",";"."]} each money from `clog
clog:select from clog where money<0,not tel like ""
gcode:1_ 4#
codes:exec distinct gcode each tel from clog
ops:codes ! `beeline`mts`megafon`beeline`mts`beeline`beeline`mts`moscow
t2::update op:ops@gcode each tel, ctime:ceiling (time%1000)%60 from clog
t::update op:`lub from t2 where tel like "79064014328"
meg3:{0.03*sum x}
mego:sum {1.20+$[x<=60;0;1.20*(x-60)%60]} each
lub:{$[x=`lub;30+y%2;y]}
exec sum {lub[x] meg3[y]}'[op;time] from select time%1000 by op from t
exec sum {lub[x] mego[y]}'[op;time] from select time%1000 by op from t
Оформить этот текст было значительно сложнее чем написать эти 14 строк. Понятное дело, что тут нет неподъёмных для любой другой базы вещей, но написать это меня сподвигла простота использования и очевидность написания некоторых конструкций. В начале было чуть сложно переключиться с обычного sql, но после понимания того, что таблица тут хранит данные в списках, а функции, как правило работают практически с любыми встроенными типами данных, стало значительно понятнее. Именно идиоматическая простота и простота реализации этой db, а по сути это помесь scheme и APL, позволяет использовать этот инструмент эффективно. Впечатления от этого — это APL и функциональщина, сдвинутая в сторону sql и баз данных.
Вся база состоит из одного файла q.exe, размером ~400kb. Скептики улыбнутся после этого, но тогда посмотрите на список заказчиков данного продукта http://kx.com/end-user-customers.php.
Поиграть с этим можно скачав тут http://kx.com/software-download.php