派生列的配置单元查询

a7qyws3x  于 2021-07-13  发布在  Hadoop
关注(0)|答案(2)|浏览(407)

请你带路好吗。
输入表(表1)有如下列:贷方日期、贷方金额、借方日期、借方金额、贷方日期、贷方金额。
输出表(表2)有日期、贷方付款、借方付款、贷款付款等列。
日期:应合并贷方日期、借方日期和贷款日期的所有值。
贷记付款:查找给定贷记日期的贷记金额之和。
借方付款:查找给定借方日期的借方金额总和。
贷款支付:查找给定贷款日期的贷款金额总和
我试过了,但没用。

insert into table2 
select 
date,
debit_payment,
credit_payment,
Loan_payment
 from (
select 
sum(credit_amount) over parttion by credit_date as credit_payment,
sum(debit_amount) over parttion by debit_date as Debit_payment
sum(loan_amount) over parttion by loan_date as Loan_payment
from table1
union all
select credit_date as date from table1
union all
select debit_date as date from table1
union all
select payment_date as date from table1
) t

z8dt9xmd

z8dt9xmd1#

--
我有另一个场景,其中贷方日期、借方日期和贷款日期可以相同。输出表有以下列
日期:应结合贷方日期、借方日期和贷款日期(贷方日期、借方日期和贷款日期也可以相同或不同)

**贷记付款:**查找给定贷记日期、实体、货币、所有者的贷记金额之和

借方付款:查找给定借方日期、实体、货币、所有者的借方金额之和
贷款支付:查找给定贷款日期、实体、货币、所有者的贷款金额之和,
实体:表1中的值
货币:表1中的值
所有者:表1中的值
合计:贷方付款+借方付款+贷款付款之和
请你带路好吗。
请查看以下截图。

vdgimpew

vdgimpew2#

在联合所有列之前,可能需要显式指定空列:

insert into table2 
select *
from (
    select credit_date as date, sum(credit_amount) as credit_payment, null as debit_payment, null as loan_payment
    from table1
    group by credit_date
    union all
    select debit_date as date, null as credit_payment, sum(debit_amount) as debit_payment, null as loan_payment
    from table1
    group by debit_date 
    union all
    select loan_date as date, null as credit_payment, null as debit_payment, sum(loan_amount) as loan_payment
    from table1
    group by loan_date
) t
order by date;

相关问题