阿里云大数据ACP专业认证实验之05-MaxCompute内置函数（上）（二）

Select ceil(3.1415926),
floor(3.1415926),
round(3.1415926,3),
trunc(3.1415926),
conv('3.1415926',10,2)
from dual;

select rand() from dual;
select rand(detail_id),rand() from t_dml limit 10;

(4) 综合使用
  使用蒙特卡洛法求π值的近似值:产生一系列的成对的随机数，根据每队随机数到点(0.5,0.5)的距离可判断该点是否在单位圆内，计算落在圆内的点占所有点的比例，即可得到π值的近似值：
     // 产生约10万对随机点进行近似值计算：
select (inCircle/totalCnt)/pow(0.5,2) as PI 
from (select count(*) as totalCnt,
sum(case when sqrt(pow((x-0.5),2)+pow((y-0.5),2)) <0.5 then 1 else 0 end) inCircle
from (select /*+mapjoin(t2)*/ rand() as x,rand() as y
from (select * from t_dml limit 10000) t1
left outer join (select * from t_dml limit 10) t2
 on t1.detail_id <> t2.detail_id) tt
) t;
// 产生约100万对随机点进行近似值计算：
select (inCircle/totalCnt)/pow(0.5,2) as PI
from (select count(*) as totalCnt,
sum(case when sqrt(pow((x-0.5),2)+pow((y-0.5),2)) <0.5 then 1 else 0 end) inCircle
from (select /*+mapjoin(t2)*/ rand() as x,rand() as y
from (select * from t_dml limit 10000) t1
left outer join (select * from t_dml limit 100) t2
on t1.detail_id <> t2.detail_id) tt
) t;

select province,length(province),lengthb(province) from t_dml limit 10;

select province, city,
instr(city,substr(province,1,3),1,1) as FirstPos,
case when instr(city,substr(province,1,3),1,2) = 0 then 'No'
else 'Yes'
end as SecondPos
 from (select distinct province, city
          from t_dml) t

order by SecondPos desc, FirstPos desc
limit 10;

             datepart(dt, 'mm') as month,
             datepart(dt, 'dd') as day,
             datepart(dt, 'hh') as hour,
             datepart(dt, 'mi') as minute,

select to_date('20150131','yyyymmdd'),
to_char('2015-01-31 00:00:00', '日期：yyyymmdd')
from dual;

 from t_dml t1
    join (select min(sale_date) as begin_dt,
                          dateadd(dateadd(min(sale_date),1,'hh'),30, 'mi') as end_dt
                 from t_dml
         where product_id=5
                   and datetrunc(sale_date,'dd')='2015-05-01 00:00:00')t2
       on t1.sale_date >= t2.begin_dt
  and t1.sale_date <= t2.end_dt;