sql - 使用 SQL 从 StartDate 列中提取 EndDate，即来自同一列的两个日期

Question

我现在的表结构是:

我想计算当天每个产品 ID 的持续时间加权平均价格。

例如

PA1 的平均价格 = (100*4 + 105*4 + 110*8 + 115*4)/20

即这里价格保持 4 小时 100，4 小时 105，8 小时 110，4 小时 115(关闭时间为午夜)

下面这个Oracle SQL可以帮助快速创建表结构，谢谢。

CREATE TABLE ProductTable
(ID NUMBER(5), 
 ProdId varchar(7),
 StartDate DATE,
Price NUMBER(4));

INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (1, 'PA1', TO_DATE('2015/08/01 4:00', 'YYYY/MM/DD hh24:mi'), 100);
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (2, 'PA1', TO_DATE('2015/08/01 8:00', 'YYYY/MM/DD hh24:mi'), 105)
;
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (3, 'PA2', TO_DATE('2015/08/01 9:00', 'YYYY/MM/DD hh24:mi'), 120);
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (4, 'PA1', TO_DATE('2015/08/01 12:00', 'YYYY/MM/DD hh24:mi'), 110)
;
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (5, 'PA3', TO_DATE('2015/08/01 14:00', 'YYYY/MM/DD hh24:mi'), 150)
;
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (6, 'PA2', TO_DATE('2015/08/01 15:00', 'YYYY/MM/DD hh24:mi'), 130)
;
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (7, 'PA2', TO_DATE('2015/08/01 18:00', 'YYYY/MM/DD hh24:mi'), 125)
;
INSERT INTO ProductTable (ID, ProdId, StartDate, Price)
VALUES (8, 'PA1', TO_DATE('2015/08/01 20:00', 'YYYY/MM/DD hh24:mi'), 115)
;

Answer 1

您基本上需要 lead() 函数，然后是一些日期算法:

select prodid,
       sum((least(trunc(startdate + 1, next_startdate) - startdate)*24) numhours
from (select p.*, lead(startdate) over (order by startdate) as next_startdate
      from producttable t
     ) t
where date = date '2015-08-01'
group by prodid;

这基于当天的第一条记录进行计算，这就是您在问题中描述结果的方式。