日常统计报表所用的Mysql语句

1：总体数据日报
   1 ：在xj_ugn_auth数据库中的 account表中  通过joindate选项统计总的注册人数！

select a.account_id from xj_ugn_character.role a where exists (select id from xj_ugn_auth.account b where a.account_id=b.id and b.platform=3);

2：渠道数据
   2 ： 在xj_ugn_auth数据库中的 account表中  通过platform 选项统计91，UC，当乐的注册人数！1：UC   2：91    3：当乐

select a.account_id from xj_ugn_character.role a where exists (select id from xj_ugn_auth.account b where a.account_id=b.id and b.platform=3) and  FLOOR(create_time/86400) = 15911;

3：等级分布
   use xj_ugn_character;  选择数据库

select level,count(1) from role group by level;

4：阵营分布  1：蓬莱   2：昆仑   3：金鳌
  use xj_ugn_character;   选择阵营所在的数据库

select sum(race=3) as total from role;  即可得到 “金鳌”的角色数
select sum(race=2) as total from role;  即可得到 “昆仑”的角色数
select sum(race=1) as total from role;  即可得到 “蓬莱”的角色数

5：付费玩家分布

select a.account_id,level from xj_ugn_character.role a where exists (select account_id from xj_payment.payment b where a.account_id=b.account_id);

6：经济系统
use xj_ugn_character;  选择数据库

select sum(gold) as total from role_money;  统计金币
select sum(ingot) as total from role_money;   统计元宝
select sum(silver) as total from role_money；  统计银币
select sum(coupon) as total from role_money;   统计元点卷
select sum(honour) as total from role_money;  统计荣誉
select sum(wingain) as total from role_money;   统计战绩

7：在线人数  查看在线人数

select from_unixtime(time),details from event_log;

8：

SELECT count(*) FROM `role` where FLOOR(create_time/86400) = 15904 and gmlevel = 0;

查的就是哪天创建角色的总数  8月8号是15925
9:统计充值的元宝

use xj_ugn_log;
select sum(ingot_amount) as total from paylog;

10:转换时间

select from_unixtime(1374200121);