按小时分组，但显示另一个字段 [英] Group by hour but show another field

问题描述

我需要找到某个小时的第一个和最后一个价格.目前，我的查询中有一个WHERE子句，该子句确定了我要看的时间.

I need to find the first and last price in a certain hour. Currently my query has a WHERE clause that determines the hour I'm looking at.

有没有办法做到这一点，这样我就可以向我显示特定日期每小时的结果?

Would there be a way of doing this so that I can just have show me the results for every hour for a particular date?

SELECT TOP 1 Price FROM MyData WHERE [Product] = 'XXXXXX' AND CAST([Deal] AS Date) = '2013-09-04' AND DATEPART(HOUR, [Deal]) = 9 Order By (datediff(minute, '1990-1-1', [Deal Time]) /60)

Deal字段的类型为DateTime

AND DATEPART(HOUR, [Deal]) = 9这是我要替换的部分，可以让我仅查看价格为该小时内第一个价格的所有条目吗?

AND DATEPART(HOUR, [Deal]) = 9 this is the bit I would like to replace with something that allows me to just see all entries where the price is the first price in that hour?

编辑，我希望能够在N分钟之内完成它<一个小时呢?就像每半小时后的结束价格...等等.

EDIT I'd like to be able to do it for any N minutes < an hour too? So like the end price after each half hour... etc..

推荐答案

您正在尝试查找每小时的最后价格(而不是最高价格).你可以这样做.让我从您的查询开始，以便您可以查看转换(此格式已格式化，因此我可以轻松阅读):

You are trying to find the last price in each hour (as opposed to the maximum). You can do this. Let me start with your query so you can see the transformations (this is formatted so I can read it easily):

SELECT TOP 1 Price
FROM MyData
WHERE [Product] = 'XXXXXX' AND
      CAST([Deal] AS Date) = '2013-09-04' AND
      DATEPART(HOUR, [Deal]) = 9
Order By (datediff(minute, '1990-1-1', [Deal Time]) /60)

这个想法是在每小时内为每个记录分配一个序号.该数字将从最近的时间戳开始，然后下降.然后，最新价格是顺序值为1的价格.这使用窗口功能row_number().

The idea is to assign a sequential number to each record within each hour. This number will start at the most recent time stamp and descend. Then, the latest price is the one where the sequential value is 1. This uses the window function row_number().

结果查询不使用显式聚合:

The resulting query doesn't use an explicit aggregation:

select DATEPART(HOUR, [Deal]), price
from (select md.*,
            row_number() over (partition by DATEPART(HOUR, [Deal])
                               order by [Deal] desc) as seqnum
      from MyData md
      where [Product] = 'XXXXXX' AND
            CAST([Deal] AS Date) = '2013-09-04'
     ) md
where seqnum = 1;

您可以将日期条件从where子句中删除，并在partition by中添加日期值，从而将其延长到几天.

You can extend this to multiple days by removing the date condition from the where clause to and adding a date value in the partition by.

这篇关于按小时分组，但显示另一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！