アクセス頻度の分布。これは、ユーザーエンゲージメントの分布を確認するのに役立ちます。
SELECT
T.cnt,
COUNT(1) AS frequency
FROM (
SELECT
user_id,
COUNT(1) AS cnt
FROM
access
GROUP BY
user_id
) T
GROUP BY
T.cnt
ORDER BY
frequency| time | user_id |
|---|---|
| 1416435585 | 2 |
| 1416435586 | 3 |
| 1416435587 | 5 |
| 1416435588 | 1 |
| ... | ... |
| cnt | frequency |
|---|---|
| 1 | 621 |
| 2 | 121 |
| 3 | 71 |
| 4 | 61 |
| 5 | 41 |
| 6 | 31 |
| 7 | 11 |
| 8 | 3 |
| 9 | 1 |