Q&A
テーブルB.日時は認定番号に対してユニークですよね？
ユニークでない場合（重複がある場合）同じ日時でさらに絞り込まないと駄目ということになり、その場合の条件が必要になります。
実現したいこと
以下の2つのテーブルがあります。
〇テーブルA(マスタテーブル)
・カラム1：認定番号(char型, 主キー)
・カラム2：種類(char型)
(サンプルデータ)
| 認定番号 | 種類 |
|---|---|
| NINTEI01 | TYPE01 |
| NINTEI02 | TYPE02 |
| NINTEI03 | TYPE03 |
〇テーブルB(トランザクションテーブル)
・カラム1：番号(char型, 主キー)
・カラム2：認定番号(char型, 外部キー)
・カラム3：判別フラグ(char型)
・カラム4：日時(date型)
（サンプルデータ）
| 番号 | 認定番号 | 判別フラグ | 日時 |
|---|---|---|---|
| 0001 | NINTEI01 | - | 2020/01/01 12:34:56 |
| 0002 | NINTEI01 | - | 2020/01/02 00:00:00 |
| 0003 | NINTEI01 | - | 2020/01/02 00:00:00 |
| 0004 | NINTEI02 | - | 2020/01/01 12:34:56 |
| 0005 | NINTEI02 | - | 2020/01/02 00:00:00 |
| 0006 | NINTEI02 | - | 2020/01/02 00:00:00 |
| 0007 | NINTEI02 | x | 2020/01/03 00:00:00 |
| 0008 | NINTEI03 | x | 2020/01/01 12:34:56 |
| 0009 | NINTEI03 | x | 2020/01/02 00:00:00 |
| 0010 | NINTEI03 | x | 2020/01/02 00:00:00 |
これらについて、
認定番号をキーに、テーブルAをテーブルBに内部結合させ、以下の様なビューを作成したいです。
カラムは以下の通りです。
・カラム1：テーブルB.番号
・カラム2：テーブルB.認定番号
・カラム3：テーブルA.種類
・カラム4：テーブルB.判別フラグ
含まれるレコードは、下記の条件で、各認定番号ごとに1レコードずつです。
・同一認定番号で、判別フラグが"-"のレコードが存在すれば、その認定番号で判別フラグが"-"で日時が最新のもの…①
・同一認定番号で、判別フラグが"-"のレコードが存在しなければ、その認定番号で日時が最新のもの…②
・上記①, ②でレコードが複数存在する(同一日時)場合、①，②該当レコードのうち、テーブルB.番号が最大のもの
（サンプルデータ）
| 番号 | 認定番号 | 種類 | 判別フラグ |
|---|---|---|---|
| 0003 | NINTEI01 | TYPE01 | - |
| 0006 | NINTEI02 | TYPE02 | - |
| 0010 | NINTEI03 | TYPE03 | x |
試したこと
未完成ですが、以下の様なSQLを試しました。
(クエリの実行は、VBA上でクエリ定義を行い、SQL文を定義、そのSQL文をクエリに適用し、OpenQueryで実行します。)
SELECT テーブルB.番号, テーブルB.認定番号, テーブルA.種類, テーブルB.判別フラグ, switch(テーブルB.判別フラグ = "-", dateadd("yyyy", 5000, テーブルB.日時), true, テーブルB.日時) as 重みづけ FROM テーブルB INNER JOIN テーブルA ON テーブルA.認定番号 = テーブルB.認定番号 ORDER BY テーブルB.認定番号, dateadd("yyyy", 5000, テーブルB.日時), true, テーブルB.日時) DESC
条件が日時と判別フラグの2つが存在するため、1カラムで判断できるdate型のカラム"重みづけ"を追加しています。(判別フラグに"-"が含まれれば、カラム"重みづけ"に5000年を追加)
（"ORDER BY テーブルB.認定番号, dateadd("yyyy", 5000, テーブルB.日時), true, テーブルB.日時) DESC"としているので、各認定番号毎に最初の1レコードずつをビューに表示させれば所望のビューになります。）
ここから、
重みづけを順位に変換した、カラム"順位"を用意し、"順位" = 1 のみ取得しようと考え、
dcount関数を用いることを検討しましたが、結合テーブルをテーブルとしてどのようにdcount関数の引数に渡せばよいかわからずにいます。
その他、よりスマートな方法を含め、良い方法はありますでしょうか。
以上、宜しくお願い致します。
補足情報（FW/ツールのバージョンなど）
OS : Windows10 Professional(x64)
Access : バージョン1803(Office 365)
ご指摘ありがとうございます。
考慮不足でした。
テーブルBにおいて、同一日時かつ同一認定番号となる可能性があります。
(条件追記しました。)
> 日時が最新のもの
とありますが、最新は１件しかないという事で宜しいのですか？
↓
同一認定番号で、かつ同一日時の場合、その認定番号でテーブルB.番号が最新のもの
なのですね。
説明に不備があり申し訳ありません。
条件を修正しました。
認定番号と判別フラグの組み合わせで最大２件で、その２件で、日時が同じでなければ判別フラグが"-"のものを優先し、同じであれば番号の大きい方を優先
ですか？
テーブルBにおいて、同一認定番号、かつ同一判別フラグの該当レコードは、3レコード以上存在する可能性があります。
クエリに関しては、まず該当レコード内で、判別フラグが"-"のものがあれば、判別フラグが"-"の日時が最新のものを優先し、もし日時が最新の同一日時のものが複数存在すれば、番号が大きいものを優先します。
判別フラグが"-"のものがなければ、判別フラグに関係なく、日時が最新のものを優先し、もし日時が最新の同一日時のものが複数存在すれば、番号が大きいものを優先します。
要約すると、優先順位は判別フラグ"-"＞日時＞番号の順になります。
回答になっていますでしょうか。
> テーブルBにおいて、同一認定番号、かつ同一判別フラグの該当レコードは、3レコード以上存在する可能性があります。
上記と、説明の以下は矛盾しています。
> 含まれるレコードは、下記の条件で、各認定番号ごとに1レコードずつです。
含まれるレコードは、下記の条件で、各認定番号ごとに1レコードずつです。
・同一認定番号で、判別フラグが"-"のレコードが存在すれば、その認定番号で判別フラグが"-"で日時が最新のもの…①
・同一認定番号で、判別フラグが"-"のレコードが存在しなければ、その認定番号で日時が最新のもの…②
・上記①, ②でレコードが複数存在する(同一日時)場合、テーブルB.番号が最大のもの
と記載している為、
テーブルBにおいて、同一認定番号、かつ同一判別フラグの該当レコードが3レコード以上存在する場合は、3つ目の条件に該当するという認識ですが、矛盾がありますでしょうか。
番号と日時の関係に於いて、「番号が大きい方が日時も大きい」と言えますか？
それから番号の型は何ですか？
それは成り立ちません。
「番号が大きい方が日時が小さい」可能性もあります。
説明が不足していると思われる点に関して、再度追記してみました。
これで矛盾は解消されますでしょうか。
質問にある条件を検証できるサンプルデータも追記して下さい。
それから、項目の型も明示願います。
テーブルの型に合わせたサンプルデータにして下さい。
そうでないと、SQLを回答しても実際には型のエラーなどになってしまいます。
サンプルデータの日時について、同じものが無いと検証できませんよ
回答2件
0
ベストアンサー
要件的に、テーブルBの番号を限定すれば良いので、必要な番号を求めるクエリーを考えてみます。
先ずは基本となる部分。
SQL
1select 認定番号 2 , CDate(Nz(max(iif(判別フラグ = "-", 日時, Null)), max(iif(判別フラグ = "-", Null, 日時)))) as 判別日時 3 , min(iif(判別フラグ = "-", True, False)) as 判別 4from テーブルB 5group by 認定番号
・説明
判別日時：判別フラグの"-"とそれ以外毎の日時の最大を求め"-"分を優先した結果の日時
判別：判別日時が判別フラグの"-"かそれ以外のどちらが採用されたかの情報
次に、上記の結果から日時が同じ場合を考慮した番号を求めます。
SQL
1select max(tb.番号) as 判別番号 2from テーブルB as TB 3 inner join ( 4 select 認定番号 5 , CDate(Nz(max(iif(判別フラグ = "-", 日時, Null)), max(iif(判別フラグ = "-", Null, 日時)))) as 判別日時 6 , min(iif(判別フラグ = "-", True, False)) as 判別 7 from テーブルB 8 group by 認定番号 9 ) as cond 10 on cond.認定番号=TB.認定番号 11 and cond.判別日時=TB.日時 12 and cond.判別=iif(TB.判別フラグ = "-", True, False) 13group by tb.認定番号
後は上記を条件として加えます。
SQL
1SELECT テーブルB.番号, テーブルB.認定番号, テーブルA.種類, テーブルB.判別フラグ 2FROM テーブルB INNER JOIN テーブルA 3 ON テーブルA.認定番号 = テーブルB.認定番号 4where テーブルB.番号 in ( 5 select max(tb.番号) as 判別番号 6 from テーブルB as TB 7 inner join ( 8 select 認定番号 9 , CDate(Nz(max(iif(判別フラグ = "-", 日時, Null)), max(iif(判別フラグ = "-", Null, 日時)))) as 判別日時 10 , min(iif(判別フラグ = "-", True, False)) as 判別 11 from テーブルB 12 group by 認定番号 13 ) as cond 14 on cond.認定番号=TB.認定番号 15 and cond.判別日時=TB.日時 16 and cond.判別=iif(TB.判別フラグ = "-", True, False) 17 group by tb.認定番号 18 )
where条件にするよりインラインビューとして結合した方が高速かもしれません。
投稿2020/11/17 06:53
編集2020/11/17 06:55総合スコア25173
詳細の説明とご提案ありがとうございます。
ご説明頂いた方法にて実行したところ、正しくデータが取得できました。
仰る通り、where条件ですと実行にかなり時間を要しますので、
ご提案頂きましたインラインビューとして結合する方法を考え、実践してみたいと思います。
(上記方法で実施結果確認後、こちらを締めさせて頂きます)
サブクエリーの場合（インラインビューも含め）、件数が多いと安定しない事（速度ではなくデータ内容）があります。
そういった場合、そのサブクエリーをテーブルに置き換え、データとして確定させると安定します。
また、そのテーブルに対しインデックスなどを適切にすると、サブクエリーより高速になる場合もあります。
現状で時間が掛かるというなら、テーブル化をお薦めします。
補足までありがとうございます。
＞件数が多いと安定しない事（速度ではなくデータ内容）
こちらは、具体的にはどういった事象が発生する可能性があるのでしょうか。
また、
＞サブクエリーをテーブルに置き換え、データとして確定させる
本アプリケーションはクライアント側で動作させますが、具体的な構想としては、
ローカルテーブル(テーブルC)を別途作成し、ご提示頂いたサブクエリの実行結果をそこに出力する
→テーブルCに対し、クエリを実行し結果を得る
で認識はあっていますでしょうか。
１点目の質問については、誤った結果になるという事です。
過去の経験では、数万件を対象に幾つかのgroup byを行うクエリーをネストさせた場合に生じていました。
はっきりとした理由は分かりませんが、経験則として大量なgroup byの結果はテーブルに出力するようにしています。
２点目については、認識は合っています。但し、元のテーブルがAccessのテーブルで同じPCに存在している前提です。
そもそも、時間が掛かっているというのは、どの程度の件数で、との程度時間が掛かっているのでしょうか？また、目標とする処理時間は？
目標次第では、アプローチが変わってきます。
例えば、テーブルのデータを更新する際に今回必要な情報も更新し、時間を分散させるなどです。
ご回答ありがとうございます。
質問2点目に関して、
＞元のテーブルがAccessのテーブルで同じPCに存在している
今回使用しているテーブルが格納されているDBは別PCに存在しております。
このような場合ですとご提示頂いた方法では効果は得られない、ということでしょうか。
また、現在該当トランザクションテーブルのレコード数は5万レコード程度、クエリの実行に3分程度要しており、目標は1分以内です。(あくまで要件ではなく目標レベルです。)
リンクテーブルとして使用していて、そのリンク先がネットワーク越しの場合、テーブルの結合の際に１件毎に問合せが行われる場合があります。
テーブル化についての効果は得られます。
言っているのは全体的な性能の話で、リンク越しの結合を行った場合についての話です。
一旦全件ローカルに落として、処理後に書き戻す方が性能的に上だったりもします。
インデックスなどの検討も行えば処理時間の短縮も期待できそうですけど、処理分散も視野に入れた方が良いかと思います。
自分なら、元のテーブルにフラグ項目を作って、そのテーブルに更新が掛かる場合にフラグ立てを行う処理分散方式で行います。
ご教示頂いたテーブル化とインデックスの最適化により、
検索時間：3分→4秒と大幅短縮となり、実行結果も安定して取得できるようになりました。
所望のパフォーマンスが得られたので、今回は処理分散に関しては対応は不要でしたが、
今後の為にも学習しておこうと思います。
この度は本当に勉強になりました。
ありがとうございました。
0
認定番号１つにつき１件ならテーブルAに対してサブクエリ書いたほうが早そうですね。
クエリデザインのGUI上ではわかりにくいかもしれませんが。
sql
1select A.* 2 ,(select top 1 B.番号 from テーブルB as B 3 where B.認定番号=A.認定番号 4 order by IIF(B.判別フラグ="-",0,1),B.日時 desc,B.番号 desc) as 番号 5from テーブルA as A
テーブルBの主キーさえ取れればもう一個ビューにして書けばフラグも取れます。
ごり押しで番号引っ張ったときにマイナス反転とか目印つけて一回で取る方法がないでもない。
sql
1select A.* 2 ,(select top 1 IIF(B.判別フラグ="-","-","") & B.番号 from テーブルB as B 3 where B.認定番号=A.認定番号 4 order by IIF(B.判別フラグ="-",0,1),B.日時 desc,B.番号 desc) as 番号 5from テーブルA as A
投稿2020/11/17 02:49
編集2020/11/17 10:11
総合スコア3828
ご回答ありがとうございます。
ご提示頂きました方法で、実践したところ所望のデータが取得できました。
本質問欄には記載しておりませんでしたが、テーブルBから取得しなければいけない項目が他にも存在するため、サブクエリが多くなってしまい、実行に時間を要してしまうです。
もう少しトライしてみます。
後者の方法を改良して適当な区切り文字で項目を全部くっつけて文字列処理するって方法もあります。
後で文字列処理が必要になりますが、問い合わせの数を減らせます。
あとは適切なインデックス貼るくらいですね。
,(select top 1 IIF(B.判別フラグ="-","-","") & chr(8) & B.番号 & chr(8) & B.日時 from テーブルB as B
where B.認定番号=A.認定番号
order by IIF(B.判別フラグ="-",0,1),B.日時 desc,B.番号 desc) as 番号
追加でのご提案ありがとうございます。
なるほど、複数項目を区切り文字で1つの項目として処理する案は考えたことがありませんでした。
この方向でも検討してみたいと思います。
あなたの回答
tips
プレビュー
