如何在impala查询中根据调整找到值的总和

ogq8wdun 于 2021-06-27 发布在 Hive

关注(0)|答案(1)|浏览(247)

我有一个名为rev的impala表，它有电汇代码、金额和每个电汇代码的报告行。

+---------+------+----------------+
|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表格中，它被调整为“def”，那么我的输出应该是-电线代码“def”的金额（和def）如下，“”金额将保持不变。
我正在使用下面提供的查询，它正在删除两个wire代码中不常见的记录，例如，def with reporting line，polland。有一个额外的报告行收入，需要添加到def代码中，因为正在转移到def。
正在调整到def-中没有的def报告行将保持不变，并调整通用报告行。

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           |
+---------+------+----------------+

Hive impala hiveql

来源：https://stackoverflow.com/questions/55376463/how-to-find-the-sum-of-value-based-on-adjustments-in-impala-query

1条答案

按热度按时间

qc6wkl3g1#

我想在Hive里工作
试试 Impala ，让我知道

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

赞(0）回复(0）举报 2021-06-27

我来回答

如何在impala查询中根据调整找到值的总和

1条答案

相关问题

热门标签

最新问答