Article Outline
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
|| greatest()
| 使用例
--・施設別 × 終業時間
, SHOP_END_TIME as (
select
*
, greatest(
MON_END_TIME, MON_END_TIME2
, TUE_END_TIME, TUE_END_TIME2
, WED_END_TIME, WED_END_TIME2
, THU_END_TIME, THU_END_TIME2
, FRI_END_TIME, FRI_END_TIME2
, SAT_END_TIME, SAT_END_TIME2
, SUN_END_TIME, SUN_END_TIME2
, HOL_END_TIME, HOL_END_TIME2
) as END_TIME
from (
select
CATALOG_ID
, ifnull(if(MON_END_TIME = 0, 2400, MON_END_TIME ), 0) as MON_END_TIME
, ifnull(if(MON_END_TIME2= 0, 2400, MON_END_TIME2), 0) as MON_END_TIME2
, ifnull(if(TUE_END_TIME = 0, 2400, TUE_END_TIME ), 0) as TUE_END_TIME
, ifnull(if(TUE_END_TIME2= 0, 2400, TUE_END_TIME2), 0) as TUE_END_TIME2
, ifnull(if(WED_END_TIME = 0, 2400, WED_END_TIME ), 0) as WED_END_TIME
, ifnull(if(WED_END_TIME2= 0, 2400, WED_END_TIME2), 0) as WED_END_TIME2
, ifnull(if(THU_END_TIME = 0, 2400, THU_END_TIME ), 0) as THU_END_TIME
, ifnull(if(THU_END_TIME2= 0, 2400, THU_END_TIME2), 0) as THU_END_TIME2
, ifnull(if(FRI_END_TIME = 0, 2400, FRI_END_TIME ), 0) as FRI_END_TIME
, ifnull(if(FRI_END_TIME2= 0, 2400, FRI_END_TIME2), 0) as FRI_END_TIME2
, ifnull(if(SAT_END_TIME = 0, 2400, SAT_END_TIME ), 0) as SAT_END_TIME
, ifnull(if(SAT_END_TIME2= 0, 2400, SAT_END_TIME2), 0) as SAT_END_TIME2
, ifnull(if(SUN_END_TIME = 0, 2400, SUN_END_TIME ), 0) as SUN_END_TIME
, ifnull(if(SUN_END_TIME2= 0, 2400, SUN_END_TIME2), 0) as SUN_END_TIME2
, ifnull(if(HOL_END_TIME = 0, 2400, HOL_END_TIME ), 0) as HOL_END_TIME
, ifnull(if(HOL_END_TIME2= 0, 2400, HOL_END_TIME2), 0) as HOL_END_TIME2
from (
select
CATALOG_ID
, safe_cast(replace(MON_END_TIME , ':', '') as int64) as MON_END_TIME
, safe_cast(replace(MON_END_TIME2, ':', '') as int64) as MON_END_TIME2
, safe_cast(replace(TUE_END_TIME , ':', '') as int64) as TUE_END_TIME
, safe_cast(replace(TUE_END_TIME2, ':', '') as int64) as TUE_END_TIME2
, safe_cast(replace(WED_END_TIME , ':', '') as int64) as WED_END_TIME
, safe_cast(replace(WED_END_TIME2, ':', '') as int64) as WED_END_TIME2
, safe_cast(replace(THU_END_TIME , ':', '') as int64) as THU_END_TIME
, safe_cast(replace(THU_END_TIME2, ':', '') as int64) as THU_END_TIME2
, safe_cast(replace(FRI_END_TIME , ':', '') as int64) as FRI_END_TIME
, safe_cast(replace(FRI_END_TIME2, ':', '') as int64) as FRI_END_TIME2
, safe_cast(replace(SAT_END_TIME , ':', '') as int64) as SAT_END_TIME
, safe_cast(replace(SAT_END_TIME2, ':', '') as int64) as SAT_END_TIME2
, safe_cast(replace(SUN_END_TIME , ':', '') as int64) as SUN_END_TIME
, safe_cast(replace(SUN_END_TIME2, ':', '') as int64) as SUN_END_TIME2
, safe_cast(replace(HOL_END_TIME , ':', '') as int64) as HOL_END_TIME
, safe_cast(replace(HOL_END_TIME2, ':', '') as int64) as HOL_END_TIME2
from SHOP_DNT
)
)
)
|| REFERENCE
