Examples with WindowFrame com.facebook.presto.sql.tree.WindowFrame used on opensource projects

Example 1 with WindowFrame

use of com.facebook.presto.sql.tree.WindowFrame in project presto by prestodb.

the class TestMergeWindows method testMergeDifferentFramesWithDefault.

@Test
public void testMergeDifferentFramesWithDefault() {
    Optional<WindowFrame> frameD = Optional.of(new WindowFrame(WindowFrame.Type.ROWS, new FrameBound(FrameBound.Type.CURRENT_ROW), Optional.of(new FrameBound(FrameBound.Type.UNBOUNDED_FOLLOWING))));
    ExpectedValueProvider<WindowNode.Specification> specificationD = specification(ImmutableList.of(SUPPKEY_ALIAS), ImmutableList.of(ORDERKEY_ALIAS), ImmutableMap.of(ORDERKEY_ALIAS, SortOrder.ASC_NULLS_LAST));
    @Language("SQL") String sql = "SELECT " + "SUM(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey) sum_quantity_C, " + "AVG(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) avg_quantity_D, " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey) sum_discount_C " + "FROM lineitem";
    assertUnitPlan(sql, anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specificationD).addFunction(functionCall("avg", frameD, ImmutableList.of(QUANTITY_ALIAS))).addFunction(functionCall("sum", UNSPECIFIED_FRAME, ImmutableList.of(DISCOUNT_ALIAS))).addFunction(functionCall("sum", UNSPECIFIED_FRAME, ImmutableList.of(QUANTITY_ALIAS))), LINEITEM_TABLESCAN_DOQS)));
}

Example 2 with WindowFrame

use of com.facebook.presto.sql.tree.WindowFrame in project presto by prestodb.

the class QueryPlanner method window.

private PlanBuilder window(PlanBuilder subPlan, QuerySpecification node) {
    List<FunctionCall> windowFunctions = ImmutableList.copyOf(analysis.getWindowFunctions(node));
    if (windowFunctions.isEmpty()) {
        return subPlan;
    }
    for (FunctionCall windowFunction : windowFunctions) {
        Window window = windowFunction.getWindow().get();
        // Extract frame
        WindowFrame.Type frameType = WindowFrame.Type.RANGE;
        FrameBound.Type frameStartType = FrameBound.Type.UNBOUNDED_PRECEDING;
        FrameBound.Type frameEndType = FrameBound.Type.CURRENT_ROW;
        Expression frameStart = null;
        Expression frameEnd = null;
        if (window.getFrame().isPresent()) {
            WindowFrame frame = window.getFrame().get();
            frameType = frame.getType();
            frameStartType = frame.getStart().getType();
            frameStart = frame.getStart().getValue().orElse(null);
            if (frame.getEnd().isPresent()) {
                frameEndType = frame.getEnd().get().getType();
                frameEnd = frame.getEnd().get().getValue().orElse(null);
            }
        }
        // Pre-project inputs
        ImmutableList.Builder<Expression> inputs = ImmutableList.<Expression>builder().addAll(windowFunction.getArguments()).addAll(window.getPartitionBy()).addAll(Iterables.transform(window.getOrderBy(), SortItem::getSortKey));
        if (frameStart != null) {
            inputs.add(frameStart);
        }
        if (frameEnd != null) {
            inputs.add(frameEnd);
        }
        subPlan = subPlan.appendProjections(inputs.build(), symbolAllocator, idAllocator);
        // Rewrite PARTITION BY in terms of pre-projected inputs
        ImmutableList.Builder<Symbol> partitionBySymbols = ImmutableList.builder();
        for (Expression expression : window.getPartitionBy()) {
            partitionBySymbols.add(subPlan.translate(expression));
        }
        // Rewrite ORDER BY in terms of pre-projected inputs
        Map<Symbol, SortOrder> orderings = new LinkedHashMap<>();
        for (SortItem item : window.getOrderBy()) {
            Symbol symbol = subPlan.translate(item.getSortKey());
            orderings.put(symbol, toSortOrder(item));
        }
        // Rewrite frame bounds in terms of pre-projected inputs
        Optional<Symbol> frameStartSymbol = Optional.empty();
        Optional<Symbol> frameEndSymbol = Optional.empty();
        if (frameStart != null) {
            frameStartSymbol = Optional.of(subPlan.translate(frameStart));
        }
        if (frameEnd != null) {
            frameEndSymbol = Optional.of(subPlan.translate(frameEnd));
        }
        WindowNode.Frame frame = new WindowNode.Frame(frameType, frameStartType, frameStartSymbol, frameEndType, frameEndSymbol);
        TranslationMap outputTranslations = subPlan.copyTranslations();
        // Rewrite function call in terms of pre-projected inputs
        Expression parametersReplaced = ExpressionTreeRewriter.rewriteWith(new ParameterRewriter(analysis.getParameters(), analysis), windowFunction);
        outputTranslations.addIntermediateMapping(windowFunction, parametersReplaced);
        Expression rewritten = subPlan.rewrite(parametersReplaced);
        boolean needCoercion = rewritten instanceof Cast;
        // Strip out the cast and add it back as a post-projection
        if (rewritten instanceof Cast) {
            rewritten = ((Cast) rewritten).getExpression();
        }
        // If refers to existing symbol, don't create another PlanNode
        if (rewritten instanceof SymbolReference) {
            if (needCoercion) {
                subPlan = explicitCoercionSymbols(subPlan, subPlan.getRoot().getOutputSymbols(), ImmutableList.of(windowFunction));
            }
            continue;
        }
        Symbol newSymbol = symbolAllocator.newSymbol(rewritten, analysis.getType(windowFunction));
        outputTranslations.put(parametersReplaced, newSymbol);
        WindowNode.Function function = new WindowNode.Function((FunctionCall) rewritten, analysis.getFunctionSignature(windowFunction), frame);
        List<Symbol> sourceSymbols = subPlan.getRoot().getOutputSymbols();
        ImmutableList.Builder<Symbol> orderBySymbols = ImmutableList.builder();
        orderBySymbols.addAll(orderings.keySet());
        // create window node
        subPlan = new PlanBuilder(outputTranslations, new WindowNode(idAllocator.getNextId(), subPlan.getRoot(), new WindowNode.Specification(partitionBySymbols.build(), orderBySymbols.build(), orderings), ImmutableMap.of(newSymbol, function), Optional.empty(), ImmutableSet.of(), 0), analysis.getParameters());
        if (needCoercion) {
            subPlan = explicitCoercionSymbols(subPlan, sourceSymbols, ImmutableList.of(windowFunction));
        }
    }
    return subPlan;
}

