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

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

ただいまの
回答率

90.61%

  • SQL

    2330questions

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

  • SQL Server

    572questions

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

  • Visual Basic .NET

    96questions

    Microsoft Visual Basic .NET (VB.NET)とはオブジェクト志向のプログラム言語です。 Microsoft"s Visual Basic 6 のバージョンアップとしてみることができますが、Microsoft.NET Frameworktによって動かされています。

重複データを消去するDELETE文について

受付中

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,583

CS12

score 11

VS2003 + SQLserverの環境で収集した測定データを
帳票化するといったソフトを作成しています。

収集したデータはCSV形式のファイルに格納され、1分毎にロギング、
1時間で締められ、最大60件のデータとなります。
(ロギング停止のタイミングでファイルが1つ出来上がります。)

このデータをソフトで取り込む際に、一度DBのワークテーブルに格納します。
その後測定記録用テーブルに格納(INSERTで追加する)する前に
ワークテーブルと重複している日時・時刻があれば、
測定記録用テーブルの対象レコードを削除したいです。
(データの手動取り込みもあり、同時刻のデータも入ってくる可能性があるため。)
SQL構文を考えているのですが、
このような構文でよいものかご意見を伺いたく投稿します。
Common SQL Environment(CSE)を使用し、SQL文を実行したところ、
大体2~4秒くらいで実行終了しますが、30秒近く実行時間がかかることもありました。

  • テーブル構成(関連テーブル抜粋)
 測定記録用テーブル(SOKUTEI_T) ※データ件数は113万件程度(過去2年分保持)です。
  名称     型  長さ
 1.HIDUKE_DT varchar 8 NOT NULL [YYYYMMDD形式]
 2.HIDUKE_TM varchar 8 NOT NULL [hh:mm:ss形式]
 3.RYURYO1  float 8 
 4.RYURYO2  float 8 

インデックス K_SOKUTEI_T ユニークキー
対象フィールド:HIDUKE_DT,HIDUKE_TM

 ワークテーブル(SOKUTEI_W) ※最大60レコード(1分データ×60件)
 1.HIDUKE_DT varchar 8 NOT NULL [YYYYMMDD形式]
 2.HIDUKE_TM varchar 8 NOT NULL [hh:mm:ss形式]
 3.RYURYO1  float 8 
 4.RYURYO2  float 8 

インデックス K_SOKUTEI_T ユニークキー
対象フィールド:HIDUKE_DT,HIDUKE_TM

  • SQL構文
DELETE FROM SOKUTEI_T  WHERE CONVERT(DATETIME,(HIDUKE_DT + ' ' + HIDUKE_TM)) IN (SELECT CONVERT(DATETIME,(HIDUKE_DT + ' ' + HIDUKE_TM)) FROM SOKUTEI_W)
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 4

+1

処理時間の目標がどれぐらいなのか分かりませんが、
気になった事を2つ書いてみますね

>・・・CONVERT(DATETIME,(HIDUKE_DT + ' ' + HIDUKE_TM))

DateTimeに変換せずvarcharのまま処理するのはダメですか?
もしインデックスとか定義してても、これだと無駄になりそうな。

>このデータをソフトで取り込む際に、一度DBのワークテーブルに格納します。 

ワークにはファイルを幾つ格納しますか?
例えば、もし直近の1ヶ月分なのであれば、テーブルSOKUTEI_Tの列HIDUKE_DTに対して
”1ヶ月以内”という条件を付ければデータアクセスが減るのではないでしょうか。
2年分のデータ見なくても1/24で済みそうです。


投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/03/05 22:28

    コメントありがとうございます。
    インデックスは定義しています。
    varcharのまま処理するのが良いか今一度確認してみます。
    ワークに格納するファイルは最大1時間分=60レコードです。
    質問本文に明確になっておりませんでした。申し訳ありません。

    キャンセル

0

>ワークに格納するファイルは最大1時間分=60レコードです。

では、もし何かの事情でconvertが必須だったとしても、
例えば次のように条件を追加して日付で絞れるのではないでしょうか。

