ログテーブルから集計テーブルを作成したい

Question

前提・実現したいこと

• MySQL5.6

ログテーブルの各codeについて出現位置（1〜4）ごとの出現回数と利用ユーザー数を集計し、集計テーブルの様な形に落とし込みたい

ログテーブル

|id|user_id|code1|code2|code3|code4|
|--|--|
|1|1|100001|100002|100003|100004|
|2|1|100003|100002|100005|100004|
|3|2|100004|100002|100003|100001|
|4|4|100004|100001|100003|100002|
|5|6|100005|100002|100003|100004|
|6|2|100001|100002|100003|100004|

CREATE TABLE `log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `code1` int(11) DEFAULT NULL,
  `code2` int(11) DEFAULT NULL,
  `code3` int(11) DEFAULT NULL,
  `code4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

（定義は簡略化してます）

集計テーブル

|code|first_count|first_user_count|second_count|second_user_count|third_count|third_user_count|fourth_count|fourth_user_count|
|--|--|
|100001|2|2|1|1|0|0|1|1|
|100002|0|0|5|3|0|0|1|1|
|100003|1|1|0|0|5|4|0|0|
|100004|2|2|0|0|0|0|4|3|
|100005|1|1|0|0|1|1|0|0|

CREATE TABLE `summary` (
  `code` int(11) NOT NULL,
  `first_count` int(11) DEFAULT NULL,
  `first_user_count` int(11) DEFAULT NULL,
  `second_count` int(11) DEFAULT NULL,
  `second_user_count` int(11) DEFAULT NULL,
  `third_count` int(11) DEFAULT NULL,
  `third_user_count` int(11) DEFAULT NULL,
  `fourth_count` int(11) DEFAULT NULL,
  `fourth_user_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

（定義は簡略化してます）

試したこと・困っていること

１−４それぞれに関しては下記の様なクエリを4つ書けば取れそうなのですが（3,4は省略）
これを1テーブルに落とし込むのに難儀しております。
最終的にはselect insertのような形にしたいと思っており、難しいようであればそれぞれselectしてスクリプトで処理するかなという感じではあるのですが何かいいアイディアはありませんでしょうか。

• 1番目に出現したデータの集計

SELECT 
    code1,
    COUNT(*) as first_count,
    COUNT(DISTINCT user_id) as first_user_count
FROM
    log
GROUP BY code1;

|code|first_count|first_user_count|
|--|--|
|100001|2|2|
|100003|1|1|
|100004|2|2|
|100005|1|1|

• 2番目に出現したデータの集計

SELECT 
    code2,
    COUNT(*) as second_count,
    COUNT(DISTINCT user_id) as second_user_count
FROM
    log
GROUP BY code2;

|code|second_count|second_user_count|
|--|--|
|100001|1|1|
|100002|5|3|

Answer 1

ご参考程度ですが作成してみました。

insert into `summary`
select t3.code1,
t3.c1, t4.u1, t3.c2, t4.u2, t3.c3, t4.u3, t3.c4, t4.u4
from
    (select t1.code1
    ,count(case rw when 1 then 1 else null end) c1
    ,count(case rw when 2 then 1 else null end) c2
    ,count(case rw when 3 then 1 else null end) c3
    ,count(case rw when 4 then 1 else null end) c4
    from (
     select           code1, user_id, 1 rw from `log`
     union all select code2, user_id, 2 from `log`
     union all select code3, user_id, 3 from `log`
     union all select code4, user_id, 4 from `log`
    ) t1 group by 1
    ) t3 join
    (select t1.code1
    ,count(case rw when 1 then 1 else null end) u1
    ,count(case rw when 2 then 1 else null end) u2
    ,count(case rw when 3 then 1 else null end) u3
    ,count(case rw when 4 then 1 else null end) u4
    from (
     select           distinct code1, user_id, 1 rw from `log`
     union all select distinct code2, user_id, 2 from `log`
     union all select distinct code3, user_id, 3 from `log`
     union all select distinct code4, user_id, 4 from `log`
    )  t1 group by 1
    ) t4 on t3.code1=t4.code1
;

Answer 2

細かい事は言語の中のコメントへ。

-- データ登録
INSERT INTO log VALUES
 (1,	1,	100001,	100002,	100003,	100004)
,(2,	1,	100003,	100002,	100005,	100004)
,(3,	2,	100004,	100002,	100003,	100001)
,(4,	4,	100004,	100001,	100003,	100002)
,(5,	6,	100005,	100002,	100003,	100004)
,(6,	2,	100001,	100002,	100003,	100004);

-- 性能問題のためのおまじない。データ量が少量であれば、実行不要。
-- ALTER TABLE log
--  ADD INDEX IX_log_1( code1, user_id )
-- ,ADD INDEX IX_log_2( code2, user_id )
-- ,ADD INDEX IX_log_3( code3, user_id )
-- ,ADD INDEX IX_log_4( code4, user_id );
-- 

-- SELECT INSERT 前に前のデータを削除。
-- TRUNCATEに抵抗があり、logテーブルのデータが更新型でなければ、TRUNCATE & INSERT INTO ではなく、REPLACE INTO SELECTでも可。
TRUNCATE TABLE summary;

-- SELECT INSERT 文。
INSERT INTO summary
SELECT	T1.code
	,	(
			SELECT	COUNT('X')
			FROM	log	T2
			WHERE	T2.code1	= T1.code
		) as code1_cnt
	,	(
			SELECT	COUNT( distinct T3.user_id )
			FROM	log	T3
			WHERE	T3.code1	= T1.code
		) as code1_user_cnt
	,	(
			SELECT	COUNT('X')
			FROM	log	T4
			WHERE	T4.code2	= T1.code
		) as code2_cnt
	,	(
			SELECT	COUNT( distinct T5.user_id )
			FROM	log	T5
			WHERE	T5.code2	= T1.code
		) as code2_user_cnt
	,	(
			SELECT	COUNT('X')
			FROM	log	T6
			WHERE	T6.code3	= T1.code
		) as code3_cnt
	,	(
			SELECT	COUNT( distinct T7.user_id )
			FROM	log	T7
			WHERE	T7.code3	= T1.code
		) as code3_user_cnt
	,	(
			SELECT	COUNT('X')
			FROM	log	T8
			WHERE	T8.code4	= T1.code
		) as code4_cnt
	,	(
			SELECT	COUNT( distinct T8.user_id )
			FROM	log	T8
			WHERE	T8.code4	= T1.code
		) as code4_user_cnt
FROM	(
						SELECT	distinct code1 as code	FROM	log
                UNION	SELECT	distinct code2 as code	FROM	log
                UNION	SELECT	distinct code3 as code	FROM	log
                UNION	SELECT	distinct code4 as code	FROM	log
		) T1
;

-- 抽出SQL
select * from summary;

Comments

[HRS_O]

回答ありがとうございます、やはりそれぞれごとでselectする形になるようですが実現出来ました。
100万件ほど抽出出来る状態（テーブル自体は1000万ほど投入）で90秒強でした。