Examples of com.google.visualization.datasource.query.QuerySelection

Package com.google.visualization.datasource.query

Examples of com.google.visualization.datasource.query.QuerySelection

com.google.visualization.datasource.query.QuerySelection
Selection definition for a query. Selection is defined as a list of column IDs. It can also include aggregations for grouping/pivoting and scalar functions. @author Itai R.

  public void testSelectionOfScalarFunction() throws Exception {
    DataTable res = MockDataSource.getData(3);


    // select name, dept, hour(lunchTime)
    Query q = new Query();
    QuerySelection selection = new QuerySelection();
    selection.addColumn(new SimpleColumn("name"));
    selection.addColumn(new SimpleColumn("dept"));
    selection.addColumn(new ScalarFunctionColumn(
        Lists.newArrayList((AbstractColumn) new SimpleColumn("lunchTime")),
        TimeComponentExtractor.getInstance(
            TimeComponentExtractor.TimeComponent.HOUR)));


    q.setSelection(selection);

View Full Code Here


    // select sum(age), dept, hour(min(lunchTime)) group by dept
    Query q = new Query();


    // Add selection.
    QuerySelection selection = new QuerySelection();
    selection.addColumn(new AggregationColumn(new SimpleColumn("age"),
        AggregationType.SUM));
    selection.addColumn(new SimpleColumn("dept"));
    selection.addColumn(new ScalarFunctionColumn(
        Lists.newArrayList((AbstractColumn) new AggregationColumn(
            new SimpleColumn("lunchTime"), AggregationType.MIN)),
        TimeComponentExtractor.getInstance(
            TimeComponentExtractor.TimeComponent.HOUR)));
    q.setSelection(selection);


    // Add group.
    QueryGroup group = new QueryGroup();
    group.addColumn(new SimpleColumn("dept"));
    q.setGroup(group);


    q.validate();


    DataTable result = QueryEngine.executeQuery(q, res, ULocale.US);


    // Test column description
    List<ColumnDescription> cols = result.getColumnDescriptions();


    assertEquals(3, cols.size());
    assertEquals("sum-age", cols.get(0).getId());
    assertEquals("dept", cols.get(1).getId());
    assertEquals("hour_min-lunchTime", cols.get(2).getId());


    String[][] resultStrings = MockDataSource.queryResultToStringMatrix(result);
    assertEquals(3, resultStrings.length);


    assertStringArraysEqual(new String[]{"92.0", "Eng", "12.0"},
        resultStrings[0]);
    assertStringArraysEqual(new String[]{"24.0", "Marketing", "13.0"},
        resultStrings[1]);
    assertStringArraysEqual(new String[]{"57.0", "Sales", "12.0"},
        resultStrings[2]);


    // Test group-by column with scalar function in the selection:
    // select count(name), hour(lunchTime) group by lunchtime
    q = new Query();
    // Add selection
    QuerySelection selection1 = new QuerySelection();
    selection1.addColumn(new AggregationColumn(new SimpleColumn("name"),
        AggregationType.COUNT));
    selection1.addColumn(new ScalarFunctionColumn(
        Lists.newArrayList((AbstractColumn) new SimpleColumn("lunchTime")),
        TimeComponentExtractor.getInstance(
            TimeComponentExtractor.TimeComponent.HOUR)));
    q.setSelection(selection1);

View Full Code Here

      QueryGroup group = new QueryGroup();
      for (AbstractColumn col : newGroupColumns) {
        group.addColumn(col);
      }
      dataSourceQuery.setGroup(group);
      QuerySelection selection = new QuerySelection();
      for (AbstractColumn col : newSelectionColumns) {
        selection.addColumn(col);
      }
      dataSourceQuery.setSelection(selection);


      // Build the completion query to group by the grouping columns. Because an aggregation is
      // required, make a dummy aggregation on the original column by which the aggregation is
      // required.
      // This original column must be unique for a given set of values for the grouping/pivoting
      // columns so any aggregation operation out of MIN, MAX, AVG will return the value
      // itself and will not aggregate anything. The example from before,
      // SELECT A, max(B) GROUP BY A PIVOT C turns into SELECT A, min(max-B) GROUP BY A PIVOT C


      completionQuery.copyFrom(query);
      completionQuery.setFilter(null);


      QuerySelection completionSelection = new QuerySelection();
      List<AbstractColumn> originalSelectedColumns =
          query.getSelection().getColumns();
      for (int i = 0; i < originalSelectedColumns.size(); i++) {
        AbstractColumn column = originalSelectedColumns.get(i);
        if (query.getGroup().getColumns().contains(column)) {
          completionSelection.addColumn(column);
        } else { // Must be an aggregation column if doesn't appear in the grouping.
          // The id here is the id generated by the data source for the column containing
          // the aggregated data, e.g., max-B.
          String id = column.getId();
          // MIN is chosen arbitrarily, because there will be exactly one.
          completionSelection.addColumn(
              new AggregationColumn(new SimpleColumn(id), AggregationType.MIN));
        }
      }


      completionQuery.setSelection(completionSelection);

View Full Code Here

   */
  private static QueryPair splitSelect(Query query) {
    Query dataSourceQuery = new Query();
    Query completionQuery = new Query();
    if (query.getSelection() != null) {
      QuerySelection selection = new QuerySelection();
      for (String simpleColumnId : query.getAllColumnIds()) {
        selection.addColumn(new SimpleColumn(simpleColumnId));
      }
      // Column selection can be empty. For example, for query "SELECT 1".
      dataSourceQuery.setSelection(selection);
    }

View Full Code Here

   * Sets up the default query.
   */
  @Override
  public void setUp() throws Exception {
    q = new Query();
    QuerySelection selection = new QuerySelection();
    selection.addColumn(new SimpleColumn("A"));
    selection.addColumn(new AggregationColumn(new SimpleColumn("B"), AggregationType.MAX));
    q.setSelection(selection);
    QuerySort sort = new QuerySort();
    sort.addSort(new ColumnSort(new SimpleColumn("A"), SortOrder.DESCENDING));
    q.setSort(sort);
    QueryFilter filter = new ColumnValueFilter(new SimpleColumn("A"), new TextValue("foo"),

View Full Code Here

  public void testSplitSQLWithPivot() throws Exception {
    QueryPair split = QuerySplitter.splitQuery(q, Capabilities.SQL);
    Query dataSourceQuery = split.getDataSourceQuery();
    Query completionQuery = split.getCompletionQuery();


    QuerySelection selection = dataSourceQuery.getSelection();
    List<AbstractColumn> columns = selection.getColumns();
    assertEquals(3, columns.size());
    assertEquals("A", ((SimpleColumn) columns.get(0)).getId());
    assertEquals("B", ((AggregationColumn) columns.get(1)).getAggregatedColumn().getId());
    assertEquals(AggregationType.MAX, ((AggregationColumn) columns.get(1)).getAggregationType());
    assertEquals("C", ((SimpleColumn) columns.get(2)).getId());
    assertFalse(dataSourceQuery.hasSort());
    ColumnValueFilter filter = (ColumnValueFilter) dataSourceQuery.getFilter();
    assertEquals("A", ((SimpleColumn) filter.getColumn()).getId());
    assertFalse(dataSourceQuery.hasRowSkipping());
    assertFalse(dataSourceQuery.hasRowLimit());
    assertFalse(dataSourceQuery.hasRowOffset());
    assertFalse(dataSourceQuery.hasOptions());
    assertFalse(dataSourceQuery.hasLabels());
    assertFalse(dataSourceQuery.hasUserFormatOptions());
    assertFalse(dataSourceQuery.hasPivot());
    columns = dataSourceQuery.getGroup().getColumns();
    assertEquals(2, columns.size());
    assertEquals("A", ((SimpleColumn) columns.get(0)).getId());
    assertEquals("C", ((SimpleColumn) columns.get(1)).getId());


    selection = completionQuery.getSelection();
    columns = selection.getColumns();
    assertEquals(2, columns.size());
    assertEquals("A", ((SimpleColumn) columns.get(0)).getId());
    assertEquals("max-B", ((AggregationColumn) columns.get(1)).getAggregatedColumn().getId());
    assertEquals(AggregationType.MIN, ((AggregationColumn) columns.get(1)).getAggregationType());
    assertFalse(completionQuery.hasFilter());

View Full Code Here

    q.getUserFormatOptions().addPattern(maxB, "maxB#");
    QueryPair split = QuerySplitter.splitQuery(q, Capabilities.SQL);
    Query dataSourceQuery = split.getDataSourceQuery();
    Query completionQuery = split.getCompletionQuery();


    QuerySelection selection = dataSourceQuery.getSelection();
    List<AbstractColumn> columns = selection.getColumns();
    assertEquals(2, columns.size());
    assertEquals("A", ((SimpleColumn) columns.get(0)).getId());
    assertEquals("B", ((AggregationColumn) columns.get(1)).getAggregatedColumn().getId());
    assertEquals(AggregationType.MAX, ((AggregationColumn) columns.get(1)).getAggregationType());
    columns = dataSourceQuery.getSort().getColumns();

View Full Code Here

  public void testSortAndPaginationOnSimpleQuery() throws Exception {
    q.setGroup(null);
    q.setFilter(null);
    q.setPivot(null);
    // Remove aggregation column:
    QuerySelection newSel = new QuerySelection();
    newSel.addColumn(new SimpleColumn("A"));
    q.setSelection(newSel);


    QueryPair split = QuerySplitter.splitQuery(q, Capabilities.SORT_AND_PAGINATION);
    Query dataSourceQuery = split.getDataSourceQuery();
    Query completionQuery = split.getCompletionQuery();

View Full Code Here

    if (!queryHasAggregation(query) || (table.getNumberOfRows() == 0)) {
      return table;
    }
    QueryGroup group = query.getGroup();
    QueryPivot pivot = query.getPivot();
    QuerySelection selection = query.getSelection();


    List<String> groupByIds = Lists.newArrayList();
    if (group != null) {
      groupByIds = group.getColumnIds();
    }


    List<String> pivotByIds = Lists.newArrayList();
    if (pivot != null) {
      pivotByIds = pivot.getColumnIds(); // contained in groupByIds
    }


    List<String> groupAndPivotIds = Lists.newArrayList(groupByIds);
    groupAndPivotIds.addAll(pivotByIds);


    List<AggregationColumn> tmpColumnAggregations = selection.getAggregationColumns();
    List<ScalarFunctionColumn> selectedScalarFunctionColumns = selection.getScalarFunctionColumns();
    
    // Remove duplicates from tmpColumnAggregations, creating columnAggregations:
    List<AggregationColumn> columnAggregations =
      Lists.newArrayListWithExpectedSize(tmpColumnAggregations.size());
    for (AggregationColumn aggCol : tmpColumnAggregations) {

View Full Code Here

            QueryGroup group = new QueryGroup();
            for(AbstractColumn col : newGroupColumns) {
                group.addColumn(col);
            }
            dataSourceQuery.setGroup(group);
            QuerySelection selection = new QuerySelection();
            for(AbstractColumn col : newSelectionColumns) {
                selection.addColumn(col);
            }
            dataSourceQuery.setSelection(selection);


            // Build the completion query to group by the grouping columns. Because an aggregation is
            // required, make a dummy aggregation on the original column by which the aggregation is
            // required.
            // This original column must be unique for a given set of values for the grouping/pivoting
            // columns so any aggregation operation out of MIN, MAX, AVG will return the value
            // itself and will not aggregate anything. The example from before,
            // SELECT A, max(B) GROUP BY A PIVOT C turns into SELECT A, min(max-B) GROUP BY A PIVOT C


            completionQuery.copyFrom(query);
            completionQuery.setFilter(null);


            QuerySelection completionSelection = new QuerySelection();
            List<AbstractColumn> originalSelectedColumns =
                    query.getSelection().getColumns();
            for(int i = 0; i < originalSelectedColumns.size(); i++) {
                AbstractColumn column = originalSelectedColumns.get(i);
                if(query.getGroup().getColumns().contains(column)) {
                    completionSelection.addColumn(column);
                }
                else { // Must be an aggregation column if doesn't appear in the grouping.
                    // The id here is the id generated by the data source for the column containing
                    // the aggregated data, e.g., max-B.
                    String id = column.getId();
                    // MIN is chosen arbitrarily, because there will be exactly one.
                    completionSelection.addColumn(
                            new AggregationColumn(new SimpleColumn(id), AggregationType.MIN));
                }
            }


            completionQuery.setSelection(completionSelection);

View Full Code Here

0 1 2 3 4

TOP

Related Classes of com.google.visualization.datasource.query.QuerySelection

com.google.visualization.datasource.query.engine.QueryEngine

com.google.visualization.datasource.query.engine.QueryEngineTest

com.google.visualization.datasource.query.parser.QueryBuilderTest

com.google.visualization.datasource.query.parser.QueryParser

com.google.visualization.datasource.QuerySplitter

com.google.visualization.datasource.QuerySplitterTest

com.google.visualization.datasource.util.SqlDataSourceHelperTest

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.