how to use windows function during merge in sql-CodePudding

I am working in oracle sql. I have two table which is linked to each other by one column - company_id (see on the picture); I want to merge table 1 to table 2 and calculate 6 month average (6 month before period from table 2) of income for each company_id and each date of table2. I appreciate any code/idea how to solve this task.

CodePudding user response：

You can use an analytic range window to calculate the averages for table1 and then JOIN the result to table2:

SELECT t2.*,
       t1.avg_income_6,
       t1.avg_income_12
FROM   table2 t2
       LEFT OUTER JOIN (
         SELECT company_id,
                dt,
                ROUND(AVG(income) OVER (
                  PARTITION BY company_id
                  ORDER BY dt
                  RANGE BETWEEN INTERVAL '5' MONTH PRECEDING
                            AND INTERVAL '0' MONTH FOLLOWING
                ), 2) AS avg_income_6,
                ROUND(AVG(income) OVER (
                  PARTITION BY company_id
                  ORDER BY dt
                  RANGE BETWEEN INTERVAL '11' MONTH PRECEDING
                            AND INTERVAL '0' MONTH FOLLOWING
                ), 2) AS avg_income_12
         FROM   table1
       ) t1
       ON (t2.company_id = t1.company_id AND t2.dt = t1.dt);

Which, for the sample data:

CREATE TABLE table1 (company_id, dt, income) AS
  SELECT 1, date '2019-01-01', 65 FROM DUAL UNION ALL
  SELECT 1, date '2019-02-01', 58 FROM DUAL UNION ALL
  SELECT 1, date '2019-03-01', 12 FROM DUAL UNION ALL
  SELECT 1, date '2019-04-01', 81 FROM DUAL UNION ALL
  SELECT 1, date '2019-05-01', 38 FROM DUAL UNION ALL
  SELECT 1, date '2019-06-01', 81 FROM DUAL UNION ALL
  SELECT 1, date '2019-07-01', 38 FROM DUAL UNION ALL
  SELECT 1, date '2019-08-01', 69 FROM DUAL UNION ALL
  SELECT 1, date '2019-09-01', 54 FROM DUAL UNION ALL
  SELECT 1, date '2019-10-01', 90 FROM DUAL UNION ALL
  SELECT 1, date '2019-11-01', 10 FROM DUAL UNION ALL
  SELECT 1, date '2019-12-01', 12 FROM DUAL UNION ALL
  SELECT 1, date '2020-01-01', 11 FROM DUAL UNION ALL
  SELECT 1, date '2020-02-01', 83 FROM DUAL UNION ALL
  SELECT 1, date '2020-03-01', 18 FROM DUAL UNION ALL
  SELECT 1, date '2020-04-01', 28 FROM DUAL UNION ALL
  SELECT 1, date '2020-05-01', 52 FROM DUAL UNION ALL
  SELECT 1, date '2020-06-01', 21 FROM DUAL UNION ALL
  SELECT 1, date '2020-07-01', 54 FROM DUAL UNION ALL
  SELECT 1, date '2020-08-01', 30 FROM DUAL UNION ALL
  SELECT 1, date '2020-09-01', 12 FROM DUAL UNION ALL
  SELECT 1, date '2020-10-01', 25 FROM DUAL UNION ALL
  SELECT 1, date '2020-11-01', 86 FROM DUAL UNION ALL
  SELECT 1, date '2020-12-01',  4 FROM DUAL UNION ALL
  SELECT 1, date '2021-01-01', 10 FROM DUAL UNION ALL
  SELECT 1, date '2021-02-01', 72 FROM DUAL UNION ALL
  SELECT 1, date '2021-03-01', 65 FROM DUAL UNION ALL
  SELECT 1, date '2021-04-01', 25 FROM DUAL;

CREATE TABLE table2 (company_id, dt) AS
  SELECT 1, date '2019-06-01' FROM DUAL UNION ALL
  SELECT 1, date '2019-09-01' FROM DUAL UNION ALL
  SELECT 1, date '2019-12-01' FROM DUAL UNION ALL
  SELECT 1, date '2020-01-01' FROM DUAL UNION ALL
  SELECT 1, date '2020-07-01' FROM DUAL UNION ALL
  SELECT 1, date '2020-08-01' FROM DUAL UNION ALL
  SELECT 1, date '2021-03-01' FROM DUAL UNION ALL
  SELECT 1, date '2021-04-01' FROM DUAL;

Outputs:

COMPANY_ID DT AVG_INCOME_6 AVG_INCOME_12

1 2019-06-01 00:00:00 55.83 55.83

1 2019-09-01 00:00:00 60.17 55.11

1 2019-12-01 00:00:00 45.5 50.67

1 2020-01-01 00:00:00 41 46.17

1 2020-07-01 00:00:00 42.67 41.83

1 2020-08-01 00:00:00 33.83 38.58

1 2021-03-01 00:00:00 43.67 38.25

1 2021-04-01 00:00:00 43.67 38

COMPANY_ID	DT	AVG_INCOME_6	AVG_INCOME_12
1	2019-06-01 00:00:00	55.83	55.83
1	2019-09-01 00:00:00	60.17	55.11
1	2019-12-01 00:00:00	45.5	50.67
1	2020-01-01 00:00:00	41	46.17
1	2020-07-01 00:00:00	42.67	41.83
1	2020-08-01 00:00:00	33.83	38.58
1	2021-03-01 00:00:00	43.67	38.25
1	2021-04-01 00:00:00	43.67	38

db<>fiddle here

CodePudding user response：

I don't think you need any window function here (if you were thinking of analytic functions); ordinary avg with appropriate join conditions should do the job.

Sample data:

SQL> with
  2  table1 (company_id, datum, income) as
  3    (select 1, date '2019-01-01', 65 from dual union all
  4     select 1, date '2019-02-01', 58 from dual union all
  5     select 1, date '2019-03-01', 12 from dual union all
  6     select 1, date '2019-04-01', 81 from dual union all
  7     select 1, date '2019-05-01', 38 from dual union all
  8     select 1, date '2019-06-01', 81 from dual union all
  9     select 1, date '2019-07-01', 38 from dual union all
 10     select 1, date '2019-08-01', 69 from dual union all
 11     select 1, date '2019-09-01', 54 from dual union all
 12     select 1, date '2019-10-01', 90 from dual union all
 13     select 1, date '2019-11-01', 10 from dual union all
 14     select 1, date '2019-12-01', 12 from dual
 15    ),
 16  table2 (company_id, datum) as
 17    (select 1, date '2019-06-01' from dual union all
 18     select 1, date '2019-09-01' from dual union all
 19     select 1, date '2019-12-01' from dual union all
 20     select 1, date '2020-01-01' from dual union all
 21     select 1, date '2020-07-01' from dual
 22    )

Query begins here:

 23  select b.company_id,
 24         b.datum ,
 25         round(avg(a.income), 2) result
 26  from table1 a join table2 b on a.company_id = b.company_id
 27                             and a.datum > add_months(b.datum, -6)
 28                             and a.datum <= b.datum
 29  group by b.company_id, b.datum;

COMPANY_ID DATUM        RESULT
---------- -------- ----------
         1 01.06.19      55,83
         1 01.09.19      60,17
         1 01.12.19       45,5
         1 01.01.20         47

SQL>