The Oracle (tm) Users' Co-Operative FAQ
How do I determine which row has a reciprocal value pair in a SQL Statement?
|
Author's name: Frank Zhou Author's Email:zhou328@comcast.net |
Date written: 12th December 2006 Oracle version(s): 10.2 |
|
How do I determine which row has a reciprocal value pair in a SQL Statement? |
The following SQL query pattern can be used to determine which row has a reciprocal value pair. (The extra requirement for this SQL pattern is that the timing of the reciprocal value pairs much be within 1 day of each other)
SQL create table t_r as
select 38 as v1, 63 as v2, sysdate as time from dual
union all
select 63 as v1, 38 as v2, sysdate - 1/24 as time from dual
union all
select 28 as v1, 28 as v2, sysdate as time from dual
union all
select 28 as v1, 28 as v2, sysdate - 1/24 as time from dual
union all
select 15 as v1, 49 as v2, sysdate as time from dual
union all
select 99 as v1, 99 as v2, sysdate as time from dual
union all
select 99 as v1, 99 as v2, sysdate -2 as time from dual
union all
select 77 as v1, 88 as v2, sysdate as time from dual
union all
select 77 as v1, 88 as v2, sysdate -2/24 as time from dual
Table created.
SQL> select * from t_r;
V1 V2 TIME
--- --- ---------
38 63 13-DEC-06 -------->
"T"
63 38 13-DEC-06 -------->
"T"
28 28 13-DEC-06 -------->
"T"
28 28 13-DEC-06 -------->
"T"
15 49 13-DEC-06 ->Doesn't
has a reciprocal value pair, so this is "F"
99 99 13-DEC-06 --->Date
range are not within one day, so this is "F"
99 99 11-DEC-06 --->Date
range are not within one day, so this is "F"
77 88 13-DEC-06 --->Doesn't
has a reciprocal, so this is "F"
77 88 13-DEC-06 --->Doesn't
has a reciprocal, so this is "F"
SELECT v1, v2, time,
CASE WHEN v1 = v2
THEN
CASE WHEN COUNT(*) OVER (PARTITION BY V1,V2 ORDER BY time
RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING
AND NUMTODSINTERVAL(1, 'day') FOLLOWING )>= 2
THEN 'T'
ELSE 'F' END
ELSE
CASE WHEN COUNT(*) OVER (PARTITION BY
GREATEST(V1, V2),LEAST(V1,V2) ORDER BY time
RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING
AND NUMTODSINTERVAL(1, 'day') FOLLOWING ) -
COUNT(*) OVER (PARTITION BY V1, V2 ORDER BY time
RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING
AND NUMTODSINTERVAL(1, 'day') FOLLOWING )>= 1
THEN 'T'
ELSE 'F' END
END AS Reciprocals_Flag
FROM t_r
V1 V2 TIME
Reciprocals_Flag
--- --- ---------
----------------
15 49 13-DEC-06
F
28 28 13-DEC-06
T
28 28 13-DEC-06
T
38 63 13-DEC-06
T
63 38 13-DEC-06
T
77 88 13-DEC-06
F
77 88 13-DEC-06
F
99 99 11-DEC-06
F
99 99 13-DEC-06
F
9 rows selected.
SQL> spool off
