时间戳

首页 > 解决方案 > 如何根据 Impala 查询中的调整找到值的总和

hive - 如何根据 Impala 查询中的调整找到值的总和

问题描述

我有一个名为 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:

“abc”在 FA 表中，它正在调整为“def”，那么我的输出应该是 - 线代码“def”的（abc 和 def）的数量如下，“abc”数量将保持不变。

我正在使用下面提供的查询，它正在删除两个 Wire 代码中不常见的记录，例如，带有报告行 Polland 的 def。并且 abc 有一个额外的报告行收入需要添加到 def 电汇代码，因为 abc 正在移动到 def。

abc 正在调整为 def - 在 abc 中不存在的 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           |
+---------+------+----------------+

标签： hivehiveqlimpala

解决方案

我认为下面的查询在蜂巢中工作

在 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

推荐阅读