关于筛选每天距八点时间最近数据的问题

[ahking]

关于筛选每天距八点时间最近数据的问题：

表结构及内容如下：

sj（时间） cjz(采集值) cjd（采集点）
2019/6/12 8:22:55 9 A1
2019/6/12 8:1:55 -99 A1
2019/6/12 8:22:55 9 A2
2019/6/12 8:1:55 20 A2


每个采集点每天一条记录，首先满足采集值大于0，然后满足采集点距八点最近，最终结果如下：

sj（时间） cjz(采集值) cjd（采集点）
2019/6/12 8:22:55 9 A1
2019/6/12 8:1:55 20 A2



请问该如何实现？

[AHUA1001]

是这个意思不 WITH T1 AS ( SELECT '2019/6/12 8:22:55' SJ,9 CJZ,'A1' CJD FROM DUAL UNION ALL SELECT '2019/6/12 8:1:55' SJ,-99 CJZ,'A1' CJD FROM DUAL UNION ALL SELECT '2019/6/12 8:22:55' SJ,9 CJZ,'A2' CJD FROM DUAL UNION ALL SELECT '2019/6/12 8:1:55' SJ,20 CJZ,'A2' CJD FROM DUAL ), T2 AS (SELECT TO_DATE(SJ,'YYYY/MM/DD HH24:MI:SS') SJ, CJZ, CJD FROM T1 WHERE T1.CJZ > 0), T3 AS (SELECT SJ, CJZ, CJD,ABS(SJ-TRUNC(SJ)+1/3) SJ_MIN FROM T2), T4 AS (SELECT CJD,MIN(SJ_MIN) SJ_MIN FROM T3 GROUP BY CJD) SELECT T3.SJ, T3.CJZ, T3.CJD FROM T3 JOIN T4 ON T4.CJD = T3.CJD AND T4.SJ_MIN = T3.SJ_MIN ORDER BY T3.CJD;

[一名即将失业的db]

WITH T1 AS (SELECT TO_DATE('2019/6/12 7:59:55', 'YYYY/MM/DD HH24:MI:SS') SJ, 9 CJZ, 'A1' CJD FROM DUAL UNION ALL SELECT TO_DATE('2019/6/12 8:1:55', 'YYYY/MM/DD HH24:MI:SS') SJ, -99 CJZ, 'A1' CJD FROM DUAL UNION ALL SELECT TO_DATE('2019/6/12 8:22:55', 'YYYY/MM/DD HH24:MI:SS') SJ, 9 CJZ, 'A2' CJD FROM DUAL UNION ALL SELECT TO_DATE('2019/6/12 8:1:55', 'YYYY/MM/DD HH24:MI:SS') SJ, 20 CJZ, 'A2' CJD FROM DUAL), T2 AS (SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY CJD, TRUNC(SJ) ORDER BY ABS(TO_DATE(TO_CHAR(SJ, 'YYYY/MM/DD') || '08:00:00', 'YYYY/MM/DD HH24:MI:SS') - SJ)) AS RN FROM T1) SELECT * FROM T2 WHERE T2.RN = 1

[一名即将失业的db]

SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY CJD, TRUNC(SJ) ORDER BY ABS(TO_DATE(TO_CHAR(SJ, 'YYYY/MM/DD') || '08:00:00', 'YYYY/MM/DD HH24:MI:SS') - SJ)) AS RN FROM T1 where CJZ > 0 少了一个条件

[nayi_224]

with tab1 as(
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,-99 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A2' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,20 CJZ,'A2' CJD FROM DUAL
)
select distinct t1.cjd, first_value(t1.cjz) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) 
  from tab1 t1
;

[AHUA1001]

引用 4 楼 nayi_224 的回复:

with tab1 as(
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,-99 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A2' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,20 CJZ,'A2' CJD FROM DUAL
)
select distinct t1.cjd, first_value(t1.cjz) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) 
  from tab1 t1
;

这个结果和楼主要求的不一样吧

[nayi_224]

引用 5 楼 AHUA1001 的回复:

[quote=引用 4 楼 nayi_224 的回复:]

with tab1 as(
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,-99 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A2' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,20 CJZ,'A2' CJD FROM DUAL
)
select distinct t1.cjd, first_value(t1.cjz) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) 
  from tab1 t1
;

这个结果和楼主要求的不一样吧[/quote]

with tab1 as(
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,-99 CJZ,'A1' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:22:55', 'yyyy/mm/dd hh24:mi:ss') SJ,9 CJZ,'A2' CJD FROM DUAL UNION ALL
SELECT to_date('2019/6/12 8:1:55', 'yyyy/mm/dd hh24:mi:ss') SJ,20 CJZ,'A2' CJD FROM DUAL
)
select distinct t1.cjd, 
       first_value(t1.cjz) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) ,
       first_value(t1.sj) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8))
  from tab1 t1
 where t1.cjz > 0
;

[kkkkk0lllll]

SELECT
	* 
FROM
	tab t1 
WHERE
	EXISTS (
	SELECT
		1 
	FROM
		( SELECT t2.cjd, max( sj ) maxtime FROM tab t2 WHERE sj < '2019-10-10 08:00:00' and cjz > 0 GROUP BY t2.cjd ) t3 
	WHERE
		t1.cjd = t3.cjd 
	AND t1.sj = t3.maxtime 
	)

[ahking]

必须用分析函数么？性能如何，数据量很大

[ahking]

查询时间都在30秒以上，有什么改进空间么？

[sxq129601]

引用 8 楼 ahking 的回复:

必须用分析函数么？性能如何，数据量很大

用分析函数是最简单的方式，如果速度不行尝试下写存储过程

[ahking]

create materialized view mv1 refresh force on demand start with to_date('15-11-2019 00:52:23', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1/3 as select distinct t1.cjd, first_value(t1.cjz) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) pv, first_value(t1.sj) over(partition by t1.cjd order by abs(to_char(t1.sj, 'sssss') - 3600 * 8)) pt from t1 t1 where t1.cjz> 0; 做了物化视图，发现不更新，请问是怎么回事？

[ahking]

up。。。。。。。。。。