グループバイ 0件を表示・・・

Question

SELECT CONVERT(NVARCHAR, 日付項目, 111) AS 日,
DATEPART (hour, 日付項目) AS 時間,
COUNT(*) AS 件数
FROM テーブル名
WHERE
条件1= XXXX
AND 日付項目 >= '2017/X/X 00:00:00'
AND 日付項目 < '2017/X/X 00:00:00'
GROUP BY CONVERT(NVARCHAR, 日付項目, 111),
DATEPART (hour, 日付項目)
ORDER BY 日,時間

上記SQLにて1時間ごとの件数をカウントしようと考えています。
GROUP BYをして0件の時もレコードを表示したい場合には、
どのように修正するのが、最もきれいな記述法でしょうか。

※時間帯のマスタはありません。
※環境上、SQL Severに新規でストアドやマスタを作成することはできません。

Answer 1

※時間帯のマスタはありません。

まさに時間帯マスタをつくって結果をjoinすればよいだけだと思います

Answer 2

再帰的な共通表式 (CTE) で連続データの一時表を作って外部結合します

【SQLServer】超シンプル！再帰CTE（共通テーブル式）で連続データを作成する。

WITH cte (
	dt
) AS (
	SELECT CAST('2017-xx-xx 00:00:00' AS datetime) AS dt
	UNION ALL
	SELECT DATEADD(hour, 1, cte.dt)
	FROM cte
	WHERE cte.dt < '2017-yy-yy 00:00:00'
)
SELECT
	CONVERT (NVARCHAR, cte.dt, 111) AS 日
	,DATEPART (hour, cte.dt) AS 時間
	,COUNT(t.日付項目) AS 件数
FROM
	cte
	LEFT OUTER JOIN テーブル名 AS t
		ON CONVERT(NVARCHAR, cte.dt, 111) = CONVERT(NVARCHAR, t.日付項目, 111)
		AND DATEPART(hour, cte.dt) = DATEPART(hour, t.日付項目)
		AND t.条件1 = xxxx
GROUP BY
	CONVERT(NVARCHAR, cte.dt, 111)
	,DATEPART(hour, cte.dt)
ORDER BY
	日
	,時間
OPTION (MAXRECURSION 0)  

※テストしてないので文法エラーがあるかも

Comments

[sazi]

おお、再帰ですか。確かにこれは思いつかなかった。流石です。
試してみました(CTE部分だけ）、ちょっとエラーがありますね。
cast() とOPTION (MAXRECURSION)です。

WITH cte (dt) AS (
SELECT cast('2017-01-01 00:00:00' as datetime) AS dt
UNION ALL
SELECT DATEADD(hour, 1, cte.dt) FROM cte
WHERE cte.dt < '2018-01-01 00:00:00'
)
select * from cte OPTION (MAXRECURSION 0)

後、CTEで条件の期間とすれば、本文のwhere条件は不要ですね。

[sazi]

元記事は結構古いですね。sqlserverは触ってなかったからなー。
増分の間隔なども調整できそうですし、これはかなり使えますね。
mysqlも8.0からは再帰に対応するそうですし、応用できますね。

[SVC34]

指摘ありがとうございます。修正しました。

Answer 3

SQLServerにGENERATE_SERIES()があれば最も綺麗な記述になります。

でも無いので、こちら（SQL Server TVF(テーブル値関数)-連続生成関数-）のストアドをお借りすると、以下で基準となる期間が生成できます。

select DATEADD(hour, 時数.num, DATEADD(day, 日数.num, [条件開始日付])) as 日付  
from GENERATE_SERIES(0, [条件日数], 1) as 日数 , GENERATE_SERIES(0, 23, 1) as 時数
-- ※[条件開始日付]：抽出条件の開始の日付、[条件日数]：抽出条件の日付の差(終了日-開始日-1)

後は、上記を基準に外部結合や相関問合せなどで求めれば。