1 Reply Latest reply: Aug 6, 2014 11:23 PM by Gala.xiong RSS

    CQL to calculate % o success / failure of transactions

    Daniel Amadei-Oracle

      Hi all,

       

      I've a stream where I receive transactions and I need to calculate the success rate among the transactions. The info if the transaction is failed or not is a field of the event.

       

      I'm not able to figure how to do that as when I do a group by the tx status, I can only get the amount of OK / FAILED transactions as two different output events, however I needed this as a single event with 2 attributes to calculate the total and then the failure rate.

       

      Thanks,

      Daniel

        • 1. Re: CQL to calculate % o success / failure of transactions
          Gala.xiong

          Hi, Daniel,

           

          I am not sure what is the transactions which you mentioned. I tried the following statement, Is this what you want?

           

          g! begin

          g! channel ch0 [symbol=String flag=String]

          ch0

          g! g! view v0 "SELECT * FROM ch0 where symbol='OK'"

          v0

          g! view v1 "SELECT COUNT(*) AS sucCount, flag FROM v0 group by flag"

          v1

          g! view v2 "SELECT COUNT(*) AS toltalCount, flag  FROM ch0 group by flag "

          v2

          g!

          g! query "ISTREAM(SELECT 1.0*sucCount/toltalCount as sucCount, v2.flag FROM v2 LEFT OUTER JOIN v1 on v1.flag = v2.flag where toltalCount != 0 )"

          q0

          g! send [symbol="FAILED" flag="IBM"]

          13:00:42 388 -> insert event: {sucCount=0.0, flag=IBM}

          g! send [symbol="OK" flag="Sun"]

          13:04:03 890 -> insert event: {sucCount=1.0, flag=Sun}

          g! send [symbol="OK" flag="Sun"]

          g! send [symbol="FAILED" flag="Sun"]

          13:04:13 826 -> insert event: {sucCount=0.6666667, flag=Sun}

          g! send [symbol="OK" flag="IBM"]

          13:04:19 250 -> insert event: {sucCount=0.5, flag=IBM}

          g! send [symbol="FAILED" flag="Sun"]

          13:04:22 058 -> insert event: {sucCount=0.5, flag=Sun}

          g! send [symbol="OK" flag="Sun"]

          13:04:25 112 -> insert event: {sucCount=0.6, flag=Sun}

          g! send [symbol="FAILED" flag="Sun"]

          13:04:27 283 -> insert event: {sucCount=0.5, flag=Sun}

           

          Gala