SQL查询按时间对项目进行分组，但前提是彼此靠近? [英] SQL Query to group items by time, but only if near each other?

问题描述

我正在尝试编写一条SQL语句以从数据库中提取样本值.该表包含与CNC机床中的刀具更换有关的值.我拥有的当前语句可以正确提取值，但前提是给定程序中只有一次出现该工具.如果工具出现多次，则时间值从第一次加载到最后一次加载相对应.只有一个时间"列，通过查找它的第一次和最后一次出现，我可以确定工具的进/出时间.

I am trying to craft a SQL statement to pull sample values from a DB. The table contains values that pertain to tool changes in a CNC machine. The current statement I have pulls values properly, but only if there is one occurrence of the tool in for a given program. If the tool appears multiple times, the time values correspond from the first load, to the last load. There is only one TIME column, and by finding the first and last occurrence of it, I can determine a tools in/out time.

基本示例:

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03

尽管该工具多次出现，但事情变得扑朔迷离，因为我无法再使用TOP和DISTINCT规则.

Things get hairy when the tool appears multiple times though, since I can no longer utilize TOP and DISTINCT rules.

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03
200            12:04
200            12:05
100            12:06
100            12:07

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:07
200            12:04       12:05

Ideal Query Returns:
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03
200            12:04       12:05
100            12:06       12:07

我们正在进行时间分析，当然，这严重地弄乱了总时间值. 当前查询是:

We are doing time analysis, and of course this seriously is messing with the total time values. Current query is:

SELECT * FROM (SELECT DISTINCT SPINDLE_POT FROM TBL_SPINDLE_DATA_M1 
WHERE TIME BETWEEN '4/3/20131:24:13 PM' AND '4/3/2013 3:07:33 PM') AS A 

CROSS APPLY

((SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT AND
TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM') AS NEWTABLE1

JOIN

(SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT 
AND TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM' ORDER BY TIME DESC) 
AS NEWTABLE2 ON (0=0))

我绝不是任何一种SQL查询专家！上面的查询可能是非常错误的，但实际上确实返回了我所需要的.无论如何，是否有将相似项目归为一组的方法，但是如果它们的索引彼此不接触，则有足够的司法公正不将其归类?

I am by no means any kind of SQL Query expert! The above query may be horribly wrong, but it does in fact return what I need. Is there anyway to group similar items, but be judicial enough to not group them if their indexes do not touch each other?

推荐答案

这里是使用LAG/LEAD的另一种方法:

Here's another approach using LAG/LEAD:

DECLARE @rawdata TABLE(Tool_Number INT, [Time] TIME(0));

INSERT @rawdata VALUES
(100,'12:00'), (100,'12:01'), (100,'12:02'), (100,'12:03'),
(200,'12:04'), (200,'12:05'),
(100,'12:06'), (100,'12:07');

;WITH x AS
(
  SELECT Tool_Number, [Time], 
    s = CASE Tool_number WHEN LAG(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END,
    e = CASE Tool_number WHEN LEAD(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END
  FROM @rawdata
),
y AS 
(
  SELECT Tool_Number, s, [Time], e = LEAD([Time],1) OVER (ORDER BY [Time]) 
  FROM x WHERE 1 IN (s,e)
)
SELECT Tool_number, TIME_IN = [Time], TIME_OUT = e 
FROM y 
WHERE s = 1
ORDER BY TIME_IN;

结果:

Tool_number  TIME_IN   TIME_OUT
-----------  --------  --------
100          12:00:00  12:03:00
200          12:04:00  12:05:00
100          12:06:00  12:07:00

这篇关于SQL查询按时间对项目进行分组，但前提是彼此靠近?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！