可変の期間内で一意なユーザーのアクセス数を集計したい
解決済
回答 4
投稿
- 評価
- クリップ 0
- VIEW 1,955
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ページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
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テーブルは非テンポラリテーブルとして作成するのが良いでしょう。
前日以前のアクセスデータが変化することはないので、
日次のバッチなどで前日分のデータを集計・格納してやれば、何度でも再利用できます。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+1
もう試されているかもしれませんが…
SELECT user_id,access_date FROM table
WHERE access_date BETWEEN "初日" AND "末日"
GROUP BY user_id,access_date;
の結果をテンポラリに作ってそれに対して select かけるのはどうでしょう。
access_date は年月日の形式にするのがいいのかな?
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
0
こんばんは、この時間は回答者が少ないと思うので勝手に答えますｗ
DBには、詳しくありませんので期待しないでください。
他の素晴らしい回答者が現れるまでのつなぎ程度です。
えと、まずSQLのDistinctは、コストが高いと思われます。
適当に検索してみたらDISTINCTの代りにEXISTSを使う なんて記事が見つかったので、調べてみてはいかがですか。
それと
1回目：2016/1/1～2016/1/2
2回目：2016/1/1～2016/1/3
と期間が重なって集計しているようですが、差分のみ取得して計算することはできないのかなと思いました。
もしくはFrom〜Toの型が分かりませんが、日時型じゃなくて日付型だったとしたら日付でDistinctoするのもよいのかもと思いました。
あとは、システムが既に動いているとかであれば厳しいかも知れませんが、ちょっと考え方を変えて登録の際にユニークなユーザなのか調べていなければ登録しておくという風にコストを分散しておけば、
集計は一瞬で出来るのではないかとか勝手に思いました。
机上の空論かも知れません^_^；
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
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%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
質問への追記・修正、ベストアンサー選択の依頼
退会済みユーザー
2016/07/02 22:48
SHOW CREATE TABLE LIKE 'テーブル名' で得られるテーブル定義をご提示ください。
KAGURA
2016/07/02 23:00
開発環境が手元にないので、テーブル定義の情報そのままを提示できないのですが
user_idはvarchar(20)、access_dateはtimestampでした。
他のカラムについてはユーザーが使用している端末名等が保持されております。