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

Question

以下二つのテーブル（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

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

Comments

[m.ts10806]

SQLiteは本件とはどう関係するのでしょう

[sazi]

抽出したい結果についてGoal Timeは関係ないのでしょうか？
それから、関係ない場合ですけど、抽出したい場合の結果のBは２では無いのですか？

[player31]

ご指摘ありがとうございます。SQLiteタグは間違えていたため削除しました。

To：sazi様
説明不足で申し訳ございません。抽出したい結果についてGoal Timeは関係ないです。
Bは、「2020/1/5 9:00」「2020/1/5 10:00」「2020/1/5 11:00」で３を抽出したいです。

[sazi]

start timeまでという事は分かりました。
同ID同dateでは、複数のstarttimeは無い（table1の主キーはidとdate）でしょうか。

[player31]

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

[sazi]

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

[player31]

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

Answer 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

Comments

[player31]

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

[player31]

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

Answer 2

条件②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];

Comments

[player31]

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

[hatena19]

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

Answer 3

検出したい、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

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

Comments

[player31]

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