MySQL8.0にて、期間を指定してその期間内の銘柄別の最高値・最安値を表示したい。

解決済

回答 1

投稿 編集

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

GARYU

score 6

データベース内にはA・Bの2つテーブルがあります。
Aは日付・証券コード・銘柄名・始値・高値・安値・終値が全銘柄分入ってます。
Bは市場が開いていた日(土・日・祝・正月等の休場日の日付は無し)の日付のみが1日=1レコードで入ってます。
各データは毎日csvファイルからその日の全銘柄のデータを各テーブルに読み込みます。

取得したいカラムは、各銘柄別に
証券コード・銘柄名・期間内の最高値・期間内の最高値を付けた日・期間内の最安値・期間内の最安値を付けた日・本日の前日比
となります。

『期間内』と付けた部分ですが、「テーブルB内にある最新日から〇日間(最新日を含む営業日のみ)遡った期間で」という意味で期間を括りたいと思ってます。
例:仮に19/6/9(日)に「過去7日間」の各データを取得するとしてSQLを実行したとしたら、6/9~6/3まで遡るというカレンダー上の「過去7日間」(6/9・6/8は休場日なのでテーブルBにはもともとデータがない)ではなく、6/7・6/6・6/5・6/4・6/3・5/31・5/30の休場日ではない(テーブルBにデータがある)「過去7日間」のうちの最高値・最安値を取得する。
DATE_ADD関数ではカレンダー上の「過去7日」は取得出来たのですが、ほしい値ではありません。
5日移動平均や25日移動平均といった時の期間の括り方です。
(もちろんここで質問しているので移動平均も出せていませんが・・・)

『本日』と付けた部分はSQL実行日ではなく、あくまでもテーブルB内のデータがある中の最新日という意味です。

SQLが思いつかず、ご指導いただきたいと思ってます。
お手間とは思いますが何も解かってない初心者ですので、ご指導頂けるSQL文の意味も注釈で教えて頂けると今後の為の勉強ともなります。
何卒よろしくお願いします。

《追記》
A・Bの二つのテーブルと書きましたが実際はデータインポート用のテーブルを作っておりデータベース内には計3つのテーブルがあります。
質問するにあたり簡素化のつもりで書いたのですが、情報が不正確であったことが何かしらに影響を与えたのでしたら大変申し訳ありません。

IMPはインポート用・Aは日々読み込んだデータの蓄積用・Bは営業日判別用のつもりで作りました。
以下のcreate table文で3つのテーブルをつくりました。
create table IMP (
DATE DATE,
STOCK_CD CHAR(4),
MARKET_CD CHAR(2),
STOCK_NAME VARCHAR(100),
OPEN float,
HIGH float,
LOW float,
CLOSE float,
VOLUME float,
MARKET_NAME VARCHAR(20)   
);

create table A (
DATE DATE,
STOCK_CD CHAR(4),
MARKET_CD CHAR(2),
STOCK_NAME VARCHAR(100),
OPEN float,
HIGH float,
LOW float,
CLOSE float,
VOLUME float,
MARKET_NAME VARCHAR(20)   
);
create table B (
DATE date primary key
);

データの取り込みは最初にcsvファイルを保存しMySQL Workbench内のテーブル表示を右クリック「Table Data Import Wizard」でテーブルIMPにデータを取り込み
insert into A
select
DATE,
STOCK_CD,
MARKET_CD,
STOCK_NAME,
OPEN,
HIGH,
LOW,
CLOSE,
VOLUME,
MARKET_NAME
from IMP;
で本日のデータを前日分までのデータを蓄積しているAに追加しています。
insert ignore into B
select distinct 
DATE
from IMP;
でテーブルIMPから日付のみを同様に前日までの営業日が蓄積されているBに追加しています。

インポートするcsvファイルは1日 ×4千数百銘柄のデータの形で各カラムと同じ内容の列が並んでいます。

何卒ご指導よろしくお願いします。

《追記2》
教えて頂いたコードを実行したところ

