dongzhunnai0140 2012-11-01 11:31
浏览 44
已采纳

在MySQL表中查找事件,操作和存储数据

This is question I have still not been able to resolve, so maybe I need to be clearer in what I need.

I have a datasource which I receive and upload to a MySQL database each month. I cannot alter the data although it may not be in the most helpful format. It looks like this:

Ref    Action  Date/Time          User      Location
00123  Create  01:02:12_09:13:13  J Jones   Home  
00456  Create  01:02:12_09:13:13  J Jones   Home  
00123  Revise  03:02:12_15:20:01  A Smith   Home   
00789  Create  01:02:12_09:13:13  J Jones   Home  
00123  Delete  05:02:12_10:51:45  B Halls   Home

x 1000's

It tracks events that occur against a reference number, which is generated by the first event (the Create event). These events occur at varying intervals and are done by various people in various locations.

Using the above example I need to be able to loop through the data for a particular month, pull out a reference number and its 'Create' event, then locate all the other events for that ref number.

I then need to be able to manipulate this information, for example calculate the time difference between Create and Revise, and then Revise and Delete, and who did them, where.

I need to be able to this for all the ref numbers created in the month or a date range.

So I am hoping I can create a query that can do this - find a ref number, find the other events, string them together in some way - so that in the end I have new data -

Ref Number, Time of Create, Create By Who, Time of Revise/By Who, Time of Delete etc

It would be useful if this new data could be stored in a new table, I would think(?)

Can this be done as a query or perhaps combination of query and PHP (arrays?)

  • 写回答

2条回答 默认 最新

  • dqunzip3183 2012-11-01 11:41
    关注

    Okay, the first stage is to transform this data in a MySQL table, and you will have something like

    ref integer,
    action  enum('create','revise','delete'),  -- ordered by life cycle: create first, delete last
    ts  timestamp,
    user    varchar(32),    -- or maybe user_id
    loc varchar(32),    -- again maybe location_id, or ENUM
    

    So to pull out an event given its reference

    SELECT * FROM mytable WHERE ref = 123 ORDER BY action;
    

    For date ranges you can use

    WHERE ts BETWEEN 'date1' AND 'date2'
    

    To get time differences,

    SELECT TIMEDIFF(b.ts, a.ts) AS delta, b.*
        FROM mytable AS a
        JOIN mytable AS b ON (a.action = 'CREATE' AND a.ref = b.ref)
        WHERE a.ref = 123;
    

    Finally to denormalize the table:

    SELECT _create.*,
        TIMEDIFF(_revise.ts - _create.ts) AS revise_delta,
        TIMEDIFF(_delete.ts - _delete.ts) AS delete_delta,
    FROM mytable AS _create
        LEFT JOIN mytable AS _revise ON (_create.ref = _revise.ref
                   AND _create.action = 'CREATE' AND AND _revise.action = 'REVISE' AND ***)
        LEFT JOIN mytable AS _delete ON (_create.ref = _delete.ref
                   AND _create.action = 'CREATE' AND AND _delete.action = 'DELETE' AND ***)
    ;
    

    Here * is some way of identifying the unique relation between a CREATE event and the corresponding DELETE event.

    If refs are unique, then AND *** is not needed (* equals to True).

    For example if the refs are recycled every month, and it never happens that an event may span two months, you can impose that the year and month of _create should be the same as those of _revise; that and the equality of .ref establish a biunivocal match.

    Otherwise it gets much more complicated, and I'd try creating a VIEW that for each CREATE event selects the COALESCE of NOW() and the datetime of the earliest CREATE event with that same ref but ts greater or equal to the current. This way you identify a "window" in which events with that ref should be attributed to that specific CREATE. But this is based on the hypothesis that it never happens that

    00123 CREATE 01-NOV-2012 Jack
    00123 CREATE 04-NOV-2012 Jill
    00123 DELETE 05-NOV-2012 Joe  <-- which event is this one?
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