Java Query.groupBy方法代码示例

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testFullyQualifiedColumnNames() throws Exception {
    final MutableSchema schema = new MutableSchema("sch");
    final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
    final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
    final MutableColumn ageColumn = new MutableColumn("age", ColumnType.INTEGER).setTable(table);
    schema.addTable(table);
    table.addColumn(nameColumn);
    table.addColumn(ageColumn);

    final Query q = new Query();
    q.select(ageColumn).selectCount();
    q.from(table);
    q.where(ageColumn, OperatorType.GREATER_THAN, 18);
    q.groupBy(ageColumn);
    q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
    q.orderBy(ageColumn);

    final String sql = new DB2QueryRewriter(null).rewriteQuery(q);

    assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > 18 "
            + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testFullyQualifiedColumnNamesWithFilterItemContainingTimestamp() throws Exception {
    final MutableSchema schema = new MutableSchema("sch");
    final MutableTable table = new MutableTable("tab", TableType.TABLE, schema);
    final MutableColumn nameColumn = new MutableColumn("name", ColumnType.VARCHAR).setTable(table);
    final MutableColumn dateColumn = new MutableColumn("age", ColumnType.TIMESTAMP).setTable(table);
    schema.addTable(table);
    table.addColumn(nameColumn);
    table.addColumn(dateColumn);

    final Query q = new Query();
    q.select(dateColumn).selectCount();
    q.from(table);
    q.where(dateColumn, OperatorType.GREATER_THAN, "2012-10-31 08:09:54");
    q.groupBy(dateColumn);
    q.having(FunctionType.COUNT, nameColumn, OperatorType.LESS_THAN, 100);
    q.orderBy(dateColumn);

    final String sql = new DB2QueryRewriter(null).rewriteQuery(q);

    assertEquals("SELECT sch.tab.age, COUNT(*) FROM sch.tab WHERE sch.tab.age > TIMESTAMP ('2012-10-31 08:09:54') "
            + "GROUP BY sch.tab.age HAVING COUNT(sch.tab.name) < 100 ORDER BY sch.tab.age ASC", sql);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
@Test
public void testGroupByQuery() throws Exception {
    Table table = dataContext.getDefaultSchema().getTableByName(peopleIndexType);

    Query q = new Query();
    q.from(table);
    q.groupBy(table.getColumnByName("gender"));
    q.select(new SelectItem(table.getColumnByName("gender")),
            new SelectItem(FunctionType.MAX, table.getColumnByName("age")),
            new SelectItem(FunctionType.MIN, table.getColumnByName("age")), new SelectItem(FunctionType.COUNT, "*",
                    "total"), new SelectItem(FunctionType.MIN, table.getColumnByName("id")).setAlias("firstId"));
    q.orderBy("gender");
    DataSet data = dataContext.executeQuery(q);
    assertEquals(
            "[peopletype.gender, MAX(peopletype.age), MIN(peopletype.age), COUNT(*) AS total, MIN(peopletype.id) AS firstId]",
            Arrays.toString(data.getSelectItems().toArray()));

    assertTrue(data.next());
    assertEquals("Row[values=[female, 20, 17, 5, 5]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[male, 19, 17, 4, 1]]", data.getRow().toString());
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testSimpleHaving() throws Exception {
    Query q = new Query();
    q.from(table2, "c");
    Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
    Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);

    q.groupBy(roleColumn);
    SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
    q.select(new SelectItem(roleColumn), countSelectItem);
    q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 1));
    q.orderBy(new OrderByItem(countSelectItem));
    assertEquals(
            "SELECT c.name, COUNT(c.contributor_id) AS my_count FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 1 ORDER BY COUNT(c.contributor_id) ASC",
            q.toString());

    DataSet data = getDataContext().executeQuery(q);
    assertTrue(data.next());
    assertEquals("Row[values=[founder, 2]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[developer, 5]]", data.getRow().toString());
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testGroupByQuery() throws Exception {
    DataContext dc = new CsvDataContext(new File("src/test/resources/csv_people.csv"));
    Table table = dc.getDefaultSchema().getTableByName("csv_people.csv");

    Query q = new Query();
    q.from(table);
    q.groupBy(table.getColumnByName("gender"));
    q.select(new SelectItem(table.getColumnByName("gender")),
            new SelectItem(FunctionType.MAX, table.getColumnByName("age")),
            new SelectItem(FunctionType.MIN, table.getColumnByName("age")), new SelectItem(FunctionType.COUNT, "*",
                    "total"), new SelectItem(FunctionType.MIN, table.getColumnByName("id")).setAlias("firstId"));
    DataSet data = dc.executeQuery(q);
    assertEquals(
            "[csv_people.csv.gender, MAX(csv_people.csv.age), MIN(csv_people.csv.age), COUNT(*) AS total, MIN(csv_people.csv.id) AS firstId]",
            Arrays.toString(data.getSelectItems().toArray()));

    String[] expectations = new String[] { "Row[values=[female, 20, 17, 5, 5]]", "Row[values=[male, 19, 17, 4, 1]]" };

    assertTrue(data.next());
    assertTrue(Arrays.asList(expectations).contains(data.getRow().toString()));
    assertTrue(data.next());
    assertTrue(Arrays.asList(expectations).contains(data.getRow().toString()));
    assertFalse(data.next());
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testGroupByQuery() throws Exception {
	Connection con = getTestDbConnection();
	DataContext dc = new JdbcDataContext(con);
	Schema schema = dc.getDefaultSchema();
	Table employeesTable = schema.getTableByName("EMPLOYEES");
	Table orderDetailsTable = schema.getTableByName("ORDERDETAILS");
	Query q = new Query().from(employeesTable, "e").from(orderDetailsTable,
			"c");
	q.select(orderDetailsTable.getColumns().get(0))
			.select(new SelectItem(FunctionType.MAX, employeesTable
					.getColumns().get(0)));
	q.groupBy(orderDetailsTable.getColumns().get(0));
	assertEquals(
			"SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c GROUP BY c._ORDERNUMBER_",
			q.toString().replace('\"', '_'));

	QuerySplitter qs = new QuerySplitter(dc, q);
	assertEquals(326, qs.getRowCount());

	List<Query> splitQueries = qs.setMaxRows(250).splitQuery();

	assertEquals(
			"[SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ < 10262 OR c._ORDERNUMBER_ IS NULL) GROUP BY c._ORDERNUMBER_, SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ > 10262 OR c._ORDERNUMBER_ = 10262) GROUP BY c._ORDERNUMBER_]",
			Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
	assertSameCount(dc, qs, splitQueries);
	assertEquals(2, splitQueries.size());
	assertEquals("[162, 164]", Arrays.toString(getCounts(dc, splitQueries)));

	DataSet data = qs.executeQueries();
	int count = 0;
	while (data.next()) {
		if (count == 2) {
			assertEquals("Row[values=[10102, 1702]]", data.getRow()
					.toString());
		}
		count++;
	}
	data.close();
	assertEquals(326, count);
}
 

import org.apache.metamodel.query.Query; //导入方法依赖的package包/类
public void testHavingFunctionNotSelected() throws Exception {
    Query q = new Query();
    q.from(table2, "c");
    Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
    Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);

    q.groupBy(roleColumn);
    SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
    q.select(new SelectItem(roleColumn));
    q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 3));
    assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 3",
            q.toString());

    DataSet data = getDataContext().executeQuery(q);
    assertTrue(data.next());
    assertEquals("Row[values=[developer]]", data.getRow().toString());
    assertFalse(data.next());
    data.close();

    q.getHavingClause().removeItems();
    q.having(new FilterItem(SelectItem.getCountAllItem(), OperatorType.GREATER_THAN, 3));
    assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(*) > 3", q.toString());
    data = getDataContext().executeQuery(q);
    assertTrue(data.next());
    assertEquals("Row[values=[developer]]", data.getRow().toString());
    assertFalse(data.next());
    data.close();
}