将表与每行的最近日期连接起来，然后进行加权平均

Question

Oracle

DB中有三个表，例如equip_type，output_history和time_history。有没有办法连接下面 （1） 所示的三个表，然后获得加权平均值，如下面的 （2） 所示？

--equip_type table and the date
CREATE TABLE equip_type  (  
  EQUIP_TYPE VARCHAR(60),    
  EQUIP VARCHAR(60)
  );
INSERT INTO equip_type  VALUES ('A','e1');
-- output_history and data
CREATE TABLE output_history (  
  EQUIP VARCHAR(60),     
  MODEL VARCHAR(60),     
  Data1 VARCHAR(60),        
  QUANTITY  NUMBER(10) 
  );
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);

--time_history table and data
CREATE TABLE time_history (  
  EQUIP VARCHAR(60),     
  MODEL VARCHAR(60),       
  Data2 VARCHAR(60),    
  time NUMBER(10)
  );
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);

（1） 如何获得以下连接表？

 EQUIP MODEL DATE1  QUANTITY   DATE2   TIME  TYPE
 ---- ---- ---------- ------  -------- ----  ----
  e1    m1  20180103  10      20180101   6    A
  e1    m1  20180106  20      20180105   5    A

对于OUTPUT_HISTORY中的每一行，*连接 DATE1* 在TIME_HISTORY点的最新行。

（2）那么，使用上面的连接表，如何获得TIME的加权平均值？

(QUANTITY * TIME) / sum of QUANTITY group by TYPE, MODEL
 for example,(10×6 + 20×5)÷(10+20) for equip type A and model m1

Answer 1

一种方法使用分析函数来获取最新的记录，然后进行简单的聚合

select sum(quantity * time) / sum(quantity)
from output_history oh left join
     (select th.*,
             row_number() over (partition by equip, model order by date2 desc) as seqnum
      from time_history th
     ) th
     on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;