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

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

ただいまの
回答率

88.11%

可変の期間内で一意なユーザーのアクセス数を集計したい

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 1,955

score 13

LAMP環境でのシステム開発を行っています。

とあるテーブルに「ユーザーID」と「アクセス時間」のデータを保持しており
このテーブルから、一定の期間内における一意なユーザーのアクセス数を集計したいです。

SELECT count(distinct user_id) FROM table WHERE access_date BETWEEN "FROM" AND "TO";

現状は、ループ処理にて上記クエリのTOの日付を1日づつカウントアップし
1回目:2016/1/1~2016/1/2
2回目:2016/1/1~2016/1/3
3回目:2016/1/1~2016/1/4
という風にして、各期間での集計を行っているのですが
該当テーブルのデータ件数が100万件ほどあり単純なSELECT文でも1秒弱かかっており
1年分集計するのに5分以上もかかってしまいます。

テンポラリテーブルやINDEXを使っても何故か処理時間に変化はありませんでした。
また、集計に必要なデータを、一旦PHP側に保持しPHP側で集計を行おうとも考えましたが
データ件数が多すぎるためメモリの消費量が多く断念いたしました。

例えば、必要な情報を1つのクエリでまとめて取得できたり
何か他の手段にて、もっと早く必要な情報を取得する方法は無いでしょうか?

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • 退会済みユーザー

    退会済みユーザー

    2016/07/02 22:48

    SHOW CREATE TABLE LIKE 'テーブル名' で得られるテーブル定義をご提示ください。

    キャンセル

  • KAGURA

    2016/07/02 23:00

    開発環境が手元にないので、テーブル定義の情報そのままを提示できないのですが
    user_idはvarchar(20)、access_dateはtimestampでした。
    他のカラムについてはユーザーが使用している端末名等が保持されております。

    キャンセル

回答 4

checkベストアンサー

+2

念のため確認させていただきますが、集計のために使用しているSQL文は、質問欄にご提示のままですか?

実は

WHERE DATE(access_date) BETWEEN "FROM" AND "TO";


だったり、他の条件が追加されていたりしませんか?

テンポラリテーブルやINDEXを使っても何故か処理時間に変化はありませんでした。 

とのことですが、MySQLでは、カラムに関数や何らかの計算を施していると、インデックスが使用されません。
(根拠となる、信用できる記述が見当たらず、提示できないのが恐縮ですが、、)

また、場合によっては、「他の条件」がインデックスの使用を妨げている可能性も考えられます。

もし、そうであれば、質問欄に以下を提示していただくと、より具体的な回答が可能になります。

  • 可能な限り「そのまま」のSQL文
  • Kosuke_Shibuya様のご指摘の通り、SHOW CREATE TABLE LIKE 'テーブル名'で得られるテーブル定義
  • EXPLAIN 可能な限り「そのまま」のSQL文というSQL文の実行結果

あるいは、すでにご提示のSQL文は十分にインデックスが効いている、ということはありませんか?
お使いの環境が不明なので確かなことは言えませんが、

データ件数が100万件ほどあり単純なSELECT文でも1秒弱

というのは、それほど悪くないパフォーマンスだと感じます。


で、ようやく以下からが回答となりますが、
やりたいことは「日」単位の期間による集計のようですので、
takasima20様の回答にあるように、あらかじめ「日別のアクセスユーザー」を記録する集計テーブルを作成してやるのが良いと思います。

具体的には、以下の通りです。

CREATE TABLE aggregated (
  access_date DATE NOT NULL,
  user_id varchar(20) NOT NULL,

  UNIQUE INDEX (access_date, user_id)
  UNIQUE INDEX (user_id, access_date)
);

INSERT IGNORE INTO aggregated 
SELECT DATE(access_date), user_id FROM table WHERE access_date BETWEEN 【集計したい期間の最小値】 AND 【同 最大値】;

あとは、集計テーブル(aggregatedテーブル)に対して以下のようなSQL文を実行してやれば、望むデータが取得できます。

SELECT COUNT(DISTINCT user_id) FROM aggregated WHERE access_date BETWEEN ...;


これなら、集計テーブルの作成には時間がかかりますが、その後のSELECT文は、おそらく十分なパフォーマンスが期待できます。
なぜなら、aggregatedテーブルに格納されるレコード数は元のテーブルから十分に絞り込まれているのと、まず確実にインデックスが効くからです。

ちなみに、aggregatedテーブルに順番が異なるだけの2つの複合インデックスを張っている理由ですが、
どちらのインデックスが有効かはデータの分布具合によって異なるため、念のため両方のインデックスを作成しています。

