使用max()的一种特定方法

Question

我有DateTime，总计，材料，和混合列。

该设备运行混合A为x小时，然后切换为混合B为x小时量。然后切换回混合A。

我想提出一个问题，即：

Select max(total), material, mix 
from database 
group by material, mix

然而，我需要抓住一个事实，混合A是两次运行，只有一个值将被记录下来，我不知道有多少材料是用在一个被跳过。

是否有一种方法来看待最大(总计)，然后寻找一个新的最大时，每次混合或物质变化？(不是实际不同的混合物/材料，而是从混合B到混合A的实际变化。

编辑以显示数据的外观

Timestamp                  Mix           Material          Tons  
2017-02-08 07:49:25.000    MixA          Mat1              30.05
2017-02-08 07:50:25.000    MixA          Mat1              30.27
2017-02-08 07:51:25.000    MixA          Mat1              30.50
2017-02-08 07:52:25.000    MixA          Mat1              30.76
2017-02-08 07:53:25.000    MixA          Mat1              31.01
2017-02-08 07:58:25.000    MixB          Mat1              0.1
2017-02-08 07:59:25.000    MixB          Mat1              0.8
2017-02-08 08:00:25.000    MixB          Mat1              1.3
2017-02-08 08:01:25.000    MixB          Mat1              2.0
2017-02-08 08:02:25.000    MixB          Mat1              2.5
2017-02-08 08:10:25.000    MixA          Mat1              0.1
2017-02-08 08:01:25.000    MixA          Mat1              0.5

所以，在上面显示的第一个记录之前，机器运行了一段时间。然后，他们切换混合，运行，然后切换回来，结束了一天的生产。

我试图找到最有效的方法来找出所用材料的数量，因为Tons列是一个正在运行的总数，我不能简单地把它相加。MAX()很简单，但是我会错过第二轮混合A。

Answer 1

在我看来就像gap-and-islands。

样本数据

注意，我添加了Mat2以表明我们需要通过它进行分区，并修正了最后一个条目的时间戳。

DECLARE @T TABLE(ts datetime2(0), Mix varchar(50), Material varchar(50), Tons float);

INSERT INTO @T (ts, Mix, Material, Tons) VALUES
('2017-02-08 07:49:25', 'MixA', 'Mat1', 30.05),
('2017-02-08 07:50:25', 'MixA', 'Mat1', 30.27),
('2017-02-08 07:51:25', 'MixA', 'Mat1', 30.50),
('2017-02-08 07:52:25', 'MixA', 'Mat1', 30.76),
('2017-02-08 07:53:25', 'MixA', 'Mat1', 31.01),
('2017-02-08 07:58:25', 'MixB', 'Mat1', 0.1  ),
('2017-02-08 07:59:25', 'MixB', 'Mat1', 0.8  ),
('2017-02-08 08:00:25', 'MixB', 'Mat1', 1.3  ),
('2017-02-08 08:01:25', 'MixB', 'Mat1', 2.0  ),
('2017-02-08 08:02:25', 'MixB', 'Mat1', 2.5  ),
('2017-02-08 08:10:25', 'MixA', 'Mat1', 0.1  ),
('2017-02-08 08:11:25', 'MixA', 'Mat1', 0.5  ),
('2017-02-08 07:49:25', 'MixA', 'Mat2', 30.05),
('2017-02-08 07:50:25', 'MixA', 'Mat2', 30.27),
('2017-02-08 07:51:25', 'MixA', 'Mat2', 30.50),
('2017-02-08 07:52:25', 'MixA', 'Mat2', 30.76),
('2017-02-08 07:53:25', 'MixA', 'Mat2', 31.01),
('2017-02-08 07:58:25', 'MixB', 'Mat2', 0.1  ),
('2017-02-08 07:59:25', 'MixB', 'Mat2', 0.8  ),
('2017-02-08 08:00:25', 'MixB', 'Mat2', 1.3  ),
('2017-02-08 08:01:25', 'MixB', 'Mat2', 2.0  ),
('2017-02-08 08:02:25', 'MixB', 'Mat2', 2.5  ),
('2017-02-08 08:10:25', 'MixA', 'Mat2', 0.1  ),
('2017-02-08 08:11:25', 'MixA', 'Mat2', 0.5  );

查询

WITH
CTE_rn
AS
(
    SELECT
        ts
        ,Mix
        ,Material
        ,Tons
        ,ROW_NUMBER() OVER (PARTITION BY Material ORDER BY ts) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY Material, Mix ORDER BY ts) AS rn2
    FROM @T
)
,CTE_Groups
AS
(
    SELECT
        ts
        ,Mix
        ,Material
        ,Tons
        ,rn1
        ,rn2
        ,rn1 - rn2 AS GroupNumber
    FROM CTE_rn
)
SELECT
    Material
    ,Mix
    ,MAX(Tons) AS MaxTons
    ,MAX(ts) AS MaxTS
FROM CTE_Groups
GROUP BY
    Material
    ,GroupNumber
    ,Mix
ORDER BY
    Material
    ,MaxTS
;

结果

+----------+------+---------+---------------------+
| Material | Mix  | MaxTons |        MaxTS        |
+----------+------+---------+---------------------+
| Mat1     | MixA | 31.01   | 2017-02-08 07:53:25 |
| Mat1     | MixB | 2.5     | 2017-02-08 08:02:25 |
| Mat1     | MixA | 0.5     | 2017-02-08 08:11:25 |
| Mat2     | MixA | 31.01   | 2017-02-08 07:53:25 |
| Mat2     | MixB | 2.5     | 2017-02-08 08:02:25 |
| Mat2     | MixA | 0.5     | 2017-02-08 08:11:25 |
+----------+------+---------+---------------------+

