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

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

ただいまの
回答率

90.62%

  • MySQL

    5690questions

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

  • SQL

    2315questions

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

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 555

HRS_O

score 9

 前提・実現したいこと

  • 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;


(定義は簡略化してます)

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

1−4それぞれに関しては下記の様なクエリを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
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

0

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

-- データ登録
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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/15 19:56

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

    キャンセル

0

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

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
;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 90.62%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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

  • MySQL

    5690questions

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

  • SQL

    2315questions

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