Hive 查询派生列并找到派生列的总数 [英] 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可以相同(或)为空)

Date: should combine credit_date, debit_date and loan_date ( credit_date, debit_date and loan_date can be same (or) null)

Credit_payment:查找给定credit_date、实体、货币、所有者的信用金额总和

Credit_payment: Find the sum of credit amount for a given credit_date, entity, currency, owner

Debit_payment:求给定 debit_date、实体、货币、所有者的借记金额总和

Debit_payment: Find the sum of debit amount for a given debit_date, entity, currency, owner

Loan_payment:求给定loan_date、实体、货币、所有者的贷款金额总和，

Loan_payment: Find the sum of loan amount for a given loan_date, entity, currency, owner,

实体: Table1 中的值

entity: values from Table1

货币:表 1 中的值

所有者: 表 1 中的值

总计:(credit_payment + debit_payement+ loan_payment)的总和

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将group by前的三个日期合并.它会处理空值.

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

这篇关于Hive 查询派生列并找到派生列的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！