[MySQL] 集計に役立つクエリサンプル

仕事で便利なSQL文をメモします。
社内のネットワークでつながったPCにXAMPPをインストールして、mysql集計用マシンとして使うとビックデータの集計も自分のマシンに負荷をかけずに便利かもしれない。

テーブル(table1)が存在する場合のみテーブルを削除するMYSQL文

DROP TABLE IF EXISTS table1;

空のテーブル作成(table1)

create table table1(
	Visitor_ID varchar(100),
	VisitNumber int(9),
	ClicktoPage int(9),
	Pages varchar(255),
	SessionID varchar(200),
	flag1 varchar(250),
	Date_f Date
);

型の宣言について

100バイトの文字列を宣言

Visitor_ID varchar(100)

9バイトの整数を宣言

VisitNumber int(9)

日付型を宣言

Date_f Date

テーブル(table1)に別テーブル(table2)から選択した要素を流し込む

INSERT INTO table1 SELECT 
	Visitor_ID,
	VisitNumber,
	ClicktoPage,
	FROM table2;

カラムとカラムを特定の文字列で連結して1つのカラムとして扱う(SELECT文で使用)

(例)カラム(Visitor_ID)、カラム(VisitNumber)を「|」でつなぐ場合の例。

Select concat_ws('|',`Visitor_ID`,`VisitNumber`) as 'SessionID'
FROM table1

SELECT文で使用するCASE句

カラム(Pages)が特定の文字列([四万温泉])の場合に文字列「(温泉)」を入れる

(例)

SELECT Pages,
	(CASE
		WHEN Pages = '[四万温泉]' THEN "(温泉)"
		WHEN Pages = '[江ノ島ホテル]' THEN "(リゾート)"
		WHEN Pages = '[佐渡島一泊の旅]' THEN "(旅行)"
	END) as mm_flag
	FROM table1;

日付のカラムがある場合に期間を指定する方法

(例)5月1日~6月30日の期間を選択する場合

SELECT * FROM table1
Where Date_f Between '2013-05-01' AND '2013-06-30';

応用編 テーブルに別テーブルから選択した要素を流し込む

INSERT INTO table1 SELECT 
	Visitor_ID,
	VisitNumber,
	ClicktoPage,
	Pages,
	concat_ws('|',`Visitor_ID`,`VisitNumber`) as 'SessionID',
	(CASE
		WHEN Pages = '[1]' THEN "(1)"
		WHEN Pages = '[2]' THEN "(2)"
	END) as mm_flag,
	Date_f
	FROM table2_2_all
	Where Date_f Between '2013-05-01' AND '2013-06-20';

あなたにオススメ(Amazon)

あなたにオススメ(楽天)