Seite 1 von 1

Query problem

Verfasst: 5. Mai 2016 13:24
von AizazZaidee
I am getting exception for the below one
Task 2 Complex Esper Queries
Have a look at the code provided on the course website, which contains the code for the “Taxi trips in NYC” application
scenario. The ZIP file contains a Maven project, which can be imported into your IDE. An Eclipse project is provided,
too. If importing the Eclipse project should not work, please have a look into the informatory section at the end of the
exercise to see how to import a Maven project into Eclipse.
The scenario uses two types of events
Pickup(int taxi_id, int location_id) and
Dropoff(int taxi_id, int location_id, int amount).
Write a query to find the ten most profitable routes in the last 30 minutes. The profitability of a route is the sum of the
amounts of all the taxi trips for that route. A route is a pair of (pickup location, dropoff location). Consider routes that
ended within the last 30 minutes.

"select _p.location_id as pl_id from Pickup as _p"
+ "INNER JOIN Dropoff"
+ "ON Pickup.location_id = Dropoff.location_id"
+ "Select _d.location_id , sum(_d.amount) as dl_id from Dropoff as _d"
+ "Where timer:within(30 minutes).win:length(10)";

Exception:
com.espertech.esper.client.EPStatementSyntaxException: Incorrect syntax near '.' at line 1 column 75, please check the outer join within the from clause [select _p.location_id as pl_id from Pickup as _pINNER JOIN DropoffON Pickup.location_id = Dropoff.location_idSelect _d.location_id , sum(_d.amount) as dl_id from Dropoff as _dWhere timer:within(30 minutes).win:length(10)]
at com.espertech.esper.epl.parse.ExceptionConvertor.convertStatement(ExceptionConvertor.java:47)
at com.espertech.esper.epl.parse.ParseHelper.parse(ParseHelper.java:118)
at com.espertech.esper.core.service.EPAdministratorHelper.compileEPL(EPAdministratorHelper.java:101)
at com.espertech.esper.core.service.EPAdministratorHelper.compileEPL(EPAdministratorHelper.java:72)
at com.espertech.esper.core.service.EPAdministratorImpl.createEPLStmt(EPAdministratorImpl.java:113)
at com.espertech.esper.core.service.EPAdministratorImpl.createEPL(EPAdministratorImpl.java:63)
at exercise.Exercise.installOutputRule(Exercise.java:99)
at exercise.Exercise.runExercise(Exercise.java:24)
at exercise.Run.main(Run.java:7)


------------------------------------------------------
Task 1 Simple Esper Queries
Assume a stock tick event StockTick(String name, int price) with the fields name and price representing the name
of an company and the associated price for a stock tick.
a) Write a query which computes the average prices over the last 30 seconds.
Select avg(price) from StockTick.Timer:within(30)
b) Write a query which alerts on each "IBM" stock tick with a price greater then 80 and within the next 60 seconds.
Every StockTick(name="IBM", price > 80) where timer:within(60 seconds)
c) Write a query that returns the average price per name for the last 100 stock ticks.
Select name as tempName avg(price) as avgP from StockPrice.win:length(100) groupby tempName
Q: I don't know how to write Scheme for those and to test them on http://esper-epl-tryout.appspot.com/epl ... nform.html any help would be welcomed.

Re: Query problem

Verfasst: 22. Jul 2016 18:32
von paprikawuerzung
In your exception message the query is:

Code: Alles auswählen

select _p.location_id as pl_id from Pickup as _pINNER JOIN DropoffON Pickup.location_id = Dropoff.location_idSelect _d.location_id , sum(_d.amount) as dl_id from Dropoff as _dWhere timer:within(30 minutes).win:length(10)
Correct is (notice the spaces):

Code: Alles auswählen

select _p.location_id as pl_id from Pickup as _p INNER JOIN Dropoff ON Pickup.location_id = Dropoff.location_id Select _d.location_id , sum(_d.amount) as dl_id from Dropoff as _d Where timer:within(30 minutes).win:length(10);
You have to add a space before or after your "line breaks" - like:

Code: Alles auswählen

select _p.location_id as pl_id from Pickup as _p" 
+ " INNER JOIN Dropoff"
+ " ON Pickup.location_id = Dropoff.location_id"
+ " Select _d.location_id , sum(_d.amount) as dl_id from Dropoff as _d"
+ " Where timer:within(30 minutes).win:length(10);