Repository: squashql/squashql (java)

Issue:
Add RELATIVE_DIFFERENCE to binary operor list

Pull request:
Relative difference as binary operator
Fixes #291 

Patch:
diff --git a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
index 3fa2fd4..76b1a41 100644
--- a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
+++ b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
@@ -66,7 +66,6 @@ public class BigQueryQueryRewriter implements QueryRewriter {
   @Override
   public String binaryOperation(BinaryOperator operator, String leftOperand, String rightOperand) {
     return switch (operator) {
-      case PLUS, MINUS, MULTIPLY -> QueryRewriter.super.binaryOperation(operator, leftOperand, rightOperand);
       // https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#safe_divide
       case DIVIDE -> new StringBuilder()
               .append("SAFE_DIVIDE")
@@ -76,6 +75,7 @@ public class BigQueryQueryRewriter implements QueryRewriter {
               .append(rightOperand)
               .append(")")
               .toString();
+      default -> QueryRewriter.super.binaryOperation(operator, leftOperand, rightOperand);
     };
   }
 }
diff --git a/core/src/main/java/io/squashql/query/BinaryOperator.java b/core/src/main/java/io/squashql/query/BinaryOperator.java
index b59b06a..f493d9b 100644
--- a/core/src/main/java/io/squashql/query/BinaryOperator.java
+++ b/core/src/main/java/io/squashql/query/BinaryOperator.java
@@ -4,7 +4,8 @@ public enum BinaryOperator {
   PLUS("+"),
   MINUS("-"),
   MULTIPLY("*"),
-  DIVIDE("/");
+  DIVIDE("/"),
+  RELATIVE_DIFFERENCE(null);
 
   public final String infix;
 
diff --git a/core/src/main/java/io/squashql/query/comp/BinaryOperations.java b/core/src/main/java/io/squashql/query/comp/BinaryOperations.java
index 55abd79..a32bee4 100644
--- a/core/src/main/java/io/squashql/query/comp/BinaryOperations.java
+++ b/core/src/main/java/io/squashql/query/comp/BinaryOperations.java
@@ -13,18 +13,22 @@ public class BinaryOperations {
     boolean isLong = outputDataType.equals(long.class) || outputDataType.equals(Long.class);
     return switch (method) {
       case ABSOLUTE_DIFFERENCE -> isLong ? BinaryOperations::minusAsLong : BinaryOperations::minusAsDouble;
-      case RELATIVE_DIFFERENCE -> (a, b) -> {
-        Double diff = BinaryOperations.minusAsDouble(a, b);
-        if (diff == null || b == null) {
-          return null;
-        } else {
-          return (a.doubleValue() - b.doubleValue()) / b.doubleValue();
-        }
-      };
+      case RELATIVE_DIFFERENCE -> relativeDifference();
       case DIVIDE -> createBiFunction(BinaryOperator.DIVIDE, dataType, dataType);
     };
   }
 
+  private static BiFunction<Number, Number, Number> relativeDifference() {
+    return (a, b) -> {
+      Double diff = BinaryOperations.minusAsDouble(a, b);
+      if (diff == null || b == null) {
+        return null;
+      } else {
+        return (a.doubleValue() - b.doubleValue()) / b.doubleValue();
+      }
+    };
+  }
+
   public static Class<? extends Number> getComparisonOutputType(ComparisonMethod method, Class<?> dataType) {
     return switch (method) {
       case ABSOLUTE_DIFFERENCE -> (Class<? extends Number>) dataType;
@@ -42,6 +46,7 @@ public class BinaryOperations {
       case MINUS -> isLong ? BinaryOperations::minusAsLong : BinaryOperations::minusAsDouble;
       case MULTIPLY -> isLong ? BinaryOperations::multiplyAsLong : BinaryOperations::multiplyAsDouble;
       case DIVIDE -> BinaryOperations::divideAsDouble;
+      case RELATIVE_DIFFERENCE -> relativeDifference();
     };
   }
 
diff --git a/core/src/main/java/io/squashql/query/database/QueryRewriter.java b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
index d8faf14..880d343 100644
--- a/core/src/main/java/io/squashql/query/database/QueryRewriter.java
+++ b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
@@ -127,12 +127,20 @@ public interface QueryRewriter {
   }
 
   default String binaryOperation(BinaryOperator operator, String leftOperand, String rightOperand) {
-    return new StringBuilder()
-            .append("(")
-            .append(leftOperand)
-            .append(operator.infix)
-            .append(rightOperand)
-            .append(")")
-            .toString();
+    switch (operator) {
+      case PLUS, MINUS, MULTIPLY, DIVIDE -> {
+        return new StringBuilder()
+                .append("(")
+                .append(leftOperand)
+                .append(operator.infix)
+                .append(rightOperand)
+                .append(")")
+                .toString();
+      }
+      case RELATIVE_DIFFERENCE -> {
+        return String.format("((%s-%s)/%s)", leftOperand, rightOperand, rightOperand);
+      }
+      default -> throw new IllegalArgumentException(operator.toString());
+    }
   }
 }
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index 11bde8b..fe0d19d 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -665,20 +665,23 @@ Unlike a basic measure, a calculated measure is computed by SquashQL (not the da
 from the underlying database before applying the defined calculation.
 It is defined as the combination of other measures that can be either basic or not.
 
-#### Elementary: addition, subtraction, multiplication and division
+#### Elementary: addition, subtraction, multiplication, division and relative difference
 
 ```typescript
 import {
   sum,
-  multiply, divide, plus, minus, TableField
+  multiply, divide, plus, minus, relativeDifference, TableField
 } from "@squashql/squashql-js"
 
 const a = new TableField("myTable.a")
+const b = new TableField("myTable.b")
 const aSum = sum("aSum", a)
+const bSum = sum("bSum", b)
 const square = multiply("square", aSum, aSum)
 const twoTimes = plus("twoTimes", aSum, aSum)
 const zero = minus("zero", aSum, aSum)
 const one = divide("one", aSum, aSum)
+const relDiff = relativeDifference("relDiff", aSum, bSum) // = (aSum - bSum) / bSum
 ```
 
 Constant measures can be defined with `decimal` or `integer` operators:
diff --git a/js/typescript-library/package-lock.json b/js/typescript-library/package-lock.json
index 7ca9a92..9fa0f43 100644
--- a/js/typescript-library/package-lock.json
+++ b/js/typescript-library/package-lock.json
@@ -1,12 +1,12 @@
 {
   "name": "@squashql/squashql-js",
-  "version": "1.14.1",
+  "version": "1.15.0",
   "lockfileVersion": 2,
   "requires": true,
   "packages": {
     "": {
       "name": "@squashql/squashql-js",
-      "version": "1.14.1",
+      "version": "1.15.0",
       "license": "Apache-2.0",
       "dependencies": {
         "axios": "^1.6.7"
diff --git a/js/typescript-library/src/generate-json-queryDto.ts b/js/typescript-library/src/generate-json-queryDto.ts
index d284c24..50e974d 100644
--- a/js/typescript-library/src/generate-json-queryDto.ts
+++ b/js/typescript-library/src/generate-json-queryDto.ts
@@ -56,6 +56,8 @@ export function generateFromQueryDto() {
   q.withMeasure(priceFood)
   const plus = new BinaryOperationMeasure("plusMeasure", BinaryOperator.PLUS, price, priceFood)
   q.withMeasure(plus)
+  const relDiff = new BinaryOperationMeasure("relDiff", BinaryOperator.RELATIVE_DIFFERENCE, price, priceFood)
+  q.withMeasure(relDiff)
   const expression = new ExpressionMeasure("myExpression", "sum(price*quantity)")
   q.withMeasure(expression)
   q.withMeasure(countRows)
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index a3744c0..4649723 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -32,6 +32,7 @@ export {
   minus,
   multiply,
   divide,
+  relativeDifference,
   integer,
   decimal,
   comparisonMeasureWithPeriod,
diff --git a/js/typescript-library/src/measure.ts b/js/typescript-library/src/measure.ts
index eca40ad..00db766 100644
--- a/js/typescript-library/src/measure.ts
+++ b/js/typescript-library/src/measure.ts
@@ -82,6 +82,7 @@ export enum BinaryOperator {
   MINUS = "MINUS",
   MULTIPLY = "MULTIPLY",
   DIVIDE = "DIVIDE",
+  RELATIVE_DIFFERENCE = "RELATIVE_DIFFERENCE"
 }
 
 export class ComparisonMeasureReferencePosition implements Measure {
@@ -260,6 +261,10 @@ export function divide(alias: string, measure1: Measure, measure2: Measure): Mea
   return new BinaryOperationMeasure(alias, BinaryOperator.DIVIDE, measure1, measure2)
 }
 
+export function relativeDifference(alias: string, measure1: Measure, measure2: Measure): Measure {
+  return new BinaryOperationMeasure(alias, BinaryOperator.RELATIVE_DIFFERENCE, measure1, measure2)
+}
+
 // CONSTANT
 
 export function integer(value: number): Measure {
diff --git a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
index d8b93b4..64ef55f 100644
--- a/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
+++ b/snowflake/src/main/java/io/squashql/query/database/SnowflakeQueryRewriter.java
@@ -32,7 +32,6 @@ class SnowflakeQueryRewriter implements QueryRewriter {
   @Override
   public String binaryOperation(BinaryOperator operator, String leftOperand, String rightOperand) {
     return switch (operator) {
-      case PLUS, MINUS, MULTIPLY -> QueryRewriter.super.binaryOperation(operator, leftOperand, rightOperand);
       // https://docs.snowflake.com/en/sql-reference/functions/div0null
       case DIVIDE -> new StringBuilder()
               .append("DIV0NULL")
@@ -42,6 +41,7 @@ class SnowflakeQueryRewriter implements QueryRewriter {
               .append(rightOperand)
               .append(")")
               .toString();
+      default -> QueryRewriter.super.binaryOperation(operator, leftOperand, rightOperand);
     };
   }
 }


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestBinaryOperationMeasure.java b/core/src/test/java/io/squashql/query/ATestBinaryOperationMeasure.java
index 7da6d1d..2850590 100644
--- a/core/src/test/java/io/squashql/query/ATestBinaryOperationMeasure.java
+++ b/core/src/test/java/io/squashql/query/ATestBinaryOperationMeasure.java
@@ -2,6 +2,7 @@ package io.squashql.query;
 
 import io.squashql.TestClass;
 import io.squashql.query.agg.AggregationFunction;
+import io.squashql.query.builder.Query;
 import io.squashql.query.dto.QueryDto;
 import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
@@ -18,7 +19,7 @@ import static io.squashql.transaction.DataLoader.MAIN_SCENARIO_NAME;
 @TestInstance(TestInstance.Lifecycle.PER_CLASS)
 public abstract class ATestBinaryOperationMeasure extends ABaseTestQuery {
 
-  protected String storeName = "store" + getClass().getSimpleName().toLowerCase();
+  private final String storeName = "store" + getClass().getSimpleName().toLowerCase();
 
   @Override
   protected Map<String, List<TableTypedField>> getFieldsByStore() {
@@ -136,4 +137,24 @@ public abstract class ATestBinaryOperationMeasure extends ABaseTestQuery {
             .assertThat(table.headers().stream().map(Header::name))
             .containsExactlyInAnyOrder("sum(sales)", "sum(quantity)", "divide1", "divide2", "divide3");
   }
+
+  /**
+   * Try with measure of different types.
+   */
+  @Test
+  void testRelativeDifference() {
+    AggregatedMeasure sales = new AggregatedMeasure("sum(sales)", "sales", AggregationFunction.SUM);
+    AggregatedMeasure quantity = new AggregatedMeasure("sum(quantity)", "quantity", AggregationFunction.SUM);
+
+    QueryDto query = Query.from(this.storeName)
+            .select(List.of(),
+                    List.of(),
+                    List.of(new BinaryOperationMeasure("rel diff", BinaryOperator.RELATIVE_DIFFERENCE, sales, quantity)))
+            .build();
+
+    Table table = this.executor.executeQuery(query);
+    double salesV = 50d;
+    long qtyV = 20l;
+    Assertions.assertThat(table).contains(List.of((salesV - qtyV) / qtyV));
+  }
 }
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index d843df9..6c7f1f8 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -44,6 +44,8 @@ public class TestJavascriptLibrary {
     q.withMeasure(priceFood);
     var plus = new BinaryOperationMeasure("plusMeasure", BinaryOperator.PLUS, price, priceFood);
     q.withMeasure(plus);
+    var relDiff = new BinaryOperationMeasure("relDiff", BinaryOperator.RELATIVE_DIFFERENCE, price, priceFood);
+    q.withMeasure(relDiff);
     var expression = new ExpressionMeasure("myExpression", "sum(price*quantity)");
     q.withMeasure(expression);
     q.withMeasure(CountMeasure.INSTANCE);