4 Replies Latest reply: Dec 5, 2012 7:19 AM by dmitryz1 RSS

    CQL cache query

    dmitryz1
      Hello all,
      I have an event and cache that suppose to add this event on each occurrence.
      I have to build CQL query with following logic:
      The same event happened three times during defined period of time (let's say last 7 days) .
      Any help would be appreciated .

      Regards,
      Dmitry
        • 1. Re: CQL cache query
          887796
          One way to go is to build a 2 stage flow:

          First a view with a time window. Some thing like:

          select myField as field, count(*) as count .....
          from myChannel [range 7 days slide 1 hour]
          where whatever
          group by userID


          Then a query that uses the view:

          select field, .....
          from myView
          where count = 3

          This will give every hour a list of user ID (events) that in the last 7 days


          Please note that the events will continue to be created every 1 hour until the window will slide to such a place that count < 3.

          If you want to have the event output only 1 time and not every hour you can also put it in a cache (the output of the query) and add it to where clause in the query. Something like

          where count = 3 and filed not in cache.

          Hope this helps
          • 2. Re: CQL cache query
            dmitryz1
            Thank you,884793 (Sorry,I don't know your name ),
            I believe that I missed some important information in my post.
            I don't have continuous flow of event .
            Time difference from event to event can be an hour or day.

            Regards,
            Dmitry
            • 3. Re: CQL cache query
              887796
              This is why there is a time window of 7 days.

              It "looks" on all the events from the last 168 hours (7 days) and counts for each instance of field how may events
              • 4. Re: CQL cache query
                dmitryz1
                Hello 884793 ,
                It seems OK now.

                Thank You!
                Regards,
                Dmitry

                Edited by: dmitryz1 on Dec 5, 2012 5:19 AM