MySQL -只计算特定日期之间的唯一实例

Question

我一直在看其他几个问题，但我无法从这些问题中找到解决办法。首先是描述，然后是我在其他线程中缺少的东西。(请注意:我非常清楚我们的数据库的非正常化结构，这是我以前在会议上提到的，但这是我们的工作，也是我必须处理的。)

背景描述

我们有一台在25个位置生产产品的机器。这些产品的生产数据被记录在一个表中，其中包括记录每个位置的电流和电压。只有当机器实际生产产品(即机器中有产品)时，才会记录这一记录。没有产品出现的时间，没有任何记录。

本机可运行在两种不同的生产模式:全面生产和研发生产。全面生产意味着产品被不断地插入，因此每个实例在任何时候都有一个产品(也就是说，在任何时候机器中都有25个产品)。第二种模式，即研发生产，一次只生产一个产品(即一个产品进入机器，一个一个地经过25个实例，当这个实例完成时，第二个产品进入机器)。

澄清:每当产品出现时，每个位置每秒钟记录一次数据，这意味着当整个生产运行时，每秒有25个实例。当R&D模式运行时，位置1将连续20秒拥有20个实例，位置2将在接下来的20秒内拥有20个实例，以此类推。

表结构

生产数据：

• id (自动增量)
• productID
• 位置
• 时间(记录数据的时间戳)
• 电流(安培)
• 电压(伏特)

问题

我们想要计算机器的正常运行时间，但是我们想要分离生产模式和研发模式的正常运行时间，我们希望每周分离这些数据。

猜解

由于我们每秒钟都会记录实例，所以我可以计算表中时间值的不同实例的数量，以找出生产模式和研发模式的总正常运行时间。要找到R&D模式，我可以安全地说，每当有一个只有一个条目的时间实例时，我就以R&D模式运行(生产模式将有25个实例)。

进展到目前为止

我有以下查询，它总结了所有不同的实例，以找到生产和研发模式：

SELECT YEARWEEK(time) AS YWeek, COUNT(DISTINCT time) AS Time_Seconds, ROUND(COUNT(DISTINCT time)/3600, 1) AS Time_Hours 
FROM Database.productiondata
WHERE YEARWEEK(time) >= YEARWEEK(curdate()) - 21
GROUP BY YWeek;

此查询将查找表中有多少不同的时间实例，并计数每周的数量和组数。

问题

上面的查询计算表中存在的实例数量，但我只想找到唯一的实例。基本上，我试图找到类似于IF count(time) = 1，然后计数该实例，如果count(time) >1，则根本不计算它(区别仍然是这个)。

我看了其他几个这样的线程，但是几乎所有的线程都解释了如何使用DISTINCT找到唯一的值，这只完成了我所寻找的一半。我得到的最接近的是这，它使用了HAVING子句。我目前被困在以下几个方面：

SELECT YEARWEEK(time) as YWeek, COUNT(Distinct time) As Time_Seconds, ROUND(COUNT(Distinct time)/3600, 1) As Time_Hours
FROM 
(SELECT * FROM Database.productiondata
WHERE time > '2014-01-01 00:00:00'
GROUP BY time
HAVING count(time) = 1) as temptime
GROUP BY YWeek
ORDER BY YWeek;

这里的问题是，嵌套select子句中有一个按时间排列的组，它要花费很长时间(今年只有500万行，所以我可以理解)。我是说，从句法上讲，我认为这是正确的，但要想成为执政者需要花费很长时间。甚至可以解释这段时间。

那就是我所处的地方这是正确的方法，还是有其他更聪明/更少的查询时间/避免组按时间子句的方法？

编辑：作为一个示例，我们有这个表(对格式化表示歉意，这里不知道如何在这里设置表格式)

id    position    time
1     1           1
2     2           1
3     5           1
4     19          1
...   ...         ...
25    7           1
26    3           2
27    6           2
...   ...         ...

这个表显示了生产运行时的样子。如您所见，在记录表中的数据时，没有哪个位置获得第一个条目的一般结构；所发生的情况是，每秒钟记录25个位置，然后根据PLC为每个位置发送数据的速度将数据添加到表中。下表显示了该表在研究模式下运行时的样子。

id    position    time
245   1           1
246   1           2
247   1           3
...   ...         ...
269   1           25
270   2           26
271   2           27
...   ...         ...

因为所有的数据都合并到一个表中，所以我们想知道当COUNT(time)完全等于1时有多少个实例，或者当COUNT(time)严格大于1时我们可以查找每个实例。

EDIT2:，作为对Alan的答复，这个建议给了我

YWeek    Time_Seconds    Time_Hours
201352   1               0.0
201352   1               0.0
201352   1               0.0
...      ...             ...
201352   1               0.0  (1000 row limit)

而我想要的输出是

Yweek    Time_Seconds    Time_Hours
201352   2146            35.8
201401   5789            96.5
...      ...             ...
201419   8924            148.7

EDIT3: --到目前为止，我已经收集了这里的尝试和结果，并在查询上方用灰色进行了描述。

Answer 1

您可以通过取消您的子选择来获得更好的结果：

SELECT YEARWEEK(time) as YWeek, 
       COUNT(time) As Time_Seconds, 
       ROUND(COUNT(time)/3600, 1) As Time_Hours
FROM Database.productiondata
WHERE time > '2014-01-01 00:00:00'
GROUP BY YWeek
HAVING count(time) = 1)
ORDER BY YWeek;

我假设time上有一个index，但是如果没有，您可以通过添加一个来期待性能上的显著提高。

更新：

根据最近添加的示例数据，我不确定您的方法是否正确。time列似乎是一个表示秒的INT，而您将它作为一个DATETIME与YEARWEEK一起处理。下面我有一个SQL中的工作示例，它执行您询问time是否实际上是一个DATETIME列的问题：

DECLARE @table TABLE
    (
      id INT ,
      [position] INT ,
      [time] DATETIME
    )


INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -1, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -2, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -2, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -2, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -2, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -3, GETDATE()) )
INSERT  INTO @table
VALUES  ( 1, 1, DATEADD(week, -3, GETDATE()) )

SELECT  CAST(DATEPART(year, [time]) AS VARCHAR)
        + CAST(DATEPART(week, [time]) AS VARCHAR) AS YWeek ,
        COUNT([time]) AS Time_Seconds ,
        ROUND(COUNT([time]) / 3600, 1) AS Time_Hours
FROM    @table
WHERE [time] > '2014-01-01 00:00:00'
GROUP BY DATEPART(year, [time]) ,
        DATEPART(week, [time])
HAVING COUNT([time]) > 0
ORDER BY YWeek;

Answer 2

SELECT pd1.* 
FROM Database.productiondata pd1
LEFT JOIN Database.productiondata pd2 ON pd1.time=pd2.time AND pd1.id<pd2.id
WHERE pd1.time > '2014-01-01 00:00:00' AND pd2.time > '2014-01-01 00:00:00'
  AND pd2.id IS NULL

您可以将LEFT JOIN放到同一个表中，并且只保留没有关联的行。

使用SQL花键更新查询工作

SELECT pd1.* From productiondata pd1
left Join productiondata pd2
ON pd1.time = pd2.time and pd1.id < pd2.id
Where pd1.time > '2014-01-01 00:00:00' and pd2.id IS NULL;