ところで、この集計作業が定例のものであれば、aggregatedテーブルは非テンポラリテーブルとして作成するのが良いでしょう。
前日以前のアクセスデータが変化することはないので、
日次のバッチなどで前日分のデータを集計・格納してやれば、何度でも再利用できます。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/07/03 11:18

    ご回答ありがとうございます!

    集計に使用しているSQLについてですが、記載した通りのクエリを使用しております。

    また、他の詳細なDB回りの情報についても、お伝えしたいのですが
    手元に開発環境がなく、かつ開発環境を見れる場所ではこういった場にアクセス出来ないので
    強引に突破しようとすると何かしらコンプライアンスに抵触する恐れがあり
    問題にならなさそうな範囲で情報公開し、有益な情報を得ようとしております。。。

    クエリのチューニングについては詳しくないので、自分で調べながら
    実行計画を取得してみましたが、indexは全く使われておらず
    100万件のレコードに対するフル検索が行われているようでした。
    試しにuser_idやaccess_dateにINDEXを張ってみたものの、そのINDEXが使われている様子もなく頭を抱えていた状態でございます。

    該当の処理は、集計画面を開こうとした時に動くものなのですが
    集計処理に時間がかかりすぎて、画面を開くことができないため対策を行っております。
    定例処理ではありませんが、他の処理にも使えそうですのでこの集計テーブルは作っておいた方が良さそうだと感じました。
    現状、こういった集計用テーブルは存在していなかった気がします。。。

    キャンセル

+1

もう試されているかもしれませんが…

SELECT user_id,access_date FROM table
WHERE access_date BETWEEN "初日" AND "末日"
GROUP BY user_id,access_date;


の結果をテンポラリに作ってそれに対して select かけるのはどうでしょう。
access_date は年月日の形式にするのがいいのかな?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/07/03 00:03

    ご回答ありがとうございます!

    確かにdate(access_date)のような形にして、このクエリからテンポラリを作れば
    集計対象のテーブル件数自体はだいぶ減らせそうな気がします!

    あとは、件数がどれだけ減るか・・・と
    それによってどの程度パフォーマンスが改善するかですね。

    まだ、このような形でテンポラリを作ることは試してなかったので
    挑戦してみます!

    キャンセル

0

こんばんは、この時間は回答者が少ないと思うので勝手に答えますw

DBには、詳しくありませんので期待しないでください。
他の素晴らしい回答者が現れるまでのつなぎ程度です。

えと、まずSQLのDistinctは、コストが高いと思われます。

適当に検索してみたらDISTINCTの代りにEXISTSを使う なんて記事が見つかったので、調べてみてはいかがですか。

それと

1回目:2016/1/1~2016/1/2 
2回目:2016/1/1~2016/1/3 

と期間が重なって集計しているようですが、差分のみ取得して計算することはできないのかなと思いました。
もしくはFrom〜Toの型が分かりませんが、日時型じゃなくて日付型だったとしたら日付でDistinctoするのもよいのかもと思いました。

あとは、システムが既に動いているとかであれば厳しいかも知れませんが、ちょっと考え方を変えて登録の際にユニークなユーザなのか調べていなければ登録しておくという風にコストを分散しておけば、
集計は一瞬で出来るのではないかとか勝手に思いました。
机上の空論かも知れません^_^;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/07/02 23:06

    ご回答ありがとうございます!

    確かにデータを保持する時点でゴニョゴニョできれば楽ですね。
    現実はなかなか。。。

    日毎の集計であれば、1度のクエリで「その日の一意なアクセス数」を取得できたのですが
    その日ごとのアクセス数を合算してしまうと、同じユーザーが日をまたいでアクセスした情報が重複して合算されるため数がズレてしまうのです。。。
    (一度、これを試して数字が合わずハマりました。。。)

    例えばAさん、Bさんが1/1と1/2にそれぞれ数回アクセスしたとして
    ■日毎の集計
    1/1-2件
    1/2-2件

    ■欲しい情報
    1/1~1/2 - 2件(この2日間でAさんとBさんの2人しかアクセスしていないため)

    説明が下手で申し訳ありません。。。

    キャンセル

  • 2016/07/02 23:16

    なるほど、いろいろと条件があるようですね。

    ちょっとカウントの仕方が理解できませんが、改めて質問を編集してもう少し情報を出せば、的確な回答が得られると思います ^^

    キャンセル

0

FROMからTOまでのdistinctなuser_idの累計ですので

下記のSelect文の結果を元にして、
PHPで集計してはどうでしょう?

select user_id,min(access_date)
from table
where access_date between "FROM" and "TO"
group by user_id
order by min(access_date)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

関連した質問

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