sf.dsl.example.SQLExpressions Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of sorm Show documentation
Show all versions of sorm Show documentation
java jpa tool for spring
The newest version!
package sf.dsl.example;
/**
* Common SQL expressions
* @see com.querydsl.sql.SQLExpressions
*/
public final class SQLExpressions {
/**
* Create a new detached SQLQuery instance with zero as the projection
* @return select(0)
*/
public static Example selectZero() {
return new Example().select(QPI.fixColumn("0"));
}
/**
* Create a new detached SQLQuery instance with one as the projection
* @return select(1)
*/
public static Example selectOne() {
return new Example().select(QPI.fixColumn("1"));
}
private static final WindowOver cumeDist = new WindowOver(new SimpleField("cume_dist()"));
private static final WindowOver rank = new WindowOver(new SimpleField("rank()"));
private static final WindowOver denseRank = new WindowOver(new SimpleField("dense_rank()"));
private static final WindowOver percentRank = new WindowOver(new SimpleField("percent_rank()"));
private static final WindowOver rowNumber = new WindowOver(new SimpleField("row_number()"));
private static SimpleField[] convertToExpressions(Object... args) {
SimpleField[] exprs = new SimpleField[args.length];
for (int i = 0; i < args.length; i++) {
if (args[i] instanceof SimpleField) {
exprs[i] = (SimpleField) args[i];
} else {
exprs[i] = new SimpleField(String.valueOf(args[i]));
}
}
return exprs;
}
/**
* Start a window function expression
* @param expr expression
* @return sum(expr)
*/
public static WindowOver sum(SimpleField expr) {
return new WindowOver(QPI.sum(expr));
}
/**
* Start a window function expression
* @return count()
*/
public static WindowOver count() {
return new WindowOver(QPI.count());
}
/**
* Start a window function expression
* @param expr expression
* @return count(expr)
*/
public static WindowOver count(SimpleField expr) {
return new WindowOver(QPI.count(expr));
}
/**
* Start a window function expression
* @param expr expression
* @return count(distinct expr)
*/
public static WindowOver countDistinct(SimpleField expr) {
return new WindowOver(QPI.funcO("count(distinct ", ")", expr));
}
/**
* Start a window function expression
* @param expr expression
* @return avg(expr)
*/
public static WindowOver avg(SimpleField expr) {
return new WindowOver(QPI.avg(expr));
}
/**
* Start a window function expression
* @param expr expression
* @return min(expr)
*/
public static WindowOver min(SimpleField expr) {
return new WindowOver(QPI.min(expr));
}
/**
* Start a window function expression
* @param expr expression
* @return max(expr)
*/
public static WindowOver max(SimpleField expr) {
return new WindowOver(QPI.max(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(SimpleField expr) {
return new WindowOver(QPI.func("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(SimpleField expr) {
return new WindowOver(QPI.func("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(SimpleField expr, String delimiter) {
return new WithinGroup(QPI.func("listagg", expr, QPI.fixColumn("'" + 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(SimpleField expr, Number n) {
return new WindowOver(QPI.func("nth_value", expr, QPI.fixColumn(String.valueOf(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(Number num) {
return new WindowOver(QPI.func("ntile", QPI.fixColumn(num.toString())));
}
/**
* 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(SimpleField... args) {
return new WithinGroup(QPI.func("rank", 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(SimpleField... args) {
return new WithinGroup(QPI.func("dense_rank", 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(SimpleField... args) {
return new WithinGroup(QPI.func("percent_rank", args));
}
/**
* Calculates a percentile based on a continuous distribution of the column value
* @param arg argument
* @return percentile_cont(arg)
*/
public static WithinGroup percentileCont(Number 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(QPI.fixColumn(String.valueOf(arg)));
}
/**
* Calculates a percentile based on a continuous distribution of the column value
* @param arg argument
* @return percentile_cont(arg)
*/
public static WithinGroup percentileCont(SimpleField arg) {
return new WithinGroup(QPI.func("percentile_cont", 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(Number 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(QPI.fixColumn(String.valueOf(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(/*Number*/SimpleField arg) {
return new WithinGroup(QPI.func("percentile_disc", 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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField arg1, SimpleField arg2) {//Number
return new WindowOver(QPI.func("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(SimpleField... args) {
return new WithinGroup(QPI.func("cume_dist", 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(SimpleField expr1, SimpleField expr2) {
return new WindowOver(QPI.func("corr", expr1, expr2));
}
/**
* CORR returns the coefficient of correlation of a set of number pairs.
* @param expr1 first arg
* @param expr2 second arg
* @return covar_pop(expr1, expr2)
*/
public static WindowOver covarPop(SimpleField expr1, SimpleField expr2) {//Number
return new WindowOver(QPI.func("covar_pop", expr1, expr2));
}
/**
* CORR returns the coefficient of correlation of a set of number pairs.
* @param expr1 first arg
* @param expr2 second arg
* @return covar_samp(expr1, expr2)
*/
public static WindowOver covarSamp(SimpleField expr1, SimpleField expr2) {//Number
return new WindowOver(QPI.func("covar_samp", 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(SimpleField expr) {
return new WindowOver(QPI.func("ratio_to_report", 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(SimpleField expr) {//Number
return new WindowOver(QPI.func("stddev", expr));
}
/**
* returns the sample standard deviation of expr, a set of numbers.
* @param expr argument
* @return stddev(distinct expr)
*/
public static WindowOver stddevDistinct(SimpleField expr) {//Number
return new WindowOver(QPI.funcO("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(SimpleField expr) {//Number
return new WindowOver(QPI.func("stddev_pop", 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(SimpleField expr) {//Number
return new WindowOver(QPI.func("stddev_samp", expr));
}
/**
* returns the variance of expr
* @param expr argument
* @return variance(expr)
*/
public static WindowOver variance(SimpleField expr) {//Number
return new WindowOver(QPI.func("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(SimpleField expr) {//Number
return new WindowOver(QPI.func("var_pop", 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(SimpleField expr) {//Number
return new WindowOver(QPI.func("var_samp", 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(SimpleField expr) {
return new WindowOver(QPI.func("first_value", 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(SimpleField expr) {
return new WindowOver(QPI.func("last_value", expr));
}
private SQLExpressions() {
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy