Java Code Examples for com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock#getGroupBy()

The following examples show how to use com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock#getGroupBy() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: TestMySQLItemVisitor.java From dble with GNU General Public License v2.0

6 votes

@Test
public void testGroupbyOrder() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2 from table1 group by col1 desc,col2 asc ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    int i = 0;
    for (SQLExpr p : groupBy.getItems()) {
        i++;
        String groupCol = "col" + i;
        MySqlOrderingExpr groupitem = (MySqlOrderingExpr) p;
        SQLExpr q = groupitem.getExpr();
        MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
        q.accept(v);
        Item item = v.getItem();
        Assert.assertEquals(true, groupCol.equals(item.getItemName()));
    }
}

Example 2

Source File: DruidSelectParser.java From dble with GNU General Public License v2.0

5 votes

private void parseAggGroupCommon(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs,
                                 MySqlSelectQueryBlock mysqlSelectQuery, TableConfig tc) throws SQLException {
    Map<String, String> aliaColumns = new HashMap<>();
    boolean isDistinct = (mysqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCT) || (mysqlSelectQuery.getDistionOption() == SQLSetQuantifier.DISTINCTROW);
    parseAggExprCommon(schema, rrs, mysqlSelectQuery, aliaColumns, tc, isDistinct);
    if (rrs.isNeedOptimizer()) {
        tryAddLimit(schema, tc, mysqlSelectQuery, rrs);
        rrs.setSqlStatement(stmt);
        return;
    }

    // distinct change to group by
    if (isDistinct) {
        mysqlSelectQuery.setDistionOption(0);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (String fieldName : aliaColumns.keySet()) {
            groupBy.addItem(new SQLIdentifierExpr(fieldName));
        }
        mysqlSelectQuery.setGroupBy(groupBy);
    }

    // setGroupByCols
    if (mysqlSelectQuery.getGroupBy() != null) {
        List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
        String[] groupByCols = buildGroupByCols(groupByItems, aliaColumns);
        rrs.setGroupByCols(groupByCols);
    }

    if (isDistinct) {
        rrs.changeNodeSqlAfterAddLimit(statementToString(stmt), 0, -1);
    }
}

Example 3

Source File: TestMySQLItemVisitor.java From dble with GNU General Public License v2.0

5 votes

@Test
public void testGroupby() {
    MySqlSelectQueryBlock query = getQuery("select col1,col2 from table1 group by col1,col2");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    int i = 0;
    for (SQLExpr p : groupBy.getItems()) {
        i++;
        String groupCol = "col" + i;
        MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
        p.accept(v);
        Item item = v.getItem();
        Assert.assertEquals(true, groupCol.equals(item.getItemName()));
    }
}

Example 4

Source File: TestMySQLItemVisitor.java From dble with GNU General Public License v2.0

5 votes

@Test
public void testGroupbyHaving() {
    MySqlSelectQueryBlock query = getQuery("select col1  from table1 group by col1 having count(*)>1  ");
    SQLSelectGroupByClause groupBy = query.getGroupBy();
    SQLExpr q = groupBy.getHaving();
    MySQLItemVisitor v = new MySQLItemVisitor(this.currentDb, utf8Charset, null, null);
    q.accept(v);
    Item item = v.getItem();
    Assert.assertEquals(true, "COUNT(*) > 1".equals(item.getItemName()));
}

Example 5

Source File: MysqlCountOutputVisitor.java From Zebra with Apache License 2.0

5 votes

public boolean visit(MySqlSelectQueryBlock x) {
	if (x.getOrderBy() != null) {
		x.getOrderBy().setParent(x);
	}

	boolean rewriteDistinct = false;
	if (x.getSelectList() != null) {
		rewriteDistinct = visitSelectItems(x.getSelectList(), SQLSetQuantifier.DISTINCT == x.getDistionOption());
	}

	if (x.getFrom() != null) {
		println();
		print0(ucase ? "FROM " : "from ");
		x.getFrom().accept(this);
	}

	if (x.getWhere() != null) {
		println();
		print0(ucase ? "WHERE " : "where ");
		x.getWhere().setParent(x);
		x.getWhere().accept(this);
	}

	if (x.getGroupBy() != null) {
		println();
		x.getGroupBy().accept(this);
	}

	if (x.getOrderBy() != null) {
		println();
		x.getOrderBy().accept(this);
	}

	if (rewriteDistinct) {
		print0(") ZebraDaoDistinctTable");
	}

	return false;
}

Example 6

