таким образом, чтобы задействовать максимум()

У меня есть столбцы DateTime, total, material и Mix.

Это оборудование запускает Mix A в течение x часов, а затем переключается на Mix B в течение x часов. Затем переключается обратно на Mix A.

Я хотел бы иметь запрос, который просто: 

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

Однако мне нужно было бы уловить тот факт, что Mix A был запущен дважды, и только 1 значение будет записано, и я не буду знать, сколько материала было использовано в том, который был пропущен.

Есть ли способ посмотреть на Max (total), тогда ищите нового Макса каждый раз, когда меняется смесь или материал? (не фактическая различная смесь / материал, но когда она фактически изменяется от смеси 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
Итак, перед первой записью, показанной выше, машина работала некоторое время. Затем они поменяли миксы, побежали, затем переключились обратно и закончили производство на день.

Я пытаюсь найти наиболее эффективный способ найти количество используемого материала., потому что колонка тонн-это текущий итог, и я не могу просто суммировать его. MAX() было бы очень просто, однако я пропустил бы второй запуск Mix A.

2 2
sql sql-server sql-server-2008-r2 gaps-and-islands

2 ответа:

Для меня это выглядит как 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 для Mat1, Если вам это нужно. Просто добавьте еще один GROUP BY Material, Mix. Вопрос не совсем ясен, каким должен быть результат.

Как это работает

Выполнить запрос шаг за шагом, cte-за-cte, чтобы понять, как это работает.

"Пробелы и острова" - это классическая проблема. Когда 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, чтобы получить конечный результат.

1

Поместите это здесь, чтобы показать, что использовалось, чтобы у вас было представление о том, насколько вы помогли очистить это и оптимизировать его. Текущий способ сделать это: 

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]

Они делают это для каждого местоположения ячейки и суммируют все значения для диапазона дат. Способ, который вы предложили, кажется не только намного быстрее запроса, но и намного чище.

0