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

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

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

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

SQL

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

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

Q&A

解決済

3回答

365閲覧

SQLでデータを集計したい

hupyaginu

総合スコア15

MySQL

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

SQL

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

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

0グッド

1クリップ

投稿2023/11/16 12:19

編集2023/11/16 12:45

実現したいこと

SQLでデータを集計したいです。

発生している問題

はじめに、以下のように商品、日付 別に販売数(sell_num)を集計する事はできました。
イメージ説明

つぎに、sell_numの右側に返品数(return_num)も追加しよう考えました。
イメージ説明

しかし、return_numは全てnullになってしまいます。

質問

以下のような結果を期待しています。
どのようなSQLを書けば実現できますか?
※追記:実現はできました。
しかし、一時テーブルを作成する部分のSQLがパフォーマンスがよくなさそうです。
最初にproduct_id, dateをあらかじめ用意せずにできそうな気もしていますがやりかたが分かりません。
もっとよいSQLはありますでしょうか?

イメージ説明

試したこと

追記:以下のように一時テーブルを作成してから集計すると期待する結果が得られました。

SQL

1create temporary table `tmp_dates` 2 select 3 tmp.`product_id`, 4 tmp.`date` 5 from ( 6 select `product_id`, `date` from sell_logs 7 union all 8 select `product_id`, `date` from return_logs 9 ) tmp 10; 11 12select 13 p.id, 14 p.`name`, 15 td.`date`, 16 sl.`num`, 17 rl.`num` 18from `tmp_dates` td 19left outer join sell_logs sl on sl.`product_id` = td.`product_id` and sl.`date` = td.`date` 20left outer join return_logs rl on rl.`product_id` = td.`product_id` and rl.`date` = td.`date` 21inner join products p on p.id = td.product_id 22 23group by 24 p.`id`, 25 td.`date`;

return_logsにだけ2023-01-04が存在するので、2023-01-04が取得できるように日付専用で一時テーブルを作成してからJOINして集計を試みました。
しかし期待する結果が得られませんでした。

<s>

SQL

1/* 一時テーブルを作成して集計する */ 2create temporary table `tmp_dates` 3 select 4 tmp.`date` 5 from ( 6 select `date` from sell_logs 7 union all 8 select `date` from return_logs 9 ) tmp 10; 11 12select 13 p.id, 14 p.`name`, 15 td.`date`, 16 sl.`num`, 17 rl.`num` 18from `tmp_dates` td 19left outer join sell_logs sl on sl.`date` = td.`date` 20left outer join return_logs rl on rl.`date` = td.`date` 21inner join products p on p.id = sl.product_id 22 23group by 24 p.`id`, 25 td.`date`;
</s>

ソースコード

https://dbfiddle.uk/tRKBk26A

補足情報

  • MariaDB 10.9.2

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

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

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

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

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

guest

回答3

2

返品はsell_numにマイナスの数値を入れるほうが楽でしょう

投稿2023/11/17 00:25

yambejp

総合スコア114339

neko_the_shadow, hupyaginu👍を押しています

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

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

1

しかし、一時テーブルを作成する部分のSQLがパフォーマンスがよくなさそうです。

SQLのパフォーマンスはデータ量や設定値などに強く影響されます。本番環境やそれに近い環境で、作成したSQLを試してみることをおすすめします。

最初にproduct_id, dateをあらかじめ用意せずにできそうな気もしていますがやりかたが分かりません。
もっとよいSQLはありますでしょうか?

SQLの改善案ですが、一時テーブルを利用しない、同じテーブルに何度もアクセスしないということを考えると、以下のようなSQLで実現可能です (もちろん実現方法はいろいろあります)

SQL

1SELECT 2 X.product_id, 3 P.name, 4 X.date, 5 SUM(CASE WHEN X.type = 'S' THEN X.num END) AS sell_num, 6 SUM(CASE WHEN X.type = 'R' THEN X.num END) AS return_num 7FROM ( 8 SELECT *, 'R' AS type FROM return_logs 9 UNION ALL SELECT *, 'S' AS type FROM sell_logs 10) AS X 11JOIN products AS P ON X.product_id = P.id 12GROUP BY X.product_id, X.date

投稿2023/11/17 00:44

neko_the_shadow

総合スコア2219

hupyaginu👍を押しています

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

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

0

ベストアンサー

temporary tableを作らなくてもサブクエリで良いのではないでしょうか?

sql

1WITH 2 s (id, date, sell_num, return_num) AS ( 3 SELECT 4 product_id 5 , date 6 , num 7 , NULL 8 FROM 9 sell_logs 10 UNION ALL 11 SELECT 12 product_id 13 , date 14 , NULL 15 , num 16 FROM 17 return_logs 18 ) 19SELECT 20 s.id 21, p.name 22, s.date 23, SUM(s.sell_num) AS sell_num 24, SUM(s.return_num) AS return_num 25FROM 26 s 27 LEFT JOIN products AS p ON s.id = p.id 28GROUP BY 29 s.id 30, s.date

https://dbfiddle.uk/zLJlG9Ur

投稿2023/11/17 00:20

takanaweb5

総合スコア354

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

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

hupyaginu

2023/11/17 05:41

ありがとうございます! WITHを初めて知りました!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.53%

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

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

質問する

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

MySQL

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

SQL

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

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。