■CASE式によるラベルの変換
・table
user_id|create_browser|
U00001 | 1|
U00002 | 2|
U00003 | 3|
・コード値をラベルに置き換えるクエリ
SELECT
user_id,
CASE
WHEN create_browser = 1 THEN 'IE'
WHEN create_browser = 2 THEN 'SAFARI'
WHEN create_browser = 3 THEN 'CHROME'
END AS ブラウザ名
FROM
user_browser;
・結果
user_id|ブラウザ名 |
U00001 |IE |
U00002 |SAFARI|
U00003 |CHROME|
■CASE式でカテゴリをまとめる
例）12ヶ月を4半期毎にまとめる
-- WITH句による、サブクエリの表記
WITH quart_table AS(
SELECT
CURRENT_TIMESTAMP,
EXTRACT( MONTH FROM CURRENT_TIMESTAMP) AS MONTH
)
SELECT
CURRENT_TIMESTAMP,
CASE
WHEN 1 <= MONTH AND MONTH <= 3 THEN '第１四半期'
WHEN 4 <= MONTH AND MONTH <= 6 THEN '第２四半期'
WHEN 7 <= MONTH AND MONTH <= 9 THEN '第３四半期'
ELSE '第４四半期'
END AS 四半期
FROM
quart_table;
■CASTによる値の型変換
・MYSQLのCAST関数で指定するデータ型
文字列 - CHAR[(N)]
日付 - DATE
日付と時刻 - DATETIME
符号あり整数 - SIGNED
符号なし整数 - UNSIGNED
実数 - DECIMAL
例）文字列の'2019'を整数に型変換
SELECT
CAST('2019' AS UNSIGNED);
■欠損値をデフォルト値に置き換える
COALESCE関数の利用：COALESCE(<リスト>)
引数はカラムか値のリストで，そのうちNULLでない最初の引数を返す。
例）
SELECT
COALESCE (NULL ,2, 1);
結果
COALESCE (NULL ,2, 1)|
---------------------|
2|
例）値引き後の売上金額を求める
・データ
purchase_id|price|coupon|
-----------|-----|------|
100001 | 2280| NULL |
100002 | 7650| 300|
100003 | 4870| NULL |
・SQL
SELECT
purchase_id,
price,
coupon,
price - coupon AS discount_price1,
price - COALESCE(coupon, 0) AS discount_price2
FROM
purchase_log_coupon;
・結果
purchase_id|price|coupon|discount_price1|discount_price2|
-----------|-----|------|---------------|---------------|
100001 | 2280| | | 2280|
100002 | 7650| 300| 7350| 7350|
100003 | 4870| | | 4870|
■１レコード内の数値データ同士を比較する
例）月ごとの売上が増加してるか減少してるかの判定
・データ
year_period|m1 |m2 |m3 |m4 |
-----------|-------|-------|--------|-------|
2015|4200000|4300000| 7400000|4300000|
2016|4500000|4500000| 4000000|4100000|
2017|5430000|4530000| 6130000|7630000|
2018|3260000|4570000|53550000|6340000|
2019|2570000|8720000| | |
・SQL
SELECT
year_period,
m1,
m2,
CASE
WHEN m1 < m2 THEN '前月比増'
WHEN m1 = m2 THEN '増減なし'
ELSE '前月比減'
END AS '前月比増減',
m2 - m1 AS 差分
FROM
monthly_sale_log
ORDER BY
year_period ;
・結果
year_period|m1 |m2 |前月比増減|差分 |
-----------|-------|-------|-----|-------|
2015|4200000|4300000|前月比増 | 100000|
2016|4500000|4500000|増減なし | 0|
2017|5430000|4530000|前月比減 |-900000|
2018|3260000|4570000|前月比増 |1310000|
2019|2570000|8720000|前月比増 |6150000|
例）LEAST(), GREATEST()関数による最小値・最大値の取得
・SQL
SELECT
year_period,
GREATEST(m1, m2, m3, m4) AS 最大売上,
LEAST(m1, m2, m3, m4) AS 最小売上
FROM
monthly_sale_log
ORDER BY
year_period;
・結果
year_period|最大売上 |最小売上 |
-----------|--------|-------|
2015| 7400000|4200000|
2016| 4500000|4000000|
2017| 7630000|4530000|
2018|53550000|3260000|
2019| | |
例）加算で平均売上を求める
・SQL
SELECT
year_period,
(m1 + m2 + m3 + m4) / 4 AS 平均
FROM
monthly_sale_log
ORDER BY
year_period;
・結果
year_period|平均 |
-----------|-------------|
2015| 5050000.0000|
2016| 4275000.0000|
2017| 5930000.0000|
2018|16930000.0000|
2019| |
■比率を求める
例）各web広告のCTRの計算
・データ
dt |add_id|imps |clicks|
----------|------|------|------|
2019-05-01|00001 | 10000| 300|
2019-05-01|00002 | 12000| 120|
2019-05-01|00003 |500000| 10000|
2019-05-02|00001 | 0| 0|
2019-05-02|00002 | 13000| 140|
2019-05-02|00003 |620000| 15000|
・SQL（0除算対応に注意）
SELECT
dt,
add_id,
CASE
WHEN imps > 0 THEN 100.0 * clicks /imps
END AS CTR3
FROM
ad_stats_imp_clic
ORDER BY
dt,
add_id;
・結果
dt |add_id|CTR3 |
----------|------|-------|
2019-05-01|00001 |3.00000|
2019-05-01|00002 |1.00000|
2019-05-01|00003 |2.00000|
2019-05-02|00001 | |
2019-05-02|00002 |1.07692|
2019-05-02|00003 |2.41935|
■■■日付・時間のデータ加工■■■
■指定の日時/時間を取得する
例）CAST()による型変換
・SQL
SELECT
CAST('2019-05-01' AS DATE) AS dt,
CAST('2019-05-01 7:00:00' AS DATETIME) AS stamp;
・結果
dt |stamp |
----------|---------------------|
2019-05-01|2019-05-01 07:00:00.0|
■年・月・日・時・分・秒などを取得する
EXTRACT()関数の利用
・SQL
SELECT
CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS current_year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS current_month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS current_day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS current_hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS current_minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS current_second;
・結果
CURRENT_TIMESTAMP |current_year|current_month|current_day|current_hour|current_minute|current_second|
---------------------|------------|-------------|-----------|------------|--------------|--------------|
2020-04-07 11:58:11.0| 2020| 4| 7| 11| 58| 11|
■生年月日から年齢を計算する
・方法：
・誕生日と年齢を算出したい日付を整数型で表現
・その差の値を10000で割る
例）年齢算出(2020/04/11現在の年齢)
・テーブル
user_id|create_stamp |birth_date|
-------|-------------------|----------|
U00001 |2016-02-29 08:00:00|1996-02-28|
U00002 |2016-02-29 08:00:00|1996-02-29|
U00003 |2016-02-29 08:00:00|1996-03-01|
U00004 |2016-02-29 08:00:00|1996-03-03|
U00005 |2016-02-29 08:00:00|1996-03-10|
・SQL
SELECT
birth_date,
20200411 AS benchmark_date,
CAST(REPLACE(birth_date, '-', '') AS UNSIGNED) AS int_birth_date,
FLOOR((20200411 - CAST(REPLACE(birth_date, '-', '') AS UNSIGNED)) / 10000) AS age
FROM
user_birth_date;
・結果
birth_date|benchmark_date|int_birth_date|age|
----------|--------------|--------------|---|
1996-02-28| 20200411| 19960228| 24|
1996-02-29| 20200411| 19960229| 24|
1996-03-01| 20200411| 19960301| 24|
1996-03-03| 20200411| 19960303| 24|
1996-03-10| 20200411| 19960310| 24|
■指定の時間の加減を行う
例）INTERVAL関数で一時間後、３０分後の時間を計算
・テーブル
user_id|create_stamp |birth_date|
-------|-------------------|----------|
U00001 |2016-02-29 08:00:00|1996-02-28|
U00002 |2016-02-29 08:00:00|1996-02-29|
U00003 |2016-02-29 08:00:00|1996-03-01|
U00004 |2016-02-29 08:00:00|1996-03-03|
U00005 |2016-02-29 08:00:00|1996-03-10|
・SQL
SELECT
user_id,
CAST(create_stamp AS datetime) AS stamp,
CAST(create_stamp AS datetime) + INTERVAL 1 HOUR AS after_1_hour,
CAST(create_stamp AS datetime) - INTERVAL 30 MINUTE AS before_30min
FROM
user_birth_date;
・結果
user_id|stamp |after_1_hour |before_30min |
-------|---------------------|---------------------|---------------------|
U00001 |2016-02-29 08:00:00.0|2016-02-29 09:00:00.0|2016-02-29 07:30:00.0|
U00002 |2016-02-29 08:00:00.0|2016-02-29 09:00:00.0|2016-02-29 07:30:00.0|
U00003 |2016-02-29 08:00:00.0|2016-02-29 09:00:00.0|2016-02-29 07:30:00.0|
U00004 |2016-02-29 08:00:00.0|2016-02-29 09:00:00.0|2016-02-29 07:30:00.0|
U00005 |2016-02-29 08:00:00.0|2016-02-29 09:00:00.0|2016-02-29 07:30:00.0|
■■■文字列のデータ加工■■■
■指定した数の文字列を取り出すSUBSTRING関数
SUBSTRING(文字列, 開始位置)
SUBSTRING(文字列, 開始位置, 長さ)
SUBSTRING(文字列 FROM 開始位置)
SUBSTRING(文字列 FROM 開始位置 FOR 文字数)
例）
・SQL
SELECT
SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS from1to4,
SUBSTRING('PostgreSQL' FROM 8) AS after8;
・結果
from1to4|after8|
--------|------|
Post |SQL |
■UTR形式の文字列からドメイン名を取り出す
・テーブル
access_timestamp |referrer |target_url |
-------------------|-------------------------------------------------------|---------------------------------------------|
2019-04-26 00:07:00|http://www.example2.jp/path1/index.php?k1=v1&k2=v2#Ref1|http://www.example.com/one/detail?ref_id=001 |
2019-04-26 00:07:01|http://www.example3.jp/path1/index.php?k1=v1&k2=v2#Ref1|http://www.example.com/one#ref |
2019-04-26 00:07:00|http://www.example2.jp/ |http://www.example.com/book/detail?ref_id=002|
・SQL
SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(target_url, '/', 3), '://', -1 ), '/', 1), '?', 1 ) AS domain
FROM
fact_web_access_log;
・結果
domain |
---------------|
www.example.com|
www.example.com|
www.example.com|
■CONCATによる文字列の連結
例）
・テーブル
user_id|pref_name|city_name|
-------|---------|---------|
U00001 |東京都 |中央区 |
U00002 |東京都 |世田谷区 |
U00003 |福岡県 |福岡市4 |
U00001 |東京都 |渋谷区 |
U00005 |神奈川県 |横浜市 |
・SQL
SELECT
user_id,
CONCAT(pref_name, city_name) AS 都道府県＋都市
FROM
user_place;
・結果
user_id|都道府県＋都市|
-------|-------|
U00001 |東京都中央区 |
U00002 |東京都世田谷区|
U00003 |福岡県福岡市4|
U00001 |東京都渋谷区 |
U00005 |神奈川県横浜市|
0コメント