存储记录之间的时间 [英] Store time between records

问题描述

我有一个名为asset_usages的表，用于记录查看者对资产的查看。相关领域是

  id（int）
 asset_id（int）
 viewer_type（string）
 viewer_id（int）
 visited_at（datetime）
  

我有一个新的字段添加了time_between_viewings，这是一个表示秒的int字段。我想把这个设置到这个时间，几秒钟内，因为这个资产最后被查看了。所以，如果我有这四个记录：

  + ----- + ---------- + ----------- + ------------- + --------------------- +  - ---------------------- + 
 | id | asset_id | viewer_id | viewer_type | seen_at | time_between_viewings | 
 + ----- + ---------- + ----------- + ------------- +  - ------------------- + ----------------------- + 
 | 506 | 7342 | 1182 |用户| 2009-01-05 11:10:01 | NULL | 
 | 509 | 7342 | 1182 |用户| 2009-01-05 11:12:47 | NULL | 
 | 514 | 6185 | 1182 |用户| 2009-01-05 11:14:28 | NULL | 
 | 524 | 6185 | 1182 |用户| 2009-01-05 11:28:18 | NULL | 
 | 618 | 1234 | 1182 |用户| 2009-01-05 11:29:03 | NULL | 
 | 729 | 1234 | 1182 |用户| 2009-01-05 11:29:01 | NULL | 
 + ----- + ---------- + ----------- + ------------- +  - ------------------- + ----------------------- + 
  

那么time_between_viewings应该设置如下：

 code> + ----- + ---------- + ----------- + ------------- +  - -------------------- + ----------------------- + 
 | id | asset_id | viewer_id | viewer_type | seen_at | time_between_viewings | 
 + ----- + ---------- + ----------- + ------------- +  - ------------------- + ----------------------- + 
 | 506 | 7342 | 1182 |用户| 2009-01-05 11:10:01 | NULL | 
 | 509 | 7342 | 1182 |用户| 2009-01-05 11:12:47 | 166 | 
 | 514 | 6185 | 1182 |用户| 2009-01-05 11:14:28 | NULL | 
 | 524 | 6185 | 1182 |用户| 2009-01-05 11:28:18 | 830 | 
 | 618 | 1234 | 1182 |用户| 2009-01-05 11:29:03 | 2 | 
 | 729 | 1234 | 1182 |用户| 2009-01-05 11:29:01 | NULL | 
 + ----- + ---------- + ----------- + ------------- +  - ------------------- + ----------------------- + 
  

其中166和830是每对之间的时差，以秒为单位。

什么是sql填充这个字段？我不太明白。

重要提示：数据并不总是按时间顺序插入数据库。也就是说，您可以有两个记录A和B，其中B具有较高的ID，但A具有稍后的views_at值。所以，找到具有较低ID的第一个匹配记录不会必须给同一个人以前的查看 - 您需要检查数据库中的所有记录。

谢谢！最大

编辑 - 表示time_between_viewings是一个表示秒的int字段。

编辑 - 添加了几行作为具有较高ID但较早时间戳的行的示例

编辑 - 我只是意识到我没有正确地规定问题。 time_between_viewings应该等于从同一个查看器最后一次查看资产的时间，即记录与之前（基于visited_at）的记录之间的时间，该记录具有相同的asset_id，viewer_id和viewer_type 。我给出的示例数据仍然成立，但是我可以放置一些不同的viewer_id和viewer_type值来实例化一些。

解决方案

此SELECT语句将为您提供正确的数据。但是，您可能需要以块为单位进行更新。

您可以删除UPDATE语句的ORDER BY子句。如所写的，派生数据不依赖于外部SELECT语句中的行顺序。

  select asset_id，viewer_id， viewer_type，seen_at，
 prev_viewed_at，timestampdiff（second，prev_viewed_at，seen_at）elapsed_sec 
 from（select asset_id，viewer_id，viewer_type，viewing_at，
（select max（t2.viewed_at））
从表1 t2 
其中t2.viewed_at< Table1.viewed_at 
和t2.asset_id = Table1.asset_id 
和t2.viewer_id = Table1.viewer_id 
）prev_viewed_at 
 from Table1 
