菜鸟高分跪求高性能的Sql语句

Question

本帖最后由 fangaiwen 于 2011-07-03 16:50:28 编辑

Answer 1

建议，每天的增量进行汇总，插入汇总表，然后统计展现的时候，直接SUM

Answer 2

SQL> 
SQL> with t as(
  2       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  3       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-13','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  4       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  5       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-16','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  6       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-15','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  7       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  8       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-19','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  9       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual)
 10  select v,toolname,edt-sdt+1 online_day from (
 11         select distinct v,toolname,min(sdt) sdt,max(edt) edt
 12         from t
 13         group by v,toolname)
 14  order by v
 15  /
 
         V TOOLNAME  ONLINE_DAY
---------- --------- ----------
         6 myeclipse          3
       6.5 myeclipse          5
         7 myeclipse          8

Answer 3

startdate enddate version toolname2011-06-12        6.0 myeclipse2011-06-13        6.0 myeclipse
差不多接进了，还有个小问题，当结束时间没有时，或异常关闭时，enddate为null 的情况下怎么统计。我还忽略了一个问题，给出的时间是连续的。但也有不连续使用的时候。如下情况时怎么统计。结果是：17天。
之前可能没有很清楚的描述问题，请高手再次赐教。startdate       enddate    version toolname2011-03-01      2011-03-03 6.0 myeclipse   2011-06-10      2011-06-11 6.0 myeclipse   2011-06-11      2011-06-12 6.0 myeclipse   2011-06-12                 6.0 myeclipse2011-06-15      2011-06-18 6.0 myeclipse2011-06-22      2011-06-28 6.0 myeclipse

Answer 4

startdate enddate version toolname2011-03-01 null   6.0 myeclipse 

Answer 5

enddate为null是什么意思？比如：
2011-06-12 null       6.0 myeclipse2011-06-15 2011-06-18 6.0 myeclipse2011-06-22 2011-06-28 6.0 myeclipse
如果enddate为null，那么后面的记录的startdate也应该为null，
toolname只要打开之后，要么关闭，要么继续使用，何来的不关闭，然后再次打开?
如果不是同一台机器，那么应该有各台机器的标识，如果是同一台机器，那么你的表数据就有问题

Answer 6

结束时间为NULL，怎么样知道程序是在什么时候出现的异常，这个问题不应该是这个层面的处理，而是让写入这个表的程序在异常的时候写入异常时间才对。
SELECT toolname, version, end_date - start_date + 1
  FROM (SELECT toolname,
               version,
               MIN(startdate) start_date,
               MAX(enddate) end_date
          FROM table_name
         GROUP BY toolname, version);

Answer 7

还有一种写法可以写的，有个partition的方式，效率比group by 要高，可惜忘啦。

Answer 8

enddate为null时可能是异常关闭时没处理好，正如7楼所说的。当enddate为Null时，以startdate那一天此，记录为1。
如：2011-06-12 null 6.0 myeclipse  ，结果为1。
    2011-06-12 2011-06-14 6.0 myeclipse  和上面的带Null的那一行数据结果为3。
像这样能统计么。不知是不是符合逻辑。但领导这样要求，做小的木有办法啊。请高手继续赐教，谢谢。

Answer 9

