query_sql for the 3 most frequent value in array

[kilow]

Hello,i have a first script wich save account id in array. 

I would like to retrieve the 3 frequent value (account id) from this array.

example with the array $account_id:

$account_id[0] = 200008

$account_id[1] = 200008

$account_id[2] = 200008

$account_id[3] = 200008

$account_id[4] = 200006

$account_id[5] = 200006

$account_id[6] = 200006

$account_id[7] = 200004

$account_id[8] = 200004

$account_id[9] = 200009

 

Here i need the script to retrieve :

1 = 200008

2 = 200006

3 = 200004

 

Thanks for the help!

 

[sader1992]

https://github.com/rathena/rathena/blob/22c7f3988dd0b8f0b8089acb2f1e2cd11ca008ee/doc/script_commands.txt#L2305

---------------------------------------

*countinarray <array name>{[<start index>]},<array name>{[<start index>]};

This command will check for matches between the array values and return the number of matches.
While being optional, if [<start index>] is supplied, the search will begin from the given index value.

	setarray .@array[0], 100, 200, 300, 400, 500, 600;
	
	.@variable = 100;
	if(countinarray(.@array[0], .@variable))
		mes "The number 100 was found in the array .@array";
	
	countinarray(.@array[0], .@variable);
	//return 1 because the number 100 is an element of the array .@array
	
	setarray .@array2[0],100,500;
	countinarray(.@array[0], .@array2[0]);
	//return 2 because the numbers 100 and 500 are elements of the array .@array
	
	setarray .@array3[0],100,700;
	countinarray(.@array[0], .@array3[0]);
	//return 1 because the number 100 is an element of the array .@array
	//but the number 700 is not an element of the array .@array

	//also you can change the position between the arrays in the command
	if(countinarray(.@array[0], .@array3[0]) == countinarray(.@array3[0], .@array[0]))
		//This is true

For more details, see the sample in 'doc/sample/inarray.txt'.

---------------------------------------

Example:

	
	//creating an array with the values with no duplication
	for(.@i=0;.@i<getarraysize($account_id);.@i++){
		if(inarray(.@vtemp,$account_id[.@i]) == -1){
			.@vtemp[getarraysize(.@vtemp)] = $account_id[.@i];
		}
	}
	//now .@vtemp have all the values without duplication
	
	//counting
	for(.@i=0;.@i<getarraysize(.@vtemp);.@i++){
		.@v = .@vtemp[.@i];
		.@ctemp[.@i] = countinarray($account_id,.@v);
	}
	//now .@ctemp have all the values count with the index of the value
	
	//the result
	for(.@i=0;.@i<getarraysize(.@vtemp);.@i++){
		debugmes .@ctemp[.@i] + " = " + .@vtemp[.@i];
	}
	
	//burning the array but extracting only the first 3 (less if there is less then 3 in the array)
	.@wanted_count = min(getarraysize(.@ctemp),3);
	while(.@max < .@wanted_count){
		.@max++;
		.@c = max(.@ctemp);
		.@ndx = inarray(.@ctemp,.@c);
		.@s = getarraysize(.@result_value);
		.@result_value[.@s] = .@vtemp[.@ndx];
		.@result_count[.@s] = .@ctemp[.@ndx];
		
		deletearray(.@vtemp[.@ndx],1);
		deletearray(.@ctemp[.@ndx],1);
	}
	
	//end result
	for(.@i=0;.@i<getarraysize(.@result_value);.@i++){
		debugmes .@result_count[.@i] + " = " + .@result_value[.@i];
	}

 

Edited November 9, 2021 by sader1992
making the result only for top 3

[sader1992]

for(.@i=0;.@i<getarraysize(.@vtemp);.@i++){

to

for(.@i=0;.@i<getarraysize(.@result_value);.@i++){

[kilow]

Thank you Sader1992 for your speedy answer. Also it works fine.

	//creating an array with the values with no duplication
	for(.@i=0;.@i<getarraysize($account_id);.@i++){
		if(inarray(.@vtemp,$account_id[.@i]) == -1){
			.@vtemp[getarraysize(.@vtemp)] = $account_id[.@i]; // ; just missing here
		}
	}

 

	for(.@i=0;.@i<getarraysize(.@vtemp);.@i++){
		debugmes .@result_count[.@i] + " = " + .@result_value[.@i]; // this part retrieves only the first index, I don't know why, but i replaced .@i by the idx (0,1,2,3...) and so one line for each index
	}