package com.anna.hive.util;

public class ConvertUtil {

    /**
     * 統(tǒng)計(jì)分析函數(shù)的計(jì)算          sum count  avg
     * @param database 數(shù)據(jù)庫(kù)名稱
     * @param table 要操作的表的名稱
     * @param columns 結(jié)果列
     * @param column 計(jì)算列 ——> 也可以是數(shù)組
     * @param type 計(jì)算功能：sum / average /count
     * @return 翻譯后的SQL
     * */
    public String SAC(String database, String table, String[] columns, String column, String type) {
        String cls = "";

        for (String c : columns) {
            cls += c + ",";
        }
        String sql = "select " + cls + type  + "(" + column + ")" + " from " + database + "." + table + " group by " + cls.substring(0,cls.length() - 1);
        return sql;
    }

    /**
     * 獲得指定數(shù)據(jù)表的前n條數(shù)據(jù)  limit
     * @param database 數(shù)據(jù)庫(kù)名稱
     * @param table 數(shù)據(jù)表
     * @param columns 要獲取的列
     * @param n 要獲取的記錄數(shù)
     * @return 翻譯后的SQL
     * */
    public String getTableDataByN(String database, String table, String[] columns, int n) {
        String cls = "";

        for (String c : columns) {
            cls += c  + ",";
        }

        cls = cls.substring(0, cls.length() - 1);
        String sql = "select " + cls + " from " + database + "." + table + " limit " + n;
        return sql;
    }

    /**
     * 獲得指定數(shù)據(jù)表的前百分之n的數(shù)據(jù)         tablesample
     * @param database 數(shù)據(jù)庫(kù)
     * @param table 數(shù)據(jù)庫(kù)表
     * @param columns 待查詢的列
     * @param percent 百分比
     * @return 翻譯后的SQL
     * */
    public String getDataByNPercent(String database, String table, String[] columns, double percent) {
        String cls = "";

        for (String s : columns) {
            cls += s + ",";
        }

        cls = cls.substring(0, cls.length() - 1);
        String sql = "select " + cls + " from " + database + "." + table + " tablesample(" + percent + " percent)";
        return sql;
    }

    /**
     * 隨機(jī)抽樣     rand()
     * @param database 數(shù)據(jù)庫(kù)名稱
     * @param table 數(shù)據(jù)表名稱
     * @param columns 待查詢返回的列
     * @param n 要獲取多少一條記錄數(shù)
     * @return 翻譯后的SQL
     * */
    public String getRandomDataN(String database, String table, String[] columns, int n) {
        String cls = "";

        for (String c : columns) {
            cls += c + ",";
        }
        String sql = "select " + cls + "rand() r from " + database + "." + table + " order by r limit " + n;
        return sql;
    }

    /**
     * 條件查詢
     * @param database 數(shù)據(jù)庫(kù)名稱
     * @param table 數(shù)據(jù)表名稱
     * @param columns 條件列名
     * @param conditions 條件
     * @param values 條件值
     * @return 翻譯后的SQL
     * */
    public String getDataByCondition(String database, String table, String[] columns, String[] conditions, String[] values) {
        String c = "";
        for (int i = 0; i < columns.length; i++) {
            c += " and  " + columns[i] + " " + conditions[i] + " " + values[i];
        }

        String sql = "select * from " + database + "." + table + "where 1 = 1 " +c;
        return sql;
    }
}