当前位置：文江博客话题详情

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

发布于 2024-10-08 00:05:11 字数 800 浏览 4 评论 0 原文

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

独立日期查询（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'

独立缩减查询（0.89 秒内 1017 行）：

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

当我尝试将它们组合起来时，它需要永远（32.547 秒内 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)

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

原文

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.

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

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):

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):

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瀟灑尐姊 2024-10-15 00:05:11

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

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

之后：
这就是我的意思。

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

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

回复收藏 0 原文

白云悠悠 2024-10-15 00:05:11

摆脱 in

为什么使用这个？：

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

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

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

最后一行应该是 AND mod(rownnum,50)=0 不是 self使用 in 加入。

Get rid of the 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)

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

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

回复收藏 0 原文

可可 2024-10-15 00:05:11

您还可以通过应用 SAMPLE() 子句：

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

将随机返回 50% 的行

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

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

Will return randomly 50% percent of the rows

回复收藏 0 原文

阳光下的泡沫是彩色的 2024-10-15 00:05:11

感谢“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, rownumhaving mod(rownum, 50)=0

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

ORAFAQ 帮助我找到了最终的解决方案。