【初心者】sql, batファイルでは実現しづらい問題を解決したいです。

前提・実現したいこと

プログラミング初心者です。
sql, batファイルでは実現しづらい問題を解決したいです。

例えばこのようなテーブルがあったとして、
0. 日にちをごとに分ける（例：20190501）
0. 果物ごとに分ける
0. イベントIDの「3」が入ったレコードを下に持つ、ユーザIDが入ったカラムをカウントし、果物ごとに買った人数をカウントする（このとき、同じ人は1人としてカウントする。つまり、重複は避ける）

ということを実現したいです。

ログテーブル

|日付時間|果物|買った人のID|イベントID
|:--|:--:|--:|
|2018/05/01 00:00:00|りんご|1|1
|2018/05/01 00:00:00|りんご||2
|2018/05/01 00:00:00|りんご||3
|2018/05/01 12:30:30|なし|2|4
|2018/05/01 12:30:30|なし||5
|2018/05/01 12:30:30|なし||6
|2018/05/01 21:55:55|りんご|1|1
|2018/05/01 21:55:55|りんご||2
|2018/05/01 21:55:55|りんご||3
|2019/01/01 00:00:00|なし|4|4
|2019/01/01 00:00:00|なし||5
|2019/01/01 00:00:00|なし||6
|2019/01/01 12:30:30|りんご|2|1
|2019/01/01 12:30:30|りんご||2
|2019/01/01 12:30:30|りんご||3

イベントIDテーブル

イベントID	状態
1	買った
2	商品をもらった
3	ありがとうございますと言われた
4	買えなかった
5	商品をもらえなかった
6	店から追い出された

発生している問題・エラーメッセージ

先輩からsqlとbatファイルだけでこの挙動を実現するのは初心者には難しい、と言われました。

補足情報（FW/ツールのバージョンなど）

MySQLを使用しております。

sazi

2019/10/15 08:26 編集

batの役割は何ですか？ファイルの出力？ SQLとBAT以外で実現するための方法をその先輩はご存知のように感じましたが、なぜ拘るのですか？

行動規範の内容に同意します

回答3件

「下に持つ」という表現をMySQLで実現するのは、
できなくはないですが、それをするくらいなら、
テーブル設計変えたいところですね。

とはいえ、例になっている、１と３は必ず同じ時間になっているので、
テーブルロックした状態で３行を時間固定で順番に登録する仕組みになっていて、
かつauto_increment等でソートできる項目があると予想しておきます。

先輩からsqlとbatファイルだけでこの挙動を実現するのは初心者には難しい、と言われました。

⇒やりたいことは、ただのカウントですよね？
というか、先輩に依頼されてるのであれば、
その先輩に質問すれば良いと思いますが、
なぜ、ここで質問・・・？

　この程度のカウントなら、sqlとbatでもできますが、
yutarofjさんの環境がsqlとbat以外の情報がないため、
アドバイスするにも、yutarofjさんはsqlとbat以外で何ができるんですか？状態です。
なので、質問は先輩にするのがベストかと思いますよ。

　強いて言うのであれば、「下に持つ」という表現にこだわりがあるのであれば、
EXCEL（またはgoogleスプレッドシート）にテーブルの中身を貼り付けて集約機能と数式を書いて、カウントするとかですかね？

ちなみに、SQLでやるなら、こんな感じ。

SQL
1insert into ログテーブル
2VALUES
3 ("2018/05/01 00:00:00","りんご","1","1")
4,("2018/05/01 00:00:00","りんご",null,"2")
5,("2018/05/01 00:00:00","りんご",null,"3")
6,("2018/05/01 12:30:30","なし","2","4")
7,("2018/05/01 12:30:30","なし",null,"5")
8,("2018/05/01 12:30:30","なし",null,"6")
9,("2018/05/01 21:55:55","りんご","1","1")
10,("2018/05/01 21:55:55","りんご",null,"2")
11,("2018/05/01 21:55:55","りんご",null,"3")
12,("2019/01/01 00:00:00","なし","4","4")
13,("2019/01/01 00:00:00","なし",null,"5")
14,("2019/01/01 00:00:00","なし",null,"6")
15,("2019/01/01 12:30:30","りんご","2","1")
16,("2019/01/01 12:30:30","りんご",null,"2")
17,("2019/01/01 12:30:30","りんご",null,"3")
18;
19
20SELECT	V1.日付
21	,	V1.果物
22	,	COUNT('X') as cnt
23FROM	(
24			SELECT	DISTINCT
25					DATE_FORMAT( T1.日付時間, '%Y%m%d' ) as 日付
26				,	T1.果物
27				,	T1.買った人のID
28			FROM	ログテーブル T1
29			WHERE	ifnull( T1.買った人のID, '' ) <> ''
30				AND	T1.イベントID	<> 3
31				-- ★★★↓「下に持つ」という表現ではありません。
32				AND	EXISTS(
33						SELECT	'X'
34						FROM	ログテーブル T2
35						WHERE	T2.日付時間		= T1.日付時間
36							AND	T2.果物			= T1.果物
37							AND	T2.イベントID	= 3
38					)
39				-- ★★★↑「下に持つ」という表現ではありません。
40		) V1
41GROUP BY V1.日付
42	,	V1.果物
43;

投稿2019/10/17 06:52

tomari_perform

総合スコア760

全く試していませんが、以下ではどうでしょうか？

SQL
1SELECT 果物
2      ,COUNT(USER) 
3  FROM (
4SELECT 日付時間
5      ,果物
6      ,MAX(買った人のID) AS USER
7  FROM ログテーブル
8 GROUP BY 日付時間
9         ,果物
10 WHERE イベントID IN(1 , 3))
11 GROUP BY SUBSTR(日付時間, 1, 8) 
12         ,果物
13         ,USER