For the most common string-CodePudding

Have a score of the string as follows:
1265535165656312653126312312
How can use SQL pleasure find predefined n strings, the emergence of the most times such as n=3,
The results of 312,
Because the actual amount of data is very big to consider efficiency, use brute force is not workable, consult!

CodePudding user response:

Trouble ah, don't use SQL, take out to use python, Java, feeling the feasibility,
Such as the great god

CodePudding user response:

This should also be exhaustive method, other don't arrived,

 with t1 as 
(select '1265535165656312653126312312' c1 from dual) 
, t2 as 
(select rownum rn from dual connect by rownum<=(select length (c1) - 2 from t1)) 
Select * from (
Select substr (c1, rn, and 3) s, the count (*) 
 cThe from t1, t2 
Group by substr (c1, rn, 3) 
The order by the count (*) desc 
Aa) 
Where rownum=1

CodePudding user response:

Yes, exhaustive efficiency has some problems, not get,
With other process and efficiency problems, did not think of a good algorithm

CodePudding user response:

Still thanks a lot, but efficiency may have a problem,

refer to the second floor ZBDZJX response:

this should also be exhaustive method, other don't arrived,

 with t1 as 
(select '1265535165656312653126312312' c1 from dual) 
, t2 as 
(select rownum rn from dual connect by rownum<=(select length (c1) - 2 from t1)) 
Select * from (
Select substr (c1, rn, and 3) s, the count (*) 
 cThe from t1, t2 
Group by substr (c1, rn, 3) 
The order by the count (*) desc 
Aa) 
Where rownum=1

CodePudding user response:

Have original and seek the longest tired bad string algorithm, but I don't know whether to ask this question, as follows, the basic idea is, draw the following form part of the standard color comparison, high efficiency! Please directly!

CodePudding user response:

Said don't say n=3, n=1 to find the maximum value, that is to split the string in a field in the database is not apart into several fields, is what algorithm; Will certainly affect the efficiency of the split,

CodePudding user response:

But how to deal with after break up? In addition, there are countless this string, all into a column, is not possible?

refer to 6th floor POM_24 response:

say n=3, say n=1 to find the maximum value, that is to split the string in the database within a field not apart into several fields, is what algorithm; Will certainly affect the efficiency of the split,

CodePudding user response:

 with t1 as 
(select '1265535165656312653126312312' c1 from dual) 
, t2 as (select substr (c1, level, and 3) s, regexp_count (c1, substr (c1, level, 3)) from t1 c 
Connect by level & lt; C=length (c1) - 2 order by desc) 
Select * from t2 where rownum=1;

CodePudding user response:

Quote: refer to 7th floor menper response:

But how to deal with after break up? In addition, there are countless this string, all into a column, is not possible?

After the split line is stored, string maximum length is 8000, which is up to 8000 lines, split again after the operation, there is no difficulty,