質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.37%

  • SQL

    3159questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • SQL Server

    758questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQLで日付を持ったデータの集計を行いたい

解決済

回答 7

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 7,007

hos

score 6

SQL(SQL Server)で日付を持ったデータの集計をしています。
開始日時と終了日時を持ったデータに対し、1ヶ月分、23時59分59秒に、
開始されていて終了されていないデータの件数を一覧にしたいのですが
うまい方法が思いつきません。

データはごく簡単に書くと以下のようなテーブルです。

CREATE TABLE hoge (
    ID int PRIMARY KEY,
    開始日時 datetime2(0),
    終了日時 datetime2(0), --終了していない場合はNULL
    )

1日分を持ってくるのは以下のような感じで考えています。

DECLARE @対象日時 datetime2(0)
    SET @対象日時 = '2016-07-22 23:59:59'

SELECT COUNT(*) ASFROM hoge
 WHERE 開始日時 <= @対象日時
   AND (終了日時 < @対象日時 OR 終了日時 IS NULL)

日付マスタのようなものはあるので、そこで1ヶ月分の日付のリストを作成し、LEFT OUTER JOIN させるようなことを考えています。

ループさせるのではなくSQLで解決させるにはどうすればよいでしょうか?

どのような結果が欲しいか追記します。

たとえば、下記のようなレコードがあった場合

ID,開始日時,終了日時
1,"2016-04-02 01:00:00","2016-04-05 01:00:00"
2,"2016-04-04 01:00:00",NULL


結果としては

日付,件数
"2016-04-01", 0 //1件もない
"2016-04-02", 1 //1のデータのみ
"2016-04-03", 1 //1のデータのみ
"2016-04-04", 2 //1と2のデータ
"2016-04-05", 1 //1のデータは23:59:59には終了していて2のデータのみ
"2016-04-06", 1 //2のデータのみ


といった感じになります。日付のところは、上記の例だと

SELECT 日付 FROM 日付マスタ WHERE 日付 BETWEEN '2016-04-01' AND '2016-04-06'


というように取り出せます。

わかりにくくて申し訳ありませんがよろしくお願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • dupont_kedama

    2016/07/27 16:28

    希望する結果セットの例を提示していただいた方が良いと思います。

    キャンセル

回答 7

+1

1か月分の終了してないデータをサブクエリで絞り込む。
この際、開始時刻は年月日の形式に変換しておく。
上記を from にし、年月日を group by して
あとは年月日ごとに count すればいける感じ!?

// ご希望の内容を勘違いしてたら申し訳ない。
--- 追加 ---

修正された質問を拝見しました。

テーブルと(必要な分の)日付マスタを cross join して
該当レコード(マスタ由来の日付が範囲内にある)を選別して
マスタ由来の日付で gropu by して count した結果と
日付マスタ(必要な分)を組み合わせればいけそうですが…

正直なところ、そこまで面倒なことをするくらいなら
すなおにループしますね。自分だったら。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/27 16:51

    ありがとうございます。
    ご回答いただいた方法だと開始時刻での集計になる感じだと思います。

    もうすこしやりたいことを追記いたしました。

    キャンセル

+1

とりあえず1ヶ月は手入力で

select cast(c.日付 as date) 日付, count(h.開始日時) 件数 
from 日付マスタ c left join hoge h on cast(c.日付 as date) >= cast(h.開始日時 as date) and 
(h.終了日時 is null or cast(c.日付 as date) <= h.終了日時) 
where c.日付 between '2016-04-01' and '2016-04-06'
group by 1

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

こんばんは。
要望の出力を行うのでしたら最終的に下記のような形のSQLに持ち込めれば良さ気です。

SELECT
    T.日付
,   T.件数
FROM
    ( 
    SELECT
        M.日付
    ,   SUM( 
            CASE 
                WHEN H.開始日時 IS NOT NULL 
                THEN 1 
                ELSE 0 
            END
        ) AS 件数
    FROM
        日付マスタ M 
        LEFT JOIN HOGE H 
            ON M.日付 BETWEEN H.開始日 AND COALESCE(H.終了日, 月末日) 
    GROUP BY
            M.日付 
    ) T
WHERE
    T.日付 BETWEEN 検索条件:開始日 AND 検索条件:終了日
ORDER BY
    T.日付

SQLの結合所条件の部分は、
終了日時NULLを考慮して月末日を入れる形を取ってますが、
この箇所は検索条件のTO条件と一致させても良いと思います。

一応月内の最終日まで表示することを考慮して月末日とは書いておりますが・・・
後、開始日、終了日の項目は時間部を切り捨てていることを前提として書いてますので悪しからず・・・

追記

SQL内のSUM関数使っている所ですが、
列名指定のCOUNTはNULLはカウント対象外となるので、
A.Ichiさん掲示のコードのようにCOUNT(開始日時)で事足りますね。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

期待する結果から考えるとこんな感じでしょうか?
1か月分が当月なのか前月なのか任意なのかがわからないので
とりあえず手入力で書いておきます

SELECT
      T2.日付
    , COUNT(*) ASFROM hoge T1
LEFT JOIN 日付マスタ T2
-- 日付マスタの日付が開始日時以降
ON T2.日付 >= CAST(T1.開始日時 AS DATE)
-- 日付マスタの日付が終了日時の0時0分より前 か 終了日時が設定されていない
AND (T2.日付 < CAST(T1.終了日時 AS DATE) OR T1.終了日時 IS NULL) 
AND T2.日付 BETWEEN '2016-04-01' AND '2016-04-06'
GROUP BY
      T2.日付

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

0

予めカレンダーテーブルを用意しておかないかぎり、
データの無い日付を抽出することはできません。
予め10年分くらいの有効日付を投入しておけばよいかと

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

結局うまくいかず、1日ごと処理を行うことにしました。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

結局うまくいかず、1日ごとデータを取込みました。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • SQL

    3159questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • SQL Server

    758questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。