SQL Server 每小时返回4行

Question

我有一个查询，可以返回一天内某个设备所有的消息（简化）：

    SELECT date, value 
    FROM Messages 
    WHERE date between '04/01/2018 00:00:00' AND '04/01/2018 23:59:59' 
    ORDER BY date asc

问题在于它返回了太多的行。例如，每分钟最少1行（一天1440行），我必须在图表中打印出来。

我该如何返回每15分钟的第一行，以便每小时获得4行？

期望结果：

        date                value  
2018-01-04 05:00:00.000  ||  5,52
2018-01-04 05:15:00.000  ||  5,48
2018-01-04 05:30:00.000  ||  5,35
2018-01-04 05:45:00.000  ||  5,42

- Louis Eloy

---

请标记您的数据库管理系统（Oracle，SQL Server，PostgreSQL，mySQL等）。 - JohnHC

刚刚添加了标签，谢谢。 - Louis Eloy

你是否将结果数据存储在表中或只是选择？我认为你可以使用CURRENT_TIMESTAMP并引用存储的数据... - Jack Nicholson

这是示例表格数据还是期望的结果？ - jarlh

@JackNicholson 我只是在选择数据。 - Louis Eloy

Answer 1

你可以通过求模运算符（%）实现，如下所示：

SELECT date, value 
    FROM Messages 
    WHERE date between '04/01/2018 00:00:00' AND '04/01/2018 23:59:59'  and (datepart(minute,date) % 15) = 0
    ORDER BY date asc;

这个查询返回的数据包含一个日期行，该行的分钟可以被15（四分之一）整除。我认为这可能解决了你的问题。

注意：我没有使用秒，因为根据你在问题中提供的语言，你的数据每分钟添加一次。

- A.D.

---

这个很好用，唯一的问题是如果在那一分钟内有多行数据，它仍然会返回多行。我该如何进行过滤？ - Louis Eloy

但是你引用了你每分钟插入的数据。 - A.D.

每分钟至少插入1个，可能会有几条消息在一分钟内。 - Louis Eloy

所以您想要获取本分钟最后添加的数据，或者您的日期部分也遵循一种模式进行插入。 - A.D.

在编程中，每15分钟的第一行将是最好的。 - Louis Eloy

Answer 2

如果您在一分钟内有多行数据，或者行数据不完全匹配小时:分钟的格式，您可以使用以下方法：

SELECT * INTO tab FROM (VALUES
('2018-01-01 05:00:01', 1),
('2018-01-01 05:10', 2),
('2018-01-01 05:20', 3),
('2018-01-01 05:28', 4),
('2018-01-01 05:31', 5)
) T(Date,Value)

SELECT Date,Value FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CAST(Date AS DATE),
                                           DATEPART(HOUR,Date),
                                           DATEPART(MINUTE,Date)/15
                              ORDER BY Date) RowNum FROM tab
) T WHERE RowNum=1

它返回：

Date                 Value
----                 -----
2018-01-01 05:00:01      1
2018-01-01 05:20         3
2018-01-01 05:31         5

- Paweł Dyl

---

+1 对于最佳答案。你可以通过以下方式对其进行改进：ROW_NUMBER() over (PARTITION BY DATEDIFF(minute, 0, Date)/15 ORDER BY Date)r - t-clausen.dk

最终这个答案帮了我很多，我同意这是最佳答案。+1 - Louis Eloy

Answer 3

你可以简单地使用“like”条件：

and date like '%00:00.000' or date like '15:00.000' ...

- Jakob

---

我不认为这会给你正确的答案。 - Ankit Bajpai

Answer 4

使用日期的分钟部分进行模运算。

select *
from mytable T1
where datepart(minute, T1.date)%15 = 0

- JohnHC

Answer 5

我会首先获得按15分钟间隔分区的行号。

SELECT Truncdate, value FROM (
    SELECT date
         , value
         , dateadd(minute, datediff(minute, 0, date) / 15 * 15, 0) AS TruncDate
         , row_number() OVER (PARTITION BY dateadd(minute, datediff(minute, 0, date) / 15 * 15, 0) ORDER BY date) as RowNum
    FROM messages
    ) x
WHERE x.rownum = 1

如果您想查看该15分钟块中实际的第一个日期时间，而不是块舍入日期，请将Truncdate更改为外部选择中的日期。

e：我实际上没有阅读问题。这种方法的优点在于，即使它发生在块开始的那一分钟之后，它仍将获得每个块中的第一个值，这是我现在注意到并不是您解决方案的要求。

- fauxmosapien