老婆公司有个这样的需求:
查询出某游戏的用户回流信息,当用户连续两天登陆,则判定为2日回流,如果间隔一天登陆,则判定为3日回流,如果间隔5天登陆,则判定为7日回流。用户数据间隔时间短为14天(固定)。
准备数据
数据库语法和mysql一致。
创建表:
<代码>创建表用户_ ( u_id int, login_date默认时间戳current_timestamp );插入数据:
//<代码>分隔符 创建过程loop_insert () 开始 声明天int; 声明usr int; 声明mx int; 声明我int; 集天=14; 设置usr=30; 设置mx=500; 组i=1; 重复 插入用户_ (u_id login_date)值(地板((RAND () * usr)), subdate (sysdate (), (RAND() *(+ 1天)))); 我=+ 1; 直到我在=mx 最后重复; 结束//<代码>调用loop_insert();最开始我想到的是用<代码> group_concat>
<代码>选择 u_id group_concat(不同DATE_FORMAT (login_date ' % Y % m % d ') order by DATE_FORMAT (login_date, ' % Y % m % d ') desc分隔符“-”)的名称 从 用户_ GROUP BY u_id;
后来想想日期转换成整数相减是不准确的(比如跨月),而且这样显示并不能解决需求。
好吧,考虑行转列。
行转列
行转列,需要<代码>在枚举,好在日期只有14天,可以做到:
<代码>选择 u_id, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180413”然后login_date 其他” d20180413结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180414”然后login_date 其他” d20180414结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180415”然后login_date 其他” d20180415结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180416”然后login_date 其他” d20180416结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180417”然后login_date 其他” d20180417结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180418”然后login_date 其他” d20180418结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180419”然后login_date 其他” d20180419结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180420”然后login_date 其他” d20180420结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180421”然后login_date 其他” d20180421结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180422”然后login_date 其他” d20180422结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180423”然后login_date 其他” d20180423结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180424”然后login_date 其他” d20180424结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180425”然后login_date 其他” d20180425结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180426”然后login_date 其他” 作为d20180426结束 从 用户_查询结果:
按用户合并
按用户合并日期,去重,用<代码> max>
<代码>选择 u_id, 情况下 当马克斯(d20180413)="然后' 0 ' 其他的' 1 ' isZ20180413结束, 情况下 当马克斯(d20180414)="然后' 0 ' 其他的' 1 ' isZ20180414结束, 情况下 当马克斯(d20180415)="然后' 0 ' 其他的' 1 ' isZ20180415结束, 情况下 当马克斯(d20180416)="然后' 0 ' 其他的' 1 ' isZ20180416结束, 情况下 当马克斯(d20180417)="然后' 0 ' 其他的' 1 ' isZ20180417结束, 情况下 当马克斯(d20180418)="然后' 0 ' 其他的' 1 ' isZ20180418结束, 情况下 当马克斯(d20180419)="然后' 0 ' 其他的' 1 ' isZ20180419结束, 情况下 当马克斯(d20180420)="然后' 0 ' 其他的' 1 ' isZ20180420结束, 情况下 当马克斯(d20180421)="然后' 0 ' 其他的' 1 ' isZ20180421结束, 情况下 当马克斯(d20180422)="然后' 0 ' 其他的' 1 ' isZ20180422结束, 情况下 当马克斯(d20180423)="然后' 0 ' 其他的' 1 ' isZ20180423结束, 情况下 当马克斯(d20180424)="然后' 0 ' 其他的' 1 ' isZ20180424结束, 情况下 当马克斯(d20180425)="然后' 0 ' 其他的' 1 ' isZ20180425结束, 情况下 当马克斯(d20180426)="然后' 0 ' 其他的' 1 ' 作为isZ20180426结束 从 (选择 u_id, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180413”然后login_date 其他” d20180413结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180414”然后login_date 其他” d20180414结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180415”然后login_date 其他” d20180415结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180416”然后login_date 其他” d20180416结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180417”然后login_date 其他” d20180417结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180418”然后login_date 其他” d20180418结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180419”然后login_date 其他” d20180419结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180420”然后login_date 其他” d20180420结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180421”然后login_date 其他” d20180421结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180422”然后login_date 其他” d20180422结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180423”然后login_date 其他” d20180423结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180424”然后login_date 其他” d20180424结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180425”然后login_date 其他” d20180425结束, 例DATE_FORMAT (login_date, ' % Y % m % d ') 当“20180426”然后login_date 其他” 作为d20180426结束 从 用户_)t0 GROUP BY u_id给老婆的一篇文章
