SQLφ(..)メモメモその3数字と文字の演算

Photo by Brett Sayles on Pexels.com

またGROUP が混乱してきた・・・

数字の丸め関数 
ROUND() 四捨五入 関数(列,桁数) -2は百の位、1は小数点1の位。指定しないと整数で帰ってくる
CEIL() (シールと呼ぶ)切り上げ 桁数調整はない
FLOOR() 切り下げ 桁数調整はない
だから切り下げ切り上げを百の位などで行いたいときは計算して100倍して100で割るなどが必要。
TRUNC関数は切り捨てることができる。日付もやれる(ほんとはORACLE関数らしい)

日付の切り捨てはDATE_TRUNCをつかう。DATE_TRUNC(日付,month)の場合、月次で切り捨てられる。

MODは余りを出す。MOD(割る数,割られる数)
CAST(対象カラム AS 変換先のデータ型) 文字列型 string 整数型 int64 CAST型変換できる方法の紹介ページ
CONCAT() 文字列連結 CONCAT(列,列,’文字’)
 例:名前の姓(lastname) 名(firstname)を半角入れて結合する
 CONCAT(lastname,’ ‘,firstname) AS 別名
LENGTH(列) 文字列の文字数取得
SUBSTR(列,位置,文字数) 文字列の一部を取り出す。真ん中の値、マイナスにすると右端、プラスで左端からになる
REPLACE(列,代替される文字列,代替する文字列) 文字列の一部を置き換える
TRIM(列,削除した文字) 文字列前後の空白もしくは任意の文字を削除

ー補足

DATE_TRANC関数は当初「ほー日付切り捨てられるのねー」ぐらいにぼーっと見てたのだが、

「四半期ごとの売上」なんてときに劇的に使えることがわかった。日別データが存在するときに、月次のデータとして扱いたいとき、この関数がすごい使える。

DATE_TRANC(丸めたい日付のカラム,丸めたい単位)で丸められるのだが、丸める方法はこんなにある。

MILLENNIUM千年紀(西暦の1000年区切り)
CENTURY世紀(西暦の100年区切り)
DECADE十年紀(西暦の10年区切り)
YEAR
QUARTER1年を4半期に区切った数値
1-3月=1
MONTH月(1-12)
WEEK週の番号(1-52,53,54)
※最大値は年により変動
DAY日(1-31)
HOUR時間(0-23)
MINUTE分(0-59)
SECOND秒(0-59)
MILLISECONDS小数部を含む秒フィールドに1000を掛けた値
MICROSECONDS小数部を含む秒フィールドに1,000,000を掛けた値

 

Toshiaki Ejiri: Born in Fukushima, working as web analytics consultant since 2000.