使用计算出的值扩展查询

huangapple go评论68阅读模式
英文:

Extending a query with calculated value

问题

我有一个类似下面示例的日志表。我有一个查询,将按FK和colB分组,以显示每个FK的每个水果的最新条目。以下是列出的查询。如果可能的话,我想扩展这个查询,以计算当前最大值与相应分组的最新条目之间的时间差,其中进度=1/10。如果需要,我可以将进度列拆分成两个单独的列,但我不确定我正在尝试实现的目标是否可行。有什么建议吗?

--我的表
FK|colA|colB|progress|timestamp
2|y|apple|2/10|2023-03-03 09:43:20
1|c|orange|3/10|2023-03-03 09:42:00
1|b|orange|2/10|2023-03-03 09:41:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|z|apple|1/10|2023-03-03 09:40:35
1|a|orange|1/10|2023-03-03 09:40:00
1|c|orange|3/10|2023-02-03 11:02:00
1|b|orange|2/10|2023-02-03 10:41:00
1|a|orange|1/10|2023-02-03 10:30:00

--当前查询
SELECT l.*
FROM log l,
(SELECT FK, ColB, MAX(TIMESTAMP) AS Timestamp
FROM log
GROUP BY FK, ColB DESC
ORDER BY TIMESTAMP DESC) l1
where l.FK=l1.FK
             AND l.Timestamp=l1.Timestamp
             AND l.colB=l1.colB
ORDER BY l.FK, l.Timestamp DESC;

--当前输出
FK|colA|colB|progress|timestamp
1|c|orange|3/10|2023-03-03 09:42:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|y|apple|2/10|2023-03-03 09:43:20

--期望输出
FK|colA|colB|progress|timestamp|Timetaken(HH:MM:SS)
1|c|orange|3/10|2023-03-03 09:42:00|00:02:00 (2023-03-03 09:42:00 - 2023-03-03 09:40:00)
2|x|pineapple|1/10|2023-03-03 09:40:40|00:00:00 (2023-03-03 09:40:40 - 2023-03-03 09:40:40)
2|y|apple|2/10|2023-03-03 09:43:20|00:02:45 (2023-03-03 09:43:20 - 2023-03-03 09:40:35)
英文:

I have a log table something like the example below. I have a query which will group by FK and colB to show the most recent entry for each fruit per FK. This query is listed below. I would like to extend this query if possible to calculate the time difference between the current max value I'm bringing back and the most recent entry for the respective grouping where the progress = 1/10. I can split the progress column into two separate columns if need be, but I'm not sure what I'm trying to achieve here is even possible. any pointers?

--My Table
FK|colA|colB|progress|timestamp
2|y|apple|2/10|2023-03-03 09:43:20
1|c|orange|3/10|2023-03-03 09:42:00
1|b|orange|2/10|2023-03-03 09:41:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|z|apple|1/10|2023-03-03 09:40:35
1|a|orange|1/10|2023-03-03 09:40:00
1|c|orange|3/10|2023-02-03 11:02:00
1|b|orange|2/10|2023-02-03 10:41:00
1|a|orange|1/10|2023-02-03 10:30:00
--Current Query
SELECT l.*
FROM log l,
(SELECT FK, ColB, MAX(TIMESTAMP) AS Timestamp
FROM log
GROUP BY FK, ColB DESC
ORDER BY TIMESTAMP DESC) l1
where l.FK=l1.FK
             AND l.Timestamp=l1.Timestamp
             AND l.colB=l1.colB
ORDER BY l.FK, l.Timestamp DESC;
--Current Output
FK|colA|colB|progress|timestamp
1|c|orange|3/10|2023-03-03 09:42:00
2|x|pineapple|1/10|2023-03-03 09:40:40
2|y|apple|2/10|2023-03-03 09:43:20
--Desired Output
FK|colA|colB|progress|timestamp|Timetaken(HH:MM:SS)
1|c|orange|3/10|2023-03-03 09:42:00|00:02:00 (2023-03-03 09:42:00 - 2023-03-03 09:40:00)
2|x|pineapple|1/10|2023-03-03 09:40:40|00:00:00 (2023-03-03 09:40:40 - 2023-03-03 09:40:40)
2|y|apple|2/10|2023-03-03 09:43:20|00:02:45 (2023-03-03 09:43:20 - 2023-03-03 09:40:35)

答案1

得分: 1

如果你正在使用MySQL 8,你可以使用LEAD()来获取下一行以进行比较:

类似这样:

SELECT 
K, 
ColB, 
MAX(TIMESTAMP) AS Timestamp,
LEAD(MAX(TIMESTAMP)) OVER w,
TIMEDIFF(LEAD(MAX(TIMESTAMP)) OVER w, MAX(TIMESTAMP)) as compare
FROM log
GROUP BY FK, ColB DESC
WINDOW w as (ORDER by TIMESTAMP)
ORDER BY TIMESTAMP DESC;

使用我的数据,不包括K和ColB,结果如下:

2020-01-28 14:25:00    2020-01-28 14:30:00    00:05:00
2020-01-28 14:30:00    2020-01-28 14:45:00    00:15:00
2020-01-28 14:45:00    2020-01-28 14:47:00    00:02:00
2020-01-28 14:47:00    2020-01-28 15:00:00    00:13:00
2020-01-28 15:00:00    2020-01-28 15:00:00    00:00:00
2020-01-28 15:00:00    2020-01-28 15:06:00    00:06:00
2020-01-28 15:06:00    2020-01-28 15:30:00    00:24:00
2020-01-28 15:30:00    2020-01-28 15:34:00    00:04:00
2020-01-28 15:34:00    2020-01-28 16:15:00    00:41:00
2020-01-28 16:15:00    2020-01-29 07:57:00    15:42:00
2020-01-29 07:57:00    2020-01-29 07:57:00    00:00:00
2020-01-29 07:57:00    2020-01-29 08:00:00    00:03:00
英文:

If you are using MySQL 8, you can use LEAD() to get your next line to compare:

Something like that :

SELECT 
K, 
ColB, 
MAX(TIMESTAMP) AS Timestamp,
LEAD(MAX(TIMESTAMP)) OVER w,
TIMEDIFF(LEAD(MAX(TIMESTAMP)) OVER w, MAX(TIMESTAMP)) as compare
FROM log
GROUP BY FK, ColB DESC
WINDOW w as (ORDER by TIMESTAMP)
ORDER BY TIMESTAMP DESC;

Results with my data without K and ColB :

2020-01-28 14:25:00	2020-01-28 14:30:00	00:05:00
2020-01-28 14:30:00	2020-01-28 14:45:00	00:15:00
2020-01-28 14:45:00	2020-01-28 14:47:00	00:02:00
2020-01-28 14:47:00	2020-01-28 15:00:00	00:13:00
2020-01-28 15:00:00	2020-01-28 15:00:00	00:00:00
2020-01-28 15:00:00	2020-01-28 15:06:00	00:06:00
2020-01-28 15:06:00	2020-01-28 15:30:00	00:24:00
2020-01-28 15:30:00	2020-01-28 15:34:00	00:04:00
2020-01-28 15:34:00	2020-01-28 16:15:00	00:41:00
2020-01-28 16:15:00	2020-01-29 07:57:00	15:42:00
2020-01-29 07:57:00	2020-01-29 07:57:00	00:00:00
2020-01-29 07:57:00	2020-01-29 08:00:00	00:03:00

huangapple
  • 本文由 发表于 2023年3月3日 18:15:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75625760.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定