I want to write an oracle sql query I have data table like this:

Table A
no ID       Time
1 A001  9/27/2021 3:22:42 PM
2 A002  9/27/2021 3:25:58 PM
3 A003  9/27/2021 2:40:48 PM
4 A004  9/27/2021 2:40:44 PM
5 A005  9/27/2021 2:40:46 PM
6 A006  9/27/2021 2:40:51 PM
........................................

1000 A1000  9/27/2021 2:44:38 PM
1001 A1001  9/27/2021 2:44:47 PM
1002 A1002  9/27/2021 2:44:36 PM
1003 A1003  9/27/2021 2:44:40 PM
1004 A1004  9/27/2021 2:44:43 PM
1005 A1005  9/27/2021 2:43:57 PM
............................................
     A99999999999................................

and 1 more table like this:

Table B
No       ID                 Time
1       A03        9/27/2021 2:40:51 PM
2       A05        9/27/2021 2:44:36 PM
............................................
A999........................................

know that table B is definitely in table A.How can we get 10 rows above and 10 rows down from table A for each row in table B?. currently I just use rank(), lag(), lead() and then join the 2 tables together but no desired result yet

CodePudding user response：

WITH JOINED_TABLES AS (
  SELECT "A".*, B."no" AS B_no
  , ROW_NUMBER() OVER (ORDER BY "A"."Time") AS RN
  FROM "A"
  LEFT JOIN B ON "A"."ID"=B."ID"
)
, CENTRE_RNS AS (
  SELECT RN FROM JOINED_TABLES WHERE B_no IS NOT NULL
)
SELECT J."no", J."ID", J."Time", C.RN FROM JOINED_TABLES J
LEFT JOIN CENTRE_RNS C ON J.RN BETWEEN C.RN-1 AND C.RN 1
ORDER BY "no"

SQL Fiddle here

You can edit the second last line to be -10 and 10

CodePudding user response：

From Oracle 12, you can use a LATERAL join and analytic functions.

For example, if you wanted the window of 2 rows either side:

SELECT *
FROM   TableB b
       INNER JOIN LATERAL (
         SELECT no,
                id,
                time,
                rn - MAX(match) OVER () AS window
         FROM   (
           SELECT no,
                  id,
                  time,
                  ROW_NUMBER() OVER (ORDER BY time) AS rn,
                  CASE
                  WHEN a.time = b.time
                  THEN ROW_NUMBER() OVER (ORDER BY time)
                  END AS match
           FROM   TableA a
         )
       ) a
       ON (a.window BETWEEN -2 AND 2)

Then, for the sample data:

CREATE TABLE TableA (no, ID, Time) AS
SELECT level,
       'A' || TO_CHAR(level, '000'),
       DATE '2021-09-27'   LEVEL * INTERVAL '3' SECOND
FROM   DUAL
CONNECT BY LEVEL <= 50;

CREATE TABLE TableB (no, ID, Time) AS
SELECT ROWNUM,
       ID,
       time
FROM   TableA
WHERE  no IN (6, 32);

The query outputs:

NO	ID	TIME	NO	ID	TIME	WINDOW
1	A 006	2021-09-27 00:00:18	4	A 004	2021-09-27 00:00:12	-2
1	A 006	2021-09-27 00:00:18	5	A 005	2021-09-27 00:00:15	-1
1	A 006	2021-09-27 00:00:18	6	A 006	2021-09-27 00:00:18	0
1	A 006	2021-09-27 00:00:18	7	A 007	2021-09-27 00:00:21	1
1	A 006	2021-09-27 00:00:18	8	A 008	2021-09-27 00:00:24	2
2	A 032	2021-09-27 00:01:36	30	A 030	2021-09-27 00:01:30	-2
2	A 032	2021-09-27 00:01:36	31	A 031	2021-09-27 00:01:33	-1
2	A 032	2021-09-27 00:01:36	32	A 032	2021-09-27 00:01:36	0
2	A 032	2021-09-27 00:01:36	33	A 033	2021-09-27 00:01:39	1
2	A 032	2021-09-27 00:01:36	34	A 034	2021-09-27 00:01:42	2

db<>fiddle here