GROUP BY 加權平均

我有一張road_insp桌子：

create table road_insp
(
   insp_id int,
   road_id int,
   insp_date date,
   condition number(10,2),
   insp_length number(10,2)
);

insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 1, 100, to_date('2017-01-01 6:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 5.0, 100);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 2, 101, to_date('2017-02-01 7:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 5.5, 500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 3, 101, to_date('2017-02-01 8:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 6.0, 1000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 4, 102, to_date('2018-04-01 9:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 6.5, 1500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 5, 102, to_date('2018-04-01 10:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 7.0, 2000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 6, 102, to_date('2018-04-01 11:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 7.5, 2500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 7, 103, to_date('2018-07-01 12:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 8.0, 3000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 8, 103, to_date('2018-07-01 1:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 8.5, 3500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 9, 103, to_date('2019-09-01 2:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 9.0, 4000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 10, 103, to_date('2019-09-01 3:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 9.5, 4500);

commit;

select
   insp_id,
   road_id,
   to_char(insp_date, 'YYYY-MM-DD HH:MI:SSAM') date_formatted,
   condition,
   insp_length
from 
   road_insp;

INSP_ID    ROAD_ID    DATE_FORMATTED         CONDITION INSP_LENGTH
---------- ---------- --------------------- ---------- -----------
        1        100 2017-01-01 06:00:00AM          5         100
        2        101 2017-02-01 07:00:00AM        5.5         500
        3        101 2017-02-01 08:00:00AM          6        1000
        4        102 2018-04-01 09:00:00AM        6.5        1500
        5        102 2018-04-01 10:00:00AM          7        2000
        6        102 2018-04-01 11:00:00AM        7.5        2500
        7        103 2018-07-01 12:00:00PM          8        3000
        8        103 2018-07-01 01:00:00PM        8.5        3500
        9        103 2019-09-01 02:00:00PM          9        4000
       10        103 2019-09-01 03:00:00PM        9.5        4500

我可以通過對同一天road_id檢查的相同檢查進行分組來成功總結表格。

select
   road_id,
   to_char(trunc( insp_date), 'YYYY-MM-DD') as insp_day,
   avg(condition) condition_avg,
   sum(insp_length) insp_length_tot
from
   road_insp
group by
   road_id,
   trunc(insp_date)
order by
   road_id,
   trunc(insp_date)

  ROAD_ID INSP_DAY   CONDITION_AVG INSP_LENGTH_TOT
---------- ---------- ------------- ---------------
      100 2017-01-01             5             100
      101 2017-02-01          5.75            1500
      102 2018-04-01             7            6000
      103 2018-07-01          8.25            6500   --Same road, different day
      103 2019-09-01          9.25            8500   --Same road, different day

condition我不想總結為一個平均值，而是總結condition為一個加權平均值（按 加權**insp_length**）。

它看起來像這樣：

+---------+------------+------------------------+-----------------+
| ROAD_ID |  INSP_DAY  | CONDITION_WEIGHTED_AVG | INSP_LENGTH_TOT |
+---------+------------+------------------------+-----------------+
|     100 | 2017-01-01 |                   5.00 |             100 |
|     101 | 2017-02-01 |                   5.83 |            1500 |
|     102 | 2018-04-01 |                   7.08 |            6000 |
|     103 | 2018-07-01 |                   8.27 |            6500 |
|     103 | 2019-09-01 |                   9.26 |            8500 |
+---------+------------+------------------------+-----------------+

---

我怎樣才能做到這一點？

select
   road_id,
   to_char(trunc( insp_date), 'YYYY-MM-DD') as insp_day,
   avg(condition) condition_avg,
   sum(insp_length) insp_length_tot,
   round(sum(condition * insp_length) / sum(insp_length), 2) as CONDITION_WEIGHTED_AVG 
from
   road_insp
group by
   road_id,
   trunc(insp_date)
order by
   road_id,
   trunc( insp_date);

  ROAD_ID INSP_DAY   CONDITION_AVG INSP_LENGTH_TOT CONDITION_WEIGHTED_AVG
---------- ---------- ------------- --------------- ----------------------
      100 2017-01-01             5             100                      5
      101 2017-02-01          5.75            1500                   5.83
      102 2018-04-01             7            6000                   7.08
      103 2018-07-01          8.25            6500                   8.27
      103 2019-09-01          9.25            8500                   9.26

引用自：https://dba.stackexchange.com/questions/189234