SQL如何从月份到日期表中计算单日收益/损失

Question

我有一个表，其中包含各种投资组合、安全标识符和业务单位按月计算的总收益损失。每个月的Period Start Date都会刷新到新的一个月的开始，但该表通常一次包含整个季度的数据。如何编写一个查询，计算所有"结束日期"的单日收益损失按投资组合，股票和业务单位分组?

Portfolio   Fund Legal Entity   SECURITY IDENTIFIER BUSINESS UNIT   GAIN/LOSS   Start Date  End Date
Portfolio1  LegalEntity1    Ticker1 PUBLIC   (4,760,588.0)  1-Feb   27-Feb
Portfolio1  LegalEntity1    Ticker1 PUBLIC   9,123,144.0    1-Feb   28-Feb
Portfolio1  LegalEntity1    Ticker1 PUBLIC   7,470,013.0    1-Mar   1-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   24,466,723.0   1-Mar   2-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   4,272,096.0    1-Mar   3-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   14,043,454.0   1-Mar   4-Mar
Portfolio1  LegalEntity1    Ticker1 PUBLIC   17,631,760.0   1-Mar   5-Mar

Answer 1

如果您的dbms支持:

SELECT Portfolio, Fund_Legal_Entity, SECURITY_IDENTIFIER,
BUSINESS_UNIT, GAIN_LOSS, Start_Date, End_Date,
(gain_loss-lag(GAIN_LOSS) OVER (PARTITION BY Portfolio,  
Fund_Legal_Entity, SECURITY_IDENTIFIER, BUSINESS_UNIT 
ORDER BY end_date)) Daily_gain_loss
FROM tablename