oracle 按行和日期范围过滤 SQL 查询

Filtering SQL query by row and by date range

提问by Jason

I have a time indexed Oracle DB which I'm trying to query by date range. I also want to do data reduction in the query so I don't get overwhelmed with too much data.

我有一个时间索引的 Oracle DB，我试图按日期范围查询。我还想在查询中减少数据，这样我就不会被太多的数据淹没。

The stand alone date query (2352 rows in 0.203s):

独立日期查询（0.203 秒内 2352 行）：

select oracle_time from t_ssv_soh_packets0
where oracle_time >= TIMESTAMP '2009-01-01 00:00:00' 
AND oracle_time <= TIMESTAMP '2009-01-31 00:00:00' 

The stand along reduction query (1017 in 0.89s):

支持减少查询（1017 in 0.89s）：

select oracle_time from t_ssv_soh_packets0
where (rowid,0) in (select rowid, mod(rownum,50) from t_ssv_soh_packets0)

When I try to combine them it takes forever (48 rows in 32.547s):

当我尝试将它们组合起来时，它需要永远（32.547s 中的 48 行）：

select oracle_time from t_ssv_soh_packets0
where oracle_time >= TIMESTAMP '2009-01-01 00:00:00' 
AND oracle_time <= TIMESTAMP '2009-01-31 00:00:00' 
AND (rowid,0) in (select rowid, mod(rownum,50) from t_ssv_soh_packets0)

Obviously I'm doing something fundamentally wrong here but I don't know how else to both query by date and reduce the data.

显然，我在这里做了一些根本性的错误，但我不知道如何按日期查询并减少数据。

采纳答案by Jason

Thanks to both 'Narveson' and 'nate c' for the pointers I finally figured it out. Here is the (probably Oracle specific) query that I came up with:

感谢 'Narveson' 和 'nate c' 的指点，我终于弄明白了。这是我想出的（可能是 Oracle 特定的）查询：

select oracle_time from t_ssv_soh_packets0 where oracle_time >= TIMESTAMP '2009-01-01 00:00:00' AND oracle_time <= TIMESTAMP '2009-01-31 00:00:00' group by oracle_time, rownum having mod(rownum, 50)=0

select oracle_time from t_ssv_soh_packets0 where oracle_time >= TIMESTAMP '2009-01-01 00:00:00' AND oracle_time <= TIMESTAMP '2009-01-31 00:00:00' group by oracle_time, rownum having mod(rownum, 50)=0

This query returns 47 rows in 0.031s. The original time query had 2352 rows so that makes sense.

此查询在 0.031 秒内返回 47 行。原始时间查询有 2352 行，所以这是有道理的。

The ORAFAQhelped me get to the final solution.

该ORAFAQ帮助我得到最终的解决方案。

回答by Narveson

You are evaluating your reduction logic against rows that are not in your chosen date range.

您正在针对不在您选择的日期范围内的行评估您的减少逻辑。

Apply the reduction logic to a subquery containing your date range.

将缩减逻辑应用于包含您的日期范围的子查询。

LATER: Here's what I meant.

后来：这就是我的意思。

select oracle_time from (
  select oracle_time, rownum as limited_row_num
  from t_ssv_soh_packets0 
  where oracle_time >= TIMESTAMP '2009-01-01 00:00:00'  
  AND oracle_time <= TIMESTAMP '2009-01-31 00:00:00'  
) as time_range
where mod(limited_row_num,50) =  0

回答by nate c

Get rid of the in

摆脱 in

Why use this?:

为什么使用这个？：

select oracle_time from t_ssv_soh_packets0
where (rowid,0) in (select rowid, mod(rownum,50) from t_ssv_soh_packets0)

Your only condition is mod(rownum, 50)

你唯一的条件是 mod(rownum, 50)

select * from t where mod(rownum, 50)=0

last line should be AND mod(rownnum,50)=0not a self join with an in.

最后一行不应AND mod(rownnum,50)=0与in.

回答by a_horse_with_no_name

You can also let Oracle choose a random sample from the result by applying the SAMPLE()clause:

您还可以通过应用SAMPLE()子句让 Oracle 从结果中选择一个随机样本：

SELECT oracle_time 
FROM t_ssv_soh_packets0
WHERE ...
SAMPLE(50)

Will return randomly 50% percent of the rows

将随机返回 50%% 的行