通过位置变化得出的总持续时间 [英] Sum time duration by location change

问题描述

我有一个现有的MySQL查询(我是一个新手，所以请保持谦虚)，我需要对其进行修改，但仍在努力确定所需的更改-

I have an existing MySQL query (I am a newbie so please be gentle) that I need to modify but am struggling to determine the changes required -

SELECT assetid, locationid, ts,    
  TIMESTAMPDIFF(SECOND,    
    (SELECT MAX(ts) FROM logs WHERE ts< t.ts),
    ts
  ) secdiff
FROM logs t    
where assetid = 1157    
order by ts DESC

(正确返回)

id   location    datetime       time difference (between rows)    
1157    1   2015-07-02 10:21:56 2    
1157    1   2015-07-02 10:21:54 2    
1157    2   2015-07-02 10:21:52 2    
1157    2   2015-07-02 10:21:50 2    
1157    2   2015-07-02 10:21:48 2    
1157    1   2015-07-02 10:21:46 2    
1157    1   2015-07-02 10:21:44 6    
1157    1   2015-07-02 10:21:38 2

我需要能够确定在每个位置花费的总时长(位置可以是1和2以外的其他数字)，但不能按位置分组，而是用/分隔，直到每个位置更改为止.行之间的时间会有所不同(并非总是2秒).根据主要查询，资产ID会发生变化.

I need to be able to determine the total duration spent at each location (location can be other numbers besides 1 & 2) but not grouped by location, rather separated by / until each location change. The time between rows will vary (not always 2 seconds). The assetid will change depending on the main query.

因此，结果将是(对于资产1157):

So the result would be (for assetid 1157):

id        location  duration
1157        1         4
1157        2         6
1157        1         8

推荐答案

以下似乎可以解决问题:

The following seems to do the trick:

SET @locationID=0,@ts=NULL,@changed=0;

SELECT
  MIN(assetID) AS id
  , MIN(locationID) AS location
  , SUM(secDiff) AS duration
FROM
  (SELECT
    assetID
    , locationID
    , @changed := IF(locationID <> previousLocationID, @changed + 1, @changed) AS changed
    , IFNULL(TIMESTAMPDIFF(SECOND,
                           previousTs,
                           ts
                           ),
             0
      ) AS secDiff
  FROM
    (SELECT
      assetID
      , locationID
      , @locationID AS previousLocationID
      , @locationID := locationID AS currentLocationID
      , ts
      , @ts AS previousTs
      , @ts := ts AS currentTs
    FROM Logs L1
    WHERE assetid = 1157    
    ORDER BY ts
    ) L2
  ORDER BY ts
  ) L3
GROUP BY changed
ORDER BY changed DESC
;

查看实际运行情况: SQL小提琴.

更新:

如果需要联接其他表，则实际上应JOIN而不是子选择.由于当前最外层有一个GROUP BY，因此需要将现有语句包装在另一组括号中-以防止对事实表进行分组.为此，还需要进行其他一些调整:

If you need to join additional tables, you should actually JOIN and not sub-select. As there is a GROUP BY at the currently outmost level, the existing statement needs to be wrapped in another set of parentheses - to prevent grouping over the fact tables. With some other adjustments towards that end:

SET @locationID=0,@ts=NULL,@changed=0;

SELECT
  A.name
  , L4.assetID
  , L.name
  , L4.locationID
  , duration
FROM
  (SELECT
    MIN(assetID) AS assetID
    , MIN(locationID) AS locationID
    , SUM(secDiff) AS duration
    , changed
  FROM
    (
-- no change in here
    ) L3
  GROUP BY changed
  ) L4
JOIN Asset A
  ON L4.assetID = A.id
JOIN Location L
  ON L4.locationID = L.id
ORDER BY changed DESC
;

扩展了 SQL小提琴.

更新2:

解决重复列表最直接的方法应该是DISTINCT将其作为第一步:

The most straightforward way to address duplicate listings should be to DISTINCT them away as the very first step:

-- no change here
  (SELECT
    assetID
    , locationID
    , @locationID AS previousLocationID
    , @locationID := locationID AS currentLocationID
    , ts
    , @ts AS previousTs
    , @ts := ts AS currentTs
  FROM
    (SELECT DISTINCT
      assetID
      , locationID
      , ts
    FROM Logs
    WHERE assetid = 1157
    ) L1
  ORDER BY ts
  ) L2
-- no change here either

此 SQL小提琴返回重复的 Logs 数据与 SQL Fiddle 相同的结果集，其中较早的查询针对没有重复项的数据运行.

This SQL Fiddle returns for the duplicated Logs data the same result set as SQL Fiddle, where the earlier query runs against data with no duplicates.

请评论，如果并且因为这需要调整/进一步的细节.

Please comment, if and as this requires adjustment / further detail.

这篇关于通过位置变化得出的总持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！