（TRANSFORM .. PIVOT ..が便利というより、クロス集計ウィザードが便利だった）
MySQL単体でもアレやりたいなぁとか思ったり思わなかったり。
実際のところ、MySQLにTRANSFORM .. PIVOT .. に該当する構文はMySQLには無いので
かなり泥臭い感じのクエリにはなるけれども、
下手をしなければテーブルスキャン1回分 + テンポラリテーブル + ファイルソートのコストで処理できたりするので、
手でログテーブルの中身をざっと洗ってざっとした傾向を見るのくらいには意外と使える。
テストデータはこんな感じ。
mysql> SELECT * FROM t1 LIMIT 3;
+-----+-----------+---------------------+
| seq | user_id | dateval |
+-----+-----------+---------------------+
| 1 | user_0009 | 2012-12-29 12:26:43 |
| 2 | user_0008 | 2012-12-29 01:12:07 |
| 3 | user_0007 | 2012-12-29 03:16:24 |
+-----+-----------+---------------------+
3 rows in set (0.00 sec)
アクセスログからカラムを絞って取り込んだ様なイメージ。
これを、user_idを縦軸に、datevalのxx時台を横軸にして引っこ抜く。
mysql> SELECT
-> user_id,
-> SUM(HOUR(dateval) = 0) AS '0',
-> SUM(HOUR(dateval) = 1) AS '1',
-> SUM(HOUR(dateval) = 2) AS '2',
-> SUM(HOUR(dateval) = 3) AS '3',
-> SUM(HOUR(dateval) = 4) AS '4',
-> SUM(HOUR(dateval) = 5) AS '5',
-> SUM(HOUR(dateval) = 6) AS '6',
-> SUM(HOUR(dateval) = 7) AS '7',
-> SUM(HOUR(dateval) = 8) AS '8',
-> SUM(HOUR(dateval) = 9) AS '9',
-> SUM(HOUR(dateval) = 10) AS '10',
-> SUM(HOUR(dateval) = 11) AS '11',
-> SUM(HOUR(dateval) = 12) AS '12',
-> SUM(HOUR(dateval) = 13) AS '13',
-> SUM(HOUR(dateval) = 14) AS '14',
-> SUM(HOUR(dateval) = 15) AS '15',
-> SUM(HOUR(dateval) = 16) AS '16',
-> SUM(HOUR(dateval) = 17) AS '17',
-> SUM(HOUR(dateval) = 18) AS '18',
-> SUM(HOUR(dateval) = 19) AS '19',
-> SUM(HOUR(dateval) = 20) AS '20',
-> SUM(HOUR(dateval) = 21) AS '21',
-> SUM(HOUR(dateval) = 22) AS '22',
-> SUM(HOUR(dateval) = 23) AS '23'
-> FROM t1
-> GROUP BY user_id WITH ROLLUP;
+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| user_id | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 |
+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| user_0001 | 79 | 88 | 86 | 88 | 70 | 71 | 84 | 91 | 70 | 77 | 79 | 91 | 85 | 90 | 89 | 93 | 84 | 67 | 90 | 82 | 88 | 88 | 83 | 80 |
| user_0002 | 88 | 71 | 85 | 74 | 75 | 85 | 82 | 93 | 85 | 71 | 91 | 106 | 80 | 103 | 70 | 95 | 89 | 90 | 86 | 72 | 103 | 80 | 72 | 79 |
| user_0003 | 84 | 79 | 81 | 85 | 90 | 87 | 84 | 111 | 80 | 88 | 80 | 86 | 84 | 85 | 70 | 81 | 85 | 92 | 75 | 75 | 85 | 89 | 74 | 82 |
| user_0004 | 82 | 73 | 83 | 89 | 75 | 79 | 74 | 93 | 76 | 90 | 89 | 86 | 70 | 88 | 93 | 87 | 68 | 73 | 91 | 90 | 94 | 86 | 82 | 93 |
| user_0005 | 75 | 83 | 82 | 96 | 96 | 80 | 85 | 78 | 101 | 79 | 82 | 102 | 80 | 99 | 67 | 92 | 79 | 78 | 82 | 76 | 88 | 95 | 88 | 86 |
| user_0006 | 79 | 82 | 81 | 81 | 93 | 95 | 76 | 87 | 86 | 73 | 91 | 74 | 87 | 85 | 87 | 94 | 96 | 91 | 71 | 73 | 92 | 89 | 85 | 81 |
| user_0007 | 87 | 73 | 79 | 87 | 97 | 75 | 98 | 85 | 82 | 94 | 82 | 76 | 80 | 93 | 81 | 81 | 100 | 74 | 88 | 79 | 58 | 94 | 73 | 80 |
| user_0008 | 86 | 81 | 82 | 90 | 83 | 73 | 67 | 92 | 81 | 90 | 75 | 85 | 79 | 81 | 80 | 90 | 63 | 84 | 70 | 82 | 75 | 76 | 89 | 75 |
| user_0009 | 109 | 75 | 66 | 82 | 83 | 98 | 96 | 80 | 52 | 93 | 72 | 78 | 85 | 86 | 66 | 70 | 82 | 102 | 101 | 89 | 77 | 79 | 67 | 85 |
| user_0010 | 97 | 87 | 71 | 78 | 75 | 78 | 87 | 98 | 81 | 82 | 85 | 77 | 79 | 66 | 69 | 73 | 91 | 71 | 98 | 93 | 88 | 77 | 94 | 95 |
| NULL | 866 | 792 | 796 | 850 | 837 | 821 | 833 | 908 | 794 | 837 | 826 | 861 | 809 | 876 | 772 | 856 | 837 | 822 | 852 | 811 | 848 | 853 | 807 | 836 |
+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
11 rows in set (0.87 sec)
……泥臭い。。
変なサブクエリでなく、SELECT 真偽文 ..で0か1を返させてSUMするのがポイント（たぶん）
ホントは横軸を日付にして、7日以上連続アクセスしているユーザーの数を数える、とか考えていて、
`この結果セットを作るところまでがSQLで、連続アクセスになるかどうかの判定はPerlでラッピングして判定しよう'とか思っている。
計らずしも、年越しエントリになってしまった。。
今年もお世話になりました、来年もよろしくお願いします。
0 件のコメント :
コメントを投稿