sql函数应用例子
select p.province, data.existUserCount, data.addUserCount, data.cancelUserCount, data.threedayCancelUserCount
, data.addUserCount-data.cancelUserCount realAddUserCount
, to_char(decode(data.existUserCount+data.cancelUserCount,0,0.00,round(data.cancelUserCount*100/(data.existUserCount+data.cancelUserCount),2)),'fm990.00')|| '%' 流失率
, to_char(decode(data.addUserCount,0,0.00,round((data.addUserCount-data.cancelUserCount)*100 /data.addUserCount,2)),'fm990.00')|| '%' 产品留存率
from (select pro.pro_province_name province from m_province pro where pro.pro_is_show = 1) p
left join
(select o.ord_user_province province,
sum(case when o.ord_status = 210 and o.ord_success_time <= to_date('2015-08-05 00:00:00','yyyy-mm-dd hh24:mi:ss') then 1 else 0 end) as existUserCount,
sum(case when o.ord_status = 210 and o.ord_success_time > to_date('2015-08-04 00:00:00','yyyy-mm-dd hh24:mi:ss')
and o.ord_success_time <= to_date('2015-08-05 00:00:00','yyyy-mm-dd hh24:mi:ss')then 1 else 0 end) as addUserCount,
sum(case when o.ord_status = 999 and o.ord_ret_success_time > to_date('2015-08-04 00:00:00','yyyy-mm-dd hh24:mi:ss')
and o.ord_ret_success_time <= to_date('2015-08-05 00:00:00','yyyy-mm-dd hh24:mi:ss')then 1 else 0 end) as cancelUserCount,
sum(case when o.ord_status = 999 and o.ord_ret_success_time < o.ord_success_time + 3 and
o.ord_success_time > to_date('2015-08-04 00:00:00','yyyy-mm-dd hh24:mi:ss')
and o.ord_success_time <= to_date('2015-08-05 00:00:00','yyyy-mm-dd hh24:mi:ss')then 1 else 0 end ) as threedayCancelUserCount
from t_order_info o group by o.ord_user_province) data on data.province = p.province
order by p.province ;
select round(0.0123,2) || '%' from dual;
select to_char(round(0,2),'fm990.00')|| '%' from dual;
最新文章
- Spark Streaming源码解读之No Receivers彻底思考
- discuz二次开发,分析和实现 之 向dz数据库插入自己的帖子吧
- arcgis 随手记
- Bzoj1711 [Usaco2007 Open]Dining吃饭
- Equal
- 自定义Property属性动画
- 移动端边框1px的实现
- eclipse/MyEclipse 日期格式、注释日期格式、时区问题
- MVC 5 的 EF6 Code First 入门
- 【长篇高能】ReactiveCocoa 和 MVVM 入门
- 数据库和Doctrine(转载自http://www.111cn.net/phper/332/85987.htm)
- linux 内核分析之list_head
- 程序员最常用的Linux命令
- 29. pt-table-usage
- BZOJ4259 残缺的字符串(FFT)
- ThinkPHP中where()使用方法详解
- VirtualBox修改现有VDI虚拟磁盘大小
- List<;String>;和String相互转换
- C# List用Lambda表达式排序
- SQL Server ->;>; T-SQL查询面试题之实例版