com.querydsl.sql.SQLExpressions Maven / Gradle / Ivy
/*
* Copyright 2015, The Querydsl Team (http://www.querydsl.com/team)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.querydsl.sql;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.ConstantImpl;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.Operator;
import com.querydsl.core.types.Ops;
import com.querydsl.core.types.Path;
import com.querydsl.core.types.SubQueryExpression;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.DateExpression;
import com.querydsl.core.types.dsl.DateTimeExpression;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.core.types.dsl.NumberExpression;
import com.querydsl.core.types.dsl.SimpleExpression;
import com.querydsl.core.types.dsl.StringExpression;
import com.querydsl.core.types.dsl.Wildcard;
import java.util.EnumMap;
import java.util.List;
import java.util.Map;
/**
* Common SQL expressions
*
* @author tiwe
*/
@SuppressWarnings("rawtypes")
public final class SQLExpressions {
private static final Map DATE_ADD_OPS = new EnumMap<>(DatePart.class);
private static final Map DATE_DIFF_OPS = new EnumMap<>(DatePart.class);
private static final Map DATE_TRUNC_OPS = new EnumMap<>(DatePart.class);
static {
DATE_ADD_OPS.put(DatePart.year, Ops.DateTimeOps.ADD_YEARS);
DATE_ADD_OPS.put(DatePart.month, Ops.DateTimeOps.ADD_MONTHS);
DATE_ADD_OPS.put(DatePart.week, Ops.DateTimeOps.ADD_WEEKS);
DATE_ADD_OPS.put(DatePart.day, Ops.DateTimeOps.ADD_DAYS);
DATE_ADD_OPS.put(DatePart.hour, Ops.DateTimeOps.ADD_HOURS);
DATE_ADD_OPS.put(DatePart.minute, Ops.DateTimeOps.ADD_MINUTES);
DATE_ADD_OPS.put(DatePart.second, Ops.DateTimeOps.ADD_SECONDS);
DATE_ADD_OPS.put(DatePart.millisecond, null); // TODO
DATE_DIFF_OPS.put(DatePart.year, Ops.DateTimeOps.DIFF_YEARS);
DATE_DIFF_OPS.put(DatePart.month, Ops.DateTimeOps.DIFF_MONTHS);
DATE_DIFF_OPS.put(DatePart.week, Ops.DateTimeOps.DIFF_WEEKS);
DATE_DIFF_OPS.put(DatePart.day, Ops.DateTimeOps.DIFF_DAYS);
DATE_DIFF_OPS.put(DatePart.hour, Ops.DateTimeOps.DIFF_HOURS);
DATE_DIFF_OPS.put(DatePart.minute, Ops.DateTimeOps.DIFF_MINUTES);
DATE_DIFF_OPS.put(DatePart.second, Ops.DateTimeOps.DIFF_SECONDS);
DATE_DIFF_OPS.put(DatePart.millisecond, null); // TODO
DATE_TRUNC_OPS.put(DatePart.year, Ops.DateTimeOps.TRUNC_YEAR);
DATE_TRUNC_OPS.put(DatePart.month, Ops.DateTimeOps.TRUNC_MONTH);
DATE_TRUNC_OPS.put(DatePart.week, Ops.DateTimeOps.TRUNC_WEEK);
DATE_TRUNC_OPS.put(DatePart.day, Ops.DateTimeOps.TRUNC_DAY);
DATE_TRUNC_OPS.put(DatePart.hour, Ops.DateTimeOps.TRUNC_HOUR);
DATE_TRUNC_OPS.put(DatePart.minute, Ops.DateTimeOps.TRUNC_MINUTE);
DATE_TRUNC_OPS.put(DatePart.second, Ops.DateTimeOps.TRUNC_SECOND);
}
private static final WindowOver cumeDist =
new WindowOver<>(Double.class, SQLOps.CUMEDIST);
private static final WindowOver rank = new WindowOver<>(Long.class, SQLOps.RANK);
private static final WindowOver denseRank = new WindowOver<>(Long.class, SQLOps.DENSERANK);
private static final WindowOver percentRank =
new WindowOver<>(Double.class, SQLOps.PERCENTRANK);
private static final WindowOver rowNumber = new WindowOver<>(Long.class, SQLOps.ROWNUMBER);
private static Expression[] convertToExpressions(Object... args) {
var exprs = new Expression[args.length];
for (var i = 0; i < args.length; i++) {
if (args[i] instanceof Expression) {
exprs[i] = (Expression) args[i];
} else {
exprs[i] = ConstantImpl.create(args[i]);
}
}
return exprs;
}
/** Wildcard expression */
public static final Expression all = Wildcard.all;
/** Wildcard count expression */
public static final Expression countAll = Wildcard.count;
/**
* Create an assignment expression
*
* @param target target expression
* @param value value to be set
* @param
* @return target = value
*/
public static Expression set(Path target, Expression value) {
if (value != null) {
return Expressions.operation(target.getType(), SQLOps.SET_PATH, target, value);
} else {
return Expressions.operation(
target.getType(), SQLOps.SET_LITERAL, target, Expressions.nullExpression());
}
}
/**
* Create an assignment expression
*
* @param target target expression
* @param value value to be set
* @param
* @return target = value
*/
public static Expression set(Path target, T value) {
if (value != null) {
return Expressions.operation(
target.getType(), SQLOps.SET_LITERAL, target, Expressions.constant(value));
} else {
return Expressions.operation(
target.getType(), SQLOps.SET_LITERAL, target, Expressions.nullExpression());
}
}
/**
* Create a new detached SQLQuery instance with the given projection
*
* @param expr projection
* @param
* @return select(expr)
*/
public static SQLQuery select(Expression expr) {
return new SQLQuery().select(expr);
}
/**
* Create a new detached SQLQuery instance with the given projection
*
* @param exprs projection
* @return select(exprs)
*/
public static SQLQuery select(Expression... exprs) {
return new SQLQuery().select(exprs);
}
/**
* Create a new detached SQLQuery instance with the given projection
*
* @param expr distinct projection
* @param
* @return select(distinct expr)
*/
public static SQLQuery selectDistinct(Expression expr) {
return new SQLQuery().select(expr).distinct();
}
/**
* Create a new detached SQLQuery instance with the given projection
*
* @param exprs distinct projection
* @return select(distinct exprs)
*/
public static SQLQuery selectDistinct(Expression... exprs) {
return new SQLQuery().select(exprs).distinct();
}
/**
* Create a new detached SQLQuery instance with zero as the projection
*
* @return select(0)
*/
public static SQLQuery selectZero() {
return select(Expressions.ZERO);
}
/**
* Create a new detached SQLQuery instance with one as the projection
*
* @return select(1)
*/
public static SQLQuery selectOne() {
return select(Expressions.ONE);
}
/**
* Create a new detached SQLQuery instance with the given projection
*
* @param expr query source and projection
* @param
* @return select(expr).from(expr)
*/
public static SQLQuery selectFrom(RelationalPath expr) {
return select(expr).from(expr);
}
/**
* Create a new UNION clause
*
* @param sq subqueries
* @param
* @return union
*/
public static Union union(SubQueryExpression... sq) {
return new SQLQuery().union(sq);
}
/**
* Create a new UNION clause
*
* @param sq subqueries
* @param
* @return union
*/
public static Union union(List> sq) {
return new SQLQuery().union(sq);
}
/**
* Create a new UNION ALL clause
*
* @param sq subqueries
* @param
* @return union
*/
public static Union unionAll(SubQueryExpression... sq) {
return new SQLQuery().unionAll(sq);
}
/**
* Create a new UNION ALL clause
*
* @param sq subqueries
* @param
* @return union
*/
public static Union unionAll(List> sq) {
return new SQLQuery().unionAll(sq);
}
/** Get an aggregate any expression for the given boolean expression */
public static BooleanExpression any(BooleanExpression expr) {
return Expressions.booleanOperation(Ops.AggOps.BOOLEAN_ANY, expr);
}
/** Get an aggregate all expression for the given boolean expression */
public static BooleanExpression all(BooleanExpression expr) {
return Expressions.booleanOperation(Ops.AggOps.BOOLEAN_ALL, expr);
}
/**
* Create a new RelationalFunctionCall for the given function and arguments
*
* @param type type
* @param function function name
* @param args arguments
* @param
* @return relational function call
*/
public static RelationalFunctionCall relationalFunctionCall(
Class type, String function, Object... args) {
return new RelationalFunctionCall<>(type, function, args);
}
/**
* Create a nextval(sequence) expression
*
* Returns the next value from the give sequence
*
* @param sequence sequence name
* @return nextval(sequence)
*/
public static SimpleExpression nextval(String sequence) {
return nextval(Long.class, sequence);
}
/**
* Create a nextval(sequence) expression of the given type
*
* Returns the next value from the given sequence
*
* @param type type of call
* @param sequence sequence name
* @return nextval(sequence)
*/
public static SimpleExpression nextval(Class type, String sequence) {
return Expressions.operation(type, SQLOps.NEXTVAL, ConstantImpl.create(sequence));
}
/**
* Convert timestamp to date
*
* @param dateTime timestamp
* @return date
*/
public static DateExpression date(DateTimeExpression dateTime) {
return Expressions.dateOperation(dateTime.getType(), Ops.DateTimeOps.DATE, dateTime);
}
/**
* Convert timestamp to date
*
* @param type type
* @param dateTime timestamp
* @return date
*/
public static DateExpression date(
Class type, DateTimeExpression dateTime) {
return Expressions.dateOperation(type, Ops.DateTimeOps.DATE, dateTime);
}
/**
* Create a dateadd(unit, date, amount) expression
*
* @param unit date part
* @param date date
* @param amount amount
* @return converted date
*/
public static DateTimeExpression dateadd(
DatePart unit, DateTimeExpression date, int amount) {
return Expressions.dateTimeOperation(
date.getType(), DATE_ADD_OPS.get(unit), date, ConstantImpl.create(amount));
}
/**
* Create a dateadd(unit, date, amount) expression
*
* @param unit date part
* @param date date
* @param amount amount
* @return converted date
*/
public static DateExpression dateadd(
DatePart unit, DateExpression date, int amount) {
return Expressions.dateOperation(
date.getType(), DATE_ADD_OPS.get(unit), date, ConstantImpl.create(amount));
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, DateExpression start, DateExpression end) {
return Expressions.numberOperation(Integer.class, DATE_DIFF_OPS.get(unit), start, end);
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, D start, DateExpression end) {
return Expressions.numberOperation(
Integer.class, DATE_DIFF_OPS.get(unit), ConstantImpl.create(start), end);
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, DateExpression start, D end) {
return Expressions.numberOperation(
Integer.class, DATE_DIFF_OPS.get(unit), start, ConstantImpl.create(end));
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, DateTimeExpression start, DateTimeExpression end) {
return Expressions.numberOperation(Integer.class, DATE_DIFF_OPS.get(unit), start, end);
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, D start, DateTimeExpression end) {
return Expressions.numberOperation(
Integer.class, DATE_DIFF_OPS.get(unit), ConstantImpl.create(start), end);
}
/**
* Get a datediff(unit, start, end) expression
*
* @param unit date part
* @param start start
* @param end end
* @return difference in units
*/
public static NumberExpression datediff(
DatePart unit, DateTimeExpression start, D end) {
return Expressions.numberOperation(
Integer.class, DATE_DIFF_OPS.get(unit), start, ConstantImpl.create(end));
}
/**
* Truncate the given date expression
*
* @param unit date part to truncate to
* @param expr truncated date
*/
public static DateExpression datetrunc(
DatePart unit, DateExpression expr) {
return Expressions.dateOperation(expr.getType(), DATE_TRUNC_OPS.get(unit), expr);
}
/**
* Truncate the given datetime expression
*
* @param unit datepart to truncate to
* @param expr truncated datetime
*/
public static DateTimeExpression datetrunc(
DatePart unit, DateTimeExpression expr) {
return Expressions.dateTimeOperation(expr.getType(), DATE_TRUNC_OPS.get(unit), expr);
}
/**
* Add the given amount of years to the date
*
* @param date datetime
* @param years years to add
* @return converted datetime
*/
public static DateTimeExpression addYears(
DateTimeExpression date, int years) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_YEARS, date, ConstantImpl.create(years));
}
/**
* Add the given amount of months to the date
*
* @param date datetime
* @param months months to add
* @return converted datetime
*/
public static DateTimeExpression addMonths(
DateTimeExpression date, int months) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_MONTHS, date, ConstantImpl.create(months));
}
/**
* Add the given amount of weeks to the date
*
* @param date datetime
* @param weeks weeks to add
* @return converted date
*/
public static DateTimeExpression addWeeks(
DateTimeExpression date, int weeks) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_WEEKS, date, ConstantImpl.create(weeks));
}
/**
* Add the given amount of days to the date
*
* @param date datetime
* @param days days to add
* @return converted datetime
*/
public static DateTimeExpression addDays(
DateTimeExpression date, int days) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_DAYS, date, ConstantImpl.create(days));
}
/**
* Add the given amount of hours to the date
*
* @param date datetime
* @param hours hours to add
* @return converted datetime
*/
public static DateTimeExpression addHours(
DateTimeExpression date, int hours) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_HOURS, date, ConstantImpl.create(hours));
}
/**
* Add the given amount of minutes to the date
*
* @param date datetime
* @param minutes minutes to add
* @return converted datetime
*/
public static DateTimeExpression addMinutes(
DateTimeExpression date, int minutes) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_MINUTES, date, ConstantImpl.create(minutes));
}
/**
* Add the given amount of seconds to the date
*
* @param date datetime
* @param seconds seconds to add
* @return converted datetime
*/
public static DateTimeExpression addSeconds(
DateTimeExpression date, int seconds) {
return Expressions.dateTimeOperation(
date.getType(), Ops.DateTimeOps.ADD_SECONDS, date, ConstantImpl.create(seconds));
}
/**
* Add the given amount of years to the date
*
* @param date date
* @param years years to add
* @return converted date
*/
public static DateExpression addYears(
DateExpression date, int years) {
return Expressions.dateOperation(
date.getType(), Ops.DateTimeOps.ADD_YEARS, date, ConstantImpl.create(years));
}
/**
* Add the given amount of months to the date
*
* @param date date
* @param months months to add
* @return converted date
*/
public static DateExpression addMonths(
DateExpression date, int months) {
return Expressions.dateOperation(
date.getType(), Ops.DateTimeOps.ADD_MONTHS, date, ConstantImpl.create(months));
}
/**
* Add the given amount of weeks to the date
*
* @param date date
* @param weeks weeks to add
* @return converted date
*/
public static DateExpression addWeeks(
DateExpression date, int weeks) {
return Expressions.dateOperation(
date.getType(), Ops.DateTimeOps.ADD_WEEKS, date, ConstantImpl.create(weeks));
}
/**
* Add the given amount of days to the date
*
* @param date date
* @param days days to add
* @return converted date
*/
public static DateExpression addDays(DateExpression date, int days) {
return Expressions.dateOperation(
date.getType(), Ops.DateTimeOps.ADD_DAYS, date, ConstantImpl.create(days));
}
/**
* Start a window function expression
*
* @param expr expression
* @return sum(expr)
*/
public static WindowOver sum(Expression expr) {
return new WindowOver<>(expr.getType(), Ops.AggOps.SUM_AGG, expr);
}
/**
* Start a window function expression
*
* @return count()
*/
public static WindowOver count() {
return new WindowOver<>(Long.class, Ops.AggOps.COUNT_ALL_AGG);
}
/**
* Start a window function expression
*
* @param expr expression
* @return count(expr)
*/
public static WindowOver count(Expression expr) {
return new WindowOver<>(Long.class, Ops.AggOps.COUNT_AGG, expr);
}
/**
* Start a window function expression
*
* @param expr expression
* @return count(distinct expr)
*/
public static WindowOver countDistinct(Expression expr) {
return new WindowOver<>(Long.class, Ops.AggOps.COUNT_DISTINCT_AGG, expr);
}
/**
* Start a window function expression
*
* @param expr expression
* @return avg(expr)
*/
public static WindowOver avg(Expression expr) {
return new WindowOver<>(expr.getType(), Ops.AggOps.AVG_AGG, expr);
}
/**
* Start a window function expression
*
* @param expr expression
* @return min(expr)
*/
public static WindowOver min(Expression expr) {
return new WindowOver<>(expr.getType(), Ops.AggOps.MIN_AGG, expr);
}
/**
* Start a window function expression
*
* @param expr expression
* @return max(expr)
*/
public static WindowOver max(Expression expr) {
return new WindowOver<>(expr.getType(), Ops.AggOps.MAX_AGG, expr);
}
/**
* expr evaluated at the row that is one row after the current row within the partition;
*
* @param expr expression
* @return lead(expr)
*/
public static WindowOver lead(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.LEAD, expr);
}
/**
* expr evaluated at the row that is one row before the current row within the partition
*
* @param expr expression
* @return lag(expr)
*/
public static WindowOver lag(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.LAG, expr);
}
/**
* LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates
* the values of the measure column.
*
* @param expr measure column
* @param delimiter delimiter
* @return listagg(expr, delimiter)
*/
public static WithinGroup listagg(Expression expr, String delimiter) {
return new WithinGroup<>(String.class, SQLOps.LISTAGG, expr, ConstantImpl.create(delimiter));
}
/**
* NTH_VALUE returns the expr value of the nth row in the window defined by the analytic clause.
* The returned value has the data type of the expr.
*
* @param expr measure expression
* @param n one based row index
* @return nth_value(expr, n)
*/
public static WindowOver nthValue(Expression expr, Number n) {
return nthValue(expr, ConstantImpl.create(n));
}
/**
* NTH_VALUE returns the expr value of the nth row in the window defined by the analytic clause.
* The returned value has the data type of the expr
*
* @param expr measure expression
* @param n one based row index
* @return nth_value(expr, n)
*/
public static WindowOver nthValue(Expression expr, Expression n) {
return new WindowOver<>(expr.getType(), SQLOps.NTHVALUE, expr, n);
}
/**
* divides an ordered data set into a number of buckets indicated by expr and assigns the
* appropriate bucket number to each row
*
* @param num bucket size
* @return ntile(num)
*/
@SuppressWarnings("unchecked")
public static WindowOver ntile(T num) {
return new WindowOver<>((Class) num.getClass(), SQLOps.NTILE, ConstantImpl.create(num));
}
/**
* rank of the current row with gaps; same as row_number of its first peer
*
* @return rank()
*/
public static WindowOver rank() {
return rank;
}
/**
* As an aggregate function, RANK calculates the rank of a hypothetical row identified by the
* arguments of the function with respect to a given sort specification. The arguments of the
* function must all evaluate to constant expressions within each aggregate group, because they
* identify a single row within each group. The constant argument expressions and the expressions
* in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments
* must be the same and their types must be compatible.
*
* @param args arguments
* @return rank(args)
*/
@SuppressWarnings("unchecked")
public static WithinGroup rank(Object... args) {
return rank(convertToExpressions(args));
}
/**
* As an aggregate function, RANK calculates the rank of a hypothetical row identified by the
* arguments of the function with respect to a given sort specification. The arguments of the
* function must all evaluate to constant expressions within each aggregate group, because they
* identify a single row within each group. The constant argument expressions and the expressions
* in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments
* must be the same and their types must be compatible.
*
* @param args arguments
* @return rank(args)
*/
public static WithinGroup rank(Expression... args) {
return new WithinGroup<>(Long.class, SQLOps.RANK2, args);
}
/**
* rank of the current row without gaps; this function counts peer groups
*
* @return dense_rank()
*/
public static WindowOver denseRank() {
return denseRank;
}
/**
* As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified
* by the arguments of the function with respect to a given sort specification. The arguments of
* the function must all evaluate to constant expressions within each aggregate group, because
* they identify a single row within each group. The constant argument expressions and the
* expressions in the order_by_clause of the aggregate match by position. Therefore, the number of
* arguments must be the same and types must be compatible.
*
* @param args arguments
* @return dense_rank(args)
*/
@SuppressWarnings("unchecked")
public static WithinGroup denseRank(Object... args) {
return denseRank(convertToExpressions(args));
}
/**
* As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified
* by the arguments of the function with respect to a given sort specification. The arguments of
* the function must all evaluate to constant expressions within each aggregate group, because
* they identify a single row within each group. The constant argument expressions and the
* expressions in the order_by_clause of the aggregate match by position. Therefore, the number of
* arguments must be the same and types must be compatible.
*
* @param args arguments
* @return dense_rank(args)
*/
public static WithinGroup denseRank(Expression... args) {
return new WithinGroup<>(Long.class, SQLOps.DENSERANK2, args);
}
/**
* As an analytic function, for a row r, PERCENT_RANK calculates the rank of r minus 1, divided by
* 1 less than the number of rows being evaluated (the entire query result set or a partition).
*
* @return percent_rank()
*/
public static WindowOver percentRank() {
return percentRank;
}
/**
* As an aggregate function, PERCENT_RANK calculates, for a hypothetical row r identified by the
* arguments of the function and a corresponding sort specification, the rank of row r minus 1
* divided by the number of rows in the aggregate group. This calculation is made as if the
* hypothetical row r were inserted into the group of rows over which Oracle Database is to
* aggregate. The arguments of the function identify a single hypothetical row within each
* aggregate group. Therefore, they must all evaluate to constant expressions within each
* aggregate group. The constant argument expressions and the expressions in the ORDER BY clause
* of the aggregate match by position. Therefore the number of arguments must be the same and
* their types must be compatible.
*
* @param args arguments
* @return percent_rank(args)
*/
@SuppressWarnings("unchecked")
public static WithinGroup percentRank(Object... args) {
return percentRank(convertToExpressions(args));
}
/**
* As an aggregate function, PERCENT_RANK calculates, for a hypothetical row r identified by the
* arguments of the function and a corresponding sort specification, the rank of row r minus 1
* divided by the number of rows in the aggregate group. This calculation is made as if the
* hypothetical row r were inserted into the group of rows over which Oracle Database is to
* aggregate. The arguments of the function identify a single hypothetical row within each
* aggregate group. Therefore, they must all evaluate to constant expressions within each
* aggregate group. The constant argument expressions and the expressions in the ORDER BY clause
* of the aggregate match by position. Therefore the number of arguments must be the same and
* their types must be compatible.
*
* @param args arguments
* @return percent_rank(args)
*/
public static WithinGroup percentRank(Expression... args) {
return new WithinGroup<>(Double.class, SQLOps.PERCENTRANK2, args);
}
/**
* Calculates a percentile based on a continuous distribution of the column value
*
* @param arg argument
* @return percentile_cont(arg)
*/
public static WithinGroup percentileCont(T arg) {
if (arg.doubleValue() < 0.0 || arg.doubleValue() > 1.0) {
throw new IllegalArgumentException("The percentile value should be a number between 0 and 1");
}
return percentileCont(ConstantImpl.create(arg));
}
/**
* Calculates a percentile based on a continuous distribution of the column value
*
* @param arg argument
* @return percentile_cont(arg)
*/
public static WithinGroup percentileCont(Expression arg) {
return new WithinGroup<>(arg.getType(), SQLOps.PERCENTILECONT, arg);
}
/**
* PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.
* It takes a percentile value and a sort specification and returns an element from the set. Nulls
* are ignored in the calculation.
*
* This function takes as an argument any numeric datatype or any nonnumeric datatype that can
* be implicitly converted to a numeric datatype. The function returns the same datatype as the
* numeric datatype of the argument.
*
* @param arg argument
* @return percentile_disc(arg)
*/
public static WithinGroup percentileDisc(T arg) {
if (arg.doubleValue() < 0.0 || arg.doubleValue() > 1.0) {
throw new IllegalArgumentException("The percentile value should be a number between 0 and 1");
}
return percentileDisc(ConstantImpl.create(arg));
}
/**
* PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.
* It takes a percentile value and a sort specification and returns an element from the set. Nulls
* are ignored in the calculation.
*
* This function takes as an argument any numeric datatype or any nonnumeric datatype that can
* be implicitly converted to a numeric datatype. The function returns the same datatype as the
* numeric datatype of the argument.
*
* @param arg argument
* @return percentile_disc(arg)
*/
public static WithinGroup percentileDisc(Expression arg) {
return new WithinGroup<>(arg.getType(), SQLOps.PERCENTILEDISC, arg);
}
/**
* REGR_SLOPE returns the slope of the line
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_slope(arg1, arg2)
*/
public static WindowOver regrSlope(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_SLOPE, arg1, arg2);
}
/**
* REGR_INTERCEPT returns the y-intercept of the regression line.
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_intercept(arg1, arg2)
*/
public static WindowOver regrIntercept(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_INTERCEPT, arg1, arg2);
}
/**
* REGR_COUNT returns an integer that is the number of non-null number pairs used to fit the
* regression line.
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_count(arg1, arg2)
*/
public static WindowOver regrCount(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_COUNT, arg1, arg2);
}
/**
* REGR_R2 returns the coefficient of determination (also called R-squared or goodness of fit) for
* the regression.
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_r2(arg1, arg2)
*/
public static WindowOver regrR2(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_R2, arg1, arg2);
}
/**
* REGR_AVGX evaluates the average of the independent variable (arg2) of the regression line.
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_avgx(arg1, arg2)
*/
public static WindowOver regrAvgx(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_AVGX, arg1, arg2);
}
/**
* REGR_AVGY evaluates the average of the dependent variable (arg1) of the regression line.
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_avgy(arg1, arg2)
*/
public static WindowOver regrAvgy(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_AVGY, arg1, arg2);
}
/**
* REGR_SXX makes the following computation after the elimination of null (arg1, arg2) pairs:
*
* {@code REGR_COUNT(arg1, arg2) * VAR_POP(arg2)}
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_sxx(arg1, arg2)
*/
public static WindowOver regrSxx(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_SXX, arg1, arg2);
}
/**
* REGR_SYY makes the following computation after the elimination of null (arg1, arg2) pairs:
*
* {@code REGR_COUNT(arg1, arg2) * VAR_POP(arg1)}
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_syy(arg1, arg2)
*/
public static WindowOver regrSyy(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_SYY, arg1, arg2);
}
/**
* REGR_SXY makes the following computation after the elimination of null (arg1, arg2) pairs:
*
* REGR_COUNT(arg1, arg2) * COVAR_POP(arg1, arg2)
*
* @param arg1 first arg
* @param arg2 second arg
* @return regr_sxy(arg1, arg2)
*/
public static WindowOver regrSxy(
Expression arg1, Expression arg2) {
return new WindowOver<>(Double.class, SQLOps.REGR_SXY, arg1, arg2);
}
/**
* CUME_DIST calculates the cumulative distribution of a value in a group of values.
*
* @return cume_dist()
*/
public static WindowOver cumeDist() {
return cumeDist;
}
/**
* As an aggregate function, CUME_DIST calculates, for a hypothetical row r identified by the
* arguments of the function and a corresponding sort specification, the relative position of row
* r among the rows in the aggregation group. Oracle makes this calculation as if the hypothetical
* row r were inserted into the group of rows to be aggregated over. The arguments of the function
* identify a single hypothetical row within each aggregate group. Therefore, they must all
* evaluate to constant expressions within each aggregate group. The constant argument expressions
* and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the
* number of arguments must be the same and their types must be compatible.
*
* @param args arguments
* @return cume_dist(args)
*/
@SuppressWarnings("unchecked")
public static WithinGroup cumeDist(Object... args) {
return cumeDist(convertToExpressions(args));
}
/**
* As an aggregate function, CUME_DIST calculates, for a hypothetical row r identified by the
* arguments of the function and a corresponding sort specification, the relative position of row
* r among the rows in the aggregation group. Oracle makes this calculation as if the hypothetical
* row r were inserted into the group of rows to be aggregated over. The arguments of the function
* identify a single hypothetical row within each aggregate group. Therefore, they must all
* evaluate to constant expressions within each aggregate group. The constant argument expressions
* and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the
* number of arguments must be the same and their types must be compatible.
*
* @param args arguments
* @return cume_dist(args)
*/
public static WithinGroup cumeDist(Expression... args) {
return new WithinGroup<>(Double.class, SQLOps.CUMEDIST2, args);
}
/**
* CORR returns the coefficient of correlation of a set of number pairs.
*
* @param expr1 first arg
* @param expr2 second arg
* @return corr(expr1, expr2)
*/
public static WindowOver corr(
Expression expr1, Expression expr2) {
return new WindowOver<>(Double.class, SQLOps.CORR, expr1, expr2);
}
/**
* CORR returns the coefficient of correlation of a set of number pairs.
*
* @param expr1 first arg
* @param expr2 second arg
* @return corr(expr1, expr2)
*/
public static WindowOver covarPop(
Expression expr1, Expression expr2) {
return new WindowOver<>(Double.class, SQLOps.COVARPOP, expr1, expr2);
}
/**
* CORR returns the coefficient of correlation of a set of number pairs.
*
* @param expr1 first arg
* @param expr2 second arg
* @return corr(expr1, expr2)
*/
public static WindowOver covarSamp(
Expression expr1, Expression expr2) {
return new WindowOver<>(Double.class, SQLOps.COVARSAMP, expr1, expr2);
}
/**
* computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then
* the ratio-to-report value also evaluates to null.
*
* @return ratio_to_report(expr)
*/
public static WindowOver ratioToReport(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.RATIOTOREPORT, expr);
}
/**
* number of the current row within its partition, counting from 1
*
* @return row_number()
*/
public static WindowOver rowNumber() {
return rowNumber;
}
/**
* returns the sample standard deviation of expr, a set of numbers.
*
* @param expr argument
* @return stddev(expr)
*/
public static WindowOver stddev(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.STDDEV, expr);
}
/**
* returns the sample standard deviation of expr, a set of numbers.
*
* @param expr argument
* @return stddev(distinct expr)
*/
public static WindowOver stddevDistinct(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.STDDEV_DISTINCT, expr);
}
/**
* returns the population standard deviation and returns the square root of the population
* variance.
*
* @param expr argument
* @return stddev_pop(expr)
*/
public static WindowOver stddevPop(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.STDDEVPOP, expr);
}
/**
* returns the cumulative sample standard deviation and returns the square root of the sample
* variance.
*
* @param expr argument
* @return stddev_samp(expr)
*/
public static WindowOver stddevSamp(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.STDDEVSAMP, expr);
}
/**
* returns the variance of expr
*
* @param expr argument
* @return variance(expr)
*/
public static WindowOver variance(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.VARIANCE, expr);
}
/**
* returns the population variance of a set of numbers after discarding the nulls in this set.
*
* @param expr argument
* @return var_pop(expr)
*/
public static WindowOver varPop(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.VARPOP, expr);
}
/**
* returns the sample variance of a set of numbers after discarding the nulls in this set.
*
* @param expr argument
* @return var_samp(expr)
*/
public static WindowOver varSamp(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.VARSAMP, expr);
}
/**
* returns value evaluated at the row that is the first row of the window frame
*
* @param expr argument
* @return first_value(expr)
*/
public static WindowOver firstValue(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.FIRSTVALUE, expr);
}
/**
* returns value evaluated at the row that is the last row of the window frame
*
* @param expr argument
* @return last_value(expr)
*/
public static WindowOver lastValue(Expression expr) {
return new WindowOver<>(expr.getType(), SQLOps.LASTVALUE, expr);
}
/**
* Get the rhs leftmost characters of lhs
*
* @param lhs string
* @param rhs character amount
* @return rhs leftmost characters
*/
public static StringExpression left(Expression lhs, int rhs) {
return left(lhs, ConstantImpl.create(rhs));
}
/**
* Get the rhs rightmost characters of lhs
*
* @param lhs string
* @param rhs character amount
* @return rhs rightmost characters
*/
public static StringExpression right(Expression lhs, int rhs) {
return right(lhs, ConstantImpl.create(rhs));
}
/**
* Get the rhs leftmost characters of lhs
*
* @param lhs string
* @param rhs character amount
* @return rhs leftmost characters
*/
public static StringExpression left(Expression lhs, Expression rhs) {
return Expressions.stringOperation(Ops.StringOps.LEFT, lhs, rhs);
}
/**
* Get the rhs leftmost characters of lhs
*
* @param lhs string
* @param rhs character amount
* @return rhs rightmost characters
*/
public static StringExpression right(Expression lhs, Expression rhs) {
return Expressions.stringOperation(Ops.StringOps.RIGHT, lhs, rhs);
}
/**
* Get a group_concat(expr) expression
*
* @param expr expression to be aggregated
* @return group_concat(expr)
*/
public static StringExpression groupConcat(Expression expr) {
return Expressions.stringOperation(SQLOps.GROUP_CONCAT, expr);
}
/**
* Get a group_concat(expr, separator) expression
*
* @param expr expression to be aggregated
* @param separator separator string
* @return group_concat(expr, separator)
*/
public static StringExpression groupConcat(Expression expr, String separator) {
return Expressions.stringOperation(SQLOps.GROUP_CONCAT2, expr, Expressions.constant(separator));
}
private SQLExpressions() {}
}