SQL最佳实践

王江玉

下载了解压不了啊

kinghow

可以解压，你重新下载试试

stilllovekk

这个下载什么时候可以不再扫二维码。。。。操作越来越麻烦了

kinghow

最近遇到个问题，要求统计每天在线人数。我们拿Oracle 11g审计日志来做个演示：
先统计最近5天每天活动用户数：
  SELECT TRUNC (timestamp) AS gp, COUNT (DISTINCT USERNAME) active_user_count
    FROM dba_audit_trail
   WHERE timestamp >= SYSDATE - 5
GROUP BY TRUNC (timestamp)
ORDER BY gp DESC;

kinghow

这样只是当天有登录登出用户，如果有隔天登录登出的用户也算是在线，应该这样统计：

with v1 as (select a.USERNAME,trunc(a.timestamp) atimestamp,trunc(b.timestamp) btimestamp from

(select USERNAME,sessionid,timestamp from dba_audit_trail where entryid=1 and timestamp>SYSDATE-5) a,
(select USERNAME,sessionid,timestamp from dba_audit_trail where entryid=2 and timestamp>SYSDATE-5) b
where a.sessionid=b.sessionid),
v2 as (    SELECT TRUNC(SYSDATE-5) +LEVEL-1 gp
      FROM DUAL
CONNECT BY LEVEL<=6)
select gp,COUNT(DISTINCT USERNAME) active_user_count
from v1,v2
where v2.gp>=atimestamp and v2.gp<=btimestamp
GROUP BY gp
ORDER BY gp DESC;

这样cost比统计每天活动增加1倍，执行时间增加4倍。

kinghow

我们用数据来验证一下:

先把登录，登出隔天的sessionid找出来：

select a.sessionid,a.timestamp,b.timestamp,trunc(b.timestamp-a.timestamp)session_times from
(select USERNAME,USERHOST,sessionid,timestamp from dba_audit_trail where entryid=1 and timestamp>SYSDATE-3) a,
(select USERNAME,USERHOST,sessionid,timestamp from dba_audit_trail where entryid=2 and timestamp>SYSDATE-3) b
where a.sessionid=b.sessionid and trunc(b.timestamp-a.timestamp)>1

每天活动用户统计：

  SELECT TRUNC(timestamp) AS gp, COUNT(DISTINCT USERNAME) active_user_count
    FROM dba_audit_trail
   WHERE timestamp>=SYSDATE-5 and sessionid=10284595
GROUP BY TRUNC(timestamp)
ORDER BY gp DESC;

可以看到漏了隔天。

每天在线用户统计：

with v1 as(select a.USERNAME,trunc(a.timestamp)atimestamp,trunc(b.timestamp)btimestamp from
(select USERNAME,sessionid,timestamp from dba_audit_trail where entryid=1 and timestamp>SYSDATE-5) a,
(select USERNAME,sessionid,timestamp from dba_audit_trail where entryid=2 and timestamp>SYSDATE-5) b
where a.sessionid=b.sessionid and  a.sessionid=10284595),
v2 as (    SELECT TRUNC(SYSDATE-5) +LEVEL-1 gp
      FROM DUAL
CONNECT BY LEVEL<=6)
select gp,COUNT(DISTINCT USERNAME) active_user_count
from v1,v2
where v2.gp>=atimestamp and v2.gp<=btimestamp
GROUP BY gp
ORDER BY gp DESC;

可以看到中间天数没有活动，但是在线，也统计出来了。

kinghow

每天用户登录、登出次数
SELECT gp,
        USERNAME,
        action_name,
         COUNT(*)AScnt
    FROM (SELECT TRUNC(timestamp) AS gp, USERNAME,action_name
            FROM dba_audit_trail
           WHERE timestamp>=SYSDATE-3)
GROUP BY gp, USERNAME,action_name
ORDER BY gp DESC, USERNAME,action_name;
每10分钟用户登录登出次数
  SELECT gp, action_name,COUNT(*) AS cnt
    FROM(SELECT timestamp,
                   TRUNC(timestamp,'mi')
                - MOD(TO_CHAR(timestamp,'mi'), 10)/24 / 60
                   AS gp,
                action_name
            FROM dba_audit_trail
           WHERE timestamp>SYSDATE-1)
GROUP BY gp, action_name
ORDER BY gp DESC;

每天用户在线时长

select a.USERNAME,a.USERHOST,TRUNC(a.timestamp),sum((b.timestamp-a.timestamp))*24*60*60 "session_time(S)"from

(select USERNAME,USERHOST,sessionid,timestamp from dba_audit_trail where entryid=1 and timestamp>SYSDATE-7) a,
(select USERNAME,USERHOST,sessionid,timestamp from dba_audit_trail where entryid=2 and timestamp>SYSDATE-7) b
where a.sessionid=b.sessionid
group by a.USERNAME,a.USERHOST,TRUNC(a.timestamp)
order by a.USERNAME,a.USERHOST,TRUNC(a.timestamp)

用户在线时长直方图分布

with v1 as(select a.sessionid,(b.timestamp-a.timestamp)*24*60*60 session_times from

(select sessionid,timestamp from dba_audit_trail where entryid=1 and timestamp>SYSDATE-1) a,
(select sessionid,timestamp from dba_audit_trail where entryid=2 and timestamp>SYSDATE-1) b
where a.sessionid=b.sessionid)
  SELECT session_times_histogram * 500 Spend_Bucket, COUNT(*) aa
    FROM(  SELECT  WIDTH_BUCKET(session_times,
                                 0,
                                 5000,
                                 10)
                      AS session_times_histogram
             FROM v1)
GROUP BY session_times_histogram * 500
ORDER BY 1;

最近3天密码错误次数统计，可以看看有没有人尝试暴力破解的：

select os_username,userhost,username,count(*)

  from dba_audit_trail
where returncode = 1017 and timestamp>SYSDATE-3
group by os_username,userhost,username
having count(*)>10

SELECT *

  FROM(  SELECT username,
                userhost,
                timestamp,
                action_name
            FROM dba_audit_trail
           WHERE returncode = 1017 and username='XXX'
        ORDER BY timestamp DESC)
WHERE ROWNUM<=100

newkid

你这代码怎么都没有空格分隔？

kinghow

直接从word，toad拷贝的，编辑了一下，现在可以直接执行了。

w961

好东西啊，值得学习。。