MySQL签到设计

2018-11-08 23:02:59
1009次阅读
1个评论
数据库设计:

CREATE TABLE `zk_sys_wx_signin` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`openid` VARCHAR(64) NOT NULL COMMENT '签到人唯一id',
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间',
`num` INT(10) UNSIGNED NOT NULL COMMENT '连续签到次数',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

 

进行签到
1.插入时判断昨天有没有进行签到,如果签到了则num在昨天的基础上+1,如果没有则设置为1
2.插入时判断今天有没有进行签到,如果没有签到则插入,如果已经签到过则不进行插入操作

INSERT INTO zk_sys_wx_signin(openid, `time`, num)
SELECT
#{openId},
#{time},
(
IFNULL(
(select num + 1 `count `
from zk_sys_wx_signin a1
where a1.openid = #{openId}
and TO_DAYS(#{time}) - TO_DAYS(a1.`time`) = 1
order by a1.`time` asc
limit 0, 1),
1)
)
FROM dual
WHERE not exists (
select 1 from zk_sys_wx_signin a2
where a2.openid = #{openId}
and TO_DAYS(#{time}) = TO_DAYS(a2.`time`)
)

现在签到的事件已经完成了,大家可以正常的愉快进行签到了,现在就要对这些数据进行一些分析来应用了。

比如这个人总计签到了多少次啊,最大连续签到了多少次啊,当月签到了多少次啊,最后一次连续签到是多少啊,都可以很简单的分析统计出来了。

比如我们来使用一个视图来简化我们的sql查询操作,我这里建立了一个视图:zk_sys_wx_signin_view

openid:用户唯一id

sum_sign_in_num:总计签到次数

max_sign_in_num:最大连续签到数

this_month_sign_in_num:这个月连续签到数

last_max_sign_in_num:最后一次连续签到数

select 
openid `open_id`,
count(*) as `sum_sign_in_num`,
max(num) as `max_sign_in_num`,
sum(IF(DATE_FORMAT(`time`,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m'),1,0))
as `this_month_sign_in_num`,
(select num from zk_sys_wx_signin t1 where t1.openid = si.openid order by t1.`time` limit 0,1)
as `last_max_sign_in_num`
from zk_sys_wx_signin si
group by si.openid
order by time desc

 

现在签到结束了,那我是第几个签到的???是第一个吗~~~

select if(
(
select count(*) from zk_sys_wx_signin t1
where t1.openid = #{openId}
and to_days(`time`) = to_days(#{time})
) = 0,
0,
(
select count(*)
from zk_sys_wx_signin
where
to_days(`time`) = to_days(#{time})
and `time` <= (
select `time`
from zk_sys_wx_signin t1
where t1.openid = #{openId}
and to_days(`time`) = to_days(#{time})
)
)
) num;


我怎么才能知道这个人某个月的签到详情呢?

select date_format(si.`time`, '%e') date
from zk_sys_wx_signin si
where si.openid = #{openId}
and date_format(si.`time`, '%Y%m') = date_format(#{time}, '%Y%m')

这样他就能返回指定月那些天进行签到啦。比如说:[1, 2, 3, 17, 18]这样的一个数组集合。

如果你有需求是希望返回的是[1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0]这种类型的话,那就在应用层做一些改动吧。比如:

public static short[] format(Integer[] data, Date time){
    Calendar cal = Calendar.getInstance();
    cal.setTimeInMillis(time.getTime());
    int dateOfMonth = cal.getActualMaximum(Calendar.DATE);
    short[] state = new short[dateOfMonth];
    for(Integer t : data){
        state[t - 1] = 1;
    }
    return state;
}

当然如果你们要int啊,boolean啊,那就按你们自己需要的需求来改动吧~
收藏00

登录 后评论。没有帐号? 注册 一个。