我有一个每个项目及其各自领域（收入、成本......）的月度交易表。
我需要按日期（ACCT_YR_NUM和ACCT_MO_NUM的组合）获取每个项目的最后金额。
我已经设法得到了最后一笔交易是什么时候完成的（列LAST_DATE），但我很难得到它各自的金额。

我的代码：

WITH init AS (
    SELECT 
        f.PRJCT_ID,
        f.ACT_PLNRT_USD_AMT,
        f.RPRTG_AREA_NM,
        last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) ACCT_DATE,
        MAX(last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15'))) over (partition by f.PRJCT_ID, f.RPRTG_AREA_NM) AS LAST_DATE
    FROM my.table f
)

SELECT 
    *,
    MAX(ACT_PLNRT_USD_AMT) OVER (PARTITION BY ACCT_DATE = LAST_DATE, RPRTG_AREA_NM, PRJCT_ID) AS MAX_DATE_AMT
FROM init 

ORDER BY 
    PRJCT_ID,
    RPRTG_AREA_NM,
    ACCT_DATE

结果（错误）：

| 采购标识|报告_区域_名称|实际计划金额美元AMT|帐户日期|最后日期|最大日期AMT|
| - -|- -|- -|- -|- -|- -|
| 009 RR 0 S系列|积压|二五八七五四三|2022年6月30日|2022年9月30日|小行星2795163|
| 009 RR 0 S系列|积压|小行星2507543| 2022年9月30日|2022年9月30日|小行星2507543|
| 009 RR 0 S系列|费用|小行星1056| 2022年6月30日|2022年6月30日|小行星1056|
| 009 RR 0 S系列|收益|小行星2112| 2022年6月30日|2022年6月30日|小行星2112|
| 009 RR 0 S系列|签名|-202540年|2021年1月31日|2022年1月31日|-202540年|
| 009 RR 0 S系列|签名|-266500年|2022年1月31日|2022年1月31日|-266500年|
| 00GRQ6W|成本费用|小行星17169| 2021年1月31日|2021年2月28日|小行星17169|
| 00GRQ6W|成本费用|小行星-17169| 2021年2月28日|2021年2月28日|小行星-17169|

预期结果：

| 采购标识|报告_区域_名称|实际计划金额美元AMT|帐户日期|最后日期|最大日期AMT|
| - -|- -|- -|- -|- -|- -|
| 009 RR 0 S系列|积压|二五八七五四三|2022年6月30日|2022年9月30日|小行星2507543|
| 009 RR 0 S系列|积压|小行星2507543| 2022年9月30日|2022年9月30日|小行星2507543|
| 009 RR 0 S系列|费用|小行星1056| 2022年6月30日|2022年6月30日|小行星1056|
| 009 RR 0 S系列|收益|小行星2112| 2022年6月30日|2022年6月30日|小行星2112|
| 009 RR 0 S系列|签名|-202540年|2021年1月31日|2022年1月31日|-266500年|
| 009 RR 0 S系列|签名|-266500年|2022年1月31日|2022年1月31日|-266500年|
| 00GRQ6W|成本费用|小行星17169| 2021年1月31日|2021年2月28日|小行星-17169|
| 00GRQ6W|成本费用|小行星-17169| 2021年2月28日|2021年2月28日|小行星-17169|
源样本表（my.table）：
| 采购标识|报告_区域_名称|实际计划金额美元AMT|会计年度编号|帐户_生产订单_编号|
| - -|- -|- -|- -|- -|
| 009 RR 0 S系列|积压|小行星2507543|小行星2022| 06年|
| 009 RR 0 S系列|积压|小行星2507543|小行星2022| 09年|
| 009 RR 0 S系列|费用|小行星1056|小行星2022| 06年|
| 009 RR 0 S系列|收益|小行星2112|小行星2022| 06年|
| 009 RR 0 S系列|签名|-202540年|小行星2021| 01年|
| 009 RR 0 S系列|签名|-266500年|小行星2022| 01年|
| 00GRQ6W|成本费用|小行星17169|小行星2021| 01年|
| 00GRQ6W|成本费用|小行星-17169|小行星2021|二|

WITH MY_TABLE (PRJCT_ID, RPRTG_AREA_NM, ACT_PLNRT_USD_AMT, ACCT_YR_NUM, ACCT_MO_NUM) AS ( VALUES ('009RR0S', 'BACKLOG' , 2507543, '2022', '06') , ('009RR0S', 'BACKLOG' , 2507543, '2022', '09') , ('009RR0S', 'EXPENSE' , 1056, '2022', '06') , ('009RR0S', 'REVENUE' , 21120, '2022', '06') , ('009RR0S', 'SIGNINGS', -202540, '2021', '01') , ('009RR0S', 'SIGNINGS', -266500, '2022', '01') , ('00GRQ6W', 'COST' , 17169, '2021', '01') , ('00GRQ6W', 'COST' , -17169, '2021', '02') ) , INIT AS ( SELECT F.* , DATE (ACCT_YR_NUM || '-' || ACCT_MO_NUM || '-01') + 1 MONTH - 1 DAY AS ACCT_DATE , ROW_NUMBER () OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM ORDER BY ACCT_YR_NUM DESC, ACCT_MO_NUM DESC) AS RN_ FROM MY_TABLE F ) SELECT PRJCT_ID, RPRTG_AREA_NM, ACT_PLNRT_USD_AMT, ACCT_DATE , MAX (ACCT_DATE) OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM) AS LAST_DATE , MAX (CASE RN_ WHEN 1 THEN ACT_PLNRT_USD_AMT END) OVER (PARTITION BY PRJCT_ID, RPRTG_AREA_NM) AS MAX_DATE_AMT FROM INIT ORDER BY PRJCT_ID, RPRTG_AREA_NM, ACCT_DATE

1条答案

按热度按时间

70gysomp1#

其思想是枚举每个组中的行，以便将行号1分配给该组中日期最晚的行，并将该行用作MAX OLAP函数中的唯一行。

| 采购标识|报告_区域_名称|实际计划金额美元AMT|帐户日期|最后日期|最大日期AMT|
| - -|- -|- -|- -|- -|- -|
| 009 RR 0 S系列|积压|小行星2507543| 2022年6月30日|2022年9月30日|小行星2507543|
| 009 RR 0 S系列|积压|小行星2507543| 2022年9月30日|2022年9月30日|小行星2507543|
| 009 RR 0 S系列|费用|小行星1056| 2022年6月30日|2022年6月30日|小行星1056|
| 009 RR 0 S系列|收益|小行星2112| 2022年6月30日|2022年6月30日|小行星2112|
| 009 RR 0 S系列|签名|-202540年|2021年1月31日|2022年1月31日|-266500年|
| 009 RR 0 S系列|签名|-266500年|2022年1月31日|2022年1月31日|-266500年|
| 00GRQ6W|成本费用|小行星17169| 2021年1月31日|2021年2月28日|小行星-17169|
| 00GRQ6W|成本费用|小行星-17169| 2021年2月28日|2021年2月28日|小行星-17169|

赞(0）回复(0）举报 2022-11-07

db2 在SQL中获取具有最新日期的每组金额

1条答案

相关问题

热门标签

最新问答