如果需要的话，可以对其进行进一步的总结，以添加31.01和0.5 for Mat1。只需再添加一个GROUP BY Material, Mix即可。问题并不是真正清楚的结果应该是什么。

它是如何工作的

一步一步地运行该查询，了解它是如何工作的。

“缺口和岛屿”是一个典型的问题。当一个Mix发生变化时，“海岛”后面跟着一个“缺口”。

CTE_rn计算两组行号。它们之间的区别是组的数目(CTE_Groups)。

WITH
...
SELECT *
FROM CTE_Groups
ORDER BY Material, ts;

产

+---------------------+------+----------+-------+-----+-----+-------------+
|         ts          | Mix  | Material | Tons  | rn1 | rn2 | GroupNumber |
+---------------------+------+----------+-------+-----+-----+-------------+
| 2017-02-08 07:49:25 | MixA | Mat1     | 30.05 |   1 |   1 |           0 |
| 2017-02-08 07:50:25 | MixA | Mat1     | 30.27 |   2 |   2 |           0 |
| 2017-02-08 07:51:25 | MixA | Mat1     | 30.5  |   3 |   3 |           0 |
| 2017-02-08 07:52:25 | MixA | Mat1     | 30.76 |   4 |   4 |           0 |
| 2017-02-08 07:53:25 | MixA | Mat1     | 31.01 |   5 |   5 |           0 |
| 2017-02-08 07:58:25 | MixB | Mat1     | 0.1   |   6 |   1 |           5 |
| 2017-02-08 07:59:25 | MixB | Mat1     | 0.8   |   7 |   2 |           5 |
| 2017-02-08 08:00:25 | MixB | Mat1     | 1.3   |   8 |   3 |           5 |
| 2017-02-08 08:01:25 | MixB | Mat1     | 2     |   9 |   4 |           5 |
| 2017-02-08 08:02:25 | MixB | Mat1     | 2.5   |  10 |   5 |           5 |
| 2017-02-08 08:10:25 | MixA | Mat1     | 0.1   |  11 |   6 |           5 |
| 2017-02-08 08:11:25 | MixA | Mat1     | 0.5   |  12 |   7 |           5 |
| 2017-02-08 07:49:25 | MixA | Mat2     | 30.05 |   1 |   1 |           0 |
| 2017-02-08 07:50:25 | MixA | Mat2     | 30.27 |   2 |   2 |           0 |
| 2017-02-08 07:51:25 | MixA | Mat2     | 30.5  |   3 |   3 |           0 |
| 2017-02-08 07:52:25 | MixA | Mat2     | 30.76 |   4 |   4 |           0 |
| 2017-02-08 07:53:25 | MixA | Mat2     | 31.01 |   5 |   5 |           0 |
| 2017-02-08 07:58:25 | MixB | Mat2     | 0.1   |   6 |   1 |           5 |
| 2017-02-08 07:59:25 | MixB | Mat2     | 0.8   |   7 |   2 |           5 |
| 2017-02-08 08:00:25 | MixB | Mat2     | 1.3   |   8 |   3 |           5 |
| 2017-02-08 08:01:25 | MixB | Mat2     | 2     |   9 |   4 |           5 |
| 2017-02-08 08:02:25 | MixB | Mat2     | 2.5   |  10 |   5 |           5 |
| 2017-02-08 08:10:25 | MixA | Mat2     | 0.1   |  11 |   6 |           5 |
| 2017-02-08 08:11:25 | MixA | Mat2     | 0.5   |  12 |   7 |           5 |
+---------------------+------+----------+-------+-----+-----+-------------+

然后我们简单地用GROUP BY Material, GroupNumber, Mix来得到最后的结果。

Answer 2

把这个放在这里，只是为了显示什么是被使用的，这样你就可以知道你有多大的帮助来清理和优化它。目前这样做的方法是：

SELECT D1.[Day],
  D1.[TimeStamp],
  D1.[Material],
  D1.Source,
  CASE WHEN D1.[RunTotal] - D2.[RunTotal] >= 0 
    THEN D1.[RunTotal] - D2.[RunTotal] ELSE 0 END AS [Tons]
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [TimeStamp]) rownum, 
  [TimeStamp], 
  cast([TimeStamp] AS date) [Day], 
  MIN(CASE Equipment WHEN 'Bin1' THEN CAST([Run Total] AS decimal(18, 3)) END) AS [RunTotal], 
  MIN(CASE Equipment WHEN 'Bin1' THEN Material END) AS [Material], 
  'Bin1' AS Source
FROM  Plant_Production.dbo.Data  
WHERE Equipment = 'Bin1'
GROUP BY [Timestamp]) D1 LEFT OUTER JOIN
  (SELECT ROW_NUMBER() OVER (ORDER BY [TimeStamp]) rownum, 
    [TimeStamp],     
    cast([TimeStamp] AS date) [Day], 
    MIN(CASE Equipment WHEN 'Bin1' THEN CAST([Run Total] AS decimal(18, 3)) END) AS [RunTotal],
    MIN(CASE Equipment WHEN 'Bin1' THEN [Material] END) AS [Material], 
    'Bin1' AS Source
  FROM Plant_Production.dbo.Data
  WHERE Equipment = 'Bin1'
  GROUP BY [TimeStamp]) D2 ON D1.RowNum - 1 = D2.RowNum AND D1.[Day] = D2.[Day]

他们对每一个Bin位置都这样做，并对日期范围的所有值进行汇总。您建议的方式不仅查询速度更快，而且更简洁。