【SQL】集計結果のカウント方法についてのご相談です。

Question

実現したいこと

会員情報テーブルの日次会員数を集計したいと考えています。

＜やりたいこと＞
入会（create）された日は、createされた日に件数をプラスカウント（カテゴリー別）。
退会（delete）された日は、
入会日のカウントを残して、deleteされた日に件数をマイナスカウントしたいです。

以下のようなテーブルから
＜データ＞

user_id	category	create	create flag	delete	delete flag
...	...	...	...	...	...
1000	A	2022-01-01	1
1001	B	2022-01-02	1	2022-01-04	1
1002	C	2022-01-03	1
1002	C	2022-01-03	1
1003	D	2022-01-04	1
1003	D	2022-01-04	1
...	...	...	...

以下のような結果を得るやり方をご教示いただけませんでしょうか。

＜SQLの集計結果＞

yyyymmdd	category	件数
2021-01-01	A	1
2021-01-02	B	1
2021-01-03	C	2
2021-01-04	D	2
2021-01-04	B（初回登録されたカテゴリー）	-1

試したこと

SQLのことを、よく理解できてないため苦戦しております。
試したこととしては、日次に集計した結果をUNION ALLで引いてみたりしましたが、カテゴリーの結果がちゃんと出せませんでした。

日次でカテゴリー別の入会データ
UNION ALL
日次でカテゴリー別の退会データ（sum(delete flag)*-1）

ご教示いただけますと幸いです。
よろしくお願いいたします。

Answer 1

たとえば以下のようなSQLで実現できるかと思います (稼働確認にはMySQL 8を利用しています。DBMSごとにSQLの文法が異なりますので、必要に応じて修正してください)

SELECT yyyymmdd, category, SUM(件数)
FROM (
            SELECT `create`, category,  1 FROM P WHERE create_flag = 1
  UNION ALL SELECT `delete`, category, -1 FROM P WHERE delete_flag = 1
) T (yyyymmdd, category, 件数)
GROUP BY yyyymmdd, category

Comments

[iios]

教えていただいた方法でうまくできました。
大変に助かりました。
ありがとうございました。