SQL Server 2008 で DATEADD と GROUP BY を使用して時間単位または10分単位でグループ化

SQL Server 2008 で時間データを時間単位または10分単位でグループ化する方法

DATEADD と GROUP BY を使用する

最も基本的な方法は、DATEADD 関数と GROUP BY 句を使用して、時間データを所望の時間間隔でグループ化することです。

SELECT
  DATEADD(HOUR, -1, [時刻列]) AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY DATEADD(HOUR, -1, [時刻列])
ORDER BY 時間;

このクエリは、時刻列 を1時間前へ繰り下げた値を 時間 列として、その時間におけるレコード数を 件数 列として集計します。 ORDER BY 句を使用して、結果を時間の昇順に並べ替えます。

10分間隔でグループ化するには、DATEADD 関数の引数に MINUTE を指定します。

SELECT
  DATEADD(MINUTE, -10, [時刻列]) AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY DATEADD(MINUTE, -10, [時刻列])
ORDER BY 時間;

CAST と FLOOR を使用する

SELECT
  FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10
ORDER BY 時間;

PIVOT テーブルを使用する

より複雑な分析が必要な場合は、PIVOT テーブルを使用して時間データを時間単位または10分単位でグループ化することができます。

SELECT
  [時間] AS 時間,
  [00:00] AS '00:00',
  [01:00] AS '01:00',
  ...
  [23:00] AS '23:00'
FROM
  (
    SELECT
      DATEADD(HOUR, -1, [時刻列]) AS 時間,
      COUNT(*) AS 件数
    FROM [テーブル名]
    GROUP BY DATEADD(HOUR, -1, [時刻列])
  ) AS 源データ
PIVOT
(
  SUM(件数)
  FOR [時間] IN ([00:00], [01:00], ..., [23:00])
) AS ピボットテーブル
ORDER BY 時間;

このクエリは、まず 源データ として、時刻列 を1時間前へ繰り下げた値を 時間 列、レコード数を 件数 列として集計します。

次に、PIVOT 句を使用して、源データ をピボットテーブルに変換します。 ピボットテーブルには、各時間におけるレコード数が表示されます。 ORDER BY 句を使用して、結果を時間の昇順に並べ替えます。

補足

• 上記のクエリはほんの一例です。 ご自身のニーズに合わせて調整してください。
• 時間データの形式は、データソースによって異なる場合があります。 クエリを実行する前に、データ型を確認してください。
• より高度な分析を行うには、窓関数を使用することができます。

-- 時間データを1時間単位でグループ化
SELECT
  DATEADD(HOUR, -1, [時刻列]) AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY DATEADD(HOUR, -1, [時刻列])
ORDER BY 時間;

-- 時間データを10分単位でグループ化
SELECT
  DATEADD(MINUTE, -10, [時刻列]) AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY DATEADD(MINUTE, -10, [時刻列])
ORDER BY 時間;

-- 時間データを1時間単位でグループ化
SELECT
  FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10
ORDER BY 時間;

-- 時間データを10分単位でグループ化
SELECT
  FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名]
GROUP BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10
ORDER BY 時間;

-- 時間データを1時間単位でグループ化
SELECT
  [時間] AS 時間,
  [00:00] AS '00:00',
  [01:00] AS '01:00',
  ...
  [23:00] AS '23:00'
FROM
  (
    SELECT
      DATEADD(HOUR, -1, [時刻列]) AS 時間,
      COUNT(*) AS 件数
    FROM [テーブル名]
    GROUP BY DATEADD(HOUR, -1, [時刻列])
  ) AS 源データ
PIVOT
(
  SUM(件数)
  FOR [時間] IN ([00:00], [01:00], ..., [23:00])
) AS ピボットテーブル
ORDER BY 時間;

-- 時間データを10分単位でグループ化
SELECT
  [時間] AS 時間,
  [00:00] AS '00:00',
  [00:10] AS '00:10',
  ...
  [59:50] AS '59:50'
