如何用点火数据网格/SQL求和一个双精度数组? [英] How to sum an array of doubles with ignite data grid / sql?

问题描述

如何用点火数据网格/sql对一个双精度数组求和?

How to sum an array of doubles with ignite data grid / sql?

给出:1个维度表 MyDimension 和1个事实表 MyFact .加入它们，按几个维度进行分组**，然后将与之相关的事实相加.现在考虑事实表的总和，不包含单个double值，而是一个double数组.要检索的总和是一个表示所有数组之和的数组.

Given: 1 dimension table MyDimension and 1 fact table MyFact. Join them, group by a few dimensions ** and sum the fact associated with it. Now consider the fact table to sum, doesn't contain a single double value, but an array of doubles. And the sum to retrieve, is an array representing the sum of all the arrays.

以单词为例:假设所有数组都是":

As an example words: Assume "All the arrays are":

array 1: double[] { 1.0, 2.0, 3.0, 4.0, 5.0 }
array 2: double[] { 2.0, 3.0, 4.0, 5.0, 6.0 }
array 3: double[] { 3.0, 4.0, 5.0, 6.0, 7.0 }

然后，代表所有数组之和的数组"将为:double [] {6.0，9.0，12.0，15.0，18.0}

then the "array representing the sum of all the arrays" would be: double[] { 6.0, 9.0, 12.0, 15.0, 18.0 }

我研究了 @QuerySqlFunction ，但是找不到任何可以帮助我的示例.

I've looked into @QuerySqlFunction, but couldn't find any example that could help me.

这不必是sql.从我的缓存中获取数组总和的任何方法都很棒.

This doesn't HAVE to be sql. Any way to get a sum of arrays out of my cache would be great.

谢谢

Johan

MyFact

package com.hsbc.rsl.babarpoc.ignite.starschema;

import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class MyFact {
/** Primary key. */
@QuerySqlField(index = true)
private long uid;

@QuerySqlField(index = true)
private long dimensionUid;

@QuerySqlField
private double values[];

public MyFact(long uid, long dimensionUid, double values[]) {
    this.uid = uid;
    this.dimensionUid = dimensionUid;
    this.values = values;
}

public long getUid() {
    return uid;
}

public void setUid(long uid) {
    this.uid = uid;
}

public long getDimensionUid() {
    return dimensionUid;
}

public void setDimensionUid(long dimensionUid) {
    this.dimensionUid = dimensionUid;
}

public double[] getValues() {
    return values;
}

public void setValues(double[] values) {
    this.values = values;
}
}

MyDimension

import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class MyDimension {
@QuerySqlField(index = true)
private long uid;

@QuerySqlField
private String groupBy1;

@QuerySqlField
private String groupBy2;

public MyDimension(String groupBy1, String groupBy2) {
    this.groupBy1 = groupBy1;
    this.groupBy2 = groupBy2;
}

public long getUid() {
    return uid;
}

public void setUid(long uid) {
    this.uid = uid;
}

public String getGroupBy1() {
    return groupBy1;
}

public void setGroupBy1(String groupBy1) {
    this.groupBy1 = groupBy1;
}

public String getGroupBy2() {
    return groupBy2;
}

public void setGroupBy2(String groupBy2) {
    this.groupBy2 = groupBy2;
}

}

查询(不起作用的查询)

我在下面添加一个示例sql，它将给出1值的总和，即它将不适用于该数组:它将生成错误.

I add an example sql below, which will give the sum of 1 value, ie it will not work for the array: it would generate an error.

SELECT 
      MyDimension.groupBy1, 
      MyDimension.groupBy2, 
      SUM(MyFact.values)
FROM 
     "dimensionCacheName".DimDimension,  
     "factCacheName".FactResult 
WHERE 
      MyDimension.uid=MyFact.dimensionUid 
GROUP BY  
      MyDimension.groupBy1,
      MyDimension.groupBy2

推荐答案

我看到两种可能的解决方案:

I see two possible solutions:

1. 在 MyFact 类中添加 valuesSum 字段，并在每次 values 更新时进行计算.然后，您可以直接查询此新字段，而不必每次都动态计算数组和.

2. 使用自定义SQL函数.该函数应在公共类中实现为静态方法，并使用 @QuerySqlFunction 批注进行批注.例如:

1. Add valuesSum field to MyFact class and calculate it each time values are updated. You can then query this new field directly instead of calculating the array sum each time on the fly.

2. Use custom SQL functions. The function should be implemented as a static method in a public class and annotated with @QuerySqlFunction annotation. For example:

public class SqlFunctions {
    @QuerySqlFunction
    public static double arraySum(double[] values) {
        double sum = 0.0;

        for (double value : values)
            sum += value;

        return sum;
    }
}

必须在缓存配置中提供此类:

This class has to be provided in the cache configuration:

cacheCfg.setSqlFunctionClasses(SqlFunctions.class);

然后您可以像这样运行查询:

You can then run the query like this:

SELECT SUM(arraySum(values)) FROM ...

这篇关于如何用点火数据网格/SQL求和一个双精度数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！