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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

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

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

Q&A

解決済

3回答

1265閲覧

MySQLのパフォーマンス改善(共通テーブル式が使えず、サブクエリが大量発生)

wwwww

総合スコア41

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

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

パフォーマンス

コード効率の向上や計算に関する質問には、このタグを使ってください。

0グッド

2クリップ

投稿2019/11/19 14:04

編集2019/11/20 15:08

■環境
MySQLバージョン:5.6.36

■質問(※2019/11/21 00:00 大きく修正しております。申し訳ありません。。)
以下のような「サンプルデータ」、「期待する結果」、「(期待結果は取得できるが、時間がかかる)SQL」において、パフォーマンスが遅く困っています。
改善方法をご存知の方がいらしたら、ご教示お願いできませんでしょうか。

●サンプルデータ Name,X,Y,create_time (A,0,0,'2019-11-20 01:00'), (A,0,1,'2019-11-20 01:10'), (B,0,1,'2019-11-20 01:20'), (A,0,1,'2019-11-20 01:30'), (A,1,1,'2019-11-20 01:40'), (B,1,1,'2019-11-20 01:50'), (A,0,1,'2019-11-20 02:00'), (A,0,1,'2019-11-20 03:50'), (B,0,1,'2019-11-20 05:50')
●期待する結果…当該Nameの、それまでのパターン別発生回数が知りたいです |Name|X|Y|count|create_time|0-0|0-1|1-0|1-1| |A|0|0|2019-11-20 01:00|0|0|0|0| |A|0|1|2019-11-20 01:10|1|0|0|0| |B|0|1|2019-11-20 01:20|0|0|0|0| |A|0|1|2019-11-20 01:30|1|1|0|0| |A|1|1|2019-11-20 01:40|1|2|0|0| |B|1|1|2019-11-20 01:50|0|1|0|0| |A|0|1|2019-11-20 02:00|1|2|0|1| |A|0|1|2019-11-20 03:50|1|3|0|1| |B|0|1|2019-11-20 05:50|0|1|0|1|

MySQL

1Select Name,X,Y,create_time,( 2 Select count(X=0 and Y=0) 3 From Table as B 4 Where A.Name=B.Name 5 And A.create_time > B.create_time 6 ), 7 ( 8 Select count(X=0 and Y=1) 9 From Table as B 10 Where A.Name=B.Name 11 And A.create_time > B.create_time 12 ) 13 ( 14 Select count(X=1 and Y=0) 15 From Table as B 16 Where A.Name=B.Name 17 And A.create_time > B.create_time 18 ), 19 ( 20 Select count(X=1 and Y=1) 21 From Table as B 22 Where A.Name=B.Name 23 And A.create_time > B.create_time 24 ) 25From Table as A 26 27(※実行・期待された結果は取得できるが、時間がかかってしまう)

X,Yの組み合わせごとに、当該データより(create_timeが)古いデータの計数を行いたいです。
組み合わせ数・行数が多いため、クエリが終わらない状況となってしまっています。

上記のMySQLバージョンだと、With句・ウィンドウ関数が使えないみたいで…
サブクエリ部分をCaseで分岐させてテーブル1周で実施できないか試したりしたのですが、うまく行きませんでした。

不足・不明点等あれば、ご指摘いただけますと幸いです。
以上、何卒宜しくお願いいたします。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2019/11/20 00:44 編集

テーブルの具体的な構成とサンプルを少々、その期待する結果を表記されるとよいかと それと「count(X=1 and Y=2)」が何をしたいかわかりません この場合件数を数えたいならsumです
wwwww

2019/11/20 05:04

記載に不足あり、失礼いたしました。ご回答、ありがとうございます。 (別途ご記載のサンプルについても、ありがとうございます。) サンプル・期待結果について、質問文にて追記いたしました。 >「count(X=1 and Y=2)」が何をしたいかわかりません >件数を数えたいならsumです X=1、Y=2に該当する場合の件数を集計したいです。 countでも取れていたのですが、sumで良い旨、知りませんでした。ご教示ありがとうございます。
guest

回答3

0

ベストアンサー

先ずは、(create_time,X,Y)のインデックスを追加してみて下さい。

投稿2019/11/19 15:04

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

wwwww

2019/11/20 15:24

ありがとうございます。インデックス追加してみたら、約50倍早くなりました。笑 初歩的な質問にてお手間頂いてしまい、失礼いたしました。
guest

