sql基礎関数
小さなメモ:
タイムスタンプ-時間変換:
タイムスタンプ変換:
現在のタイムスタンプを出力:
時間書式:
日付関数:
取得日時時間、分、秒:[to_date、year、month、day、hour、minute、second]
差分日数:
増加日数:
日数を減らす:
時間差:unix_timestamp() - unix_timestamp(ymdhms)は、2つの時間がtimestampに変換された後に減算され、timestamp単位は秒であり、減算された後に2つの時間の差の秒数である.
eg:時間
全型回転時間タイプ:ステップ:先回転時間をstring、後回転タイムスタンプ、最後回転時間
1、ifとcase:いずれも単一カラムを処理する判断クエリー結果
2、nvl nullに対する判断:null値の置換select nvl(col,1)from tbを実現する--最初のパラメータがnullであれば、出力は2番目の置換3、coalesce:非空検索関数で、多くのパラメータを持つことができる
1、like[_は単一文字、%は複数文字を識別する]
2、rlikeはregexpと同じ使い方
"."任意の1文字"*"は前の文字に0回または複数回"+"は前の文字に1回または複数回"?前の文字に一致する0回または1回の「d」は[0-9]に等しく、使用する場合は「d」「D」は[^0-9]に等しく、使用する場合は「D」に書く
3、regexp_replace:正規表現に置き換える
4、regexp_extract:正規に分割し、指定したindex文字を1から返す
欲張りは最後のスラッシュにマッチします.
1、round保留n位小数
テーブルのフィールド情報とメタデータの格納パスの表示
ダイナミックパーティションパラメータの設定
パーティションデータの削除
挿入データinsert overwriteは既存のデータを上書きし,insert intoは単純な挿入であり,元のテーブルのデータを考慮せずに直接テーブルに追加する.
bashクエリーデータローカル保存
likeの使い方
テーブルの作成
join join hive join不等量がないjoinはleft joinを必要とした後、空の操作を判定します.
次のエラー
データ型のフォーマット:
cast(col as int)
日付、時刻
タイムスタンプ-時間変換:
select FROM_UNIXTIME(1156219870)
--> 2006-08-22 12:11:10
タイムスタンプ変換:
select UNIX_TIMESTAMP('2006-11-04 12:23:00');
-->1162614180
現在のタイムスタンプを出力:
select UNIX_TIMESTAMP(); | select UNIX_TIMESTAMP(now());
時間書式:
select DATE_FORMAT(FROM_UNIXTIME(1156219870),'yyyy-MM-dd HH:mm:ss')
-->2006-08-22 12:11:10
日付関数:
select trunc(CURRENT_DATE, 'MM'), --
last_day(CURRENT_DATE); --
取得日時時間、分、秒:[to_date、year、month、day、hour、minute、second]
select to_date('2011-12-08 10:03:01')
-->2011-12-08
select year('2011-12-08 10:03:01')
-->2011
select month('2011-12-08 10:03:01')
-->12
select day('2011-12-08 10:03:01')
-->8
select hour('2011-12-08 10:03:01')
-->10
select minute('2011-12-08 10:03:01')
-->3
select second('2011-12-08 10:03:01')
-->1
差分日数:
select datediff('2012-12-08','2012-10-08');
-->61
増加日数:
select date_add('2012-12-08',4);
-->2012-12-12
日数を減らす:
select date_sub('2012-12-08',4);
-->2012-12-04
時間差:unix_timestamp() - unix_timestamp(ymdhms)は、2つの時間がtimestampに変換された後に減算され、timestamp単位は秒であり、減算された後に2つの時間の差の秒数である.
CAST((unix_timestamp() - unix_timestamp(ymdhms)) % 60 AS int) 。
CAST((unix_timestamp() - unix_timestamp(ymdhms)) / 60 AS int) % 60 。
CAST((unix_timestamp() - unix_timestamp(ymdhms)) / (60 * 60) AS int) % 24 。
CAST((unix_timestamp() - unix_timestamp(ymdhms)) / (60 * 60 * 24) AS int) 。
eg:時間
select CAST((unix_timestamp('2020-10-21 12:23:00') - unix_timestamp('2020-10-21 10:23:00'))/ (60 * 60) AS int) % 24
-->2
全型回転時間タイプ:ステップ:先回転時間をstring、後回転タイムスタンプ、最後回転時間
set dt=20200701 --
select ${dt},
from_unixtime(unix_timestamp(cast(${dt} as string),'yyyyMMdd'),'yyyy-MM-dd') ;
判断条件：
1、ifとcase:いずれも単一カラムを処理する判断クエリー結果
select count(if(d2['01008'] is not null and d2['01008']!='' ,1,null)) IMSI,
case when ：
select case os when 'android' then 'android' when 'ios' then 'iPhone' else 'PC' end as os,
case when ：
select sum(case when before_prefr_unit_price < 100 then 1 else 0 end ) as a1,
sum(case when before_prefr_unit_price >= 100 and before_prefr_unit_price < 200 then 1 else 0 end) as a2,
sum(case when before_prefr_unit_price >= 200 and before_prefr_unit_price < 300 then 1 else 0 end) as a3,
sum(case when before_prefr_unit_price >= 300 then 1 else 0 end) as a4
from gdm_m04_ord_det_sum
where to_date(sale_ord_dt) = '2014-11-11' and dp = 'ACTIVE'
2、nvl nullに対する判断:null値の置換select nvl(col,1)from tbを実現する--最初のパラメータがnullであれば、出力は2番目の置換3、coalesce:非空検索関数で、多くのパラメータを持つことができる
select COALESCE(null,null,null,50);
substr切り取り文字列:
select substr('abcde',3)
-->cde
select substr('abcde',-3,2) --2 len
-->cd
split文字列分割:
select split('192.168.0.1','\\.')[0];
-->192
正則[like,rlike,regexp,regexp_replace,regexp_extract]
1、like[_は単一文字、%は複数文字を識別する]
select 'football' like '__otba%'
-->true
2、rlikeはregexpと同じ使い方
"."任意の1文字"*"は前の文字に0回または複数回"+"は前の文字に1回または複数回"?前の文字に一致する0回または1回の「d」は[0-9]に等しく、使用する場合は「d」「D」は[^0-9]に等しく、使用する場合は「D」に書く
select 'football' rlike '^footba'
-->true
select 'does' rlike 'do(es)?'
-->true
3、regexp_replace:正規表現に置き換える
select regexp_replace('h234ney', '\\d+', 'o')
-->honey
4、regexp_extract:正規に分割し、指定したindex文字を1から返す
select regexp_extract('honeymoon', 'hon(.*?)(moon)', 1);
-->honeymoon
select regexp_extract('honeymoon', 'hon(.*?)(moon)', 1);
-->ey
select regexp_extract('honeymoon', 'hon(.*?)(moon)', 2);
-->moon
欲張りは最後のスラッシュにマッチします.
select
regexp_extract('com.baidu.searchbox/oat/arm/chinamobile/cactus.com.baidu.searchbox','.*/(.*)',1) c_0
数学関数:
1、round保留n位小数
select round(12.344,2)
-->12.34
テーブルのフィールド情報とメタデータの格納パスの表示
desc formatted table_name;
ダイナミックパーティションパラメータの設定
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
パーティションデータの削除
alter table drop partition(stat_date=',,');
挿入データinsert overwriteは既存のデータを上書きし,insert intoは単純な挿入であり,元のテーブルのデータを考慮せずに直接テーブルに追加する.
bashクエリーデータローカル保存
hive -e 'select * from table_name;' >> /home/test.txt
hive -f exer.sql >> /home/test.txt
likeの使い方
select * from user where name like concat(“%”, ,”%”)
テーブルの作成
CREATE TABLE IF NOT EXISTS tmp.code_name (
rule_name string,
rule_code string)
row format delimited fields terminated by '\t' lines terminated by '
' stored as textfile;
join join hive join不等量がないjoinはleft joinを必要とした後、空の操作を判定します.
on b.noriskfactor=c.tag where c.tag is null
次のエラー
on b.noriskfactor=c.tag and c.tag is null