90 — Teletype

Возникла необходимость выбрать новый тарифный план для сотового. Провозившить минут 30 с excel и google-docs стало понятно, что ничего толкового из этого не выйдет и без db тут не обойтись.

Чуть подумав рука сама набрала "q", так как это было единсвенное доступное на компьютере здесь и сейчас. Что про него знал, что первый и последний раз запускал год назад, минут на 30, для задачки nponeccop.

Дальше будет много q, а именно гибрида APL'а (а именно k k kx) и sql.

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

Далее переходим к словарям, описываются они просто <ключи> ! <значения>. Создаю словарь код<>оператор.

Многие тарифы округляют минуту до полной, ввожу поле для удобства, которое будет просто целым количеством минут. Я не сохраняю его в таблицу, просто получаю результат, так как позже создам 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

Всё что выше - просто подготовка данных, теперь чуть интереснее: q-sql

Посмотрим кому звонил больше всего, тут начинает группировка. Группировка - параметр функции 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]}

К сожалению, q я знаю плохо и как передать ключ и значение результата "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 "79060414294"

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

#qsql #kdb #kx #q