0

一応サンプル
(w,zは重要ではないので省略しidをつけておきました)

SQL

1create table tbl(id int primary key auto_increment,x int,y int,create_time datetime, 2index(x,y,create_time)); 3insert into tbl(x,y,create_time) values 4(1,2,'2019-11-20 01:00'), 5(1,2,'2019-11-20 01:10'), 6(1,2,'2019-11-20 01:20'), 7(1,2,'2019-11-20 01:20'), 8(1,2,'2019-11-20 01:20'), 9(1,2,'2019-11-20 01:30'), 10(1,3,'2019-11-20 01:00'), 11(1,3,'2019-11-20 01:10'), 12(1,3,'2019-11-20 01:10'), 13(1,3,'2019-11-20 01:10'), 14(1,3,'2019-11-20 01:20'), 15(1,3,'2019-11-20 01:30'); 16 17select a.*, 18(select sum(x=1 and y=2) from tbl where a.create_time>create_time) as cnt 19from tbl as a;

確認

「●期待する結果」がなぜそういう結果になるかわかりません

自分のcreate_timeより前のx=1かつy=2の件数だったらこうなりませんか?

xycreate_timecount
002019-11-20 01:00:00NULL
012019-11-20 01:10:000
022019-11-20 01:20:000
012019-11-20 01:30:000
122019-11-20 01:40:000
112019-11-20 01:50:001
202019-11-20 02:00:001
012019-11-20 03:50:001
012019-11-20 05:50:001

修正版

SQL

1create table tbl(id int primary key auto_increment,name varchar(10),x int,y int,create_time datetime, 2index(name,x,y,create_time)); 3insert into tbl(name,x,y,create_time) values 4('A',0,0,'2019-11-20 01:00'), 5('A',0,1,'2019-11-20 01:10'), 6('B',0,1,'2019-11-20 01:20'), 7('A',0,1,'2019-11-20 01:30'), 8('A',1,1,'2019-11-20 01:40'), 9('B',1,1,'2019-11-20 01:50'), 10('A',0,1,'2019-11-20 02:00'), 11('A',0,1,'2019-11-20 03:50'), 12('B',0,1,'2019-11-20 05:50'); 13 14select a.*, 15coalesce((select sum(x=0 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_0, 16coalesce((select sum(x=0 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_1, 17coalesce((select sum(x=1 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_0, 18coalesce((select sum(x=1 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_1 19from tbl as a 20order by id;
  • 結果
idnamexycreate_timecnt_0_0cnt_0_1cnt_1_0cnt_1_1
1A002019-11-20 01:00:000000
2A012019-11-20 01:10:001000
3B012019-11-20 01:20:000000
4A012019-11-20 01:30:001100
5A112019-11-20 01:40:001200
6B112019-11-20 01:50:000100
7A012019-11-20 02:00:001201
8A012019-11-20 03:50:001301
9B012019-11-20 05:50:000101

投稿2019/11/20 00:57

編集2019/11/21 01:00
yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2019/11/20 05:11

追記分について確認をかいておきました? ロジックを説明したほうが良いと思います
wwwww

2019/11/20 15:10 編集

ご指摘いただき、ありがとうございます。大変失礼いたしました。 私の初期の記載に大幅に誤りがあったため、修正させていただきました。申し訳ありません。。
yambejp

2019/11/21 00:58

追記しました、内容的にあってますか?
wwwww

2019/11/21 13:13

数回にわたり、ありがとうございます。 ご記載の内容で欲しい結果取れますこと、確認できました お手数おかけいたしました。
guest

0

う~ん自信なしですが。
テーブル1周で済むように工夫してみました。
もっといい方法があるような気がします。

SQL

1SELECT 2create_time,W,X,Y,Z, 3@i, 4(CASE WHEN X=1 and Y=2 THEN @i := @i + 1 ELSE @i END)AS dummy 5FROM 6(SELECT @i := 0) AS dummy, 7Table 8ORDER BY create_time;

@iが求めている値です。dummyも結果に出てしまいますが無視して下さい。

投稿2019/11/19 14:51

KonPota

総合スコア121

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

wwwww

2019/11/20 15:28

ご教示ありがとうございます。恥ずかしながら、SQLで変数を使えると知りませんでした。 改めて勉強しておきたいと思っています。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問