常用sql语句

mysql 单表重复记录统计

select count(*) as repeat_count,a.u_id from user_blind_box as a  group by a.u_id having repeat_count = 28

日活/月活

select log.*,count(DISTINCT(u_id)) as total,DATE_FORMAT(time,'%Y-%m-%d') as day from log GROUP BY day;

# time为时间戳的情况，转为日期格式在查询
select log.*,count(DISTINCT(u_id)) as total,DATE_FORMAT(from_unixtime(time, '%Y-%m-%d %H:%i:%S'),'%Y-%m-%d') as day from log GROUP BY day;

# 字段减8小时
SELECT COUNT(*) as sum, DATE_FORMAT(DATE_SUB(time_format,INTERVAL 8 HOUR),'%Y-%m-%d') as day from history_crypto GROUP BY day;

多条sql合并为一条

select day,sum(pv) as 'PV',sum(uv) as 'UV',sum(a) as '交互用户数',sum(b) as '当日新增用户数',sum(c) as '新增openid',sum(d) as '新增手机号',sum(e) as '新增订阅消息数' 
 from (
(SELECT date_format( `created_at`,'%Y-%m-%d') as day,count(id) pv,count( DISTINCT uid) uv,'' as 'a','' as 'b','' as 'c','' as 'd','' as 'e' from `account_log` as log GROUP BY day ) union all  
(SELECT date_format( `created_at`,'%Y-%m-%d') as day ,'' as 'pv','' as 'uv',count(u.id) as 'a','' as 'b','' as 'c','' as 'd','' as 'e' from  `account` as u where u.has_auth_privacy = 1 GROUP BY day) union all 
(SELECT date_format( `created_at`,'%Y-%m-%d') as day ,'' as 'pv','' as 'uv','' as 'a',count(u.id) as 'b','' as 'c','' as 'd','' as 'e'  from  `account` as u GROUP BY day) union all 
(SELECT date_format( `created_at`,'%Y-%m-%d') as day ,'' as 'pv','' as 'uv','' as 'a','' as 'b',count(u.id) as 'c','' as 'd','' as 'e' from  `account` as u where u.is_new_openid=1 GROUP BY day) union  all 
(SELECT date_format( `created_at`,'%Y-%m-%d') as day ,'' as 'pv','' as 'uv','' as 'a','' as 'b','' as 'c',count(u.id) as 'd','' as 'e' from  `account` as u where u.is_new_openid=1 and `phone` <> '' GROUP BY day) union  all 
(SELECT date_format( `created_at`,'%Y-%m-%d') as day ,'' as 'pv','' as 'uv','' as 'a','' as 'b','' as 'c','' as 'd',count(u.id) as 'e' from  `account` as u where u.is_subscribe_nft=1 GROUP BY day)
) as test  where day <> '' GROUP BY day;