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

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

ただいまの
回答率

87.58%

【SQL】ACCESSのSQLを使って複数の条件による値をカウントしたいです。

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 299

score 21

以下二つのテーブル(Tabl1とTabl2)からSQLを使って複数の条件による値をカウントしたいです。
条件①Tabl1とTabl2をIDとDateで照合
条件②Tabl1のStart Timeになる前にTable2のValueの値が”最後に”5を超えた時間からStart Timeまでの時間をカウント

Tabl1

ID Date Start Time Goal Time
A 2020/2/10 2020/2/10 10:00 2020/2/10 14:00
B 2020/1/5 2020/1/5 12:00 2020/1/5 13:00

Table2

ID Time Value
A 2020/2/10 5:00 2
A 2020/2/10 6:00 5
A 2020/2/10 7:00 3
A 2020/2/10 8:00 6
A 2020/2/10 9:00 1
A 2020/2/10 10:00 1
A 2020/2/10 11:00 3
A 2020/2/10 12:00 5
A 2020/2/10 13:00 2
A 2020/2/10 14:00 8
A 2020/2/10 15:00 3
B 2020/1/5 8:00 1
B 2020/1/5 9:00 5
B 2020/1/5 10:00 1
B 2020/1/5 11:00 1
B 2020/1/5 12:00 3
B 2020/1/5 13:00 1
B 2020/1/5 14:00 5

抽出したい結果

ID Date ValueTime
A 2020/2/10 2
B 2020/1/5 3

下記のようなクエリを考えていますが、なかなかうまくできません…

SELECT Table1.ID, Table1.Date, Count(Table2.Value) as ValueTime
FROM Table1, Table2
WHERE Table1.ID = Table2.ID AND Table1.Date = Format(Table2.Time,"yyyy/mm/dd") AND Table2.Time <> BETWEEN Start Time AND Goal Time
GROUP BY Table1.ID, Table1.Date


方法をご存じの方ががいらっしゃいましたら、ぜひ教えていただけたら幸いです。よろしくお願いいたします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • player31

    2021/03/10 09:04

    ご連絡ありがとうございます。
    同ID同dateでは、複数のstarttimeは無い認識で合っています。
    主キーは、’ID’と’Date’です。
    よろしくお願いいたします。

    キャンセル

  • sazi

    2021/03/10 09:16 編集

    > Bは、「2020/1/5 9:00」「2020/1/5 10:00」「2020/1/5 11:00」で3を抽出したいです。
    同じ理屈なら、Aは4ではないですか?
    ↑失礼。6がいますね。

    キャンセル

  • player31

    2021/03/10 09:21

    Aは、Start Timeになる前にValueの値が”最後に”5を超えた時間が「2020/2/10 8:00」なので、「2020/2/10 8:00」と「2020/2/10 9:00」の2になります。
    すみません、"最後に"をつけ忘れていました。修正させて頂きます。

    キャンセル

回答 3

checkベストアンサー

+1

  • 「start_timeと同じ日、かつ、start_timeより前、かつ、最後にvalueが5以上になった時間」以上
  • 「start_time」未満

上の条件を満たすtable2の行数を数えたいということであっていますか?

であれば、これをそのままクエリにすれば良いかと思います。

SELECT
  a.id,
  a.date,
  (SELECT COUNT(*)
   FROM table2 AS b
   WHERE a.id = b.id
   AND a.date = DATEVALUE(b.time)
   AND (SELECT MAX(c.time)
        FROM table2 AS c
        WHERE a.id = c.id
        AND a.date = DATEVALUE(c.time)
        AND c.time < a.start_time
        AND c.value >= 5) <= b.time
   AND b.time < a.start_time) AS value_time
FROM table1 AS a

2021-03-11 10:00 追記

  • 「start_timeより前、かつ、最後にvalueが5以上になった時間(Table1.Dateと同じ日とは限らない)」以上
  • 「start_time」未満

