是否可以仅使用读取访问来将查询按每分钟进行分组

Question

我有一个查询，可以在其中提取数据并将其显示在行图中。我想在演示文稿上遇到麻烦，因为我希望每分钟每分钟将结果分组(在我的示例中为5(。

我希望我的结果设置为每行n：第三分钟，即使在给定的n：第三分钟没有命中。

我面临的另一个问题是在我只有阅读权利的连接上工作。

我尝试阅读有关如何解决我的问题的各种建议，但是大多数人似乎推荐包括每个n：第一分钟作为可以加入的表格或包括服务器端功能的额外时间表。我无法使用这些特定建议，因为我无法将功能和/或表添加到DB。

。

非常感谢您对如何解决此问题的任何建议。 -Jonas

SELECT 
    CONVERT(varchar,DATEADD(MINUTE, DATEDIFF(MINUTE, 0, P.[TIME])/5 * 5,0), 120),
    SUM(P.[AMOUNT]),
    P.[Q],
FROM
(SELECT
    MessageType AS [Q],
    Handled AS [TIME],
    count(Handled) AS [AMOUNT]
FROM dbo.CosMessage
WHERE MessageType IN('Z03', 'Z04', 'Z05')
GROUP BY Handled, MessageType) AS P
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, P.[TIME])/5 * 5,0), P.[Q]

我当前的结果集看起来像这样：

2019-07-02 10:45    2   Z03
2019-07-02 10:45    2   Z04
2019-07-02 10:45    2   Z05
2019-07-02 10:50    7   Z03
2019-07-02 10:50    24  Z04
2019-07-02 10:50    2   Z05
2019-07-02 11:20    1   Z05

我希望我的结果订购：

2019-07-02 10:45    2   Z03
2019-07-02 10:45    2   Z04
2019-07-02 10:45    2   Z05
2019-07-02 10:50    7   Z03
2019-07-02 10:50    24  Z04
2019-07-02 10:50    2   Z05
2019-07-02 10:55    0   Z03
2019-07-02 10:55    0   Z04
2019-07-02 10:55    0   Z05
…       
2019-07-02 11:20    0   Z03
2019-07-02 11:20    0   Z04
2019-07-02 11:20    1   Z05

Answer 1

根据我的评论，我们需要生成一些行日期。为此使用数字表好得多，但我假设您没有一个：

WITH dg (d) AS (
  SELECT CONVERT(datetime, '2019-07-02 10:45:00', 120) AS d
  UNION ALL
  SELECT dateadd(minute, 5, a.d) as d
  FROM   dg a
  WHERE  a.d < CONVERT(datetime, '2019-07-02 11:20:01', 120)
)

从中选择将生成一个相隔5分钟的日期列表，在1045和1120

之间

接下来，我们需要用一些值交叉加入以获取我们的z号;

SELECT * FROM
  dg
  CROSS JOIN
  (SELECT 'Z03' as z UNION ALL SELECT 'Z04' UNION ALL SELECT 'Z05') z

这每次都会重复三次，与每个不同的z匹配，所以现在我们刚刚将我们的真实数据加入其中：

SELECT dg.d, z.z, COUNT(m.MessageType) as c FROM
  dg
  CROSS JOIN
  (SELECT 'Z03' as z UNION ALL SELECT 'Z04' UNION ALL SELECT 'Z05') z
  LEFT JOIN dbo.CosMessage m
  ON
    z.z = m.MessageType AND
    m.Handled >= dg.d AND
    m.Handled < DATEADD(minute, 5, dg.d)
GROUP BY
  dg.d, z.z

您的原始查询付出了一些努力，将时间绕到最接近的5分钟。我们可以通过让SQLServer根据实际时间落入的时间范围来进行联接来避免这种情况 - 10:46的时间落入10:45 to 10:45+5范围。在z.z = m.messagetype上匹配将仅限于Z3到Z5。最后，因为如果没有匹配的记录，DG.D和Z.Z具有淡水谷，但M.MessageType将是null的，并且计数不会计数nulls，因此DG.D和Z.Z的零件将返回这些行的零：

dg.d               z.z  m.handled         m.messagetype
2019-07-02 10:50   Z05  2019-07-02 10:51  Z05            --it will count as 2 when grouped
2019-07-02 10:50   Z05  2019-07-02 10:52  Z05            --it will count as 2 when grouped
2019-07-02 10:55   Z03  null              null           --it will count as 0 when grouped

请参阅如何将10:51和10:52分配给10:50时插槽。当我们仅在DG.D和Z.Z上进行分组时，这些将在该插槽中算作2，而空行将计算为0

要在更多操作中查看最终查询，请删除group by并将其作为select *

不要忘记，您需要从顶部组装并从此答案的底部进行选择；您不能独自运行选择，因为它需要我一开始就定义的CTE，但是为了清晰度，我没有在最终查询中重复使用

Answer 2

是的，这是可能的，但有点复杂。您应该在分组之前修改日期。在您的示例中，您需要每5分钟记录一次。因此，将您的最后一位数字更改为0或5。您可以通过模块作为DATEADD(MINUTE, (DATEPART(MINUTE, your_date) % 5) * -1, your_date)实现这一目标。更改之后，将计算为FORMAT(DATEADD(MINUTE, (DATEPART(MINUTE, your_date) % 5) * -1, your_date), 'yyyyMMddhhmm')格式化。现在您可以按照此值进行分组。

SELECT FORMAT(DATEADD(MINUTE, (DATEPART(MINUTE, your_date) % 5) * -1, your_date), 'yyyyMMddhhmm'), COUNT(*) 
FROM your_table
GROUP BY FORMAT(DATEADD(MINUTE, (DATEPART(MINUTE, your_date) % 5) * -1, your_date), 'yyyyMMddhhmm')