at moment, have following table of values:
-------------------------------- | id | action | time | -------------------------------- | 1 | | 01 | | 1 | bb | 10 | | 1 | c | 12 | | 2 | c | 05 | | 2 | | 08 | | 2 | bb | 17 | | 2 | | 26 | | 2 | bb | 47 | assume data arranged ascending time. want each call, when data grouped id, have new column called bb_time has time difference of bb , previous event in call, like:
---------------------------- | id | ... | bb_time | ---------------------------- | 1 | ... | 09 | | 2 | ... | 21 | if bb occurs @ start of call, bb_time equal zero, , if bb occurs more once in call, column should return max time difference. edit: , if bb doesn't appear in call, either null or 0 work.
the closest(?) i've been able crack limiting bb actions in where clause , tracking time way, doesn't give lag.
thoughts?
some people don't trust session variables, find long careful relatively reliable. disclaimer: believe mysql not officially have defined order of evaluation evaluation of select result fields. in practice seems left right, query relies on, consider/re-evaluate whenever mysql server versions change. also, i've seen other related questions on here suggest kind of query may not "play nice" views.
select id, max(bb_time) bb_time ( select id, `action` , @previd := ifnull(@previd, 0) previd , if(`action`<>'bb' , 0 , if(@previd<>id, 0, `time` - ifnull(@prevtime, `time`)) ) bb_time , @prevtime := `time` , @previd := id the_table order id, `time` ) subq group id ; updated show id values, 0 ids no bb actions.
edit: tested sample data. first time came 0's, second time worked. might need initialize @prev session variables before select safe.
Comments
Post a Comment