如何获取配置单元中所有可能的模式 [英] How to fetch all possible pattern in hive

问题描述

我有下表:

+----------+----+
|customerID|name|
+----------+----+
|         1| Ram|
+----------+----+

我希望输出为(列值的所有可能值):

I want output as (All possible value of column-value):

+----------+----+
|customerID|name|
+----------+----+
|         1| Ram|
|         2| Arm|
|         3| Mar|
|         .| ...|
|         .| ...|
+----------+----+

推荐答案

分割字符串，爆炸数组，并使用交叉连接自身查找所有可能的组合:

Split string, explode array and use cross join with itself to find all possible combinations:

 with s as (select col 
              from (select explode( split(lower('Ram'),'')) as col)s 
             where col <>''
           ) 
 select concat(upper(s1.col), s2.col, s3.col) as name, 
        row_number() over() as customerId
   from s s1 
        cross join s s2 
        cross join s s3
where s1.col<>s2.col and s2.col<>s3.col;

结果:

OK
name    customerid
Mam     1
Mar     2
Mrm     3
Mra     4
Ama     5
Amr     6
Arm     7
Ara     8
Rma     9
Rmr     10
Ram     11
Rar     12
Time taken: 185.638 seconds, Fetched: 12 row(s)

如果没有最后一个 WHERE s1.col<> s2.col和s2.col<> s3.col ，您将获得 Aaa ，Arr ， Rrr 等

Without last WHERE s1.col<>s2.col and s2.col<>s3.col you will get all combinations like Aaa, Arr, Rrr, etc.

这篇关于如何获取配置单元中所有可能的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！