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一头雾水 无从下手了 请大神赐教
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一头雾水 无从下手了 请大神赐教
select RT_T,RT_H,max(RT_V) over(partition by RT_H group by RT_T) from tablename;
SELECT RT_T,RT_H,MAX(RT_V) OVER(PARTITION BY RT_H GROUP BY RT_T) FROM TABLENAME;
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
252163 3555 2012-3-7 105.215 正常 23 7
252164 3555 2012-3-7 105.215 正常 23 7
252165 3555 2012-3-7 105.215 正常 23 7
252166 3555 2012-3-7 105.215 正常 23 7
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')
)
从当前时间开始前推24小时 这个怎么写啊
(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')
不过你的需求是需要写个过程才能实现的
WITH 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;
输出结果为:
RT_T RT_D RT_H MAX(RT_V)
1 2012-07-04 4 15 110.18
2 2012-07-04 4 23 105.18
3 2012-07-05 5 11 107.18
数据可归为:2012-7-4 11时 至 2012-7-5 11时 各小时内RT_V的峰值。
对SQL无处下手
with t as (.......) -- 是为了构造模拟数据select rt_t,rt_d, rt_h, max(rt_v) --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
--to_date 函数进行比较 来约束数据选择范围为 前24小时之内 即 不超过2012-7-5 11时的前24小时的数据,由于date加减是以天数作为计量单位,所以1代表为1天,同理等于24小时。 group by rt_t,rt_d, rt_h order by rt_d,rt_h;--group by rt_t,rt_d, rt_h 意思是 以rt_t,rt_d,rt_h为参考依据进行汇总比较然后取max,order by 意思是 以 rt_d,rt_h为参考依据做排序。
比如表A:
【ID】 【NAME】
1001 JOHN
1002 SMITH表B
【ID】 【JOB】
1002 Manager
1003 Worker想连接后变成
【ID】 【NAME】 【JOB】
1001 JOHN
1002 SMITH Manager
1003 Worker请教各位大神,多谢
借用帖子问一下,如何使用公共列的并集JOIN两个表进行查询
比如表A:【ID】 【NAME】
1001 JOHN
1002 SMITH表B【ID】 【JOB】
1002 Manager
1003 Worker想连接后变成【ID】 【NAME】 【JOB】
1001 JOHN
1002 SMITH Manager
1003 Worker
UNION
SELECT DISTINCT B.ID FROM B )
SELECT TT.ID ,A.NAME,B.JOB FROM TT LEFT JOIN A ON A.ID=TT.ID
LEFT JOIN B ON B.ID =TT.ID ORDER BY TT.ID