聚合查询——有人能帮我检查一下吗?

I have the following Table:

我有下表:

DPR_TS  TIMESTAMP(6)    Y           
DPR_QOT_ID  NUMBER(10)  Y           
DPR_CLOSE   NUMBER(16,4)    Y           
DPR_OPEN    NUMBER(16,4)    Y           
DPR_HIGH    NUMBER(16,4)    Y           
DPR_LOW NUMBER(16,4)    Y           
DPR_VOLUME  NUMBER(16,4)    Y

There are many entries very frequently with same DPR_TS and different qots. DPR_LOW, DPR_HIGH, DPR_VOLUME, DPR_OPEN could be NULL.

有许多条目非常频繁地使用相同的DPR_TS和不同的qots。DPR_LOW, DPR_HIGH, DPR_VOLUME, DPR_OPEN可以是NULL。

Please see this example:

请看这个例子:

DRP_TS            DPR_QOT_ID   DPR_CLOSE  DPR_LOW
30.07.2013 09:00  1            12         12
30.07.2013 09:00  1            12         12
30.07.2013 09:00  1            12         12
30.07.2013 09:00  1            12         NULL 
31.07.2013 09:00  1            13         13
31.07.2013 09:00  1            13         13
31.07.2013 09:00  1            13         NULL 
30.07.2013 09:00  2            17         17
30.07.2013 09:00  2            18         17
30.07.2013 09:00  2            17         17
30.07.2013 09:00  2            17         17
30.07.2013 09:10  2            15         15
30.07.2013 09:10  2            15         NULL

I wish to select these entries(most recent dpr_close, min(dpr_low), max(dpr_high), max(dpr_open), max(dpr_volume), all aggregated ny dpr_qot_id and trunc(dpr_ts)) from the above table:

我希望选择这些条目(最近的dpr_close、min(dpr_low)、max(dpr_high)、max(dpr_open)、max(dpr_volume)、所有从上表聚合的ny dpr_qot_id和trunc(dpr_ts)):

    DRP_TS            DPR_QOT_ID   DPR_CLOSE  DPR_LOW
    30.07.2013 09:00  1            12         12 
    31.07.2013 09:00  1            13         13
    30.07.2013 09:10  2            15         15

I have tried this query:

我试过这个问题:

      SELECT dpr_qot_id
          ,trunc(dpr_ts)
          ,(SELECT (dpr_close)
             FROM tti_qot_load_ma t1
            WHERE t1.dpr_qot_id = (t2.dpr_qot_id)
                  AND dpr_ts = (SELECT MAX(dpr_ts)
                                  FROM tti_qot_load_ma t3
                                 WHERE t1.dpr_qot_id = t3.dpr_qot_id)
                  AND rownum = 1)
          ,MAX(t2.dpr_high)           
          ,MIN(t2.dpr_low)           
          ,MAX(t2.dpr_open)           
          ,MAX(t2.dpr_volume)    
      FROM tti_qot_load_ma t2
GROUP BY dpr_qot_id, trunc(dpr_ts)

Anyhow could someone have a look at it and tell me if it is correct? The explain plain looks like fine to me. Anyhow it takes really long when I run it. I'm not sure if my query is right. The table has many entries btw.

不管怎样，有人能看一下并告诉我它是否正确吗?对我来说，解释清楚就好了。不管怎样，当我运行它的时候需要很长时间。我不确定我的查询是否正确。这张桌子有许多条目。

1 个解决方案

#1

Please see if the following meets your needs:

请查看以下内容是否符合您的需要:

SELECT DRP_TS, MAX(DPR_QOT_ID), MIN(DPR_CLOSE), MAX(DPR_LOW)
FROM Table1 
GROUP BY DRP_TS
ORDER BY DRP_TS;

You can use the following demo to verify: SQL Fiddle.

您可以使用下面的演示来验证:SQL Fiddle。

It produces the following result:

它产生如下结果:

DRP_TS      MAX(DPR_QOT_ID) MIN(DPR_CLOSE)  MAX(DPR_LOW)
30.07.2013 09:00        2                 12    17
30.07.2013 09:10        2                 15    15
31.07.2013 09:00        1                 13    13

It seems to match yours.

它似乎和你的一样。

#1