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

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

ただいまの
回答率

87.89%

テーブル自体を簡略化したいです。

解決済

回答 5

投稿 編集

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

score 16

sqlはdmlができるくらいのレベルです。ほぼ初心者レベルです。
Hive,バージョン1.0.0
すべてを記載すると長くて読みづらいので割愛しています。

以下のようなinsert文が96あります。
15分おきのデータで1日分ありテーブルにinsertを96回行わないといかず日付データを入れ替えるだけで面倒だしミスもしやすいし時間がかかります。
insertを96回せずに1回で終わる方法はないでしょうか?
CASE文とか使えば上手くいきそうな気がしますが、良い案が思いつきません。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • m6u

    2016/08/16 16:52

    mysqlなのかpostgresqlなのかそれともoracleなのか、RDBMSについても示していただければより適切なドキュメントへの誘導が出来そうです。

    キャンセル

回答 5

checkベストアンサー

0

ボトルネックは15分おきの最小時刻を決定するデータが副問い合わせで表現されている点につきます。
つまり、日付と最小時分秒(その他一意化に非必要な項目)のみを持つ介在補助テーブルを実在する
(副問い合わせでなく)とみなせるならば、INSERTコマンドは96個ではなく1個に集約できます。
よって、格段にスピードアップできます。
具体的には、時刻を15で割った項目(+一意化に非必要な項目)でGROUP BY して、最小時分秒を求めた仲介補助テーブルが存在し、仲介補助テーブルによる検索条件を満たすレコードのみからなる第二の仲介補助テーブルが存在すると仮定するならば、INSERTコマンドは96個ではなく1個に集約できます。
※済みませんん。mysqlは TEMPORARYテーブルを作成できます、HiVEでは等価な仲介補助テーブルのような
ものが利用できるかどうかは勉強不足でお答えできません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/10 14:01

    DIV演算子 X DIV n :== X - (X % n) を使えばより簡明ですが、おそらくSQL標準ではないので、、、 time DIV 10000, (time % 1000) DIV 1500

    キャンセル

  • 2016/09/10 14:23

    余談2.英大文字と英小文字の使い分け。
    SQLはプログラム言語の中によく記載し、予約語がよく重複します。
    私にの習慣ではSQLの予約語(CASE,IN,IF,ELSE,...)は英大文字で、プログラム言語の予約語は英小文字(case,in,if,else,...)で記載するようにしています。
    目視やエディタで検索するとき区別しやすいためです。

    キャンセル

  • 2016/09/19 23:18

    上手くいきました。ありがとうございました

    キャンセル

0

人に読んでもらいたいなら、もうちょっとなんとかなりませんかと、さすがに思いますが、
以下の5カ所しか差がないSQL、日付以外は 15ずつ増えるだけという事でしょうか。

他の方法で思いつくのはストアドプロシージャでループですね。

Insert Into Table test_tmp.Am_15min Partition (sp_yyyymmdd,sp_hhmm) 
Select am01.test1, am02.test2, am02.test3, am02.test4,
       am02.test5, am02.test6, am02.test7, am01.test8,
       am02.test9, am02.test10,am02.test_time, am01.run_time,
       am01.test13,am02.test14, '0000'  -- (1)
From 
    (Select test1,test8,avg(run_time) as run_time_avg,count(test8) as test13,
            min(test_time) as test_time_min 
     From tmp.test77_tbl 
     Where sp_date='20160801'     -- (2)
       and Substr(test_time,1,4) between 0000 and 0014 Group by test1, test8) am01  --(3)
  Left outer Join 
     (Select * from tmp.test77_tbl 
      where sp_date='20160801'    -- (4)
        and Substr(test_time,1,4) between 0000 and 0014) am02  --(5)
  On (am01.test1 = am02.test1 And am01.test8 = am02.test8
      And am01.test_time_min = am02.test_time);

(2個目)

Insert Into Table test_tmp.Am_15min Partition (sp_yyyymmdd,sp_hhmm) 
Select am01.test1, am02.test2, am02.test3, am02.test4,
       am02.test5, am02.test6, am02.test7, am01.test8,
       am02.test9, am02.test10,am02.test_time, am01.run_time,
       am01.test13,am02.test14, '0015'
From 
    (Select test1,test8,avg(run_time) as run_time_avg,count(test8) as test13,
            min(test_time) as test_time_min 
     From tmp.test77_tbl 
     Where sp_date='20160801' 
       and Substr(test_time,1,4) between 0015 and 0029 Group by test1, test8) am01 
  Left outer Join 
     (Select * from tmp.test77_tbl 
      where sp_date='20160801' 
        and Substr(test_time,1,4) between 0015 and 0029) am02 
  On (am01.test1 = am02.test1 And am01.test8 = am02.test8
      And am01.test_time_min = am02.test_time);

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/16 10:55

    すいません、読みにくて。日付以外は15ずつ増えるだけです。

    キャンセル

  • 2016/08/16 11:02

    ストアドプロシージャでループって具体的にはどのように書けばいいのですか?

    キャンセル

  • 2016/08/16 11:53

    それは調べてみてください。
    2、3列の練習用のテーブルを使ってやってみるといいでしょう。
    そもそも利用しているDBよって文法は少し変わってきます。

    select文は普通より変わる部分が出てきますが、
    insert文(insert 〜Select含む)を単体で実行するのはそれほど差はないはずなのでinsert文で練習してみると良いかと。

    変数とWhileやFor、必要に応じて数値から文字への変換(これは普通のSQLでも使いますね)を組み合わせれば実現できるかと思います。

    キャンセル

