—PSQL常用日期函数—
CURRENT_DATE --当前日期
CURRENT_DATE - INTERVAL ‘1 day’ --昨天
(now() - interval ‘1 month’) – 将查询时间结果偏移一月到上个月的当前时刻
(now() - interval ‘1 year’) – 将查询时间结果偏移一年到上年的当前时刻
(now() - interval '1 week ') – 将查询时间结果偏移一周到上周的当前时刻
date_part(‘days’,now()-date_trunc(‘week’,current_date)) --本周的第几天
date_part(‘week’,now()- INTERVAL ‘1 day’) --今天第几周
date_part(‘days’,now()-date_trunc(‘month’,current_date) --截止本月的天数
date_part(‘week’,date(ds) ) --第几周
----ODPS常用函数----
set odps.sql.type.system.odps2 = true; ----加参
set odps.optimizer.mode = off; ----控制优化器
set odps.sql.reshuffle.dynamicpt = false; ----动态分区设置
set odps.sql.hive.compatible = true; ----udf函数
set odps.isolation.session.enable = true; ----udf函数
set odps.sql.udf.ppr.deterministic = true;----to_char时间参数
set odps.sql.mapper.split.size=3600;
set odps.sql.groupby.skewindata=true; ----Group By数量量较大防倾斜
----时间格式----
replace(substr(create_time,1,10),’-’,’’)
SUBSTR(ds,1,6) = SUBSTR(’${bizdate}’,1,6) ----本月
ds=to_char(dateadd(getdate(),-1,‘dd’),‘yyyymmdd’) -----昨天
ds>=to_char(datetrunc(dateadd(getdate(),-1,‘dd’),‘month’),‘yyyymmdd’)----当月1日
ds>=to_char(datetrunc(dateadd(dateadd(getdate(),-1,‘dd’),-1,‘mm’),‘month’),‘yyyymmdd’)----上月1日
datepart(dateadd(getdate(),-1,‘dd’),‘dd’)----本月天数
datepart(dateadd(datetrunc(dateadd(dateadd(getdate(),-1,‘dd’),1,‘mm’),‘month’),-1,‘dd’),‘dd’)----当月月份天数
datepart(dateadd(datetrunc(dateadd(getdate(),-1,‘dd’),‘month’),-1,‘dd’),‘dd’)----上月天数
to_char(dateadd(datetrunc(dateadd(getdate(),-1,‘dd’),‘month’),-1,‘dd’),‘yyyymmdd’)----上月最后一天
ds>=to_char(dateadd(getdate(),-30,‘dd’),‘yyyymmdd’)----近30天
case when weekday(to_date(ds,‘yyyymmdd’)) in (‘5’,‘6’) then ‘周末’ else ‘工作日’ end
【ODPS不可用】datename(weekday,‘2019-01-15’)----返回星期二
to_char(dateadd(getdate(),-30,‘dd’),‘yyyy-mm-dd’)
weekofyear(to_date(ds, “yyyymmdd”)) ----返回当前周
datediff(a,b,‘ss’)----a-b时间ss\mi\hh\dd\mm\yyyy差 时间差----datediff( to_date(’${bizdate}’,‘yyyymmdd’) ,to_date(ds,‘yyyymmdd’) ,‘dd’ )
分母为0情况count(a)/if(count(b)=0,1,count(b))----当分母为0时，则为1
coalesce(a,0)----当a为null（空值），则显示0
nvl(a,0)----当a为null（空值），则显示0
,row_number() over(partition by a order by b desc) rn----对a进行分组，再对b进行降序，得出排名（同分也123）,需加参（set odps.sql.reshuffle.dynamicpt = false; ----动态分区设置）
,dense_rank() over(partition by a order by b desc) rn ----对a进行分组，再对b进行降序,得出排名（同分同排名112）
,rank() over(partition by a order by b desc) rn ----对a进行分组，再对b进行降序,得出排名（同分同排名113）
,sum(a) over (ORDER BY b) ----按b升序，对a进行累加
,sum(a) over (PARTITION BY b ORDER BY c) ----按b分组，再对c升序，在b组内对a进行累加
,sum(a) over (PARTITION BY b) ----按b分组，在b组内对a进行累加给每个b
,FLOOR() 向下取整
,ceil()向上取整
a/nullif(b,0)----避免分母为0公式，当b=0时，输出null，与null乘除都得null
where ds=max_pt(‘eleme_cdm.dws_ele_slr_shop_wide_view_102’)----取a表的最新ds时间
concat_ws(’_’, collect_set(standard_product_name)) as product_name----字段组合 ‘蜂鸟专送_蜂鸟快送’ 需要group by
cast(a as BIGINT)----文本格式转化成整数
HAVING ----用于group by后的条件设置，=再次select后的where
percentile(jgmv,0.8) ----净G升序，数量的80%百分位，也就是TOP20商户
max_pt(‘表名’) ----取最新分区
----字符串处理----
REGEXP_EXTRACT(activity_info,‘使用了红包减([0-9]\.?[0-9]+)元\[(?:商家|代理商)承担:([0-9]\.?[0-9]+)元’,1) AS discount_member_coupon_amt
select SPLIT_PART(‘123-456-789-111’,’-’,1) ----对字符串按照-进行拆分，并取第1位的值，没有则为空
select t.id from (values(‘123’),(‘456’)) t (id) ----直插数据并引用
regiment_name not rlike ‘12|34’ ----字段内不含12/34，出现即不要
----表格处理----
临时表数据删除&填充
drop table if exists table_name;
create table if not exists table_name as
TRUNCATE table table_name --删除表数据但不删除表结构
表分区
insert overwrite table qz_super_5_order_shop----无分区
insert overwrite table qz_super_5_order_shop partition (ds = ‘${bizdate}’) ----有分区
集合
drop table if exists qz_super_5_order_shop;
CREATE TABLE IF NOT EXISTS qz_super_5_order_shop AS
----insert overwrite table qz_super_5_order_shop
----INSERT OVERWRITE TABLE qz_super_5_order_shop PARTITION(ds = ‘${bizdate}’)
insert into qz_super_5_order_shop ----直插数据，不迭代
alter table qz_super_5_order_shop add columns(独家标签_细分 string) ----直插字段在最后一列
CREATE TABLE IF NOT EXISTS dim_datedetailutils
(
id BIGINT COMMENT '自增id'
,dates STRING COMMENT '正常格式 20190101'
,dates_format_1 STRING COMMENT '日期格式1 2019-01-01'
,dates_format_2 STRING COMMENT '日期格式2 2019/01/01'
,dates_format_3 STRING COMMENT '日期格式3 2019 01 01'
,dates_startsec BIGINT COMMENT '当日0点对应的秒值'
,dates_endsec BIGINT COMMENT '23点59分59秒对应的秒值'
,years BIGINT COMMENT '日期所在年份'
,years_start STRING COMMENT '当前年份第一天'
,years_startsec BIGINT COMMENT '年份第一天0点对应的秒值'
,years_end STRING COMMENT '当前年份最后一天'
,years_endsec BIGINT COMMENT '年份最后一天23点59分59秒对应的秒值'
,quarters BIGINT COMMENT '日期所在季度'
,months BIGINT COMMENT '日期所在月份'
,months_start STRING COMMENT '当前月份第一天'
,months_startsec BIGINT COMMENT '月份第一天0点对应的秒值'
,months_end STRING COMMENT '当前月份最后一天'
,months_endsec BIGINT COMMENT '月份最后一天23点59分59秒对应的秒值'
,yearweek BIGINT COMMENT '日期位于那一年的第几周'
,days BIGINT COMMENT '日期中的日'
,weeks BIGINT COMMENT '日期当前周的第几天'
,weeks_mon STRING COMMENT '当前周周一'
,weeks_monsec BIGINT COMMENT '当前周周一0点对应的秒值'
,weeks_tues STRING COMMENT '当前周周二'
,weeks_tuessec BIGINT COMMENT '当前周周二0点对应的秒值'
,weeks_wed STRING COMMENT '当前周周三'
,weeks_wedsec BIGINT COMMENT '当前周周三0点对应的秒值'
,weeks_thur STRING COMMENT '当前周周四'
,weeks_thursec BIGINT COMMENT '当前周周0点对应的秒值'
,weeks_fri STRING COMMENT '当前周周五'
,weeks_frisec BIGINT COMMENT '当前周周五0点对应的秒值'
,weeks_sat STRING COMMENT '当前周周六'
,weeks_satsec BIGINT COMMENT '当前周周六0点对应的秒值'
,weeks_sun STRING COMMENT '当前周周日'
,weeks_sunsec BIGINT COMMENT '当前周周日0点对应的秒值'
,weeks_long string COMMENT '当周时间截取'
)
COMMENT '日期对应明细工具表'
;
--日期转换
SELECT ${id} AS id
,to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyymmdd'
)AS dates
,to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyy-mm-dd'
) AS dates_format_1
,to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyy/mm/dd'
) AS dates_format_2
,to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyy mm dd'
) AS dates_format_3
,UNIX_TIMESTAMP(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
) AS dates_startsec
,UNIX_TIMESTAMP(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
) + 86399 AS dates_endsec
,YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS YEARS
,CONCAT(
YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
,'0101'
) AS years_start
,unix_timestamp(
to_date(
CONCAT(
YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
,'0101'
)
,'yyyymmdd'
)
) AS years_startsec
,CONCAT(
YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
,'1231'
) AS years_end
,unix_timestamp(
to_date(
CONCAT(
YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
,'1231'
)
,'yyyymmdd'
)
) AS years_endsec
,quarter(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
) AS quarters
,MONTH(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS MONTHS
,concat(
to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyymm'
)
,'01'
) AS months_start
,unix_timestamp(
TO_DATE(
concat(
to_char(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
,'yyyymm'
)
,'01'
)
,'yyyymmdd'
)
)AS months_startsec
,to_char(
LASTDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
,'yyyymmdd'
) AS months_end
,UNIX_TIMESTAMP(
LASTDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'))
)+86399 AS months_endsec
,WEEKOFYEAR(
dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')
) AS yearweek
,DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS DAYS
,WEEKDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS weeks
,REPLACE(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'-'
,''
) AS weeks_mon
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)AS weeks_monsec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400
)
,'yyyy-mm-dd'
) AS weeks_tues
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400 AS weeks_tuessec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*2
)
,'yyyy-mm-dd'
) AS weeks_wed
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*2 AS weeks_wedsec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*3
)
,'yyyy-mm-dd'
) AS weeks_thur
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*3 AS weeks_thursec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*4
)
,'yyyy-mm-dd'
) AS weeks_fri
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*4 AS weeks_frisec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*5
)
,'yyyy-mm-dd'
) AS weeks_sat
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*5 AS weeks_satsec
,to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*6
)
,'yyyy-mm-dd'
) AS weeks_sun
,UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*6 AS weeks_sunsec
,concat(
REPLACE(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'-'
,''
)
,'~'
,REPLACE(
to_char(
FROM_UNIXTIME(
UNIX_TIMESTAMP(
to_date(
NEXT_DAY(
dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd')
,'mo'
)
,'yyyy-mm-dd'
)
)+86400*6
)
,'yyyy-mm-dd'
)
,'-'
,''
)
) AS weeks_loog
;
在这里插入代码片#!/bin/bash
#********************************************************************#
##author:chenying
##create time:2019-12-13 04:55:12
#********************************************************************#
u=${1}
p=${2}
start=20181231
for((id=1002;id<=2000;id++))
do
echo ${id} + "当前id"
/opt/文件名 -u $u -p $p --project=hive库名 --endpoint=http://service.odps.aliyun.com/api -e "set odps.sql.type.system.odps2 = true; INSERT INTO TABLE hive库名 .dim_datedetailutils SELECT ${id} AS id, to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyymmdd')AS dates, to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyy-mm-dd') AS dates_format_1, to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyy/mm/dd') AS dates_format_2, to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyy mm dd') AS dates_format_3, UNIX_TIMESTAMP(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS dates_startsec, UNIX_TIMESTAMP(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) + 86399 AS dates_endsec, YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS YEARS, CONCAT(YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')),'0101') AS years_start, unix_timestamp(to_date(CONCAT(YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')),'0101'),'yyyymmdd')) AS years_startsec, CONCAT(YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')),'1231') AS years_end, unix_timestamp(to_date(CONCAT(YEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')),'1231'),'yyyymmdd')) AS years_endsec, quarter(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS quarters, MONTH(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS MONTHS, concat(to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyymm'),'01') AS months_start, unix_timestamp(TO_DATE(concat(to_char(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd'),'yyyymm'),'01'),'yyyymmdd'))AS months_startsec, to_char(LASTDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')),'yyyymmdd') AS months_end, UNIX_TIMESTAMP(LASTDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')))+86399 AS months_endsec, WEEKOFYEAR(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS yearweek , DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS DAYS, WEEKDAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id},'dd')) AS weeks, REPLACE(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo'),'-','') AS weeks_mon, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))AS weeks_monsec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400),'yyyy-mm-dd') AS weeks_tues, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400 AS weeks_tuessec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*2),'yyyy-mm-dd') AS weeks_wed, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*2 AS weeks_wedsec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*3),'yyyy-mm-dd') AS weeks_thur, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*3 AS weeks_thursec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*4),'yyyy-mm-dd') AS weeks_fri, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*4 AS weeks_frisec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*5),'yyyy-mm-dd') AS weeks_sat, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*5 AS weeks_satsec, to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*6),'yyyy-mm-dd') AS weeks_sun, UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*6 AS weeks_sunsec ,concat(REPLACE(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo'),'-',''),'~',replace(to_char(FROM_UNIXTIME(UNIX_TIMESTAMP(to_date(NEXT_DAY(dateadd(TO_DATE(${start},'yyyymmdd'),${id}-7,'dd'),'mo') ,'yyyy-mm-dd'))+86400*6),'yyyy-mm-dd'),'-','')) AS weeks_loog ;"
done
619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
