配置单元查询派生列并查找派生列的总数 [英] Hive query for derived columns and find the total of derived column
问题描述
我有一个方案,其中credit_Date,debit_date和loan_date可以相同.输出表具有以下列
I have a scenario where credit_Date, debit_date and loan_date can be same. Output table have below columns
日期:应结合credit_date,debit_date和loan_date(credit_date,debit_date和loan_date可以相同(或为null)
Date: should combine credit_date, debit_date and loan_date ( credit_date, debit_date and loan_date can be same (or) null)
贷方付款:查找给定贷方日期,实体,货币,所有者的贷方金额之和
Credit_payment: Find the sum of credit amount for a given credit_date, entity, currency, owner
借方付款:查找给定借方日期,实体,货币,所有者的借方金额之和
Debit_payment: Find the sum of debit amount for a given debit_date, entity, currency, owner
贷款还款:查找给定贷款日期,实体,货币,所有者,
Loan_payment: Find the sum of loan amount for a given loan_date, entity, currency, owner,
实体:值
currency:值
所有者:值
总计:(贷记付款+借记付款+贷款付款)的总和
Total : sum of ( credit_payment + debit_payement+ loan_payment)
我在下面的查询中尝试过,但是没有用
I tried below query but not working
insert into table2
select *
from (
select credit_date as date, sum(credit_amount) as credit_payment, null as debit_payment, null as loan_payment, entity, owner, currency
from table1
group by credit_date, entity, owner, currency
union all
select debit_date as date, null as credit_payment, sum(debit_amount) as debit_payment, null as loan_payment, entity, owner, currency
from table1
group by debit_date, entity,owner, currency
union all
select loan_date as date, null as credit_payment, null as debit_payment, sum(loan_amount) as loan_payment, entity, owner, currency
from table1
group by loan_date, entity, owner, currency
) t
order by date;
推荐答案
您可以使用 coalesce
组合三个日期,然后再分组.它将处理空值.
You can use coalesce
to combine the three dates before group by. It will take care of the nulls.
select coalesce(credit_date, debit_date, loan_date) as date,
sum(credit_amount) as credit_payment,
sum(debit_amount) as debit_payment,
sum(loan_amount) as loan_payment,
entity, currency, owner,
sum(credit_amount) + sum(debit_amount) + sum(loan_amount) as Total
from table1
group by coalesce(credit_date, debit_date, loan_date), entity, currency, owner
这篇关于配置单元查询派生列并查找派生列的总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!