SQL Oracle /聚合查询

前爱国者

我正在尝试在Oracle上运行查询。我有一个帐户的结算付款表，我有一个查询，可以查询我需要此信息的任何帐户的最后三个结算金额以及已注销的任何金额。

但是，有些帐户是基于每周的，因此，我想将其每周结算汇总到其每月组中。这是我到目前为止的代码：

SELECT   *
FROM     (
    SELECT *
    FROM   (
            SELECT   gwod.account_id,
                     gwod.charge_period_start,
                     SUM(gwod.total_due_on_charge) total_due_on_charge,
                     SUM(gwod.amount_written_off) amount_written_off,
                     DENSE_RANK() over (PARTITION BY gwod.account_id 
                                        ORDER BY charge_period_start DESC) rownumber
            FROM     report.accounts_write_off gwod
            WHERE    account_id IN (‘account_number’)
            GROUP BY gwod.account_id,
                     gwod.charge_period_start
            HAVING   SUM (gwod.total_due_on_charge) <> 0) t1
    WHERE  t1.rownumber <=3) 
    PIVOT (MAX(charge_period_start) charge_period, 
           MAX(total_due_on_charge) total_due_on_charge, 
           MAX(amount_written_off) amount_written_off 
           FOR rownumber IN (1,2,3))
ORDER BY account_id.*

这非常有效，但是对于基于每周的帐户，因此我不想跳过过去的三个每周已结算的金额，即25-09-17、18-09-17、11-09-2017， 9月，8月和7月的总付款额。

我希望所有这些都是有道理的。

完善

只需将聚集来自当前单元的水平（即，每周）与改变月份的水平提取物（月...）在内部查询的SELECT和GROUP BY以及PARTITION和PIVOT条款：

SELECT   *
FROM     (
    SELECT *
    FROM   (
            SELECT   gwod.account_id,
                     EXTRACT(month FROM gwod.charge_period_start) charge_period_month,
                     SUM(gwod.total_due_on_charge) total_due_on_charge,
                     SUM(gwod.amount_written_off) amount_written_off,
                     DENSE_RANK() over (PARTITION BY gwod.account_id 
                                        ORDER BY EXTRACT(month FROM gwod.charge_period_start) DESC) rownumber
            FROM     report.accounts_write_off gwod
            WHERE    account_id IN ('account_number')
            GROUP BY gwod.account_id,
                     EXTRACT(month FROM gwod.charge_period_start)
            HAVING   SUM (gwod.total_due_on_charge) <> 0) t1
    WHERE  t1.rownumber <=3) 
    PIVOT (MAX(charge_period_month) charge_period, 
           MAX(total_due_on_charge) total_due_on_charge, 
           MAX(amount_written_off) amount_written_off 
           FOR rownumber IN (1,2,3))
ORDER BY account_id.*

演示 （带有随机数据）：

http://rextester.com/UJK84858

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。