0

testやtmpといった文字が多くてなかなか頭に入ってこないSQLですね(^_^;

目的の動作をさせるために、あらかじめ以下のような時間幅のテーブルを用意しておいてはどうでしょうか。

[W_TIME_TBL]
HM_F, HM_T
-------------
0000, 0014
0015, 0029
0030, 0044
・・・
2345, 2359
-------------

上記テーブルを利用したSQLは以下のようになると思います。

Insert Into Table test_tmp.Am_15min Partition (sp_yyyymmdd,sp_hhmm)  
Select  
    am01.test1, am02.test2, am02.test3, am02.test4, 
    am02.test5, am02.test6, am02.test7, am01.test8, 
    am02.test9, am02.test10,am02.test_time, am01.run_time, 
    am01.test13,am02.test14, am01.HM_F  -- (1) 
From 
(select
    T1.TEST1
  , T1.TEST8
  , AVG(T1.RUN_TIME)  as RUN_TIME_AVG
  , COUNT(T1.TEST8)   as TEST13
  , MIN(T1.TEST_TIME) as TEST_TIME_MIN 
  , T2.HM_F
from
    TMP.TEST77_TBL T1
  , TMP.W_TIME_TBL T2
where
      T1.SP_DATE = '20160801'                            -- (2)
  and SUBSTR(T1.TEST_TIME, 1, 4) between T2.HM_F and T2.HM_T 
group by
    T1.TEST1
  , T1.TEST8
  , T2.HM_F
) am01

Left outer Join
(select T1.* 
   from TMP.TEST77_TBL T1
      , TMP.W_TIME_TBL T2
  where
      T1.SP_DATE = '20160801'                            -- (4)
  and SUBSTR(T1.TEST_TIME, 1, 4) between T2.HM_F and T2.HM_T 
) am02  --(5) 
    On (am01.test1 = am02.test1 And am01.test8 = am02.test8 
    And am01.test_time_min = am02.test_time)

SQL実行環境がないためそのままではエラー等あるかもしれませんが、参考になれば幸いです。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/23 21:24

    ありがとうございます。
    明日実際に試してみます。

    キャンセル

  • 2016/08/24 22:18

    試してみたら動いて流せましたが
    最初に
    Warning: Map Join MAPJOIN[63][bigTable=t1] in task 'Stage-9:MAPRED' is a cross product
    Warning: Map Join MAPJOIN[64][bigTable=t1] in task 'Stage-4:MAPRED' is a cross product
    という警告が出てしまいました。

    キャンセル

0

SQL自体に関することではありませんが…
insert先のテーブルにインデックスや制約など付与されているなら
いったん解除してから insertしてみるのはどうでしょう。
(insert 後にまた付与します)

あと、参照されるテーブルの方に適切なインデックスは
はってあるか? ってのもちょっと気になります。

ただ、Hiveに関して上記が有効かどうかは
ちょっと確信が無いので、まあ、そういうことで。(^_^;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/23 22:02

    回答ありがとうございます。
    実はHiveにはIndexがないため、適切なパーティションを設定し、検索範囲の限定をしないといけないんです。

    キャンセル

  • 2016/08/23 22:10

    あー、そういうものでしたか~
    勉強になります。

    キャンセル

0

INDEXを使えない場合、今回のSQLではどう頑張っても性能改善は難しいと思いますが。

一応、MySQL版のINDEX・SQLを紹介しますが、適宜Hiveに変換して試してくださいね。
※Hiveの場合、テーブル追加が必要かな。

ALTER TABLE test77_tbl
 ADD INDEX IX_test77_sp_date_1_8_time( sp_date, test1, test8, test_time, run_time )
;

Select    am02.test1, am02.test2, am02.test3, am02.test4, 
        am02.test5, am02.test6, am02.test7, am02.test8, 
        am02.test9, am02.test10,am02.test_time
    ,    (
            Select    avg(am01.run_time)
            From    test77_tbl am01
            Where    am01.sp_date    = am02.sp_date
                And    am01.test1        = am02.test1
                And    am01.test8        = am02.test8
                And    Substr( am01.test_time, 1, 4 ) between '0000' and '0014'    -- (3)
        ) as run_time
    ,    (
            Select    count( 'X' )
            From    test77_tbl am01
            Where    am01.sp_date    = am02.sp_date
                And    am01.test1        = am02.test1
                And    am01.test8        = am02.test8
                And    Substr( am01.test_time, 1, 4 ) between '0000' and '0014'    -- (3)
        ) as test13
    ,    am02.test14, '0000'  -- (1) 
From    test77_tbl am02
Where    am02.sp_date    = '20160801'    -- (2)(4)
    And    am02.test_time    = (
            Select    min( T1.test_time )
            From    test77_tbl T1
            Where    T1.sp_date    = am02.sp_date
                And    T1.test1    = am02.test1
                And    T1.test8    = am02.test8
                And    Substr( T1.test_time, 1, 4 ) between '0000' and '0014'    -- (5)
        )
;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/26 08:58

    KVSなのでは?

    キャンセル

  • 2016/08/26 09:26

    はい、Hive=KVSかと思います。
    一部、誤解を与える表現だったので、修正しました。

    キャンセル

  • 2016/08/28 22:04

    テーブル追加も必要なんでしょうかね。
    なんかエラーがでてしまいました。
    INDEXはバージョンによっては使えるようになってきるようですが、うまく動かない事のが多いのかもしれません。

    キャンセル

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

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

関連した質問

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