Example 3 with WindowFrame

use of com.facebook.presto.sql.tree.WindowFrame in project presto by prestodb.

the class QueryPlanner method window.

private PlanBuilder window(PlanBuilder subPlan, List<FunctionCall> windowFunctions) {
    if (windowFunctions.isEmpty()) {
        return subPlan;
    }
    for (FunctionCall windowFunction : windowFunctions) {
        Window window = windowFunction.getWindow().get();
        // Extract frame
        WindowFrame.Type frameType = WindowFrame.Type.RANGE;
        FrameBound.Type frameStartType = FrameBound.Type.UNBOUNDED_PRECEDING;
        FrameBound.Type frameEndType = FrameBound.Type.CURRENT_ROW;
        Expression frameStart = null;
        Expression frameEnd = null;
        if (window.getFrame().isPresent()) {
            WindowFrame frame = window.getFrame().get();
            frameType = frame.getType();
            frameStartType = frame.getStart().getType();
            frameStart = frame.getStart().getValue().orElse(null);
            if (frame.getEnd().isPresent()) {
                frameEndType = frame.getEnd().get().getType();
                frameEnd = frame.getEnd().get().getValue().orElse(null);
            }
        }
        // Pre-project inputs
        ImmutableList.Builder<Expression> inputs = ImmutableList.<Expression>builder().addAll(windowFunction.getArguments()).addAll(window.getPartitionBy()).addAll(Iterables.transform(getSortItemsFromOrderBy(window.getOrderBy()), SortItem::getSortKey));
        if (frameStart != null) {
            inputs.add(frameStart);
        }
        if (frameEnd != null) {
            inputs.add(frameEnd);
        }
        subPlan = subPlan.appendProjections(inputs.build(), variableAllocator, idAllocator);
        // Rewrite PARTITION BY in terms of pre-projected inputs
        ImmutableList.Builder<VariableReferenceExpression> partitionByVariables = ImmutableList.builder();
        for (Expression expression : window.getPartitionBy()) {
            partitionByVariables.add(subPlan.translateToVariable(expression));
        }
        // Rewrite ORDER BY in terms of pre-projected inputs
        LinkedHashMap<VariableReferenceExpression, SortOrder> orderings = new LinkedHashMap<>();
        for (SortItem item : getSortItemsFromOrderBy(window.getOrderBy())) {
            VariableReferenceExpression variable = subPlan.translateToVariable(item.getSortKey());
            // don't override existing keys, i.e. when "ORDER BY a ASC, a DESC" is specified
            orderings.putIfAbsent(variable, toSortOrder(item));
        }
        // Rewrite frame bounds in terms of pre-projected inputs
        Optional<VariableReferenceExpression> frameStartVariable = Optional.empty();
        Optional<VariableReferenceExpression> frameEndVariable = Optional.empty();
        if (frameStart != null) {
            frameStartVariable = Optional.of(subPlan.translate(frameStart));
        }
        if (frameEnd != null) {
            frameEndVariable = Optional.of(subPlan.translate(frameEnd));
        }
        WindowNode.Frame frame = new WindowNode.Frame(toWindowType(frameType), toBoundType(frameStartType), frameStartVariable, toBoundType(frameEndType), frameEndVariable, Optional.ofNullable(frameStart).map(Expression::toString), Optional.ofNullable(frameEnd).map(Expression::toString));
        TranslationMap outputTranslations = subPlan.copyTranslations();
        // Rewrite function call in terms of pre-projected inputs
        Expression rewritten = subPlan.rewrite(windowFunction);
        boolean needCoercion = rewritten instanceof Cast;
        // Strip out the cast and add it back as a post-projection
        if (rewritten instanceof Cast) {
            rewritten = ((Cast) rewritten).getExpression();
        }
        // If refers to existing variable, don't create another PlanNode
        if (rewritten instanceof SymbolReference) {
            if (needCoercion) {
                subPlan = explicitCoercionVariables(subPlan, subPlan.getRoot().getOutputVariables(), ImmutableList.of(windowFunction));
            }
            continue;
        }
        Type returnType = analysis.getType(windowFunction);
        VariableReferenceExpression newVariable = variableAllocator.newVariable(rewritten, returnType);
        outputTranslations.put(windowFunction, newVariable);
        // TODO: replace arguments with RowExpression once we introduce subquery expression for RowExpression (#12745).
        // Wrap all arguments in CallExpression to be RawExpression.
        // The utility that work on the CallExpression should be aware of the RawExpression handling.
        // The interface will be dirty until we introduce subquery expression for RowExpression.
        // With subqueries, the translation from Expression to RowExpression can happen here.
        WindowNode.Function function = new WindowNode.Function(call(windowFunction.getName().toString(), analysis.getFunctionHandle(windowFunction), returnType, ((FunctionCall) rewritten).getArguments().stream().map(OriginalExpressionUtils::castToRowExpression).collect(toImmutableList())), frame, windowFunction.isIgnoreNulls());
        ImmutableList.Builder<VariableReferenceExpression> orderByVariables = ImmutableList.builder();
        orderByVariables.addAll(orderings.keySet());
        Optional<OrderingScheme> orderingScheme = Optional.empty();
        if (!orderings.isEmpty()) {
            orderingScheme = Optional.of(new OrderingScheme(orderByVariables.build().stream().map(variable -> new Ordering(variable, orderings.get(variable))).collect(toImmutableList())));
        }
        // create window node
        subPlan = new PlanBuilder(outputTranslations, new WindowNode(subPlan.getRoot().getSourceLocation(), idAllocator.getNextId(), subPlan.getRoot(), new WindowNode.Specification(partitionByVariables.build(), orderingScheme), ImmutableMap.of(newVariable, function), Optional.empty(), ImmutableSet.of(), 0));
        if (needCoercion) {
            subPlan = explicitCoercionVariables(subPlan, subPlan.getRoot().getOutputVariables(), ImmutableList.of(windowFunction));
        }
    }
    return subPlan;
}

