需要编写等效于以下sql查询的java代码(算法)-
选择u。浏览器,a.app_name,count(*) 作为用户的no_of_apps和内部加入应用程序a 其中u.user_id =由u.browsers组的a.user_id组,由no_of_apps限制的a.app_name顺序限制为0.2。
有两个不同的.tsv文件(user.tsv和app.tsv),它们包含的数据格式为-
+ 1.users.tsv
user_id浏览器 1个Mozilla 2 Mozilla 3铬
+2。apps.tsv
app_id user_id app_name 1 1 HelloWorld 2 3声纳 3 1声纳 4 2 HelloWorld 5 1 HelloWorld
在此,user_id的行为类似于apps.tsv文件中的外键。我真正需要的是java中从两个文件中获取数据的算法,如下所示:
浏览器app_name no_of_apps Mozilla HelloWorld 3 Mozilla声纳1 铬声纳1
我为此编写的代码是-
import java.util.*;
import java.util.Map.*;
import java.util.HashMap;
import java.io.*;
import java.nio.file.*;
public class Task1 {
public static void main(String... args) {
//cloumn places
int state_col = 2;
int user_col = 0;
int jobid_col = 2;
List<String> userLines = null;
List<String> appLines = null;
String[][] userResultArray = null;
String[][] appResultArray = null;
Map<String, String> userMap = new HashMap<String,String>();
Map<String, String> appMap = new HashMap<String, String>();
Map<String, String> userJobMap = new HashMap<String,String>();
List<Integer> app = new ArrayList<Integer>();
try {
userLines = Files.readAllLines(Paths.get("/home/indg/java-spring/spring/idea_workspace/Task1/tabFiles/users.tsv"));
appLines = Files.readAllLines(Paths.get("/home/indg/java-spring/spring/idea_workspace/Task1/tabFiles/apps.tsv"));
} catch(Exception ex) {
ex.printStackTrace();
}
if(userLines != null) {
userResultArray = new String[userLines.size()][];
for(int i =0; i<userLines.size()-1; i++){
userResultArray[i] = userLines.get(i).split("\t"); //tab-separated
if(userResultArray[i][state_col] != "" && userResultArray[i][state_col] != null) {
userMap.put(userResultArray[i][user_col],userResultArray[i][state_col]);
}
}
} else {
System.out.println("Error in reading line");
}
if(appLines != null) {
appResultArray = new String[appLines.size()][];
for(int i =0; i<appLines.size()-1; i++){
appResultArray[i] = appLines.get(i).split("\t"); //tab-separated
if(appResultArray[i][jobid_col] != "" && appResultArray[i][jobid_col] != null) {
appMap.put(appResultArray[i][user_col], appResultArray[i][jobid_col]);
}
}
} else {
System.out.println("Error in reading line");
}
for(String userKey: userMap.keySet()) {
//System.out.println(userEntry.getKey() + "**");
String jobId = appMap.get(userKey);
if(jobId != null && userMap.get(userKey) != null && userMap.get(userKey) != "" ) {
userJobMap.put(userMap.get(userKey), jobId);
}
}
System.out.println(userJobMap + " --");
}
}
我也想例如在查询中应用order by和limit-by by no_of_apps limit 0,2如何在代码中做到这一点
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
public class Task1 {
public static void main(String... args) {
List<String> userLines = Arrays.asList(new String[] { "1\tMozilla", "2\tMozilla", "3\tChrome" });
List<String> appLines = Arrays
.asList(new String[] { "1\t1\tHelloWorld", "2\t3\tSonar", "3\t1\tSonar", "4\t2\tHelloWorld", "5\t1\tHelloWorld" });
Map<String, String> userMap = new HashMap<String, String>();
Map<String, Map<String, Integer>> resultMap = new HashMap<String, Map<String, Integer>>();
if (userLines != null) {
for (int i = 0; i < userLines.size(); i++) {
String[] userResultArray = userLines.get(i).split("\t"); // tab-separated
userMap.put(userResultArray[0], userResultArray[1]);
}
} else {
System.out.println("Error in reading line");
}
if (appLines != null) {
for (int i = 0; i < appLines.size(); i++) {
String[] appResultArray = appLines.get(i).split("\t"); // tab-separated
if (userMap.containsKey(appResultArray[1])) {
String userName = userMap.get(appResultArray[1]);
if (resultMap.containsKey(userName)) {
Map<String, Integer> map = resultMap.get(userName);
Integer n = map.get(appResultArray[2]) == null ? 0 : map.get(appResultArray[2]);
map.put(appResultArray[2], ++n);
} else {
Map<String, Integer> map = new HashMap<String, Integer>();
map.put(appResultArray[2], 1);
resultMap.put(userName, map);
}
}
}
} else {
System.out.println("Error in reading line");
}
// sort
Map<Integer, List<String>> sortedMap = new TreeMap<Integer, List<String>>();
for (String userName : resultMap.keySet()) {
for (String app : resultMap.get(userName).keySet()) {
Integer n = resultMap.get(userName).get(app);
List<String> list = sortedMap.get(n) == null ? new ArrayList<String>() : sortedMap.get(n);
list.add(userName + "\t" + app);
sortedMap.put(n, list);
}
}
int limit = 0;
outer:
for (Integer n : sortedMap.keySet()) {
for (String s : sortedMap.get(n)) {
System.out.print(s);
System.out.println("\t" + n);
if (++limit == 2) break outer;
}
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句