oracle 数据处理

没空写具体的语句，给你个思路：
把每个ID所有的时间虚构出来，也就是将ID表和时间表(select 开始时间+5/24*rownum from dual connect by rownum<=(结束时间-开始时间)*24/5)做个笛卡尔连接。
然后查询出这个虚构的表的每条记录的时间对应你上面表中，ID相同而时间最近的那条的数据，就ok了

好久不见:)SQL> SELECT id1, id2, to_char(datetime,'yyyymmdd HH24:MI:SS') datetime FROM idle_test;

       ID1        ID2 DATETIME
---------- ---------- -----------------
       345        343 20130801 08:00:00
       340        346 20130801 08:35:00
       340        347 20130801 08:45:00
       338        349 20130801 08:55:00
       336        349 20130801 09:00:00
       336        349 20130801 09:05:00
       335        349 20130801 09:10:00
       334        349 20130801 09:15:00
       333        349 20130801 09:20:00

9 rows selected
SQL> SELECT id1, id2, to_char(tmpdatetime,'yyyymmdd HH24:MI:SS') datetime
  2    FROM (SELECT id1, id2, tdatetime, tmpdatetime, row_number() over(PARTITION BY tmpdatetime ORDER BY tdatetime DESC) rn
  3             FROM (SELECT t.id1, t.id2, t.datetime AS tdatetime, tmp.datetime AS tmpdatetime
  4                      FROM idle_test t,
  5                           (SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime
  6                               FROM dual
  7                             CONNECT BY rownum <= 1 + (to_date('2013/8/1 9:20:00', 'yyyy/mm/dd HH24:MI;SS') -
  8                                        to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) tmp)
  9            WHERE tdatetime <= tmpdatetime)
10   WHERE rn = 1
11   ORDER BY tmpdatetime;

       ID1        ID2 DATETIME
---------- ---------- -----------------
       345        343 20130801 08:00:00
       345        343 20130801 08:05:00
       345        343 20130801 08:10:00
       345        343 20130801 08:15:00
       345        343 20130801 08:20:00
       345        343 20130801 08:25:00
       345        343 20130801 08:30:00
       340        346 20130801 08:35:00
       340        346 20130801 08:40:00
       340        347 20130801 08:45:00
       340        347 20130801 08:50:00
       338        349 20130801 08:55:00
       336        349 20130801 09:00:00
       336        349 20130801 09:05:00
       335        349 20130801 09:10:00
       334        349 20130801 09:15:00
       333        349 20130801 09:20:00

17 rows selected

好久不见:)SQL> SELECT id1, id2, to_char(datetime,'yyyymmdd HH24:MI:SS') datetime FROM idle_test;

       ID1        ID2 DATETIME
---------- ---------- -----------------
       345        343 20130801 08:00:00
       340        346 20130801 08:35:00
       340        347 20130801 08:45:00
       338        349 20130801 08:55:00
       336        349 20130801 09:00:00
       336        349 20130801 09:05:00
       335        349 20130801 09:10:00
       334        349 20130801 09:15:00
       333        349 20130801 09:20:00

