如何基于Impala查询中的调整查找值的总和 [英] How to find the sum of value based on Adjustments in Impala query

问题描述

我有一个名为REV的Impala表，其中包含每个电汇代码的wire_code，金额和报告行.

I have an Impala table named REV having wire_code, amount and Reporting line for each wire code.

+---------+------+----------------+
|wire_code| amt  | Reporting_line |
+---------+------+----------------+
| abc     | 100  | Database       |
+---------+------+----------------+
| abc     | 10   | Revenue        |
+---------+------+----------------+
| def     | 50   | Database       |
+---------+------+----------------+
| def     | 25   | Polland        |
+---------+------+----------------+
| ghi     | 250  | Cost           |
+---------+------+----------------+
| jkl     | 300  | Cost           |
+---------+------+----------------+

and the other table is FA which is having wire_code and Ajusted_wire_code    

    +---------+------+
    |wire_code|adj_wc|
    +---------+------+
    | abc     | def  |
    +---------+------+
    |  ghi    | jkl  |
    +---------+------+


I need to adjust the amount of wire code which is available as adj_wc in FA table.
For example:

FA表中有"abc"，并且将其调整为"def"，然后我的输出应该是-wire_code"def"的数量(abc和def)如下，而"abc"的数量将保持不变.

"abc" is there in FA table and it's getting adjusted to "def" then my output should be - wire_code "def" is having the amount of (abc and def) as below and "abc" amount will remain same.

我正在使用下面提供的查询，它正在删除两种电汇代码中都不常见的记录，例如，报告行Polland的def.并且abc有一个额外的报告行收入，随着abc转移到def，需要将其添加到def有线代码中.

I am using the query provided below and it is removing the records which are not common in both the Wire code, for example, def with reporting line, Polland. and abc has one extra reporting line Revenue which needs to be added to def wire code as abc is moving to def.

abc正在调整为def-abc中不存在的def报告行将保持不变，并且将调整公共报告行.

abc is getting adjusted to def - reporting lines of def which are not there in abc will remain same and the common reporting lines will be adjusted.

  select r.wire_code, r.amt+coalesce(a.amt,0) as amt
      from REV r
           left outer join FA f on r.wire_code=f.adj_wc     --adjustments
           left outer join REV a on f.wire_code=a.wire_code --adjusted amount
         Where REP.REPORTING_LINE = REP1.REPORTING_LINE
    ;

预期结果:

+---------+------+----------------+
|wire_code| amt  | Reporting_line |
+---------+------+----------------+
| abc     | 100  | Database       |
+---------+------+----------------+
| abc     | 10   | Revenue        |
+---------+------+----------------+
| def     | 150  | Database       |
+---------+------+----------------+
| def     | 10   | Revenue        |
+---------+------+----------------+
| def     | 25   | Polland        |
+---------+------+----------------+
| ghi     | 250  | Cost           |
+---------+------+----------------+
| jkl     | 550  | Cost           |
+---------+------+----------------+

推荐答案

我认为下面的查询在蜂巢中有效

I think below query working in hive

尝试黑斑羚，让我知道

create table rev
(
wire_code varchar(200),
amt   int,
reporting varchar(200)
);

insert into rev values ('abc',100,'Database');
insert into rev values ('abc',10,'Revenue');
insert into rev values ('def',50,'Database');
insert into rev values ('def',25,'Polland');
insert into rev values ('ghi',250,'cost');
insert into rev values ('jkl',300,'cost');

create table fa
(
wire_code varchar(200),
adj_wc varchar(200)
);

insert into fa values ('abc','def');
insert into fa values ('ghi','jkl');

select rev.wire_code,
case when rev.wire_code=adj_wc then sum(amt) over(partition by reporting)
else amt end as amt,reporting
from rev inner join fa 
on (rev.wire_code=fa.wire_code or rev.wire_code=fa.adj_wc)
order by 1

这篇关于如何基于Impala查询中的调整查找值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！