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

Question

关于筛选每天距八点时间最近数据的问题：表结构及内容如下： 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请问该如何实现？

Answer 1

是这个意思不
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;

Answer 2

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

Answer 3

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少了一个条件

Answer 4

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
;

Answer 5

这个结果和楼主要求的不一样吧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
;

Answer 6

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 
)

Answer 7

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;做了物化视图，发现不更新，请问是怎么回事？