每天每小时从包裹中数出多少批

Question

我对DISTINCT、GROUP BY子句非常困惑，无法理解我需要做些什么才能得到我想要的值。

我的桌子(简化)

CREATE TABLE manufacturing
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dt] [datetime] NOT NULL,
    [parcel_id] [int] NULL,
    [batch_no] [int] NULL,
    [tn] [int] NULL
)
GO

每个parcel_id都有batch_no 1到“anything”，batch_no有tn 1到“anything”，所以它们不是固定的最大值。

当机器完成一个步骤(万亿)时，它会将它写入DB。

我需要知道每小时在db中有多少批(从0到24)。

一批可以从15:00开始，在17:00结束--我需要写到17。

我试过的是

SELECT 
    COUNT(parcel_batch) AS batch_count, dt
FROM 
    (SELECT 
         parcel_batch as parcel_batch, MAX(dt) as dt
     FROM 
         (SELECT DISTINCT(CONVERT(varchar, parcel_id) + CONVERT(varchar, batch_no)) as parcel_batch, DATEPART(HOUR, dt) as dt
          FROM manufacturing 
          WHERE DATEPART(YEAR, dt) = 2021 
            AND DATEPART(MONTH, dt) = 09 
            AND DATEPART(DAY, dt) = 15 
            AND fabrika = 2) AS qq 
     GROUP BY
         parcel_batch) AS new 
GROUP BY
    dt

我在第一次查询(第3行)中尝试的是将包裹和批处理结合起来，可以给出唯一的编号，这样我就可以区分。一开始它似乎起作用了，和我的测试值一样。然后，我在真正的db上使用它(这要大得多)，它没有正确地给出。

我得到了今天的全部批次

SELECT DISTINCT rh.parcel_id, CONVERT(varchar, MIN(rh.dt)), CONVERT(varchar, MAX(rh.dt)), MAX(rh.batch_no) FROM manufacturing as rh
WHERE DATEPART(YEAR, rh.dt) = DATEPART(YEAR, GETDATE())
AND DATEPART(MONTH, rh.dt) = DATEPART(MONTH, GETDATE())
AND DATEPART(DAY, rh.dt) = DATEPART(DAY, GETDATE())
GROUP BY rh.parcel_id

但我真的想不出每个小时能得到多少批次，因为我不能把我的头弄清楚(我想在选择之后，我只需要第一次或更多的时间)或分组。

提前谢谢。

更新：

我的数据就是这样。

BEGIN TRANSACTION
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:03:00.000'), 290, 1, 45)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:05:00.000'), 290, 1, 48)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:34:00.000'), 290, 2, 44)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:47:00.000'), 291, 1, 58)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:54:00.000'), 291, 1, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:58:00.000'), 291, 1, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:02:00.000'), 291, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:08:00.000'), 291, 1, 41)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:24:00.000'), 291, 2, 46)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:36:00.000'), 291, 2, 47)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:58:00.000'), 291, 3, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:04:00.000'), 291, 3, 42)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:23:00.000'), 291, 3, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:54:00.000'), 292, 1, 57)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:55:00.000'), 292, 1, 53)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:59:00.000'), 292, 2, 48)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:12:00.000'), 292, 2, 45)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:15:00.000'), 292, 2, 44)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:22:00.000'), 292, 3, 51)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:46:00.000'), 293, 1, 58)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:55:00.000'), 293, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:24:00.000'), 293, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:33:00.000'), 293, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:45:00.000'), 294, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:46:00.000'), 294, 1, 57)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:58:00.000'), 294, 1, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:23:00.000'), 295, 1, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:32:00.000'), 295, 1, 53)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:35:00.000'), 295, 2, 51)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:38:00.000'), 295, 3, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:43:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:47:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:49:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:45:00.000'), 296, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:47:00.000'), 296, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:53:00.000'), 296, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:56:00.000'), 296, 2, 3)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:58:00.000'), 296, 3, 7)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:26:00.000'), 296, 3, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:35:00.000'), 296, 4, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:42:00.000'), 296, 5, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:46:00.000'), 296, 5, 43)

SELECT * FROM manufacturing
COMMIT TRANSACTION

我需要的是上表的输出：

-------------------
| dt | batch_count|
-------------------
| 0  |      2     |
| 1  |      2     |
| 2  |      2     |
| 3  |      3     |
| 4  |      2     |
| 5  |      3     |
| 6  |      2     |
| 7  |      3     |
-------------------

总共18小时除以小时。

Answer 1

我想出了自己的解决方案，看起来效果很完美。我所做的是分组和批次，不首先，所以我只得到一个parcel_id和其中一个批号与他们的最大时间。

然后，从这个查询中，我将结果按小时分组，同时计算该小时内的批数。这给了我完美无瑕的预期结果。

SELECT grp.dt, COUNT(grp.batch_no) batch_count
FROM (
     SELECT MAX(DATEPART(HOUR, dt)) dt, batch_no
     FROM manufacturing
          WHERE dt >= CONVERT(DATE, GETDATE())
     GROUP BY parcel_id, batch_no
     ) grp
GROUP BY grp.dt

还感谢您指出更容易的日期时间比较，并澄清GROUP BY和DISTINCT子句。

Answer 2

我相信以下内容会得到你想要的结果。它仍然在每小时计算不同的批次，但不包括不想计算的批，因为它们不是在当前时间结束的。日期范围安装与问题无关，您可以在必要时添加。

select DatePart(hour, dt) hr, Count(distinct batch_no) batch_count
from manufacturing m
    where not exists (
        select * from manufacturing m2 
        where m2.parcel_id=m.parcel_id 
            and m2.batch_no=m.batch_no 
            and DatePart(hour, m2.dt)>DatePart(hour, m.dt)
    )
group by DatePart(hour, dt)

Answer 3

你似乎在描述：

SELECT parcel_id, DATEPART(hour, rh.dt), COUNT(DISTINCT rh.batch_no)
FROM manufacturing rh
WHERE rh.dt >= CONVERT(DATE, GETDATE()) AND
      rh.dt < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
GROUP BY rh.parcel_id, DATEPART(hour, rh.dt);

注意对日期比较所做的简化。