SQLでデータを集計したい

Question

実現したいこと

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

発生している問題

はじめに、以下のように商品、日付　別に販売数（sell_num）を集計する事はできました。

つぎに、sell_numの右側に返品数（return_num）も追加しよう考えました。

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

質問

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

試したこと

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

create temporary table `tmp_dates`
  select
  	tmp.`product_id`,
		tmp.`date`
	from (
    select `product_id`, `date` from sell_logs
    union all
    select `product_id`, `date` from return_logs
  ) tmp
;

select
	p.id,
	p.`name`,
	td.`date`,
	sl.`num`,
	rl.`num`
from `tmp_dates` td
left outer join sell_logs sl on sl.`product_id` = td.`product_id` and sl.`date` = td.`date`
left outer join return_logs rl on rl.`product_id` = td.`product_id` and rl.`date` = td.`date`
inner join products p on p.id = td.product_id

group by
	p.`id`,
	td.`date`;

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

<s>

/* 一時テーブルを作成して集計する */
create temporary table `tmp_dates`
  select
		tmp.`date`
	from (
    select `date` from sell_logs
    union all
    select `date` from return_logs
  ) tmp
;

select
	p.id,
	p.`name`,
	td.`date`,
	sl.`num`,
	rl.`num`
from `tmp_dates` td
left outer join sell_logs sl on sl.`date` = td.`date`
left outer join return_logs rl on rl.`date` = td.`date`
inner join products p on p.id = sl.product_id

group by
	p.`id`,
	td.`date`;

</s>

ソースコード

https://dbfiddle.uk/tRKBk26A

補足情報

• MariaDB 10.9.2

Answer 1

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

Answer 2

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

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

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

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

SELECT 
  X.product_id, 
  P.name,
  X.date, 
  SUM(CASE WHEN X.type = 'S' THEN X.num END) AS sell_num, 
  SUM(CASE WHEN X.type = 'R' THEN X.num END) AS return_num
FROM ( 
            SELECT *, 'R' AS type FROM return_logs
  UNION ALL SELECT *, 'S' AS type FROM sell_logs
) AS X
JOIN products AS P ON X.product_id = P.id
GROUP BY X.product_id, X.date

Answer 3

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

WITH
  s (id, date, sell_num, return_num) AS (
    SELECT
      product_id
    , date
    , num
    , NULL
    FROM
      sell_logs
    UNION ALL
    SELECT
      product_id
    , date
    , NULL
    , num
    FROM
      return_logs
  )
SELECT
  s.id
, p.name
, s.date
, SUM(s.sell_num) AS sell_num
, SUM(s.return_num) AS return_num
FROM
  s
  LEFT JOIN products AS p ON s.id = p.id
GROUP BY
  s.id
, s.date

https://dbfiddle.uk/zLJlG9Ur

Comments

[hupyaginu]

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