BigData data flow: step by step

Market basket analysis with R

Affinity analysis is a data analysis and data mining technique that discovers co-occurrence relationships <...>. In retail, affinity analysis is used to perform market basket analysis, in which retailers seek to understand the purchase behavior of customers. This information can then be used for purposes of cross-selling and up-selling, in addition to influencing sales promotions, loyalty programs, store design, and discount plans. [from Wikipedia]
In other words, you want to find all items from your sails that are sold together, for example: people usually buy chips with beer. There are several algorithms and one of them is Apriori algorithm which is available in R and implemented in 'arules' package.

calc median in mysql

Aloha! As you know, MySQL is a very poor rdbms, but cheap. There aren't a lot of build in functions, special for statistic processing...
But imagine, you need to calculate median. It seems very easy, isn't it? Well, I have bad news for you - Mysql doesn't have this aggregation function.
I've googled it and found several solutions, but all of them have a problems with performance and ugly plan... So, I created own stored procedure (inspired by comment to http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)

CREATE PROCEDURE calcMedian(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
DECLARE counter INT;
SET @counter:= 0;
SET @s1 = CONCAT('select floor(count(',col,')/2) into @counter from ',tbl);
prepare rowsCounterStmt from @s1;
execute rowsCounterStmt;
SET @s2 = CONCAT('select ',col,' from ',tbl,' order by ',col,' asc limit ',@counter,', 1' );
PREPARE stmt FROM @s2;
EXECUTE stmt;
END


Another way: you can use User defined function as explained here http://mysql-udf.sourceforge.net/. In fact, it's much more powerful solution, but it's not suitable for all of us