2

I want to process the following JSON Objects :

[
   {
       id: "38485ndndndn4848",
       value : 120, // kw
       ts: 1456983266,
       sensor_id: 20
   },
   {
       id: "48485ndndndn4848",
       value : 189, //kw
       ts: 1456984286,
      sensor_id: 20
   },
   {
       id: "98485ndndndn4848",
       value : 99, // kw
       ts: 1457984286,
       sensor_id: 21
   },
  {
       id: "64640485ndndndn4848",
       value : 56,  // kw
       ts: 1458984286,
       sensor_id: 21
   }
.
.
.
]

in order to obtain the DAILY consumption for each sensor in kWh. So far i have created the following Java class:

public class Tick {
   String id;
   Integer sensorId;
   Double value;
   Date ts;


  public Tick(JSONObject obj) {
    this.id = (String) obj.get('id');
    long timestamp = (Long) obj.get("ts");
    this.ts = new Date(timestamp * 1000);
    this.value = (Double)message.get("value");

  }

with this epl queries :

EPStatement cepStatement = cepAdm.createEPL("select  max(ts) as date, sensorId from " +
            "StockTick() group by deviceId ");

EPStatement cepStatement3 = cepAdm.createEPL("insert into Consumption" +
            "select t0.ts, sum(t0.value*(t1.ts-t0.ts)/3600) as val , sensorId from StockTick() as t0 join StockTick() as t1 on t0.sensorId=t1.sensorId group by  ts.getYear(), ts.getMonthOfYear(), ts.getDayOfMonth() , sensorId ");

---UPDATE --------------

EPStatement cepStatement3 = cepAdm.createEPL("insert into DailyConsumption " +
                 "select t0.sync, sum( t0.value * ( t1.ts - t0.ts ) / 3600) as val , t0.sensorId from StockTick().win:length(2) "+
                 "as t0 join StockTick().win:length(2)  as t1 on t1.sensorId = t0.sensorId "+          
                 "where  t1.id != t0.id and t1.ts > t0.ts"+
                 "group by  sync.getYear(), sync.getMonthOfYear(), sync.getDayOfMonth() , deviceId");

it produces the following error :

Incorrect syntax near 'by' (a reserved keyword) at line 1 column 261 [insert into DailyConsumption select
user4958578943093890
  • 21
  • 2
  • I don't understand the question, "how can i process the data in the Consumption view". The "insert-into Consumption" indicates that Consumption is a stream (and not a view). You can process that stream by attaching a listener or subscriber to the statement. – goodie Jan 17 '17 at 16:23
  • @goodie Please check the updated question... the problem is with the group by ... – user4958578943093890 Jan 17 '17 at 16:31
  • Maybe this is because "t0.tsgroup by" has no space char and should be "t0.ts group". – goodie Jan 17 '17 at 18:47
  • `Error starting statement: Failed to validate group-by-clause expression 'sync': Property named 'sync' is ambiguous as is valid for more then one stream` I already have the getter inside the Java class :( – user4958578943093890 Jan 17 '17 at 18:50
  • This is a join and "sync" is a field in both streams. Therefore instead of "sync.getYear()" use "t0.sync.getYear()" either "t1.sync.getYear()" – goodie Jan 17 '17 at 19:08
  • But is not what i want to get.. how can i obtain the DAILY results? is not correct to group only in one (t0 or t1)... How ever thank u for the support :) – user4958578943093890 Jan 17 '17 at 19:28

0 Answers0