）t3 
 order by asset_id，viewer_id，seen_at; 
  

I have a table called asset_usages which records the viewing of an asset by a viewer. The relevant fields are

id (int)
asset_id (int)
viewer_type (string)
viewer_id (int)
viewed_at (datetime)

I have a new field i just added called time_between_viewings, which is an int field representing seconds. I want to set this to the time, in seconds, since that asset was last viewed. So, if i had these four records:

+-----+----------+-----------+-------------+---------------------+-----------------------+
| id  | asset_id | viewer_id | viewer_type | viewed_at           | time_between_viewings |
+-----+----------+-----------+-------------+---------------------+-----------------------+
| 506 |     7342 |      1182 | User        | 2009-01-05 11:10:01 |      NULL             |
| 509 |     7342 |      1182 | User        | 2009-01-05 11:12:47 |      NULL             |
| 514 |     6185 |      1182 | User        | 2009-01-05 11:14:28 |      NULL             |
| 524 |     6185 |      1182 | User        | 2009-01-05 11:28:18 |      NULL             |
| 618 |     1234 |      1182 | User        | 2009-01-05 11:29:03 |      NULL             |
| 729 |     1234 |      1182 | User        | 2009-01-05 11:29:01 |      NULL             |        
+-----+----------+-----------+-------------+---------------------+-----------------------+

then time_between_viewings should be set as follows:

+-----+----------+-----------+-------------+---------------------+-----------------------+
| id  | asset_id | viewer_id | viewer_type | viewed_at           | time_between_viewings |
+-----+----------+-----------+-------------+---------------------+-----------------------+
| 506 |     7342 |      1182 | User        | 2009-01-05 11:10:01 |      NULL             |
| 509 |     7342 |      1182 | User        | 2009-01-05 11:12:47 |      166              |
| 514 |     6185 |      1182 | User        | 2009-01-05 11:14:28 |      NULL             |
| 524 |     6185 |      1182 | User        | 2009-01-05 11:28:18 |      830              |
| 618 |     1234 |      1182 | User        | 2009-01-05 11:29:03 |      2                |
| 729 |     1234 |      1182 | User        | 2009-01-05 11:29:01 |      NULL             |     
+-----+----------+-----------+-------------+---------------------+-----------------------+

where 166 and 830 are the time difference between each pair, in seconds.

What would be the sql to populate this field? I can't quite figure it out.

IMPORTANT NOTE: the data is not always inserted into the db in chronological order. Ie, you could have two records A and B, where B has a higher id but A has a later value for viewed_at. So, looking for the first matching record with a lower id would not necesarily give you the previous viewing by the same person - you'll need to examine all the records in the database.

thanks! max

EDIT - stated that time_between_viewings is an int field representing seconds.

EDIT - added a couple of rows as an example of a row with a higher id but earlier timestamp

EDIT - i just realised that i didn't stipulate the question properly. The time_between_viewings should be equal to the time since the asset was last viewed by the same viewer, ie the time between the record and the previous (based on viewed_at) record that has the same asset_id, viewer_id and viewer_type. The example data i gave still holds, but i could have put in some different viewer_id and viewer_type values to flesh the example out a bit.

解决方案

This SELECT statement will give you the right data. You might need to do the update in chunks, though.

You can drop the ORDER BY clause for the UPDATE statement. As written, the derived data doesn't depend on the order of rows in the outer SELECT statement.

select asset_id, viewer_id, viewer_type, viewed_at, 
       prev_viewed_at, timestampdiff(second, prev_viewed_at, viewed_at) elapsed_sec
from (select asset_id, viewer_id, viewer_type, viewed_at,
            (select max(t2.viewed_at) 
             from Table1 t2
             where t2.viewed_at < Table1.viewed_at
               and t2.asset_id = Table1.asset_id
               and t2.viewer_id = Table1.viewer_id
            ) prev_viewed_at
      from Table1
      )t3
order by asset_id, viewer_id, viewed_at;

这篇关于存储记录之间的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！