SQL-从数据库中选择最“活跃”的时间 [英] SQL - select most 'active' time from db

问题描述

与 SQL密切相关-选择最活跃的时间跨度fromdb 但有不同的问题。

我有一个交易表。在此表中，我将交易日期时间存储在UTC中。我有几个月的数据，大约有20,000个交易日期。

"I have a table of transactions. In this table I store the transaction datetime in UTC. I have a few months of data, about 20,000 transactions a day."

更改方式

  select datepart(hour, the_column) as [hour], count(*) as total 
  from t 
  group by datepart(hour, the_column) 
  order by total desc

这样我就可以选择最活跃的特定年，月，日，时，分和秒。

so that I can select the specific year, month, day, hour, minute, and second that was the most 'active'.

要澄清一下，我不是在寻找一天中哪几小时或哪几分钟最活跃。相反，哪个时刻最活跃。

To clarify, I'm not looking for which hour or minute of the day was most active. Rather, which moment in time was the most active.

推荐答案

Select 
    DATEPART(year, the_column) as year
    ,DATEPART(dayofyear,the_column) as day
    ,DATEPART(hh, the_column) as hour
    ,DATEPART(mi,the_column) as minute
    ,DATEPART(ss, the_column) as second
    ,count(*) as count from t
Group By 
    DATEPART(year, the_column)
    , DATEPART(dayofyear,the_column)    
    , DATEPART(hh, the_column)
    , DATEPART(mi,the_column)
    , DATEPART(ss, the_column)
order by count desc

这篇关于SQL-从数据库中选择最“活跃”的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！