Source File: MySqlSelectParser.java From baymax with Apache License 2.0

4 votes

/**
 * 解析groupby
 * @param result
 * @param plan
 * @param mysqlSelectQuery
 */
protected void parseGroupBy(ParseResult result, ExecutePlan plan, MySqlSelectQueryBlock mysqlSelectQuery){
    if(mysqlSelectQuery.getGroupBy() == null) {
        return;
    }
    List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
    if (groupByItems == null || groupByItems.size() == 0){
        return;
    }
    List<SQLSelectItem> selectList      = mysqlSelectQuery.getSelectList();
    List<String> groupbyColumns  = new ArrayList<String>(groupByItems.size());
    for(SQLExpr item : groupByItems){
        String name = null;
        if (item instanceof MySqlSelectGroupByExpr){
            name = StringUtil.removeDot(((MySqlSelectGroupByExpr) item).getExpr().toString());
        }else if (item instanceof SQLIdentifierExpr){
            name = item.toString();
        }else if (item instanceof SQLName){
            name = ((SQLName) item).getSimpleName();
        }else {
            throw new BayMaxException("group by 不支持的表达式:" + item.toString());
        }
        if (result.getAliaColumns() != null){
            // 有别名,说明在select list中使用了别名
            String alias = result.getAliaColumns().get(name);
            if (alias != null){
                // select user_id as uid ....order by user_id
                // 要把oderby的user_id转换为uid,以便结果集合并,这个替换是等价的
                // 因为合并的时候需要根据orderby的字段,取值,比较
                groupbyColumns.add(alias);
                continue;
            }
        }
        if (!result.isHasAllColumnExpr()){
            // select列表中没有orderby的字段 添加，用于后面做合并
            SQLIdentifierExpr exp = new SQLIdentifierExpr(name);
            // item
            SQLSelectItem addItem = new SQLSelectItem();
            addItem.setExpr(exp);
            exp.setParent(item);
            selectList.add(addItem);
        }
        groupbyColumns.add(name);
    }
    plan.setGroupbyColumns(groupbyColumns);
}

Example 7

Source File: SqlParser.java From elasticsearch-sql with Apache License 2.0

4 votes

private void findGroupBy(MySqlSelectQueryBlock query, Select select) throws SqlParseException {
    SQLSelectGroupByClause groupBy = query.getGroupBy();

    //modified by xzb group by 增加Having语法
    if (null != query.getGroupBy() && null != query.getGroupBy().getHaving()) {
        select.setHaving(query.getGroupBy().getHaving().toString());
    }

    SQLTableSource sqlTableSource = query.getFrom();
    if (groupBy == null) {
        return;
    }
    List<SQLExpr> items = groupBy.getItems();

    List<SQLExpr> standardGroupBys = new ArrayList<>();
    for (SQLExpr sqlExpr : items) {
        //todo: mysql expr patch
        if (sqlExpr instanceof MySqlOrderingExpr) {
            MySqlOrderingExpr sqlSelectGroupByExpr = (MySqlOrderingExpr) sqlExpr;
            sqlExpr = sqlSelectGroupByExpr.getExpr();
        }
        if ((sqlExpr instanceof SQLParensIdentifierExpr || !(sqlExpr instanceof SQLIdentifierExpr || sqlExpr instanceof SQLMethodInvokeExpr)) && !standardGroupBys.isEmpty()) {
            // flush the standard group bys
            // zhongshu-comment 先将standardGroupBys里面的字段传到select对象的groupBys字段中，然后给standardGroupBys分配一个没有元素的新的list
            select.addGroupBy(convertExprsToFields(standardGroupBys, sqlTableSource));
            standardGroupBys = new ArrayList<>();
        }

        if (sqlExpr instanceof SQLParensIdentifierExpr) {
            // single item with parens (should get its own aggregation)
            select.addGroupBy(FieldMaker.makeField(((SQLParensIdentifierExpr) sqlExpr).getExpr(), null, sqlTableSource.getAlias()));
        } else if (sqlExpr instanceof SQLListExpr) {
            // multiple items in their own list
            SQLListExpr listExpr = (SQLListExpr) sqlExpr;
            select.addGroupBy(convertExprsToFields(listExpr.getItems(), sqlTableSource));
        } else {
            // everything else gets added to the running list of standard group bys
            standardGroupBys.add(sqlExpr);
        }
    }
    if (!standardGroupBys.isEmpty()) {
        select.addGroupBy(convertExprsToFields(standardGroupBys, sqlTableSource));
    }
}