elt区间分布
select DATE_FORMAT(CURDATE(),'%Y%m%d') DateId,elt(interval(curnum,0, 10000,20000,30000,40000,50000, 100000,200000,300000, 500000,1000000,2000000,3000000,5000000,10000000,30000000),
'1W以下','1W-2W','2W-3W','3W-4W','4W-5W','5W-10W', '10W-20W', '20W-30W', '30W-50W', '50W-100W','100W-200W','200W-300W','300W-500W','500W-1000W','1000W-3000W','3000W以上') as coinrange
, count(userid) as usernum,sum(curnum) sumcoin,b.addmoney,b.addmoney_all
from MonitorServer.user_coin_9041 a,
(select sum(case when daynum>=concat(DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL -1 day),'%Y%m'),'01') then money
else 0 end) addmoney,sum(money) addmoney_all from MonitorServer.user_pay_9041
where daynum<DATE_FORMAT(CURDATE(),'%Y%m%d') and type<>4) b
where updated<>0 and coinid=1 and FROM_UNIXTIME(updated)>=ADDDATE(CURDATE(),INTERVAL -31 day)
and a.userid not in(22091258,22178140,22268579,22144984,22212840,22212784,22209741,
22228179,22257539,22260231,21954080,21683531,22142028,22192965,21643645,21576370,21638291,21437986,21681971,21519260,22145626
,20989476,20989473,22268085,22151310,21703679,22239574,22388084,22387973,22388047,22424079,22425919)
group by elt(interval(curnum,0, 10000,20000,30000,40000,50000, 100000,200000,300000, 500000,1000000,2000000,3000000,5000000,10000000,30000000),
'1W以下','1W-2W','2W-3W','3W-4W','4W-5W','5W-10W', '10W-20W', '20W-30W', '30W-50W', '50W-100W','100W-200W','200W-300W','300W-500W','500W-1000W','1000W-3000W','3000W以上')
order by curnum;
最新文章
- 个人Win10 +archlinux安装笔记
- struts2框架基本操作总结
- Fedora 19 vim c语言开发环境
- Struts2，Hibernate和Spring之间的框架整合关系
- 复制构造函数2——深入理解
- S3C6410 纯粹的裸机启动,自己写的SD BOOT启动
- ajax方式提交带文件上传的表单,上传后不跳转
- JAVA基础--正则表达式
- hdu2159 FATE 经典二维背包
- vs2017中生成.Net Standard Libarary的Nuget Package
- C#设计模式(0)-设计模式系列文章导航
- ansible-play中关于标签tages，handler，notify的使用
- 软件测试人员必备网络知识（一）：什么是cookie？
- 学习3DES加密算法笔记
- Edusoho之LAMP环境搭建
- Prism框架研究(二)
- JDK8新特性04 方法引用与构造器引用
- 用单向链表实现两数倒序相加(java实现)
- JAVA基础——时间Date类型转换
- JDK5.0 特性-线程同步装置之Semaphore