< min(), max() and the collation dependent ordering of character columns on Greenplum (Postgres 8.2) >

Recently I came across an interesting collation related problem while using a Greenplum (Postgres 8.2) database setup with an UTF-8 collation.

Each character column that undergoes an order by, min() or max() will be sorted according to that collation.

But in some cases, I needed to perform ordering or min(), max() aggregations based on the ASCII content of character columns - luckily, this is quite easily achieved on Greenplum using operator classes and custom aggregates:

create temp table col_test (a text);

insert into col_test (a) values ('"quoted"'),('Abc'),('123'),('Öfföff'),('Zürich'),('Zuhause'),('Zahnpasta');

--
-- order by UTF-8 content
--
select * from col_test order by a asc;

123
Abc
Öfföff
"quoted"
Zahnpasta
Zuhause
Zürich

select * from col_test order by a desc;

Zürich
Zuhause
Zahnpasta
"quoted"
Öfföff
Abc
123

--
-- order by ASCII content
-- 
select * from col_test order by a using ~<~;

"quoted"
123
Abc
Zahnpasta
Zuhause
Zürich
Öfföff

select * from col_test order by a using ~>~;

Öfföff
Zürich
Zuhause
Zahnpasta
Abc
123
"quoted"


The min(), max() aggregation can be taken care of by creating two custom aggregates:

drop aggregate ascii_max(bpchar) cascade;

drop aggregate ascii_min(bpchar) cascade;

create or replace function ascii_bpchar_larger(prev bpchar, curr bpchar) returns bpchar AS $$
begin if (prev ~>~ curr) then return prev; end if; return curr; end; $$ language plpgsql immutable strict;

create or replace function ascii_bpchar_smaller(prev bpchar, curr bpchar) returns bpchar AS $$
begin if (prev ~<~ curr) then return prev; end if; return curr; end; $$ language plpgsql immutable strict;

create aggregate ascii_max(bpchar) ( sfunc = ascii_bpchar_larger, stype = bpchar, sortop = '~>~' );

create aggregate ascii_min(bpchar) ( sfunc = ascii_bpchar_smaller, stype = bpchar, sortop = '~<~' );

The custom aggregates can be used like any builtin aggregate:

--
-- aggregate by UTF-8 content
--
select min(a), max(a) from col_test;
 min |  max 
-----+--------
 123 | Zürich

--
-- aggregate by ASCII content
--
select ascii_min(a), ascii_max(a) from col_test;
 ascii_min | ascii_max
-----------+-----------
 "quoted"  | Öfföff

See http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html for more information regarding custom aggregates and http://www.postgresql.org/docs/8.2/static/indexes-opclass.html regarding operator classes.

< Evolution of a trading system - three years of software development visualized in under eight minutes >

We used gource to create a video from the svn logs of our trading system - the background song is available here for free.

 

< +107% in twelve months of algo papertrading... >

Today, our fully automated algorithmic trading system has been up and running for twelve months.

During this period, the P&L rose from $100k to $207k - an increase of +107.58%

Here is a screenshot of our paper account:

For comparison - during this time, the Dow made +14.31%, S&P 500 +19.88% and Nasdaq +31.28%.

Want to know more? Drop me a line :-)

< Oracle "pipelined" functions and Powerdesigner 16.1 >

During a recent project assignment, I had to generate Oracle 11g "pipelined" functions using Sybase Powerdesigner 16.1.

The stock Powerdesigner does not provide a "pipelined" keyword for Oracle.

But, luckily, Powerdesigner has an excellent metadata system which can be used to add the necessary keyword on demand.

1. from the main menu, choose Database/Edit Current DBMS... 
2. in the General tab, right-click Profile and choose Add Metaclasses... 
3. click on the DatabasePackageProcedure selection box, then OK 
4. right-click DatabasePackageProcedure and choose New->Extended Attribute 
5. name the new attribute IsPipelined, use false as the default value and click Accept 
6. in the tree-selection, go to Script/Objects/DB Package Procedure/Add 
7. in the Value text-field, replace this existing code:
   
   %DBPKPROCTYPE% %DBPKPROC%[ (%DBPKPROCPARAM%)][ return %DBPKPROCRETURN%][ [.O:[as][is]] %DBPKPROCCODE%]
   
   with this one:
   
   %DBPKPROCTYPE% %DBPKPROC%[ (%DBPKPROCPARAM%)][ return %DBPKPROCRETURN%][%IsPipelined%? pipelined][ [.O:[as][is]] %DBPKPROCCODE%]
   
   and choose OK. The DBMS properties window will close and Powerdesigner will ask you to save the changes you just made - click Yes.
8. open the appropriate database function and right-click to open it's properties
9. activate the new IsPipelined attribute in the Extended Attributes tab for "pipelined" functions

< +20.06% in three months of algo papertrading... >

Continuing our fully automated algorithmic trading system test, our paper account P&L rose to +20.06% from February, 11th to May, 17th - this amounts to a gain of around $20k in approximately three months.

Here is a screenshot of our paper account:

For comparison - during this time, the DJI made +9.73%, S&P 500 +9.85% and Nasdaq +9.60%.

< sample backtest equity curve >

Here is a backtested equity curve of our automated trading system since 2000, starting with $100k:

< an outstanding trade >

Normally, our automatic trading system does not do day trading, but today was not normal:

The system bought FSLR at 27.53, then added tranches at 29.17, 31.78, 33.50, 37.00, 40.00 and was finally stopped out at 39.01 during a pull back - net gain for our paper account today with this trade: +$7023.61 - nice ;-)