Loading

环比同比 

 1 SELECT 
 2     TEMP.*,
 3     NVL(round(((TEMP.CollectionAmount + TEMP.StayCollectionAmount) / lag(TEMP.CollectionAmount + TEMP.StayCollectionAmount) over(order by TEMP.MonthID)) * 100, 2), '0') || '%' "环比",
 4     NVL(round(((TEMP.CollectionAmount + TEMP.StayCollectionAmount) / lag(TEMP.CollectionAmount + TEMP.StayCollectionAmount) over(partition by SUBSTR(to_char(TEMP.MonthID), 5, 2) ORDER BY TEMP.MonthID)) * 100, 2), '0') || '%' "同比" 
 5 FROM
 6     (SELECT 
 7         *
 8     FROM 
 9         tds_agent_org_revcoll_mon T
10     WHERE 
11         T.companyId = -1
12             AND T.MonthID >= '201601'
13             AND T.MonthID <= '201707') TEMP

参考lag和lead函数。

^_^

posted @ 2017-10-12 17:39  dai.sp  阅读(268)  评论(0)  编辑  收藏  举报