… where HIDUKE_DT = '20150306' and convert( …

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

せっかくワークテーブルに入っているのなら、結合を使いましょう!
測定記録用テーブルを内部結合するか、Exists句を使うのが良いと思いますよ。

DELET FROM SOKUTEI_T 
 FROM SOKUTEI_T AS T
WHERE EXISTS
      (SELECT * FROM SOKUTEI_W AS W
        WHERE T.HIDUKE_DT = W.HIDUKE_DT
          AND T.HIDUKE_TM = W.HIDUKE_TM);

DELET FROM SOKUTEI_T 
 FROM SOKUTEI_T AS T
INNER JOIN SOKUTEI_W AS W
   ON T.HIDUKE_DT = W.HIDUKE_DT
  AND T.HIDUKE_TM = W.HIDUKE_TM;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

-1

1点気づいたので、再コメントを

インデックスを有効に使うには、複合キーにして、
第1キーに指定する列に格納される値の種類が少ないほうがいいんです。

たとえば、今回の第一キーはT.HIDUKE_DT(日付)です。
日付だと運用が長くなればなるほど、格納されるデータ種類が増えていきますね。

仮にHIDUKE_DTに5万日種類あったとしたら、
インデックスを張ってあったとしても、
目標にたどり着くために最悪5万回演算しないといけません。

たとえば、NENDO(年度)という列を追加して、インデックスを
第一キーNendo、第二キーHIDUKE_DT、第三キーHIDUKE_TMとした場合

日付種類が5万日だったとしても年度種類が150年ぐらいだったとしたら、
Nendoを探すのに最大150回の演算。
そこから日付を探すのに最大365回の演算。
すなわち目標の日付に到達するまでに最大150+365=515回の
演算ですむ計算になります。

もちろん上記はデータ113万件の中に複数年分のデータが含まれることを前提としています。
もし、データが2~3年分のデータで、時刻種類の多さのほうがネックになるなら、
HIDUKE_TMを時と分秒の列に分けて、インデックスを
第一キーHIDUKE_DT、第二キーHIDUKE_HH、第三キーHIDUKE_MMSSとしたらよいです。

このとき、HHMMSSの形式のデータもほしい(実装効率の観点で)
というなら、計算列を定義して、HHMMSSの形式の列を追加しておくといいですね。

インデックスの設計はコツが要ります。
じっくり業務要件をながめて、しっかり検討してみてください。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2015/04/19 18:21

    1ヶ月以上過去の回答ですが、誤解を招くと思ったためコメントします。
    CS12さんのケースでは、「日付・時刻」の重複確認ですので、日付型のインデックスはそのままで機能すると思います。

    > 仮にHIDUKE_DTに5万日種類あったとしたら、
    > インデックスを張ってあったとしても、
    > 目標にたどり着くために最悪5万回演算しないといけません。
    これは誤り…というか、誤解です。

    データベース毎に特性が異なるため、常にそうとはいえませんが、SQL Serverに関しては左の列は値の一意性が高いほどよくなります。
    しかし、日付型の場合には、扱い方に注意が必要、というのが正しい認識だと思います。

    まず、MSDNでも以下のようにアドバイスされています。
    > インデックスに複数の列が含まれる場合は、列の順序を考慮します。 等しい (=)、より大きい (>)、より小さい (<)、BETWEEN などの検索条件の WHERE 句で使用されるか、結合に含まれる列は、先頭に配置します。 その他の列は、差異の程度、つまり最も差異の大きいものから最も差異の小さいものの順に配置します。
    https://msdn.microsoft.com/ja-jp/library/jj835095%28v=sql.110%29.aspx

    日付型は先頭に配置するのに適したカラムです。
    日付で絞り込む際、複数列からなるインデックスの走査範囲を大幅に減らすことができます。
    値の分布が細かい列ほどディスク上のページ範囲を正確に絞りこむことができます。

    しかし、sirokumaさんの言うように「日付型」をインデックスに含めると検索時間がO(N)になってしまう可能性はあります。
    (上でいう最悪5万回演算しなければならないケース)
    理由は、日付型の選択性(と言えばいいんでしょうか)が場合によって高くなりすぎるためです。

    日付型の列をインデックスにする場合、時間の粒度に気をつける必要があります。
    もしも日付型の列に時間まで入力されている場合、このインデックスの選択性は「時間」にまで及びます。
    これはつまり、日付型より右側の列は「時間が重複したとき」にしか絞込みに使えなくなってしまうということです。
    1日分のデータを参照することが多い場合、「年月日」のみを格納した列を別に設けた方が適切に絞込みができます。

    年度列の追加は常にすればいいというものではありません。
    まず、単にインデックスの一番左に年度列を追加すると、年度列を指定せず日付のみをフィルターした時にインデックスが効きません。
    これでは扱いの難しいテーブルになってしまいます。
    1年分のデータを特定列で絞り込みたいというケースが多いなら、年度列のアプローチは有効です。
    しかし、「年度-日付(時間含む)-別の列」というインデックスを作ると、最後のカラムはやはり情報の取得以外に使えなくなってしまいます。
    この場合、「年度-別の列」でインデックスを作成する必要があります。

    日付型のインデックスは、以下のようにインデックスが張られていると分割して考えるとイメージしやすいでしょうか。
    「年-月-日-時-分-秒-その他の列」


    私の分かる範囲では以上です。よろしくお願いします。

    キャンセル

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

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

関連した質問

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

  • SQL

    2330questions

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

  • SQL Server

    572questions

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

  • Visual Basic .NET

    96questions

    Microsoft Visual Basic .NET (VB.NET)とはオブジェクト志向のプログラム言語です。 Microsoft"s Visual Basic 6 のバージョンアップとしてみることができますが、Microsoft.NET Frameworktによって動かされています。