hive中with…as…的用法

然后分别是大宗商品表

[En]

Then there are commodity tables * good* respectively.

现在我们需要单独进行统计。

[En]

Now we need to make separate statistics.

除了子查询,上述的的例子还可以用 join来实现,

如果你与…连用..。语句,如下所示

[En]

If you use with…as… Statement, as follows

看起来使用 with…as… 语句反而更复杂一点,但如果 tmp_shanghai要被多次使用的使用,就很有必要

让我们来看一个实际的例子。有一个操作表Event的主要字段如下:

[En]

Let’s look at a practical example. There is an operation table * the main fields of event* are as follows:

date event_key 20190530 Delete 20190530 Put 20190530 Get 20190530 Get 20190601 Set

……

现在要求一条sql统计出 GetSet 操作的数量,先使用子查询实现

select (
    select count(*) from event where event_key = "Get"
) as get_num,(
    select count(*) from event where event_key = "Set"
) as set_num

如果添加其他项的统计数据,是否需要添加自查询来扫描事件表?

[En]

If you add statistics for other items, does each need to add a self-query to scan the event table?

与…连用,用作…

[En]

Use with…as…

with temp as(
    select * from event where event_key = "Get" or event_key = "Set"
)
select
    sum(case when event_key = "Get" then 1 else 0 end) as get_num,
    sum(case when event_key = "Set" then 1 else 0 end) as Set_num
from temp

可读性比以前好了吗?此外,此语句仅扫描事件表一次,并将符合条件的数据存储在TEMP中以供后续计算

[En]

Is the readability better than before? In addition, this statement scans the event table only once and stores the qualified data in temp for subsequent calculation

在event表数据集非常大的情况下,性能将比子查询的方式优秀很多

Original: https://www.cnblogs.com/itboys/p/14167603.html
Author: 大葱拌豆腐
Title: hive中with…as…的用法

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6777/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

发表回复

登录后才能评论
免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部