Article Outline
COALESCE関数
TOC
Collection Outline
[BIGQUERY] 分析入門
[BIGQUERY] SQL
■ 句(節)
■ 演算子
■ BigQuery特有
■ 関数
- approx_count_distinct() - 集計近似
- coalesce()
- countif()
- extract()
- greatest()
- ifnull()
- last_day()
- lead() / lag()
- left() / right()
- lower()
- nullif()
- parse_date()
- row_number() - 番号付
- safe_cast()
- split()
- st_distance()
- nth_value()
- first_value() / last_value()
- percentile_()
データ型
■ エラー
- error
- Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex
[BIGQUERY] ADVANCE
[BIGQUERY] ML
[BIGQUERY] DS
■ DS100ノック
■
[BIGQUERY] GA
|| coalesce()
- 欠損処理に使える。(Null補完ができる。)
select
ID
, coalesce(height, avg(mean)) as HEIGHT
, coalesce(weight, avg(mean)) as WEIGHT
from
BMI
身長体重をユーザー別に記録しテーブルがあったとして、「NulL」のデータを全体の平均値で埋めたい時。とか
- 抽出時の条件文的にも使える。
select disinct
r.MEMBER_ID
, coalesce(m.INTEGRATION_ID1, D.INTEGRATION_ID_1, CAST(P.STATION_ID AS STRING)) AS STATION_ID
, coalesce(m.STATION1, D.STATION1, P.STATION_NAME) AS STATION_NAME
, coalesce(
concat(m.PROV_NAME, '_', REPLACE(m.STATION1,'駅', ''))
, concat(d.PROV_NAME, '_', REPLACE(d.STATION1,'駅', ''))
, concat(p.PROV_NAME, '_', REPLACE(p.STATION_NAME,'駅', ''))
) AS KEY
from
T_RSV_PREV as r
left outer join
M_MEDICAL_ALL as m
on
r.SHOPO_ID = m.SHOP_ID
and r.TYPE_CODE = 'M'
left outer join
M_DENTAL_ALL as d
on
r.SHOP_ID = d.SHOP_ID
and r.TYPE_CODE = 'D'
left outer join
M_PHARMACY_ALL as p
on
r.SHOP_ID = CAST(p.shop_id AS STRING)
and r.TYPE_CODE = 'P'
あるユーザーに対して、業種別のテーブルをマージして、「Null」でない値を取得する場合。とか