↑が実際の仕様であるならば、SQLは以下のようになるかと思います。

SELECT
  a.id,
  a.date,
  (SELECT COUNT(*)
   FROM table2 AS b
   WHERE a.id = b.id
   AND (SELECT MAX(c.time)
        FROM table2 AS c
        WHERE a.id = c.id
        AND c.time < a.start_time
        AND c.value >= 5) <= b.time
   AND b.time < a.start_time) AS value_time
FROM table1 AS a

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2021/03/10 23:18

    ご回答ありがとうございます。
    最後にvalueが5以上になった時間がstart_timeと異なる日(1日前の日)になることもあるのですが、その場合はどのようにすればよいのでしょうか。
    よろしくお願いいたします。

    キャンセル

  • 2021/03/11 22:41

    ありがとうございました。やりたいことができました。

    キャンセル

+1

検出したい、table2の条件を作ります。

select t1.ID, t1.Keydate, t1.starttime, max(t2.time) as rangetime
from  Table1 as t1 
      inner join table2 as t2
      on    t1.id=t2.id
        and t1.keydate = DateValue(t2.time) 
        and t1.starttime > t2.time
where  t2.data_value >= 5
group by t1.ID, t1.Keydate, t1.starttime


これを軸に、table2と結合したものを集計します。

select t3.id, t3.keydate, count(t4.ID) as valuetime
from  (
        select t1.ID, t1.Keydate, t1.starttime, max(t2.time) as rangetime
        from  Table1 as t1 
              inner join table2 as t2
              on    t1.id=t2.id
                and t1.keydate = DateValue(t2.time)  
                and t1.starttime > t2.time
        where  t2.data_value >= 5
        group by t1.ID, t1.Keydate, t1.starttime
      ) t3
      left join table2 as t4
      on t3.id=t4.id
      and t4.time < t3.starttime
      and t4.time >= t3.rangetime
group by t3.id, t3.keydate


※予約語のカラム名などがあるので、適宜変更しています。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2021/03/10 21:13

    ご回答ありがとうございます。
    大変勉強になりました。
    ご回答いただいた内容で試してみたいと思います。

    キャンセル

+1

条件②Tabl1のStart Timeになる前にTable2のValueの値が”最後に”5を超えた時間からStart Timeまでの時間をカウント

提示のデータでは Time は1時間間隔の連続数になってますので、”最後に”5を超えた時間からStart Timeまでの時間差としました。(連続数とは限らないなら指摘してください)

SELECT
    Tabl1.ID,
    Tabl1.Date,
    DateDiff("h",Max(Tabl2.Time),Tabl1.[Start Time]) AS ValueTime
FROM
    Tabl1 INNER JOIN Tabl2
     ON Tabl1.ID = Tabl2.ID
     AND Tabl1.Date = DateValue(Tabl2.Time)
WHERE
    Tabl2.Time < Tabl1.[Start Time]
    AND Tabl2.Value >= 5
GROUP BY
    Tabl1.ID, Tabl1.Date, Tabl1.[Start Time];

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2021/03/10 21:12

    ご回答ありがとうございます。DateDiffで時間差が取れること、とても勉強になりました。
    また、1つ教えていただけないでしょうか。
    ”最後に”5を超えた時間がStart Timeと異なる日にちの場合(Start Timeの1日前の日)は、どうすればよろしいのでしょうか。
    お手数をおかけしますがどうぞよろしくお願いいたします。

    キャンセル

  • 2021/03/11 04:23

    質問の
    条件①Tabl1とTabl2をIDとDateで照合
    がTimeの日付部分がDateと同じという意味なら、Start Time と Time の日付が異なることはないと思いますが。
    条件①を矛盾のない、明確な基準で誤解なく伝わるように修正してもらえませんか。
    「”最後に”5を超えた時間がStart Timeと異なる日にちの場合」となる場合のデータ例を質問に追記してもらえますか。

    キャンセル

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

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

関連した質問

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