Предыстория
В проекте возникла необходимость отобразить товары, которые поставляются или могут поставляться в определенные регионы Казахстана.
У каждого поставщика есть перечень товаров с указанием регионов поставки.
Есть таблица товаров, назовем ее
product.
Регионов в Казахстане всего 17, поэтому я решил хранить данные о регионах поставки в одном числовом поле c помощью битов.
Таблица product выглядит следующим образом:
create table product (
code varchar(20),
name varchar(50),
regions bigint,
company_code varchar(20),
company_name varchar(50)
);
code - код продукта;
name - наименование продукта;
regions - регионы поставки;
company_code - код компании;
company_name - наименование компании;
Теперь о битовом хранение регионов. Каждому региону соответствует свой бит:
| Число
| Битовое представление числа
| Наименование региона
|
| 1
| 000000000000000001
| По всей территории РК
|
| 2
| 000000000000000010
| г. Астана
|
| 4
| 000000000000000100
| г. Алматы
|
| 8
| 000000000000001000
| Акмолинская область
|
| 16
| 000000000000010000
| Актюбинская область
|
| 32
| 000000000000100000
| Алматинская область
|
| 64
| 000000000001000000
| Атырауская область
|
| 128
| 000000000010000000
| Западно-Казахстанская область
|
| 256
| 000000000100000000
| Жамбылская область
|
| 512
| 000000001000000000
| Карагандинская область
|
| 1024
| 000000010000000000
| Костанайская область
|
| 2048
| 000000100000000000
| Кызылординская область
|
| 4096
| 000001000000000000
| Мангистауская область
|
| 8192
| 000010000000000000
| Туркестанская область
|
| 16384
| 000100000000000000
| Павлодарская область
|
| 32768
| 001000000000000000
| Северо-Казахстанская область
|
| 65536
| 010000000000000000
| Восточно-Казахстанская область
|
| 131072
| 100000000000000000
| г. Шымкент
|
Например, если нужно указать, что товар поставляется в три региона: г. Астана, г. Алматы и Карагандинскую область, то нужно применить
бинарную операцию побитового "ИЛИ" к битам регионов, в итоге регионы закодируются в число 518:
| ИЛИ
|
000000000000000010
| 2
|
|
000000000000000100
| 4
|
|
000000001000000000
| 512
|
| Результат
|
000000001000000110
| 518
|
Заполним таблицу тестовыми данными:
INSERT INTO product VALUES ('001', 'Трансформатор тока', 6, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('001', 'Трансформатор тока', 2, 'xyz', 'Power Inc.');
INSERT INTO product VALUES ('002', 'Усилитель низкой частоты', 1, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('003', 'Набор резисторов', 20, 'abc', 'Energy Corp.');
INSERT INTO product VALUES ('003', 'Набор резисторов', 160, 'volt', 'Volt Group');
INSERT INTO product VALUES ('003', 'Набор резисторов', 36, 'xyz', 'Power Inc.');
Данные готовы, но проблема в том, что в Vertica нет подходящей агрегационной функции, позволяющей делать побитовое "ИЛИ" чисел. В Vertica есть функция
bit_or, но по ряду причин она не подходит:
- Функция bit_or работает с бинарными данными
- Напрямую преобразовать число в бинарный вид нет возможности, только через hex
- Функция bit_or производит побитовые операции слева направо, поэтому если битов не хватает правая часть дополняется нулями, что дает не тот результат, который мы ожидаем.
Таким образом, мы пришли к необходимости создания своей функции.
Vertica позволяется писать свои функции на разных языках: C++, Java и Python.
Но именного агрегационные функции User Defined Aggregate Function (UDAF) можно почему-то писать только на С++.
В каталоге, где вы установили Vertica, есть SDK с примерами.
Например, в CentOS 7 это директория:
/opt/vertica/sdk/examples
У вас должна быть установлена среда разработки g++.
Чтобы установить среду разработки g++, запустите:
yum install gcc gcc-c++ make
Если у вас Ubuntu, библиотеки могу собираться, но не работать выдавая ошибки вида:
Failure in UDx RPC call InvokeGetLibraryManifest()
Насколько я понял, это из-за версий gcc и g++, которые в Ubuntu по умолчанию 5.4.0, а в Centos 7 они по умолчанию
4.8.5.
Поэтому, если у вас возникли подобные проблемы, можно доставить нужные версии gcc и g++:
sudo apt-get install gcc-4.8
sudo apt-get install g++-4.8
Создайте файл
OrSum.cpp со следующим содержимым:
#include "Vertica.h"
#include
#include
#include
using namespace Vertica;
class OrSum : public AggregateFunction
{
public:
virtual void initAggregate(ServerInterface &srvInterface,
IntermediateAggs &aggs)
{
vint &sum = aggs.getIntRef(0);
sum = 0;
}
void aggregate(ServerInterface &srvInterface,
BlockReader &arg_reader,
IntermediateAggs &aggs)
{
vint &sum = aggs.getIntRef(0);
do {
const vint &val = arg_reader.getIntRef(0);
if (val != Vertica::vint_null) {
sum |= val;
}
} while (arg_reader.next());
}
virtual void combine(ServerInterface &srvInterface,
IntermediateAggs &aggs,
MultipleIntermediateAggs &aggs_other)
{
vint &sum = aggs.getIntRef(0);
do {
const vint &otherSum = aggs_other.getIntRef(0);
if (otherSum != Vertica::vint_null) {
sum |= otherSum;
}
} while (aggs_other.next());
}
virtual void terminate(ServerInterface &srvInterface,
BlockWriter &res_writer,
IntermediateAggs &aggs)
{
res_writer.setInt(aggs.getIntRef(0));
}
InlineAggregate()
};
class OrSumFactory : public AggregateFunctionFactory
{
virtual void getPrototype(ServerInterface &srvInterface,
ColumnTypes &argTypes,
ColumnTypes &returnType)
{
argTypes.addInt();
returnType.addInt();
}
virtual void getReturnType(ServerInterface &srvInterface,
const SizedColumnTypes &input_types,
SizedColumnTypes &output_types)
{
output_types.addInt();
}
virtual void getIntermediateTypes(ServerInterface &srvInterface,
const SizedColumnTypes &input_types,
SizedColumnTypes &intermediateTypeMetaData)
{
intermediateTypeMetaData.addInt();
}
virtual AggregateFunction *createAggregateFunction(ServerInterface &srvInterface)
{ return vt_createFuncObject(srvInterface.allocator); }
};
RegisterFactory(OrSumFactory);
Сборка библиотеки
В разработке...
Создание функции
CREATE OR REPLACE LIBRARY KESH_LIB AS '/opt/vertica/sdk/examples/build/KeshFunctions.so' LANGUAGE 'C++';
CREATE OR REPLACE AGGREGATE FUNCTION KESH_OR_SUM AS LANGUAGE 'C++' NAME 'KeshOrSumFactory' LIBRARY EPROC_LIB;
Использование функции
SELECT code, kesh_or_sum(regions) FROM product GROUP BY code
Удаление функции
DROP AGGREGATE FUNCTION KESH_OR_SUM(bigint);
DROP LIBRARY KESH_LIB;
