计算 SQL Server 中值随时间变化的模式（或频率）分布

发布于 2024-07-08 22:54:32 字数 569 浏览 7 评论 0原文

给定下表，如何计算每小时模式，或每小时频率最高的值？

CREATE TABLE Values
(
    ValueID int NOT NULL,
    Value int NOT NULL,
    LogTime datetime NOT NULL
)

到目前为止，我已经提出了以下查询。

SELECT count(*) AS Frequency, 
DatePart(yy, LogTime) as [Year], 
DatePart(mm, LogTime) as [Month],
DatePart(dd, LogTime) as [Day], 
DatePart(hh, LogTime) as [Hour]
FROM Values
GROUP BY 
Value,
DatePart(yy, LogTime), 
DatePart(mm, LogTime),
DatePart(dd, LogTime), 
DatePart(hh, LogTime)

然而，这会产生每小时每个不同值的频率。如何添加约束以仅返回每小时最大频率的值？

谢谢

原文

Given the following table, how does one calculate the hourly mode, or value with the highest frequency by hour?

CREATE TABLE Values
(
    ValueID int NOT NULL,
    Value int NOT NULL,
    LogTime datetime NOT NULL
)

So far, I've come up with the following query.

SELECT count(*) AS Frequency, 
DatePart(yy, LogTime) as [Year], 
DatePart(mm, LogTime) as [Month],
DatePart(dd, LogTime) as [Day], 
DatePart(hh, LogTime) as [Hour]
FROM Values
GROUP BY 
Value,
DatePart(yy, LogTime), 
DatePart(mm, LogTime),
DatePart(dd, LogTime), 
DatePart(hh, LogTime)

However, this yields the frequency of each distinct value by hour. How do I add a constraint to only return the value with the maximum frequency by hour?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧夏天 2024-07-15 22:54:32

下面的查询可能看起来很奇怪......但它有效并且它给了你你想要的。此查询将为您提供特定“小时”（时间片）内出现频率最高的值。

我不划分为年、月、日等...仅按小时（按照您的要求），即使您的示例查询中有其他字段。

我选择在下面执行“MAX（值）”，因为可能会出现多个“值”并列第一的情况，并且每小时频率最高。如果您愿意，您可以选择进行 MIN、MAX 或其他“决胜局”。

WITH GroupedValues (Value, Frequency, Hour) AS
    (SELECT
        Value,
        COUNT(*) AS Frequency,
        DATEPART(hh, LogTime) AS Hour
    FROM
        dbo.MyValues
    GROUP BY
        Value,
        DATEPART(hh, LogTime))

SELECT
    MAX(Value) AS Value,
    a.Hour
FROM
    GroupedValues a INNER JOIN
        (SELECT MAX(Frequency) AS MaxFrequency,
            Hour FROM GroupedValues GROUP BY Hour) b
    ON a.Frequency = b.MaxFrequency AND a.Hour = b.Hour
GROUP BY
    a.Hour

The following query may look odd... but it works and it gives you what you want. This query will give you the value that had the highest frequency in a particular "hour" (slice of time).

I am NOT dividing into Year, Month, Day, etc... only hour (as you requested) even though you had those other fields in your example query.

I chose to do "MAX(Value)" below, because the case can come up where more than one "value" tied for first place with the highest frequency by hour. You can choose to do MIN, or MAX or some other 'tiebreaker' if you want.

WITH GroupedValues (Value, Frequency, Hour) AS
    (SELECT
        Value,
        COUNT(*) AS Frequency,
        DATEPART(hh, LogTime) AS Hour
    FROM
        dbo.MyValues
    GROUP BY
        Value,
        DATEPART(hh, LogTime))

SELECT
    MAX(Value) AS Value,
    a.Hour
FROM
    GroupedValues a INNER JOIN
        (SELECT MAX(Frequency) AS MaxFrequency,
            Hour FROM GroupedValues GROUP BY Hour) b
    ON a.Frequency = b.MaxFrequency AND a.Hour = b.Hour
GROUP BY
    a.Hour

回复收藏 0 原文

兔姬 2024-07-15 22:54:32

嵌套聚合...

SELECT
    MAX(Frequency) AS [Mode],
    [Year],[Month],[Day],[Hour]
FROM
    (SELECT
         COUNT(*) AS Frequency, 
         DatePart(yy, LogTime) as [Year], 
         DatePart(mm, LogTime) as [Month], 
         DatePart(dd, LogTime) as [Day], 
         DatePart(hh, LogTime) as [Hour]
    FROM 
         Values 
    GROUP BY 
         Value, 
         DatePart(yy, LogTime), 
         DatePart(mm, LogTime), 
         DatePart(dd, LogTime), 
         DatePart(hh, LogTime)
    ) foo
GROUP By
    [Year],[Month],[Day],[Hour]

Nest the aggregates...

SELECT
    MAX(Frequency) AS [Mode],
    [Year],[Month],[Day],[Hour]
FROM
    (SELECT
         COUNT(*) AS Frequency, 
         DatePart(yy, LogTime) as [Year], 
         DatePart(mm, LogTime) as [Month], 
         DatePart(dd, LogTime) as [Day], 
         DatePart(hh, LogTime) as [Hour]
    FROM 
         Values 
    GROUP BY 
         Value, 
         DatePart(yy, LogTime), 
         DatePart(mm, LogTime), 
         DatePart(dd, LogTime), 
         DatePart(hh, LogTime)
    ) foo
GROUP By
    [Year],[Month],[Day],[Hour]

回复收藏 0 原文

~没有更多了~