Daily Archives: May 29, 2015

Least Perfect Square

This problem is from careercup. http://www.careercup.com/question?id=5725584103571456

Given a number “n”, find the least number of perfecet square numbers sum needed to get “n”.
Exmaple:
n=12, return 3 (4+4+4)=(2^2+2^2+2^2) NOT (3^2+1+1^2+1^2)
n=6, return 3(4+1+1)=(2^2+1^2+1^2)

At first sight, I thought this could be resolved by greedy method:
round(sqrt(12))=3, remainder=3;
round(sqrt(3))=1, remainder=2;
round(sqrt(2))=1, remainder=1;
But this won’t give out a optimized answer.

This problem is actually a dynamic programming problem. It is very similar to Fibonacci. Let’s assume table[i] stores the answer for least perfect square. Let’s think about how to get table[12].
12 = 11+1^2
12 = 8+2^2
12 = 3+ 3^2
In this way, we know that table[12]=min{table[3]+1, table[8]+1, table[11]+1}

We can compute table[3], table[8], table[11] accordingly like this. We just need to find the numbers before each one, which add a power number and can reach it. To understandy the solution, I wrote below code. It helps to understand. But it is not efficient.

/** Inefficient version */
static int leastPerfectSquare1(int n){
    if(n==1){
        return 1;
    }
    else if(n==0){
        return 0;
    }
    int maxSquare = (int)Math.sqrt(n);
    int result = n;
    for(int i=1;i<=maxSquare; i++){
        int tmpResult = leastPerfectSquare1(n - (int) Math.pow(i, 2)) + 1;
        result = (tmpResult < result) ? tmpResult : result;
    }
    return result;
}

Below version stores the sub solutions.

/** Recursion storing sub solutions **/
static int leastPerfectSquare2(int n){
    int[] storedResult = new int[n + 1];
    storedResult[1] = 1;
    return leastPerfectSquare2Helper(n, storedResult);
}

static int leastPerfectSquare2Helper(int n, int[] storedResult){
    if(n==0){
        return 0;
    }
    int maxSquare = (int)Math.sqrt(n);
    int result = n;
    for(int i=1;i<=maxSquare; i++){
        int power = (int)Math.pow(i, 2);
        int tmpResult = 0;
        if(storedResult[n - power]!=0){
            tmpResult = storedResult[n - power] + 1;
        }
        else {
            tmpResult = leastPerfectSquare2Helper(n - power, storedResult) + 1;
        }
        result = (tmpResult < result) ? tmpResult : result;
    }
    storedResult[n] = result;
    return result;
}

Below version is the effective one compared to above 2 versions. It only uese loop, takes O(n) time. It loop all the potentional squre number, and see what position of number can use it to compute the next one.(hard to explain, reading the code would be helpful.)

/** Non-recursion solution **/
static int leastPerfectSquare3(int n){
    int[] table = new int[n+1];
    for(int i=0;iint maxSquare = (int) Math.sqrt(n);
    for(int i=2; i<=maxSquare; i++){
        int power = (int) Math.pow(i, 2);
        int boundary = n - power;
        for(int j=0;j<=boundary; j++){
            if(table[j] + 1 < table[j+power] ){
                table[j+power] = table[j] + 1;
            }
        }
    }
    return table[n];
}

Translate query from mysql to oracle

IF
Mysql: select if(field1=‘ok’,1,0) AS ‘DR‘
Oracle:
select
case
when field1=‘ok’ then
1
else
0
end AS DR

Date in condition
Mysql: where creation_date>=’2014-06-01‘
Oracle: where trunc(creation_date)>= trunc(to_date(‘2014-06-01’, ‘rrrr-mm-dd’))
In Oracle, we should use trunc besides to_date to compare. For example trunc(sysdate+1).

CONCAT
Mysql: concat(field1, field2, field3)
Oracle: concat(field1, concat(field2, field3)) or field1||field2||field3

GROUP_CONCAT
Mysql: select GROUP_CONCAT(field1 SEPARATOR ‘,’)
Oracle: select listagg(field1, ‘,’)  WITHIN group (order by null)

GRUOP BY, Add aggregator in those fields which are not specified in gorup by:
Mysql: select field1, field2, field3 from t1 group by field1
Oracle: select field1, max(field2), max(field3) from t1 group by field1

UNION,
Mysql: select ‘abc’ as field1 union select field1 from t1
Oracle: select ‘abc’ as field1 from dual union select field1 from t1

Other things we need to pay attention between mysql and oracle

Select date in Oracle
In Oracle select clause, use: select to_char(creation_date, ‘mm/dd/rrrr‘), not: select to_date(creation_date, ‘mm/dd/rrrr’)

Oracle select result are all uppercase
Mysql: select field1 as f1 from t1. The result will be:f1
Oracle: select field1 as f1 from t1. The result will be:F1

Select same field for more than one time:
Mysql: select field1, field1 from t1. The result will be:field1, field1
Oracle: select field1, field1 from t1. The result will be:field1, field1_1

TO_DAYS
Mysql: select to_days(curdate()) from dual.
Oracle: select TRUNC(sysdate) – DATE’0000-01-03′ from dual