我有一个现有的MySQL查询(我是一个新手,所以请温柔),我需要修改,但我正在努力确定所需的更改-
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秒)。asset会根据主查询而改变。
所以结果将是(对于资产1157):
id location duration
1157 1 4
1157 2 6
1157 1 8
似乎可以做到:
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 Fiddle.
更新:
如果您需要连接其他表,您应该实际JOIN
而不是子选择。由于在当前最外层有一个GROUP BY
,因此需要将现有语句包装在另一组括号中—以防止对事实表进行分组。还有一些其他的调整:
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
删除:
-- 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 Fiddle返回与SQL Fiddle相同的结果集,其中前面的查询针对没有重复的数据运行。
如果需要调整/进一步的细节,请评论。