派生列的配置单元(hql)并找到总数 [英] Hive (hql) for derived columns and find the total

问题描述

请您指导

我有一个方案，其中credit_Date，debit_date和loan_date可以具有不同的date值或相同的值.输出表具有以下列

I have a scenario where credit_Date,debit_date and loan_date can have different values of date or 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) have different dates)

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

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,

实体:值

货币:表1中的值

所有者:值

总计:(贷记付款+借记付款+贷款付款)的总和

Total : sum of ( credit_payment + debit_payement+ loan_payment)

请找到以下屏幕截图.

Please find the screenshot as below.

推荐答案

您可以使用与我的上一个答案类似的查询，并更改 table1 的格式，以便于按日期分组:

You can use a similar query as in my previous answer, and change the format of table1 in order to facilitate grouping by date:

select coalesce(credit_date, debit_date, loan_date) as date, 
       coalesce(sum(credit_amount), 0) as credit_payment, 
       coalesce(sum(debit_amount), 0) as debit_payment,
       coalesce(sum(loan_amount), 0) as loan_payment,
       entity, currency, owner,
       coalesce(sum(credit_amount), 0) + coalesce(sum(debit_amount), 0) + coalesce(sum(loan_amount), 0) as Total
from (
    select credit_date, credit_amount, null as debit_date, null as debit_amount, null as loan_date, null as loan_amount, entity, currency, owner
    from table1
    union all
    select null as credit_date, null as credit_amount, debit_date, debit_amount, null as loan_date, null as loan_amount, entity, currency, owner
    from table1
    union all
    select null as credit_date, null as credit_amount, null as debit_date, null as debit_amount, loan_date, loan_amount, entity, currency, owner
    from table1
) t
group by coalesce(credit_date, debit_date, loan_date), entity, currency, owner

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