Evaluate with group on timeComponent

#1

I would like to get a count of objects grouped by year. I tried the following but it doesn’t seem to be supported.

e = MyType.evaluate({
   projection:"parent.id, timeComponent('YEAR', start), count()", 
   group:"parent.id, timeComponent('YEAR', start)"})
0 Likes

Execute a "like SQL" GROUP BY on Cassandra
Projection with sum
#2

I’ve included a bit more detail on what is currently supported.

tl;dr - grouping on the result of the timeComponent function is not supported; grouping for Cassandra-stored types is not supported. If you want to group by year, and the type is not stored in Cassandra, I recommend a calc field that stores the year individually.

ctx = TestApi.createContext("evaluate-group-test");
legacySeries = TestApi.createEntity(ctx, "PhysicalMeasurementSeries", {
  asset: "legacyAsset",
  measurementType: "Consumption",
  unitConstraint: {
    id: "kilowatt_hour"
  }
});

amiSeries = TestApi.createEntity(ctx, "PhysicalMeasurementSeries", {
  asset: "amiAsset",
  measurementType: "Consumption",
  unitConstraint: {
    id: "kilowatt_hour"
  }
});

TestApi.createBatchEntity(ctx, "Measurement", [
  {
    parent: legacySeries,
    start: "2016-12-01",
    end: "2016-12-31",
    quantity: {
      value: 300
    }
  },
  {
    parent: amiSeries,
    start: "2016-12-02",
    end: "2016-12-30",
    quantity: {
      value: 700
    }
  }
]);

// throws: "group in evaluate spec for CassandraConnection is not yet supported"
e = Measurement.evaluate({
  projection:"parent.id", 
  group:"parent.id"})

/////////////////////////////////////////////////////////////////////////
  
legacyService = TestApi.createEntity(ctx, "Service", {});

amiService = TestApi.createEntity(ctx, "Service", {});

TestApi.createBatchEntity(ctx, "DetailedBill", [
  {
    parent: legacyService,
    start: "2016-12-01",
    end: "2016-12-31",
    usage: 300
  },
  {
    parent: amiService,
    start: "2016-12-02",
    end: "2016-12-30",
    usage: 700
  }
]);

// succeeds
e = DetailedBill.evaluate({
  projection:"parent.id, count()", 
  group:"parent.id"})

// succeeds
e = DetailedBill.evaluate({
  projection:"parent.id, start, count()", 
  group:"parent.id, start"})

  
// throws: "Can't group on a projection that isn't handled by the db engine: 'timeComponent('YEAR', start)'"
e = DetailedBill.evaluate({
  projection:"timeComponent('YEAR', start)", 
  group:"timeComponent('YEAR', start)"})
0 Likes

#3

@lpoirier try
e = MyType.evaluate({
projection:“parent.id, year(start), count()”,
group:“parent.id, year(start)”})

0 Likes