Hi, I have an index with events such as:
CITY , TICKET, CREATION_DATE, OTHER METADATA FIELDS
Paris , 0001, 01 jan 2020, .......
Rome, 0002, 03 jan 2020, .......
Paris, 0003, 05 jan 2020, .......
Berlin, 0004, 08 jan 2020, .......
Berlin, 0006, 09 jan 2020, .......
Paris, 0003, 05 jan 2020, .......
Rome, 0002, 03 jan 2020, .......
Rome, 0009 , 10 jan 2020, .......
Paris, 0007, 07 jan 2020, .......
Berlin, 0006 , 09 jan 2020, .......
I'd like to see which CITIES have more than 2 different tickets within 14 days; so i'd like to get all the events, with all its metadata, ordered by CITY, with different TICKET from the previous one (the previous in CREATION_DATE) , with the additional info about the difference in days from the previous ticket (DAYS_DIFF); with these added conditions:
only if DAYS_DIFF is < 14 AND the number of different TICKET, grouped by CITY, is > 2.
The first event by city has to be listed as well, with a "-" in the DAYS_DIFF field.
So in my case: only Paris has 3 different TICKET, each with DAYS_DIFF <14. ok!
Berlin and Rome have only 2 different TICKET . Not listed!
Desiderata result:
CITY, TICKET, CREATION_DATE , DAYS_DIFF , OTHER METADATA FIELDS
Paris, 0001, 01 jan 2020 , - , .....
Paris, 0003, 05 jan 2020 , 5 , ....
Paris , 0007, 07 jan 2020 , 2 , ....
How can I achieve this result?
I've tried with streamstats: I have the correct results, but listed by rows with n-ples CITY, OLD_EVENT, NEW_EVENT ,DAYS_DIFF ; I'd like to have the above visualization instead.
Thanks in advance.
