sql - SQL (Hive) group-by 使用空值作为通配符
问题描述
我有一张这样的桌子:
group val1 val2 val3
group1 5 . .
group1 . 2 1
group1 . . 3
group2 1 4 .
group2 . . 8
group2 2 . 7
我需要使用空值 (.) 作为通配符来计算 Hive 中每个组的所有可能组合的出现次数。这会给我这样的结果:
group val1 val2 val3 cnt
group1 5 2 1 2
group1 5 2 3 2
group2 1 4 8 2
group2 2 4 8 1
group2 2 4 7 1
我知道我可以通过选择所有不同的 group-val1 对,将其与所有不同的 group-val2 对完全连接,并将其与所有不同的 group-val3 对完全连接来做到这一点。这为我提供了每个组的所有可能组合,然后我可以将它们与我的表进行内部连接,计算我的原始数据行是组合子集的情况。
像这样的东西:
create table my_results as
with combos as (
select *
from (select distinct group, val1 from data) A
full join (select distinct group, val2 from data) B
on A.group = B.group
full join (select distinct group, val3 from data) C
on A.group = C.group
)
select A.group, A.val1, A.val2, A.val3, count(*)
from combos A
inner join data B
on A.group = B.group
and (A.val1 = B.val1 OR B.val1 is null)
and (A.val2 = B.val2 OR B.val2 is null)
and (A.val3 = B.val3 OR B.val3 is null)
group by A.group, A.val1, A.val2, A.val3
但!我的数据集非常大(数百万行),我可以预期的所有可能组合的数量也非常大(数万行)。这样的连接太大了。
还有其他方法吗?我想知道是否可以使用正则表达式,但我不知道从哪里开始。
解决方案
在您的示例数据中,只有第三列有多个值。因此,您可以只为另外两列填写一个值:
select group,
max(max(col1)) over (partition by group) as col1,
max(max(col2)) over (partition by group) as col2,
col3,
count(*)
from data
group by group;
推荐阅读
- angular - 如何在Angular中的同一字段中显示相应类中的字段名称
- reactjs - 如何在 react-navigation 5 中从当前屏幕更新以前的屏幕参数
- asp.net-core - 返回字符串时调用 Http.PostAsync 时应用程序停止工作
- php - 如何使用 mPDF 快速高效地编写大型 PDF?
- makefile - qmake自动生成的Makefile中的混淆变量
- jsf - p:selectOneMenu 在按 F5 时没有正确刷新
- c# - 如何在 c# 中使用存储过程将对象列表保存到具有多对多关系的数据库中?
- python - 将字典列表作为 csv 直接写入 S3
- python - 未在 Docker 容器/映像中创建 venv 目录
- javascript - 使用用户语言环境访问 json 表中的良好语言环境