時間範囲を5分間隔でグループ化

mySQLのコマンドでやりたいことがあるのですが、うまくいきません。

SELECT a.timestamp, name, count(b.name) 
FROM time a, id b 
WHERE a.user = b.user
  AND a.id = b.id
  AND b.name = 'John'
  AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00' 
GROUP BY a.timestamp

これは私の現在の出力ステートメントです。

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:32:22  John  2
2010-11-16 10:35:12  John  7
2010-11-16 10:36:34  John  1
2010-11-16 10:37:45  John  2
2010-11-16 10:48:26  John  8
2010-11-16 10:55:00  John  9
2010-11-16 10:58:08  John  2

どうすれば5分間隔の結果にグループ分けできますか？

次のような出力にしたいのですが

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:30:00  John  2
2010-11-16 10:35:00  John  10
2010-11-16 10:40:00  John  0
2010-11-16 10:45:00  John  8
2010-11-16 10:50:00  John  0
2010-11-16 10:55:00  John  11
TMS
編集された質問 17日 4月 2012 в 8:03
プログラミング
sql mysql group-by
3日 12月 2010 в 4:03
まだビューはありません
質問へのコメント (1)

これはすべてのインターバルで機能する。

**PostgreSQL***

Răzvan Flavius Panda
編集した答え 13日 3月 2019 в 5:24
解説 (7)

postgres の場合、使用する方が簡単で正確であることがわかりました。

date_trunc。

のような機能:

select name, sum(count), date_trunc('minute',timestamp) as timestamp
FROM table
WHERE xxx
GROUP BY name,date_trunc('minute',timestamp)
ORDER BY timestamp

「分」、「時間」、「日」などのさまざまな解像度を提供できます。.. date_truncまで。

解説 (5)

むしろ、ラウンドの代わりに「GROUP BY UNIX_TIMESTAMP(time_stamp)DIV 300」を使用する必要があります(。./ 300)丸めのため、一部のレコードが2つのグループ化された結果セットにカウントされることがわかりました。

解説 (3)

同じ問題に出くわしました。

分単位でグループ化するのは簡単であることがわかりました。 epoch を数秒単位で分けてから、丸めるか、床を使用して残りの乗車するだけです。 したがって、 5分で間隔を取得する場合は、 300秒を使用します。 。

SELECT COUNT(*) cnt, 
to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300) 
AT TIME ZONE 'UTC' as interval_alias
FROM TABLE_NAME GROUP BY interval_alias

< / code>。

。
interval_alias cnt。
------------------- - - - - -。
  2010-11-16 10:30:00 2。
2010-11-16 10:35:00 10。
2010-11-16 10:45:00 8。
2010-11-16 10:55:00 11。
 < / pre>。

これにより、選択した分間隔でデータを正しくグループ化します。ただし、データを含まない間隔は返されません。 これらの空の間隔を取得するには、関数generate_seriesを使用できます。


。


SELECT generate_series(MIN(date_trunc('hour',timestamp_column)),
max(date_trunc('minute',timestamp_column)),'5m') as interval_alias FROM 
TABLE_NAME


< / code>。
結果:


。
interval_alias。
       -------------------。
    2010-11-16 10:30:00。
  2010-11-16 10:35:00。
2010-11-16 10:40:00。
   2010-11-16 10:45:00。
2010-11-16 10:50:00。
   2010-11-16 10:55:00。
   < / pre>。

ここで、発生がゼロの間隔で結果を取得するには、両方の結果セットを outer結合します。


。


SELECT series.minute as interval,  coalesce(cnt.amnt,0) as count from 
   (
   SELECT count(*) amnt,
   to_timestamp(floor((extract('epoch' from timestamp_column) / 300 )) * 300)
   AT TIME ZONE 'UTC' as interval_alias
   from TABLE_NAME  group by interval_alias
   ) cnt

RIGHT JOIN 
   (    
   SELECT generate_series(min(date_trunc('hour',timestamp_column)),
   max(date_trunc('minute',timestamp_column)),'5m') as minute from TABLE_NAME 
   ) series


on series.minute = cnt.interval_alias。


< / code>。
最終結果には、値のないものでも、5分間隔のシリーズが含まれます。


。
間隔数。
------------------- - - - - -。
  2010-11-16 10:30:00 2。
2010-11-16 10:35:00 10。
2010-11-16 10:40:00 0。
2010-11-16 10:45:00 8。
2010-11-16 10:50:00 0。
 2010-11-16 10:55:00 11。
 < / pre>。

間隔は、prene_seriesの最後のパラメーターを調整することで簡単に変更できます。 私たちの場合、 '5m' を使用しますが、必要な間隔は**です。
Nestor Martinez
編集した答え 6日 12月 2017 в 4:12
解説 (2)

クエリーは次のようなものになる：

SELECT 
  DATE_FORMAT(
    MIN(timestamp),
    '%d/%m/%Y %H:%i:00'
  ) AS tmstamp,
  name,
  COUNT(id) AS cnt 
FROM
  table
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp) / 300), name
解説 (0)

おそらく、タイムスタンプをymd:HMに分割し、DIV 5を使って分単位を5分単位に分割する必要があるでしょう。

select year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 5,
       name, 
       count(b.name)
FROM time a, id b
WHERE a.user = b.user AND a.id = b.id AND b.name = 'John' 
      AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00'
GROUP BY year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 12

...そして、クライアントコードの出力をあなたの好きなように調整する。あるいは、別々のカラムを取得する代わりに、SQLの連結演算子を使用して日付文字列全体を構築することもできます。

select concat(year(a.timestamp), "-", month(a.timestamp), "-" ,day(a.timestamp), 
       " " , lpad(hour(a.timestamp),2,'0'), ":", 
       lpad((minute(a.timestamp) DIV 5) * 5, 2, '0'))

...そしてそれをグループ化する

Bill Dueber
編集した答え 3日 12月 2010 в 5:05
解説 (1)

これはどうですか:

select 
    from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp) mod 300) as ts,  
    sum(value)
from group_interval 
group by ts 
order by ts
;
解説 (0)

それでも必要かどうかはわかりません。

SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(timestamp))/300)*300) AS t,timestamp,count(1) as c from users GROUP BY t ORDER BY t;

2016-10-29 19:35:00 | 2016-10-29 19:35:50 | 4 |。 。 2016-10-29 19:40:00 | 2016-10-29 19:40:37 | 5 |。 。 2016-10-29 19:45:00 | 2016-10-29 19:45:09 | 6 |。 。 2016-10-29 19:50:00 | 2016-10-29 19:51:14 | 4 |。 。 2016-10-29 19:55:00 | 2016-10-29 19:56:17 | 1 |。

Simon PA
編集した答え 29日 10月 2016 в 2:29
解説 (0)

MySQLではおそらく正しいクエリが次のようになることがわかりました。

SELECT SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                 '%Y-%m-%d %H:%i:%S' ) , 1, 19 ) AS ts_CEILING,
SUM(value)
FROM group_interval
GROUP BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                   '%Y-%m-%d %H:%i:%S' ) , 1, 19 )
ORDER BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                   '%Y-%m-%d %H:%i:%S' ) , 1, 19 ) DESC

あなたの考えを教えてください。

Matt Fenwick
編集した答え 24日 2月 2012 в 7:47
解説 (0)
select 
CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2)) AS '5MINDATE'
,count(something)
from TABLE
group by CONCAT(CAST(CREATEDATE AS DATE),' ',datepart(hour,createdate),':',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2))
解説 (1)