sql 百分悬赏-Oracle教程-爱易网页

sql 百分悬赏

日期：2014-05-17　浏览次数：20966 次

求一个sql 百分悬赏
ID RT_ID RT_GID RT_T RT_V RT_S RT_H RT_D
252159 3555 2012-3-7 105.18 正常 23 7
252160 3555 2012-3-7 105.18 正常 23 7
252161 3555 2012-3-7 105.18 正常 23 7
252162 3555 2012-3-7 105.18 正常 23 7
这是部分数据来源是某仪器每6秒抓取一个数据即 RT_V 现在需要取的数据是当前系统时间前推24小时取每小时里RT_V的峰值
其中 RT_H 即RT_hour 是小时数 RT_D是RT_day 是年月日中的日

还有要求是可以手动输入开始结束时间点并取这段时间内的数据（两时间点之间时间过长的话可以按天取数据取一天的峰值）
这个SQL一头雾水无从下手了请大神赐教

------解决方案--------------------
为了方便测试，只是简单的模拟了你的数据，只做了往前推24小时的，你要求的输入一段时间的逻辑同样可以参考一下...

SQL code

WITH t AS(
SELECT '2012-3-5' RT_T,115.18 RT_V,20 RT_H FROM dual
UNION ALL 
SELECT '2012-3-5',115.18,20 FROM dual
UNION ALL 
SELECT '2012-3-6',105.18,23 FROM dual
UNION ALL 
SELECT '2012-7-4',106.00,09 FROM dual
UNION ALL 
SELECT '2012-7-4',107.18,09 FROM dual
UNION ALL 
SELECT '2012-7-4',110.18,15 FROM dual
UNION ALL 
SELECT '2012-7-4',103.18,15 FROM dual
UNION ALL 
SELECT '2012-7-4',108.18,15 FROM dual
UNION ALL 
SELECT '2012-7-4',105.18,23 FROM dual
UNION ALL 
SELECT '2012-7-5',105.18,11 FROM dual
UNION ALL 
SELECT '2012-7-5',107.18,11 FROM dual
)
SELECT rt_t,rt_h,Max(rt_v) FROM 
(
  SELECT * FROM t WHERE To_Char(To_Date(rt_t||' '||RT_H),'yyyymmddhh24')>=To_Char(SYSDATE-1,'yyyymmddhh24')
) GROUP BY rt_t,rt_h ORDER BY rt_t,rt_h;
或者
SELECT DISTINCT rt_t,rt_h,Max(rt_v) over (PARTITION BY rt_t,rt_h ORDER BY rt_t,rt_h) AS MAX_V FROM 
(
 SELECT * FROM t WHERE To_Char(To_Date(rt_t||' '||RT_H),'yyyymmddhh24')>=To_Char(SYSDATE-1,'yyyymmddhh24')
)

------解决方案--------------------

探讨
没人来回回答了吗 RT_GID 这个是空字段 其余的都对应好了啊 这样吧 从当前时间开始前推24小时 这个怎么写 每个小时里的峰值可以用取得的时间结合 RT_H RT_D 这俩字段来写 
从当前时间开始前推24小时 这个怎么写啊


------解决方案--------------------

with tmp_a as
  (SELECT '2012-3-7' rt_t, 105.8 rt_v, 23 rt_h
   FROM dual
 union all
 SELECT '2012-3-7' rt_t, 105.8 rt_v, 23 rt_h
   FROM dual
 union all
 SELECT '2012-3-7' rt_t, 105.8 rt_v, 23 rt_h
   FROM dual
 union all
 SELECT '2012-3-6' rt_t, 103.8 rt_v, 23 rt_h FROM dual
 union all
 SELECT '2012-3-6' rt_t, 102.8 rt_v, 23 rt_h FROM dual
 union all
 SELECT '2012-3-5' rt_t, 105.8 rt_v, 23 rt_h FROM dual
 union all
 SELECT '2012-3-5' rt_t, 105.8 rt_v, 23 rt_h FROM dual)
SELECT to_date(rt_T || ' ' || rt_h || ':00:00', 'yyyy-mm-dd hh24:mi:ss'),
      max(rt_v) --over(partition by to_date(rt_T || ' ' || rt_h || ':00:00', 'yyyy-mm-dd hh24:mi:ss'))
 FROM TMP_A
WHERE to_date(rt_T || ' ' || rt_h || ':00:00', 'yyyy-mm-dd hh24:mi:ss') < TRUNC(SYSDATE,'HH24') - 1/24
GROUP BY to_date(rt_T || ' ' || rt_h || ':00:00', 'yyyy-mm-dd hh24:mi:ss')
不过你的需求是需要写个过程才能实现的

------解决方案--------------------

借用一下楼上数据，鉴于楼主并未交代数据类型，冒昧的将RT_T 推测为date类型，将输入的时间假设为：2012-7-5 11时，实现SQL如下：
SQL codeWITH t AS(
SELECT date'2012-3-5' RT_T,115.18 RT_V,20 RT_H,5 RT_D FROM dual
UNION ALL 
SELECT date'2012-3-5',115.18,20,5 FROM dual
UNION ALL 
SELECT date'2012-3-6',105.18,23,6 FROM dual
UNION ALL 
SELECT date'2012-7-4',106.00,09,4 FROM dual
UNION ALL 
SELECT date'2012-7-4',107.18,09,4 FROM dual
UNION ALL 
SELECT date'2012-7-4',110.18,15,4 FROM dual
UNION ALL 
SELECT date'2012-7-4',103.18,15,4 FROM dual
UNION ALL 
SELECT date'2012-7-4',108.18,15,4 FROM dual
UNION ALL 
SELECT date'2012-7-4',105.18,23,4 FROM dual
UNION ALL 
SELECT date'2012-7-5',105.18,11,5 FROM dual
UNION ALL 
SELECT date'2012-7-5',107.18,11,5 FROM dual
)
select rt_t,rt_d, rt_h, max(rt_v)
  from t
 where to_date('2012-7-5 11', 'yyyy-mm-dd hh') -
       to_date(to_char(rt_t, 'yyyy-mm-dd') || ' ' || rt_h,'yyyy-mm-dd hh24') <= 1
 group by rt_t,rt_d, rt_h order by rt_d,rt_h;

------解决方案--------------------

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

sql 百分悬赏

相关资料更多>

推荐阅读更多>