9 rows selected
SQL> SELECT id1, id2, to_char(tmpdatetime,'yyyymmdd HH24:MI:SS') datetime
  2    FROM (SELECT id1, id2, tdatetime, tmpdatetime, row_number() over(PARTITION BY tmpdatetime ORDER BY tdatetime DESC) rn
  3             FROM (SELECT t.id1, t.id2, t.datetime AS tdatetime, tmp.datetime AS tmpdatetime
  4                      FROM idle_test t,
  5                           (SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime
  6                               FROM dual
  7                             CONNECT BY rownum <= 1 + (to_date('2013/8/1 9:20:00', 'yyyy/mm/dd HH24:MI;SS') -
  8                                        to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) tmp)
  9            WHERE tdatetime <= tmpdatetime)
10   WHERE rn = 1
11   ORDER BY tmpdatetime;

       ID1        ID2 DATETIME
---------- ---------- -----------------
       345        343 20130801 08:00:00
       345        343 20130801 08:05:00
       345        343 20130801 08:10:00
       345        343 20130801 08:15:00
       345        343 20130801 08:20:00
       345        343 20130801 08:25:00
       345        343 20130801 08:30:00
       340        346 20130801 08:35:00
       340        346 20130801 08:40:00
       340        347 20130801 08:45:00
       340        347 20130801 08:50:00
       338        349 20130801 08:55:00
       336        349 20130801 09:00:00
       336        349 20130801 09:05:00
       335        349 20130801 09:10:00
       334        349 20130801 09:15:00
       333        349 20130801 09:20:00

17 rows selected

谢谢哦，但是能否优化下，我用这个去查询我的表的时候，查询一天的时间的数据，也就是24小时，所用时间为5秒多，感觉时间有点长
O(∩_∩)O谢谢

本帖最后由 wildwave 于 2014-04-28 10:30:04 编辑

那么试试这个。
SELECT SUM(id1) over(PARTITION BY cnt) id1, SUM(id2) over(PARTITION BY cnt) id2, datetime
  FROM (SELECT t.id1, t.id2, dt.datetime, COUNT(t.id1) over(ORDER BY dt.datetime) cnt
           FROM idle_test t,
                (SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime
                    FROM dual
                  CONNECT BY rownum <= 1 + (to_date('2013/8/1 9:20:00', 'yyyy/mm/dd HH24:MI;SS') -
                             to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) dt
          WHERE dt.datetime = t.datetime(+));

img=http://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/003/monkey/16.gif][/img]

那么试试这个。
SELECT SUM(id1) over(PARTITION BY cnt) id1, SUM(id2) over(PARTITION BY cnt) id2, datetime
  FROM (SELECT t.id1, t.id2, dt.datetime, COUNT(t.id1) over(ORDER BY dt.datetime) cnt
           FROM idle_test t,
                (SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime
                    FROM dual
                  CONNECT BY rownum <= 1 + (to_date('2013/8/1 9:20:00', 'yyyy/mm/dd HH24:MI;SS') -
                             to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) dt
          WHERE dt.datetime = t.datetime(+));
   SELECT SUM(p_z) over(PARTITION BY cnt) p_z, SUM(l_z) over(PARTITION BY cnt) l_z, datetime
FROM (SELECT t.stcd,t.p_z, t.l_z,t.tm,dt.datetime, COUNT(t.p_z) over(ORDER BY dt.datetime) cnt FROM st_river_r t,
(SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime FROM dual CONNECT BY rownum <= 1 + (to_date('2013/8/2 8:00:00', 'yyyy/mm/dd HH24:MI;SS') -
to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) dt WHERE dt.datetime= t.tm(+) and t.stcd = 1)这个是我按照你给的这种方式修改的，最后得到的结果等同于下面这个查询select p_z,l_z,tm from st_river_r where stcd = 1 and tm between to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') and to_date('2013/8/2 8:00:00', 'yyyy/mm/dd HH24:MI;SS')
是我哪里出错了么

因为t.stcd = 1 这个条件位置不对，导致外连接失效了，需要放到里面去，这样除了可以保证数据准确性外还可以让两表join时的数据量小一些。SELECT SUM(p_z) over(PARTITION BY cnt) p_z, SUM(l_z) over(PARTITION BY cnt) l_z, datetime
  FROM (SELECT t.stcd, t.p_z, t.l_z, t.tm, dt.datetime, COUNT(t.p_z) over(ORDER BY dt.datetime) cnt
           FROM (SELECT *
                    FROM st_river_r
                   WHERE stcd = 1
                     AND tm BETWEEN to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') AND
                         to_date('2013/8/2 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) t,
                (SELECT to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS') + (rownum - 1) / 24 / 12 datetime
                    FROM dual
                  CONNECT BY rownum <= 1 + (to_date('2013/8/2 8:00:00', 'yyyy/mm/dd HH24:MI;SS') -
                             to_date('2013/8/1 8:00:00', 'yyyy/mm/dd HH24:MI;SS')) * 12 * 24) dt
          WHERE dt.datetime = t.tm(+))

yyyy/mm/dd HH24:MI;SS 注意分号改冒号

非常感谢，我还想问个问题，就是我在显示数据的时候是以从今天的8点到明天的8点来显示数据的，但是在显示的时候，可能我查询某天的数据，到了那天的19点之后就没有数据了，那么使19点到第二天八点的p_z,l_z里面的值空着这样的怎么弄？

最外层选择时加一个case when 判断就可以了，比如SELECT ( CASE WHEN to_char(datetime,'HH24') BETWEEN 8 AND 18 THEN SUM(p_z) over(PARTITION BY cnt) ELSE 0 END ) p_z, ...

#16已经写出来第一个字段了阿，仿造写出第二个就可以了。关于起始日期值为空，可以先单独查出一个符合的日期，然后使用用union all合并得到结果。

调试易

oracle 数据处理

解决方案 »