SQL> 
SQL> with a as(
  2       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  3       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-13','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  4       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  5       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-16','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  6       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-15','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  7       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  8       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-19','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  9       select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
 10       select to_date('2011-03-01','yyyy-mm-dd') sdt,to_date('2011-03-03','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 11       select to_date('2011-06-10','yyyy-mm-dd') sdt,to_date('2011-06-11','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 12       select to_date('2011-06-11','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 13       select to_date('2011-06-12','yyyy-mm-dd') sdt,null,6.0 v,'myeclipse' toolname from dual union all
 14       select to_date('2011-06-15','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 15       select to_date('2011-06-22','yyyy-mm-dd') sdt,to_date('2011-06-28','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual)
 16  ,b as(select v,toolname,sdt,edt,lag(sdt) over (order by sdt,edt) ssdt,lag(edt) over (order by sdt,edt) eedt
 17        from a
 18        order by v)
 19  select c.v,c.toolname,nvl(c.cnt,0)+nvl(d.cnt,0)+nvl(e.cnt,0) cnt
 20  from (
 21        select v,toolname,max(edt)-min(sdt)+1 cnt from (
 22        select * from b
 23        where eedt>=sdt and eedt<=edt)
 24        group by v,toolname) c,
 25       (
 26        select v,toolname,sum(edt-sdt+1) cnt from (
 27        select * from b
 28        where eedt<sdt
 29           or ssdt is null
 30           or eedt is null)
 31        group by v,toolname) d,
 32       (
 33        select v,toolname,sum(1) cnt from (
 34        select * from b
 35        where edt is null)
 36        group by v,toolname) e
 37  where c.v=d.v(+)
 38    and c.v=e.v(+)
 39  /
 
         V TOOLNAME         CNT
---------- --------- ----------
         6 myeclipse         21
       6.5 myeclipse          5
         7 myeclipse          8
 
SQL> 

Answer 10

--edt 为 null 的版本以及在线天数：
         V TOOLNAME      SUM(1)
---------- --------- ----------
         6 myeclipse          1
--连续在线的版本以及在线天数：
         V TOOLNAME  MAX(EDT)-MIN(SDT)+1
---------- --------- -------------------
         6 myeclipse                   4
         7 myeclipse                   8
       6.5 myeclipse                   5
--不连续在线的版本以及在线天数：
         V TOOLNAME  SUM(EDT-SDT+1)
---------- --------- --------------
         6 myeclipse             16

Answer 11

上面或许也未能满足你的需求，
如果连续在线的版本中version不齐全，那么就不能够用左连接，
1.应先union all然后再sum相同版本的数据，
2.这样才能够得到结果
3.最重要的是，别人不清楚你遇到的具体情况以及数据，关键参照别人的思路，自己谋出路

Answer 12

SQL> desc t;
Name      Type    Nullable Default Comments 
--------- ------- -------- ------- -------- 
STARTDATE DATE    Y                         
ENDDATE   DATE    Y                         
VERSION   CHAR(3) Y                         
TOOLNAME  CHAR(9) Y                         
 
SQL> select * from t;
 
STARTDATE   ENDDATE     VERSION TOOLNAME
----------- ----------- ------- ---------
2011-3-1    2011-3-3    6.0     myeclipse
2011-6-10   2011-6-11   6.0     myeclipse
2011-6-11   2011-6-12   6.0     myeclipse
2011-6-12               6.0     myeclipse
2011-6-15   2011-6-18   6.0     myeclipse
2011-6-22   2011-6-28   6.0     myeclipse
2011-6-12   2011-6-16   6.5     myeclipse
2011-6-12   2011-6-15   6.5     myeclipse
2011-6-12   2011-6-14   7.0     myeclipse
2011-6-12   2011-6-19   7.0     myeclipse
2011-6-12   2011-6-18   7.0     myeclipse
 
11 rows selected
 
SQL> 
SQL> SELECT t.version, t.toolname, COUNT(DISTINCT t1.column_value) cnt
  2    FROM t,
  3         TABLE(CAST(MULTISET
  4                    (SELECT to_char(t.startdate + LEVEL - 1, 'yyyy-mm-dd')
  5                       FROM dual
  6                     CONNECT BY LEVEL <= nvl(t.enddate,t.startdate) - t.startdate + 1) AS
  7                    sys.odcivarchar2list)) t1
  8   GROUP BY t.toolname, t.version;
 
VERSION TOOLNAME         CNT
------- --------- ----------
6.0     myeclipse         17
6.5     myeclipse          5
7.0     myeclipse          8
 
SQL> 

Answer 13

with a as(
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-13','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-16','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-15','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-19','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
select to_date('2011-03-01','yyyy-mm-dd') sdt,to_date('2011-03-03','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-10','yyyy-mm-dd') sdt,to_date('2011-06-11','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-11','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-12','yyyy-mm-dd') sdt,null,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-15','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
select to_date('2011-06-22','yyyy-mm-dd') sdt,to_date('2011-06-28','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual
),t1 as(
select sdt,nvl(edt,lead(sdt)over(partition by v order by sdt)) next_sdt,edt,v,toolname
  from a
)select v,toolname,sum((trunc(next_sdt)-trunc(sdt)))
   from t1
 group by v,toolname
 order by v;

Answer 14

[SYS@orcl] SQL>with a as(
  2  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  3  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-13','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  4  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
  5  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-16','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  6  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-15','yyyy-mm-dd') edt,6.5 v,'myeclipse' toolname from dual union all
  7  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-14','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  8  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-19','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
  9  select to_date('2011-06-12','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,7.0 v,'myeclipse' toolname from dual union all
 10  select to_date('2011-03-01','yyyy-mm-dd') sdt,to_date('2011-03-03','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 11  select to_date('2011-06-10','yyyy-mm-dd') sdt,to_date('2011-06-11','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 12  select to_date('2011-06-11','yyyy-mm-dd') sdt,to_date('2011-06-12','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 13  select to_date('2011-06-12','yyyy-mm-dd') sdt,null,6.0 v,'myeclipse' toolname from dual union all
 14  select to_date('2011-06-15','yyyy-mm-dd') sdt,to_date('2011-06-18','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual union all
 15  select to_date('2011-06-22','yyyy-mm-dd') sdt,to_date('2011-06-28','yyyy-mm-dd') edt,6.0 v,'myeclipse' toolname from dual
 16  ),t1 as(
 17  select sdt,nvl(edt,lead(sdt)over(partition by v order by sdt)) next_sdt,edt,v,toolname
 18    from a
 19  )select v,toolname,sum((trunc(next_sdt)-trunc(sdt)))
 20     from t1
 21   group by v,toolname
 22   order by v;         V TOOLNAME  SUM((TRUNC(NEXT_SDT)-TRUNC(SDT)))
---------- --------- ---------------------------------
         6 myeclipse                                16
       6.5 myeclipse                                 7
         7 myeclipse                                15

Answer 15

兄弟多谢了，偶才学比较浅，TABLE(CAST(MULTISET 这种语法目前还没用过。不过非常的感谢。