sql-server - 如何获取每10分钟数据sql查询的最高记录

Question

 WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(
                PARTITION BY cdt, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdt), 0)
                ORDER BY cdt DESC 
             )
    FROM brands
)
SELECT
    brandID, brandname, cdt, udt, brandstatus, addedby
FROM Cte
WHERE rn = 1 and cdt between '2013-11-01 00:00:00' and '2013-11-16 00:00:00'  ORDER BY brandId

我的表格如下:

ID  brandname     cdt                          udt  brandstatus addedby
1   khasim  2013-11-01 19:14:18.120 2013-11-15 19:14:18.123 1   1
2   khasim  2013-11-01 19:14:18.121 2013-11-15 19:14:18.123 1   1
3   khasim  2013-11-01 19:14:18.122 2013-11-15 19:14:18.123 1   1          
4   khasim  2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1   1
5   khasim  2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1   2
6   tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1   2
7   abcdef  2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1   4

我想获取 x 小时/天范围内的每 n 分钟数据。如果 n=10，它应该返回 x=5 小时之间 10 分钟间隔的最大值。

Answer 1

对于 n = 10

rn = ROW_NUMBER() OVER(
                PARTITION BY DATEDIFF(MINUTE, 0, cdt) / 10
                ORDER BY cdt DESC)