日志数据分析之主帐号登录、登出时间过于集中

1、一个分析场景，分析某公司主账号登录的集中情况，数据源为一个月登录原始日志：

       ID    MASTERLOGINID    USERNAME    PACKAGETIME    CLIENTIP    SERVERIP    APPLICATIONID    OPERATENAME    OPERATETYPE    RETURNCODE    LOG_TYPE    ORGNAME    CONTENT

2、各个字段的意思：
日志id，主账号，用户姓名，操作时间，客户端ip，服务器ip，应用系统id，操作名称，操作类型，返回码，日志类型，组织结构，操作内容
表结构：

SQL> desc int_main_acct_login_100;
Name Type Nullable Default Comments
------------- -------------- -------- ------- --------
ID            VARCHAR2(64)
MASTERLOGINID VARCHAR2(50) Y
USERNAME      VARCHAR2(64) Y
PACKAGETIME   VARCHAR2(64) Y
CLIENTIP      VARCHAR2(32) Y
SERVERIP      VARCHAR2(256) Y
APPLICATIONID NUMBER         Y
OPERATENAME   VARCHAR2(1024) Y
OPERATETYPE   VARCHAR2(1024) Y
RETURNCODE    VARCHAR2(1024) Y
LOG_TYPE      VARCHAR2(32) Y
ORGNAME       VARCHAR2(1024) Y
CONTENT       VARCHAR2(4000) Y

SQL>

3、执行分析，查看每分钟、每天的登录数量：

select to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24'),count(t.operatetype) from int_main_acct_login_100 t group by to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
 order by to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24');
       TO_CHAR(TO_DATE(T.PACKAGETIME,    COUNT(T.OPERATETYPE)
1    2016-05-01 00    62
2    2016-05-01 01    41
3    2016-05-01 02    34
4    2016-05-01 03    36
5    2016-05-01 04    40
6    2016-05-01 05    37
7    2016-05-01 06    70
8    2016-05-01 07    469
9    2016-05-01 08    1001

4、存在的问题和解决方案：
由于部分时间（某些小时、某些分钟、某些天）没人登录，最终产生的数据的时间不连续，图表的效果不好，根据和厂商一个开发人员沟通，创建一个常量的表，通过这个表进行两个表关联左连接查询就可以了：
创建该表的话，通过存储过程来处理：
首先创建表结构：

create table yk_tb_time(mtime timestamp,pid number);

#oracle时间运算：
当前系统日期加1天：
select to_date(sysdate,'YYYY-MM-DD') +1 from dual;
当前系统日期加1分钟：
select sysdate + 1/1440 from dual;
其中1440＝1/(24*60)是一天之一分钟

对varchar类型的“时间”转成时间时（oracle中varchar类型的日期格式转换date类型 ）必须写全：to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'），要添加时分秒，不能写成to_date(t.packagetime,'yyyy-mm-dd hh24'），然后再进行to_char处理：to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
创建存储过程：

create or replace procedure pro_yk_tb_time is
i number :=0;
begin
  while i <744 loop
    --select to_date('2016-5-1 0:0:1','yyyy-mm-dd hh24:mi:ss')+i/24 into ttime from dual;
    insert into yk_tb_time values(to_date('2016-5-1 0:0:1','yyyy-mm-dd hh24:mi:ss')+i/24,yk_seq.nextval);
    i:=i+1;
  end loop;
--24 * 30 = 720,31day；
    --dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss'));
    commit;
end pro_yk_tb_time

检查：
select to_char(s.mtime,'yyyy-mm-dd hh24') from yk_tb_time s；
       TO_CHAR(S.MTIME,'YYYY-MM-DDHH2
1    2016-05-01 00
2    2016-05-01 01
3    2016-05-01 02
4    2016-05-01 03
5    2016-05-01 04
6    2016-05-01 05
7    2016-05-01 06
8    2016-05-01 07
9    2016-05-01 08

最终版的sql脚本：
我的全量、常量信息表yk_tb_time要放到前面，因为我用的是左连接，如果放后面的话，下面提到的13号那行就出不来了：

select to_char(s.mtime,'yyyy-mm-dd'),count(t.packagetime) from yk_tb_time s left join int_main_acct_login_100 t
on to_char(to_date(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd')=to_char(s.mtime,'yyyy-mm-dd') group by
to_char(s.mtime,'yyyy-mm-dd') order by to_char(s.mtime,'yyyy-mm-dd');
       TO_CHAR(S.MTIME,'YYYY-MM-DD')    COUNT(T.PACKAGETIME)
1    2016-05-01    197640
2    2016-05-02    166488
3    2016-05-03    428592
4    2016-05-04    306288
5    2016-05-05    494160
6    2016-05-06    481416
7    2016-05-07    216816

生成图表来看5月份登录数量（13号那天没人登录数据，可能其他天某些分钟也没有；如果不这么弄，那么可以12、14号就是连续的，很难看出来中间有一天没人登录，这可能是有问题的）：

之前写的以分钟为单位的图表：

根据图表，就可以看出登录频次多的时间了，设置一个阈值，就可以进一步生成分析结构了。