Mobile Apps is a Fad

With the explosion of smartphone market, there are tons and tons of mobile application studios and indie developers sprung up from nowhere. Just like previous fads this is a bubble waiting to burst. The side that benefits the most from this trend is the application store and SDK creators.

Apple

• 70/30 split with the developers. The payout to Apple scales according to how much developers make. 
• App review is hard as it is done manually by people (!)
• The application can only be compiled on Macs if you're using native Objective C.

Android Market

• 70/30 split with developers on application price. 
• Limits the apk file (your applicatio file) to a max of 50MB. Good for puzzle games and indie developers. Bad for anything significant like RPGs.
• The SDK is really low level. e.g. if you're trying to make a small FPS game with one map like fy_iceworld in Counter Strike, good luck wrestling with openGL in Java.

It is recommend to try this SDK for anyone that wants to take a ride on this fad. You can develop apps in a blazingly fast speed.

Be warned though, this fad will burst. So ride on while you can.

Oracle Analytics function

- the other side of SQL summary functions

Aggregate functions are the norm in SQL worlds, where SUM, AVG, COUNT are all the rage. We are here to see another part of summary functions where it is less frequently used because of its obscure syntax that is a wee bit hard to use. Nevertheless, these function will do well in your bag of tricks. Analytics function can shorten the time you took to write long queries with these keywords rather than old-style multiple sub-queries.

TABLENAME: TRANSACT

The above data shows a transaction data for 2 products. Now lets say we need to get the second last scrap activity rows for every LOCATION and every PRODUCT (not the SCRAP rows with MAX(TIME)-1). We can do this by:

select *,
count(*) over (partition by productid, location) as count,
dense_rank() over (partition by productid, location order by time, rownum) as rank
from transact
where activity='SCRAP'

you would get:

from here it is simply filtering out the rows where COUNT= RANK +1 and you would be getting the result you wanted. It will be more difficult to pull this off using aggregate functions and sub-queries. As a comparison, here's the old style query:

select *
from (
select max(rownum) as row_num
from transact
where activity='SCRAP' and rownum not in (select max(rownum) as row_num from transact where activity='SCRAP' group by productid, location)
group by productid, location
) max_rows
inner join
(
select *, rownum
from transact
where activity='SCRAP'
) main on max_rows.row_num=main.rownum

and it will have a restriction: the rows must be inserted with the column TIME = insertion time to ensure the correctness of ROWNUM. This will not always happen in production environment where data editing always happens.

I have yet to find any Microsoft, MySQL equivalent of analytic functions, if there are any gurus out there which have seen it all, please comment!


Regards,
Popo