MySQL(5.5)にて、「商品」「売上」「仕入れ」の3テーブルについて、商品ごとの売上合計、仕入れ合計等を算出したいと考えています。
実現自体は出来ているのですが、処理に時間がかかっているため、もっとよい(高速で正しい値が取れる)方法がないかお知恵を貸していただきたく質問しました。
テーブル構造は以下の通りです。
product(商品)テーブル
id | name | price |
---|---|---|
1 | 商品1 | 100 |
2 | 商品2 | 200 |
3 | 商品3 | 300 |
sell(売上)テーブル (※is_back=1の場合、返品扱い)
|id|product_id|qty|is_back|datetime|
|:--|:--:|:--:|--:|
|1|1|2|0|2018-10-10 00:00:00|
|2|2|1|0|2018-10-10 00:00:00|
|3|3|3|0|2018-10-10 00:00:00|
|4|1|1|1|2018-10-10 00:00:00|
|5|2|1|1|2018-10-10 00:00:00|
|6|3|1|1|2018-10-10 00:00:00|
buy(仕入れ)テーブル(※is_back=1の場合、返品扱い)
|id|product_id|qty|is_back|datetime|
|:--|:--:|:--:|--:|
|1|1|5|0|2018-10-10 00:00:00|
|2|2|3|0|2018-10-10 00:00:00|
|3|3|10|0|2018-10-10 00:00:00|
|4|1|2|1|2018-10-10 00:00:00|
|5|2|1|1|2018-10-10 00:00:00|
|6|3|8|1|2018-10-10 00:00:00|
|7|1|1|0|2018-10-10 00:00:00|
最初に書いた(間違った)SQL
まず最初に、各TableをproductについてLeft Joinし、合算しました。
SQL
1select 2 product.id, 3 product.price, 4 ifnull(sum(sell_out.qty), 0) as summed_sell, 5 ifnull(sum(sell_in.qty), 0) as summed_back, 6 ifnull(sum(buy_out.qty), 0) as summed_buy, 7 ifnull(sum(buy_in.qty), 0) as summed_return, 8 9 /** 実際にはこれらの値も取得しているが、説明の為割愛 10 ifnull(sum(sell_out.qty * product.price), 0) as summed_sell_price, 11 ifnull(sum(sell_in.qty * product.price), 0) as summed_back_price, 12 ifnull(sum(buy_out.qty * product.price), 0) as summed_buy_price, 13 ifnull(sum(buy_in.qty * product.price), 0) as summed_return_price 14 **/ 15from 16 product 17 left join 18 sell as sell_out 19 on 20 sell_out.product_id = product.id 21 and sell_out.is_back = 0 22 and sell_out.`datetime` > '2018-10-01 00:00:00' 23 24 left join 25 sell as sell_in 26 on 27 sell_in.product_id = product.id 28 and sell_in.is_back = 1 29 and sell_in.`datetime` > '2018-10-01 00:00:00' 30 31 left join 32 buy as buy_out 33 on 34 buy_out.product_id = product.id 35 and buy_out.is_back = 1 36 and buy_out.`datetime` > '2018-10-01 00:00:00' 37 38 left join 39 buy as buy_in 40 on 41 buy_in.product_id = product.id 42 and buy_in.is_back = 0 43 and buy_in.`datetime` > '2018-10-01 00:00:00' 44group by 45 product.id;
このSQLでは、全てLEFT JOINで結合されるため、商品1(product.id=1)は仕入れ(buy)が2件あるため、JOINされたrow数が2つずつになり、結果として売上、返品等の件数がすべて倍になっています。
product.id | product.price | summed_sell | summed_back | summed_buy | summed_return |
---|---|---|---|---|---|
1 | 100 | 4 | 2 | 4 | 6 |
2 | 200 | 1 | 1 | 1 | 3 |
3 | 300 | 3 | 1 | 8 | 10 |
そこで、JOINする前にGroup Byで集計し、その値を出すようにしまして、正しい値が取れるようになりました。
SQL
1select 2 product.id, 3 product.price, 4 sell_out.summed_sell as summed_sell, 5 sell_in.summed_back as summed_back, 6 buy_out.summed_buy as summed_buy, 7 buy_in.summed_return as summed_return, 8 9 /** 実際にはこれらの値も取得しているが、説明の為割愛 10 ifnull(sum(sell_out.summed_sell * product.price), 0) as summed_sell_price, 11 ifnull(sum(sell_in.summed_back * product.price), 0) as summed_back_price, 12 ifnull(sum(buy_out.summed_buy * product.price), 0) as summed_buy_price, 13 ifnull(sum(buy_in.summed_return * product.price), 0) as summed_return_price 14 **/ 15 16from 17 product 18 left join 19 (select 20 ifnull(sum(sell.qty), 0) as summed_sell, 21 sell.product_id 22 from 23 sell 24 where 25 sell.is_back = 0 26 and sell.`datetime` > '2018-10-01 00:00:00' 27 group by 28 sell.product_id 29 ) as sell_out 30 on 31 sell_out.product_id = product.id 32 33 left join 34 (select 35 ifnull(sum(sell.qty), 0) as summed_back, 36 sell.product_id 37 from 38 sell 39 where 40 sell.is_back = 1 41 and sell.`datetime` > '2018-10-01 00:00:00' 42 group by 43 sell.product_id 44 ) as sell_in 45 on 46 sell_in.product_id = product.id 47 48 left join 49 (select 50 ifnull(sum(buy.qty), 0) as summed_buy, 51 buy.product_id 52 from 53 buy 54 where 55 buy.is_back = 1 56 and buy.`datetime` > '2018-10-01 00:00:00' 57 group by 58 buy.product_id 59 ) as buy_out 60 on 61 buy_out.product_id = product.id 62 63 left join 64 (select 65 ifnull(sum(buy.qty), 0) as summed_return, 66 buy.product_id 67 from 68 buy 69 where 70 buy.is_back = 0 71 and buy.`datetime` > '2018-10-01 00:00:00' 72 group by 73 buy.product_id 74 ) as buy_in 75 on 76 buy_in.product_id = product.id 77group by 78 product.id;
↓結果
product.id | product.price | summed_sell | summed_back | summed_buy | summed_return |
---|---|---|---|---|---|
1 | 100 | 2 | 1 | 2 | 6 |
2 | 200 | 1 | 1 | 1 | 3 |
3 | 300 | 3 | 1 | 8 | 10 |
ただ、こちらの場合は処理にかなり時間がかかってしまい、とても残念な感じです。
なんとか高速に正しい値が処理出来るようにならないでしょうか。
ちなみに、上記に記載したテーブル構造・データは下記のSQLで構築出来ますので、是非ご検証いただけると助かります。
※実際には、件数が万件単位になっている場合に処理時間が体感出来るのですが。。
SQL
1-- 2-- Table structure for table `buy` 3-- 4 5DROP TABLE IF EXISTS `buy`; 6CREATE TABLE `buy` ( 7 `id` int(11) NOT NULL AUTO_INCREMENT, 8 `product_id` int(11) NOT NULL, 9 `qty` int(11) DEFAULT NULL, 10 `is_back` tinyint(4) DEFAULT '0', 11 `datetime` datetime DEFAULT NULL, 12 PRIMARY KEY (`id`) 13) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 14 15-- 16-- Dumping data for table `buy` 17-- 18 19LOCK TABLES `buy` WRITE; 20/*!40000 ALTER TABLE `buy` DISABLE KEYS */; 21INSERT INTO `buy` VALUES (1,1,5,0,'2018-10-10 00:00:00'),(2,2,3,0,'2018-10-10 00:00:00'),(3,3,10,0,'2018-10-10 00:00:00'),(4,1,2,1,'2018-10-10 00:00:00'),(5,2,1,1,'2018-10-10 00:00:00'),(6,3,8,1,'2018-10-10 00:00:00'),(7,1,1,0,'2018-10-10 00:00:00'); 22/*!40000 ALTER TABLE `buy` ENABLE KEYS */; 23UNLOCK TABLES; 24 25-- 26-- Table structure for table `product` 27-- 28 29DROP TABLE IF EXISTS `product`; 30CREATE TABLE `product` ( 31 `id` int(11) NOT NULL AUTO_INCREMENT, 32 `name` varchar(45) DEFAULT NULL, 33 `price` int(11) DEFAULT NULL, 34 PRIMARY KEY (`id`) 35) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 36 37-- 38-- Dumping data for table `product` 39-- 40 41LOCK TABLES `product` WRITE; 42/*!40000 ALTER TABLE `product` DISABLE KEYS */; 43INSERT INTO `product` VALUES (1,'商品1',100),(2,'商品2',200),(3,'商品3',300); 44/*!40000 ALTER TABLE `product` ENABLE KEYS */; 45UNLOCK TABLES; 46 47-- 48-- Table structure for table `sell` 49-- 50 51DROP TABLE IF EXISTS `sell`; 52CREATE TABLE `sell` ( 53 `id` int(11) NOT NULL AUTO_INCREMENT, 54 `product_id` int(11) NOT NULL, 55 `qty` int(11) DEFAULT NULL, 56 `is_back` tinyint(4) DEFAULT '0', 57 `datetime` datetime DEFAULT NULL, 58 PRIMARY KEY (`id`) 59) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 60 61-- 62-- Dumping data for table `sell` 63-- 64 65LOCK TABLES `sell` WRITE; 66/*!40000 ALTER TABLE `sell` DISABLE KEYS */; 67INSERT INTO `sell` VALUES (1,1,2,0,'2018-10-10 00:00:00'),(2,2,1,0,'2018-10-10 00:00:00'),(3,3,3,0,'2018-10-10 00:00:00'),(4,1,1,1,'2018-10-10 00:00:00'),(5,2,1,1,'2018-10-10 00:00:00'),(6,3,1,1,'2018-10-10 00:00:00'); 68/*!40000 ALTER TABLE `sell` ENABLE KEYS */; 69UNLOCK TABLES; 70
回答4件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。