Java TableNode.groupBy方法代码示例

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
public void test_单表merge_函数下推() {
    TableNode table = new TableNode("TABLE1");
    table.select("MAX(ID) AS ID");
    table.orderBy("COUNT(ID)");
    table.groupBy("SUBSTRING(NAME,0,10)");
    table.having("COUNT(ID) > 1");
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency());// 并行

    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("SUBSTRING(NAME, 0, 10)", query.getColumns().get(1).toString());// 下推成功
    Assert.assertEquals("COUNT(ID)", query.getColumns().get(2).toString());// 下推成功
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_orderby_merge下推_group为order的子集() {
    TableNode table1 = new TableNode("TABLE1");
    table1.orderBy("ID", false);
    table1.orderBy("NAME", true);
    table1.groupBy("ID");

    MergeNode merge = new MergeNode();
    merge.addChild(table1);
    merge.build();
    QueryTreeNode qtn = OrderByPusher.optimize(merge);

    // 子节点先按照NAME做group
    // by,因为子节点的orderby和父节点的groupBy满足一个前序匹配，这里直接使用父节点id,Name做order by
    // merge节点先按id做group
    // by(底下子节点为id,name排序，不需要临时表排序)，groupby处理后的结果也满足id,name的排序，直接返回，不需要临时表

    Assert.assertEquals(2, ((TableNode) qtn.getChild()).getOrderBys().size());
    Assert.assertEquals("ID", ((TableNode) qtn.getChild()).getOrderBys().get(0).getColumnName());
    Assert.assertEquals("NAME", ((TableNode) qtn.getChild()).getOrderBys().get(1).getColumnName());

    Assert.assertEquals(1, ((TableNode) qtn.getChild()).getGroupBys().size());
    Assert.assertEquals("ID", ((TableNode) qtn.getChild()).getGroupBys().get(0).getColumnName());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_TableNode转化为index到kv_复杂条件查询() {
    TableNode table = new TableNode("TABLE1");
    table.select("(ID + NAME) AS NEWNAME"); // 设置为函数
    table.query("NAME = 1 AND ID > 3 AND SCHOOL = 1");
    table.orderBy("SCHOOL", false);// 增加一个隐藏列
    table.groupBy("NEWNAME");
    build(table);

    QueryTreeNode qn = table.convertToJoinIfNeed();

    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertEquals("TABLE1._NAME.NAME = 1", ((JoinNode) qn).getLeftNode().getKeyFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID > 3", ((JoinNode) qn).getLeftNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1.SCHOOL = 1", ((JoinNode) qn).getRightNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID = TABLE1.ID", ((JoinNode) qn).getJoinFilter().get(0).toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void testWhere_字段多级子查询() throws QueryException, SqlParserException {
    String subSql = "SELECT B.* FROM TABLE2 B WHERE B.ID=1 GROUP BY SCHOOL HAVING COUNT(*) > 1 ORDER BY ID DESC LIMIT 1";
    String sql = "SELECT NAME FROM TABLE1 WHERE NAME=(SELECT C.NAME FROM (" + subSql + ") C )";
    QueryTreeNode qn = query(sql);
    qn.build();

    TableNode table1 = new TableNode("TABLE1");
    QueryTreeNode qnExpected = table1.select("NAME");

    TableNode table2 = new TableNode("TABLE2");
    table2.alias("C").setSubAlias("B").select("*").query("B.ID=1");
    table2.groupBy("SCHOOL");
    table2.having("COUNT(*) > 1");
    table2.orderBy("ID", false);
    table2.limit(0, 1);
    table2.setSubQuery(true);

    QueryNode subQuery = new QueryNode(table2);
    subQuery.select("C.NAME");

    IColumn column = ASTNodeFactory.getInstance().createColumn().setColumnName("NAME");
    IBooleanFilter filter = ASTNodeFactory.getInstance()
        .createBooleanFilter()
        .setColumn(column)
        .setValue(subQuery)
        .setOperation(OPERATION.EQ);
    table1.query(filter);
    qnExpected.build();
    assertEquals(qn, qnExpected);
}
 

import com.taobao.tddl.optimizer.core.ast.query.TableNode; //导入方法依赖的package包/类
@Test
public void test_merge的distinct下推() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");

    IColumn id = ASTNodeFactory.getInstance().createColumn();
    id.setColumnName("ID");
    id.setDistinct(true);

    IColumn name = ASTNodeFactory.getInstance().createColumn();
    name.setColumnName("NAME");
    name.setDistinct(true);

    IColumn school = ASTNodeFactory.getInstance().createColumn();
    school.setColumnName("SCHOOL");
    school.setDistinct(true);

    table1.groupBy("NAME");
    table1.orderBy("ID");

    MergeNode merge = table1.merge(table2);
    merge.select(id, name, school);
    merge.build();

    OrderByPusher.optimize(merge);
    Assert.assertEquals(3, table1.getOrderBys().size());
    Assert.assertEquals("TABLE1.NAME", table1.getOrderBys().get(0).getColumn().toString());
}