2 onlyyjco onlyyjco 于 2015.06.03 13:55 提问

mysql按周一到周日分段统计

图片说明
如图,表名lottery_activity_record,现在需要统计一个起止时间范围内,某个活动每周一至周日的人均数量,即每一周内的该活动(activity_Id)的总量除以人数(DISTINCT open_Id),结果四舍五入取整,存在跨年的情况.很急很急,求大神们帮助

2个回答

danielinbiti
danielinbiti   Ds   Rxr 2015.06.03 14:25
差不多下面这样
 SELECT s/n,t1.w from (
select sum(activity_id) S,w from (
select activity_id,WEEK(createtime,1) as w from lottery_activity_record
) b group by w
) t1
,
(
select count(open_id) n,w from(
select distinct open_Id,WEEK(createtime,1) as w from lottery_activity_record
) a group by w
) t2 where t1.w=t2.w
datou431
datou431   Rxr 2015.06.18 15:08

SELECT s/n,t1.w from (
select sum(activity_id) S,w from (
select activity_id,WEEK(createtime,1) as w from lottery_activity_record
) b group by w
) t1
,
(
select count(open_id) n,w from(
select distinct open_Id,WEEK(createtime,1) as w from lottery_activity_record
) a group by w
) t2 where t1.w=t2.w

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!