FROM
  (
    SELECT
      FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
      COUNT(*) AS 件数
    FROM [テーブル名]
    GROUP BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10
  ) AS 源データ
PIVOT
(
  SUM(件数)
  FOR [時間] IN ([00:00], [00:10], ..., [59:50])
) AS ピボットテーブル
ORDER BY 時間;

注記

• 上記のコードはあくまで例であり、実際の状況に合わせて変更する必要があります。
• [テーブル名] と [時刻列] は、実際のテーブル名と列名に置き換えてください。

SQL Server 2008 で時間データを時間単位または10分単位でグループ化するその他の方法

ROW_NUMBER() 関数と PARTITION BY 句を使用する

この方法は、より柔軟なグループ化と集計を行うことができます。

-- 時間データを1時間単位でグループ化
SELECT
  [時間],
  COUNT(*) AS 件数
FROM
  (
    SELECT
      ROW_NUMBER() OVER (PARTITION BY DATEADD(HOUR, -1, [時刻列]) ORDER BY [時刻列]) AS 行番号,
      DATEADD(HOUR, -1, [時刻列]) AS 時間,
      [時刻列]
    FROM [テーブル名]
  ) AS 中間データ
WHERE 行番号 = 1
ORDER BY 時間;

-- 時間データを10分単位でグループ化
SELECT
  [時間],
  COUNT(*) AS 件数
FROM
  (
    SELECT
      ROW_NUMBER() OVER (PARTITION BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 ORDER BY [時刻列]) AS 行番号,
      FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
      [時刻列]
    FROM [テーブル名]
  ) AS 中間データ
WHERE 行番号 = 1
ORDER BY 時間;

LAG と LEAD 関数を使用する

この方法は、前後の時間データとの差を計算するのに役立ちます。

-- 時間データを1時間単位でグループ化
SELECT
  DATEADD(HOUR, -1, [時刻列]) AS 時間,
  COUNT(*) AS 件数
FROM
  (
    SELECT
      [時刻列],
      LAG([時刻列], 1) OVER (ORDER BY [時刻列]) AS 前の時間,
      LEAD([時刻列], 1) OVER (ORDER BY [時刻列]) AS 次の時間
    FROM [テーブル名]
  ) AS 中間データ
WHERE [時刻列] IS NOT NULL
GROUP BY DATEADD(HOUR, -1, [時刻列])
ORDER BY 時間;

-- 時間データを10分単位でグループ化
SELECT
  FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10 AS 時間,
  COUNT(*) AS 件数
FROM
  (
    SELECT
      [時刻列],
      LAG([時刻列], 10) OVER (ORDER BY [時刻列]) AS 前の時間,
      LEAD([時刻列], 10) OVER (ORDER BY [時刻列]) AS 次の時間
    FROM [テーブル名]
  ) AS 中間データ
WHERE [時刻列] IS NOT NULL
GROUP BY FLOOR(CAST([時刻列] AS DATETIME) TO MINUTE) * 10
ORDER BY 時間;

Tally 表関数を使用する

この方法は、連続する行を簡単に生成することができます。

-- 時間データを1時間単位でグループ化
SELECT
  t.n AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名] AS t
CROSS JOIN
  (
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
    FROM 
      (
        SELECT 1 AS n UNION ALL
        SELECT n + 1 FROM dbo.Tally(1, 24)
      ) AS x
  ) AS t2
WHERE DATEADD(HOUR, -1, t.[時刻列]) = t2.n
GROUP BY t.n
ORDER BY t.n;

-- 時間データを10分単位でグループ化
SELECT
  t.n * 10 AS 時間,
  COUNT(*) AS 件数
FROM [テーブル名] AS t
CROSS JOIN
  (
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS n
    FROM 
      (
        SELECT 1 AS n UNION ALL
        SELECT n + 10 FROM dbo.Tally(1, 144)
      ) AS x
  ) AS t2
WHERE FLOOR(CAST(t.[時刻列] AS DATETIME) TO MINUTE) * 10 = t2.n
GROUP BY t.n * 10
ORDER BY t.n * 10;