mysql 获取小时/分钟/秒的总和

Question

我有一个表，其中包含:用户ID和时间戳，每次用户打开页面时都会插入一个新字段。

我正在尝试获取出现在的小时/分钟/天/周总数多个用户的间隔为 1 个月。

我尝试了很多不同的查询，但每个查询的效率都非常低。

理想情况下，我希望得到类似的结果:

   userid | minutes | hours | days | weeks
      1      10080     168      7      1
      2       1440      24      1      0

希望有人能够阐明如何做到这一点。

下面是我尝试过的查询:

  SELECT 
    w.time AS `week`, 
    d.time AS `day`, 
    h.time AS `hour`, 
    m.time AS `minutes`
    FROM (
             SELECT 
            SUM( t.time ) AS `time`
             FROM (
                    SELECT 
                    COUNT( DISTINCT WEEK( `timestamp` ) ) AS `time`
                FROM table
                    WHERE 
                    userid =  "1"
                    AND 
                    `timestamp` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                    GROUP BY MONTH( `timestamp` )
                    ) t
         ) w, 
        (
             SELECT 
            SUM( t.time ) AS `time`
             FROM (
                    SELECT 
                    COUNT( DISTINCT DAY( `timestamp` ) ) AS `time`
                    FROM table
                    WHERE 
                    userid =  "52"
                    AND 
                    `timestamp` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                    GROUP BY MONTH( `timestamp` )
             ) t
            ) d,
         (
        SELECT 
            SUM( t.timestamp ) AS `time`
            FROM (
                SELECT 
                    COUNT( DISTINCT HOUR( `timestamp` ) ) AS `time`
                    FROM table
                    WHERE 
                    userid =  "1"
                    AND 
                    `timestamp` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                GROUP BY DAY( `timestamp` )
                 ) t
            ) h, 
        (
            SELECT 
            SUM( t.timestamp ) AS `time`
            FROM (
                SELECT 
                    COUNT( DISTINCT MINUTE( `timestamp` ) ) AS `time`
                 FROM table
                WHERE 
                    userid =  "1"
                AND 
                    `timestamp` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
                    GROUP BY HOUR( `timestamp` )
             ) t
         ) m

这个任务似乎太过分了，也许有人有更好的东西？

Answer 1

我不清楚你想要“总计”什么。

如果您想确定用户在一个月内的任何给定分钟是否有“点击”(或您存储在表中的任何交易)，然后您想要计算“分钟周期”的数量“用户点击的一个月内:

 SELECT t.userid
      , COUNT(DISTINCT DATE_FORMAT(t.timestamp,'%Y-%m-%d %H:%i')) AS minutes
      , COUNT(DISTINCT DATE_FORMAT(t.timestamp,'%Y-%m-%d %H'   )) AS hours
      , COUNT(DISTINCT DATE_FORMAT(t.timestamp,'%Y-%m-%d'      )) AS days
      , COUNT(DISTINCT DATE_FORMAT(t.timestamp,'%X-%V'         )) AS weeks
 FROM mytable t
WHERE t.timestamp >= '2012-06-01'
  AND t.timestamp <  '2012=07-01'
GROUP BY t.userid 

它所做的就是获取每个时间戳，并将其放入“桶”中，通过砍掉秒，砍掉分钟，砍掉时间等。

基本上，我们获取一个时间戳(例如'2012-07-25 23:15:30')并将其分配给

minute '2012-07-25 23:15'
hour   '2012-07-25 23'
day    '2012-07-25'

'2012-07-25 23:25:00' 的时间戳将被分配给

minute '2012-07-25 23:25'
hour   '2012-07-25 23'
day    '2012-07-25'

然后我们检查并计算分配了时间戳的不同存储桶的数量。如果这是该用户当月的所有点击，则查询将返回 2(表示分钟数)和 1(表示所有其他时间段计数)。

对于当月内有一次点击的用户，该用户的所有计数将为 1。

对于在同一分钟内完成所有“点击”的用户，查询将再次为所有计数返回 1。

(对于一个月内没有“点击”的用户，不会返回任何行。(如果您想返回零计数，则需要加入另一个行源来获取用户列表。)

对于一天内每秒都有一次“点击”的用户，此查询将返回类似于示例中 userid 2 所示的计数。

此结果集为您提供了用户一个月内事件的指示...该用户在一个月内活跃了多少个“分钟周期”。

可以返回的“天”的最大值是该月的天数。 “小时”返回的最大可能值是月份时间中天数的 24 倍。 “分钟”返回的最大可能值将是该月天数的 1440 倍。

但同样，我并不完全清楚您想要返回什么结果集。但这似乎是一个比之前“选定”答案中的结果集更合理的结果集。