Example 4 with WindowFrame

use of com.facebook.presto.sql.tree.WindowFrame in project presto by prestodb.

the class TestMergeWindows method testMergeDifferentFrames.

@Test
public void testMergeDifferentFrames() {
    Optional<WindowFrame> frameC = Optional.of(new WindowFrame(WindowFrame.Type.ROWS, new FrameBound(FrameBound.Type.UNBOUNDED_PRECEDING), Optional.of(new FrameBound(FrameBound.Type.CURRENT_ROW))));
    ExpectedValueProvider<WindowNode.Specification> specificationC = specification(ImmutableList.of(SUPPKEY_ALIAS), ImmutableList.of(ORDERKEY_ALIAS), ImmutableMap.of(ORDERKEY_ALIAS, SortOrder.ASC_NULLS_LAST));
    Optional<WindowFrame> frameD = Optional.of(new WindowFrame(WindowFrame.Type.ROWS, new FrameBound(FrameBound.Type.CURRENT_ROW), Optional.of(new FrameBound(FrameBound.Type.UNBOUNDED_FOLLOWING))));
    @Language("SQL") String sql = "SELECT " + "SUM(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_quantity_C, " + "AVG(quantity) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) avg_quantity_D, " + "SUM(discount) OVER (PARTITION BY suppkey ORDER BY orderkey ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_discount_C " + "FROM lineitem";
    assertUnitPlan(sql, anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specificationC).addFunction(functionCall("avg", frameD, ImmutableList.of(QUANTITY_ALIAS))).addFunction(functionCall("sum", frameC, ImmutableList.of(DISCOUNT_ALIAS))).addFunction(functionCall("sum", frameC, ImmutableList.of(QUANTITY_ALIAS))), LINEITEM_TABLESCAN_DOQS)));
}