13:09:05    with    date_range as (     -- 指定した期間内(最新日付より過去7日)     select date from b hst     where date <= (select max(date) from b)       and (select count(*) from b where date>=hst.date) <= 7   ) , peak as (      --期間高値:指定した期間内の最高値、最安値     select STOCK_CD, STOCK_NAME, Max(HIGH) max_peak, Min(LOW) min_peak     from a where date in (select date from date_range)     group by STOCK_CD, STOCK_NAME   )  , rasio as (     --前日比 :テーブルB内にある最新日の終値とその前営業日の終値との比較     select date, STOCK_CD          , close - lag(CLOSE) over (partition by STOCK_CD order by date) rasio     from a     )  select pk.*     --・高値日 :期間高値を付けた日     , (select max(date) from a         where STOCK_CD=pk.STOCK_CD and HIGH=pk.max_peak           and date in (select date from date_range)        )     --・安値日 :期間安値を付けた日     , (select max(date) from a         where STOCK_CD=pk.STOCK_CD and LOW=pk.min_peak           and date in (select date from date_range)       )     , (select max(rasio) from rasio         where date = (select max(date) from b) and STOCK_CD=pk.STOCK_CD       )  from peak pk    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--期間高値:指定した期間内の最高値、最安値     select STOCK_' at line 9    0.000 sec

のエラーが返ってきました。
MySQL Workbenchdeしました。
上から
use データベース名;
withから始まる教えて頂いたSQLをペースト
となってますがwithの左横にエラーがあった時の赤い×が付いてます。

indexについては①使い方をよく理解していないこと ②記事を探しましたがどのカラムに対して作成するのが良いのか により手を着けられていません。「現状インデックスがないなら使い物にならないほど低速」とおっしゃっている部分が非常に気になっています。
インデックスを使えば解消するのか? またよく見かける記事の「PHP+SQL」や「python+SQL」といった他プログラミング言語でのSQL操作の方が良いのか(他言語は1からの勉強となりますが・・・)。
ゆくゆくはSQL内からデータを取り出し他言語で分析というのが目的ではありますが、まずはSQLからと思い質問させていただいています。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • Orlofsky

    2019/06/06 02:26

    質問のテーブルの定義情報は、できれば CREATE TABLE に修正できると適切なコメントが付きやすいです。

    キャンセル

  • Orlofsky

    2019/06/08 17:40

    CREATE TABLEなどのコードは https://teratail.com/help#about-markdown の [コードを入力] に修正してください。

    キャンセル

回答 1

checkベストアンサー

0

取り敢えず集計していないSQLです。

select ta.*
from ta inner join (
       select * from tb t1
       where 日付 <= date()
         and (select count(*) from tb where 日付>=t1.日付)<=7
     ) tc
     on ta.日付=tc.日付

追記

MySQL8.0はwith分析関数が使えるので利用すると簡潔になります。
MySQL8.0は手元にないのでpostgresを使用して検証しました。

キーワードとしてはwithlag位ですね。
最終的に相関副問合せを用いてますが、集計を別テーブルに分けて結合する方法もあると思います。
現状インデックスが無いなら使い物にならないほど低速です。
また、日付と証券コードで一意になっていないのも前日比などでは問題です。

with 
  date_range as (
    -- 指定した期間内(最新日付より過去7日)
    select date from b hst
    where date <= (select max(date) from b)
      and (select count(*) from b where date>=hst.date)<=7
  )
, peak as (
     --期間高値:指定した期間内の最高値、最安値
    select STOCK_CD, STOCK_NAME, Max(HIGH) max_peak, Min(LOW) min_peak
    from a where date in (select date from date_range)
    group by STOCK_CD, STOCK_NAME
  )
 , rasio as (
    --前日比 :テーブルB内にある最新日の終値とその前営業日の終値との比較
    select date, STOCK_CD
         , close - lag(CLOSE) over (partition by STOCK_CD order by date) rasio
    from a 
   ) 
select pk.*
    --・高値日 :期間高値を付けた日
    , (select max(date) from a 
       where STOCK_CD=pk.STOCK_CD and HIGH=pk.max_peak 
         and date in (select date from date_range)
       )
    --・安値日 :期間安値を付けた日
    , (select max(date) from a 
       where STOCK_CD=pk.STOCK_CD and LOW=pk.min_peak 
         and date in (select date from date_range)
      )
    , (select max(rasio) from rasio 
       where date = (select max(date) from b) and STOCK_CD=pk.STOCK_CD
      ) 
from peak pk

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/06/08 00:32

    すみません、初心者につき意味を理解出来ていません。
    ネット上のサイトからCSVをダウンロードし自分のPCに保存してからインポートているのですが・・・。
    再現していただけるとのことですので、そのサイトのアドレスという認識で良いでしょうか?
    こちらがcsvファイルダウンロード元になります。
    http://mujinzou.com/

    キャンセル

  • 2019/06/08 09:14

    この中の
    ■データ・ダウンロード■ 内の
    ■当日株価データに入り、該当日をクリックcsvを取得しています。

    キャンセル

  • 2019/06/10 20:20

    お付き合い・ご指導ありがとうございました。
    まだ望むところには辿りつけてませんが、一から違った形で考えてみます。
    ありがとうございました。

    キャンセル

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

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