TeraData常用函数

大家好，我是讯享网，很高兴认识大家。

1.替换空格：

SELECT OReplace('a b c',' ','')

2.创建临时表

CREATE VOLATILE TABLE LTMP17

(str varchar(10), id int)

ON COMMIT PRESERVE ROWS;

插入数据：

insert into LTMP17

select 'b' as str, 2 as id

创建临时表-不包含重复值

CREATE MULTISET VOLATILE TABLE VT_CUST (

STAT_DT DATE

,HOST_CUST_ID VARCHAR(10)

)ON COMMIT PRESERVE ROWS

;

–根据SQL结果集建表

CREATE MULTISET VOLATILE TABLE VT_CUST AS (

SELECT HOST_CUST_ID FROM TEST.CUST SAMPLE 1111

)WITH DATA ON COMMIT PRESERVE ROWS

;

–根据已有表创建新表

CREATE MULTISET VOLATILE TABLE VT_CUST2 AS VT_CUST1 WITH NO DATA ON COMMIT PRESERVE ROWS; --不含VT_CUST1的数据

CREATE MULTISET VOLATILE TABLE VT_CUST2 AS VT_CUST1 WITH DATA ON COMMIT PRESERVE ROWS; --含VT_CUST1的数据

数据操作

–插入数据

INSERT INTO VT_CUST (HOST_CUST_ID) VALUES (‘’); --指定插入数据

INSERT INTO VT_CUST VALUES (DATE ‘2019-01-01’,‘’); --全字段插入数据

INSERT INT VT_CUST SELECT * FROM VTABLE ;–插入结果集

–删除数据

DELETE FROM VT_CUST WHERE HOST_CUST_ID =‘’ ;

–更改数据

UPDATE VT_CUST SET HOST_CUST_ID=‘’ WHERE STAT_DT IS NULL ;

表结构操作

–增加列 (临时表不能更改表结构)

ALTER TABLE VT_CUST ADD BAL DECIMAL(18,2) ,BAL_SUM DECIMAL(20,2) ;

–删除列

ALTER TABLE VT_CUST DROP BAL ;

–修改列

ALTER TABLE VT_CUST ADD HOST_CUST_ID FORMAT VARCHAR(20) ;

改表名的写法如下：

RENAME TABLE tb_name to tb_name2

–展示表定义

SHOW TABLE VT_CUST;

qualify row_number() over(partition by col1,col2 order by col3 desc,col4 asc)=1;

/*-------------------------------创建临时表开始------------------------------------*/

/*CREATE MULTISET TABLE PT_TEMP.TMP1024 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

ID INTEGER TITLE '序号',

Level_Name VARCHAR(20) CHARACTER SET Latin CaseSpecific TITLE '等级');*/

SELECT * FROM PT_TEMP.TMP1024

/*INSERT INTO PT_TEMP.TMP1024 (ID,Level_Name) VALUES(1, '一级')*/

/*DROP TABLE PT_TEMP.TMP1024*/

/*-------------------------------创建临时表结束------------------------------------*/

/*-------------------------------创建临时表开始------------------------------------*/

映射另一张表的表结构 CREATE TABLE PT_TEMP.TMP1030 AS PT_TEMP.TMP1024 WITH DATA(with no data) CREATE TABLE PT_TEMP.TMP1030 AS (SELECT * FROM PT_TEMP.TMP1024) WITH DATA;

讯享网

/*-------------------------------创建临时表结束------------------------------------*/

---------------------

递归案例： with recursive tmp(id)()

CREATE VOLATILE TABLE gift_info (

gf_id varchar(50),

channels varchar(100)

)ON COMMIT PRESERVE ROWS;

INSERT INTO gift_info

SELECT '' AS gf_id, '215|482|850|870' AS channels

WITH RECURSIVE temp_table (gf_id,channels) AS

(

SELECT

gf_id

,trim(channels)

from gift_info

WHERE channels<>''

union all

SELECT

gf_id

,substr(channels,5 )

from temp_table aa

where char(aa.channels ) <>3

)

SELECT

Trim(gf_id)

,SUBSTR(channels,1,3)

from temp_table

;

------------------

3.日期操作

TERADATA中取年初，月初，月末的写法如下

SELECT CAST(SUBSTR('',1,4)||'0101' AS DATE FORMAT 'YYYYMMDD')

,CAST(SUBSTR('',1,6)||'01' AS DATE FORMAT 'YYYYMMDD')

,CAST(SUBSTR('',1,6)||'01' AS DATE FORMAT 'YYYYMMDD')-1

EXTRACT函数用来截取一个日期中的天数，月，年

SELECT EXTRACT(DAY FROM CAST('' AS DATE FORMAT 'YYYYMMDD'))

,EXTRACT(MONTH FROM CAST('' AS DATE FORMAT 'YYYYMMDD'))

,EXTRACT(YEAR FROM CAST('' AS DATE FORMAT 'YYYYMMDD'))

获取两个日期之间的天数，月数，年数之差写法如下日期相减：

SELECT (DATE '2014-02-07' - DATE '2014-01-01') DAY(4)

, (DATE '2014-02-07' - DATE '2013-12-01') MONTH(4)

, (DATE '2014-02-07' - DATE '2013-05-01') YEAR(4)

-----------------

CREATE VOLATILE TABLE lcm

(id INT)

ON COMMIT PRESERVE ROWS*/

INSERT INTO lcm

SELECT 1 AS id*/

/*SELECT * FROM lcm*/

WITH RECURSIVE tmp(id) AS(

SELECT 1 AS id from lcm

UNION ALL

SELECT id+1 AS id FROM tmp WHERE id <9

)

SELECT * FROM tmp

--3生成日期维表

WITH RECURSIVE tmp(dt) AS(

SELECT Cast(''||'01' AS DATE FORMAT 'YYYYMMDD') AS dt FROM pv_member.D_V_DATE WHERE date_id=DATE'2021-12-01'

UNION ALL

SELECT dt + INTERVAL '1' DAY FROM tmp WHERE dt<Last_Day(dt)

)

SELECT * FROM tmp

--4 多行拼接成一行，用逗号隔开

WITH RECURSIVE tmp(id) AS (

SELECT 1 AS id FROM pv_member.D_V_DATE WHERE date_id=DATE'2021-12-01'

UNION ALL

SELECT id+1 AS id FROM tmp a WHERE a.id < 9

)

SELECT

Trim(Trailing ',' FROM (XmlAgg(txt || ',')(VARCHAR(500))))

FROM (

SELECT

a.id AS aid,b.id AS bid,

Trim(a.id||'*'||b.id||'='||a.id*b.id) AS txt

FROM tmp a , tmp b

WHERE a.id < b.id

SELECT
member_id,
Trim(txt)||Trim(Cast(group_sum AS INT) ) AS b
FROM (
SELECT
member_id,
Sum(group_id) AS group_sum,
OReplace(Trim(Trailing ',' FROM (XmlAgg(group_id2 || '' ORDER BY group_id) (VARCHAR(500)) )),' ','') AS txt
/*Trim(Trailing ',' FROM (XmlAgg(Trim(group_id2) || '')(VARCHAR(500)))) AS txt*/
FROM tmp37
WHERE member_id = '00245'
GROUP BY member_id
) t

九九乘法表

WITH RECURSIVE tmp(id) AS (

SELECT 1 AS id FROM pv_member.D_V_DATE WHERE date_id=DATE'2021-12-01'

UNION ALL

SELECT id+1 AS id FROM tmp a WHERE a.id < 9

)

SELECT

aid,

Trim(Trailing ',' FROM (XmlAgg(txt || ' ')(VARCHAR(500))))

FROM (

SELECT

a.id AS aid,b.id AS bid,

Trim(b.id||'*'||Trim(a.id)||'='||Trim(a.id*b.id)) AS txt

FROM tmp a , tmp b

WHERE b.id <= a.id

GROUP BY aid

ORDER BY 1

-- 常用日期函数

SELECT Cast('' AS DATE FORMAT 'YYYYMMDD') AS dt,

Current_Timestamp (FORMAT 'MMMBDD,BYYYYBHH:MIBT') AS DT2,

TIME,

Current_Date,

Current_Time,

Last_Day(Current_Date) ,

TYPE(Current_Date),

Cast('' AS DATE FORMAT 'YYYYMMDD') AS dt3, --字符串变成日期

DATE '2019-01-31' AS dt4, --字符串变成日期

To_Char(Current_Date) AS dt5 , --转换成字符类型

To_Char(Current_Date,'yyyymmdd') AS dt6,

Add_Months(DATE '2019-02-28', 1) AS dt7, --下月同一天。返回结果：'2019-03-28'

Add_Months(DATE '2019-01-31', 1) AS dt8, --下月同一天。返回结果：'2019-02-28'

Add_Months(DATE '2019-03-31',-1) AS dt9, --上月同一天。返回结果：'2019-02-28'

Extract( YEAR From Current_Date) AS dt10, --获取日期中的年份（返回int类型）

Extract( MONTH From Current_Date) AS dt11, --获取月份

Extract( DAY From Current_Date) AS dt12, --获取T天

Trunc(Current_Date,'MM') AS dt13, --月初。返回date类型

Trunc(Current_Date,'MM')-1 AS dt14, --上月末

Trunc(Current_Date,'YEAR') AS dt15, --年初

Current_Date AS dt16 , --今天

Current_Date-1 AS dt17 , --昨天

Current_Date+1 AS dt18 , --明天

Trunc(Add_Months(Current_Date,1),'MM')-1 AS dt19 , --月末

--Extract(DAY From Current_Date+1)=1 AS dt20 , --月末判断

Extract(DAY From Last_Day(Current_Date)) AS dt21, --当月天数

(Extract(MONTH From Current_Date)+2)/3 AS dt22 , --当前季度(量个int类型计算结果为int)

--(Extract(MONTH From Current_Date)MOD 3) =0 AS dt23, --季末判断

Add_Months(Trunc(Current_Date,'MM'),Nvl(NullIfZero((Extract(MONTH From Current_Date) MOD 3)*-1),-3)+1) AS dt24 , --本季度初

Add_Months(Trunc(Current_Date,'MM'),4-Nvl(NullIfZero((Extract(MONTH From Current_Date)MOD 3)),3))-1 AS dt25 ,--本季度末

Nvl(NullIfZero((Current_Date - DATE'1979-12-30') MOD 7),7) AS dt26 , --今天是周几

Current_Date-Nvl(NullIfZero((Current_Date-DATE'1979-12-30') MOD 7),7)+1 AS dt27 , --本周一

Current_Date-Nvl(NullIfZero((Current_Date-DATE'1979-12-30') MOD 7),7)+7 AS dt28 , --本周末

Current_Date-Nvl(NullIfZero((Current_Date-DATE'1979-12-30') MOD 7),7)-7+1 AS dt29 , --上周一

Current_Date-Nvl(NullIfZero((Current_Date-DATE'1979-12-30') MOD 7),7)-7+7 AS dt30 --上周末

--生肖算法 (或用Case When)

Decode((Substr(DATE1 ,1,4)-'1900')MOD 12 ,0,'鼠' ,1,'牛' ,2,'虎' ,3,'兔' ,4,'龙' ,5,'蛇' ,6,'马' ,7,'羊' ,8,'猴' ,9,'鸡' ,10,'狗' ,11,'猪') AS dt31*/

-- 日期加一年

讯享网SELECT Current_Date+INTERVAL '1' year

-- 计算天，时，分，秒

WITH tmp AS(

SELECT Row_Number() Over(ORDER BY effect_tm ASC) AS rn ,effect_tm FROM PV_MEMBER.F_V_MEMBER_BEHAVIOR_EXPERIENCE

WHERE Member_Id = '96652'

AND effect_tm = DATE'2022-03-11'

)

SELECT

/*Cast(((t.effect_tm-t2.effect_tm)Minute(4)) AS DECIMAL(18,4))*60 AS Seconds,*/

t.rn,t2.rn,

/*Extract(SECOND From t.effect_tm) - Extract(SECOND From t2.effect_tm) AS Seconds,*/

/*(Cast((Cast(t.effect_tm AS DATE)- Cast(t2.effect_tm AS DATE)) AS DECIMAL(18,6)) * 60*24)*/

((Extract(HOUR From t.effect_tm) - Extract(HOUR From t2.effect_tm))* 60*60)

+ ((Extract(MINUTE From t.effect_tm) - Extract(MINUTE From t2.effect_tm))*60)

+ ((Extract(SECOND From t.effect_tm) - Extract(SECOND From t2.effect_tm)))

AS "Difference in seconds",

t.effect_tm,t2.effect_tm

FROM tmp t

LEFT JOIN tmp t2 ON t.rn = t2.rn+1

计算得到分钟

,Cast((Cast (t1.Fst_Effect_TM AS TIME)-Cast (t2.Scd_Effect_TM AS TIME) Minute(4)) AS INT) AS a

-- 每天累加

WITH tmp AS (

SELECT

To_Char(effect_tm,'yyyy-mm-dd') AS dt,

Sum (Change_Val) AS val

FROM table

WHERE Effect_Tm >= Add_Months (Effect_Tm,-24) AND Effect_Tm < Current_Date

AND Area_Cd = 'A001'

AND Member_Id = '00245'

GROUP BY To_Char(effect_tm,'yyyy-mm-dd')

/*ORDER BY 1 DESC*/

) SELECT

t.dt,

Sum(val) AS val,

(SELECT

Sum(val)

From tmp t2

WHERE t2.dt <= t.dt) AS val2

FROM tmp t

GROUP BY t.dt

ORDER BY t.dt

-- 七日平均移动平均

DROP TABLE tmp39;

CREATE MULTISET VOLATILE TABLE tmp39 AS(

WITH tmp AS (

SELECT

To_Char(effect_tm,'yyyy-mm-dd') AS dt,

Sum (Change_Val) AS val

FROM table

WHERE Effect_Tm >= DATE'2022-02-01' AND Effect_Tm < Current_Date

AND Area_Cd = 'A001'

--AND Member_Id = '00245'

GROUP BY To_Char(effect_tm,'yyyy-mm-dd')

/*ORDER BY 1 DESC*/

) SELECT

t.dt,

Sum(val) AS val,

(SELECT

Sum(val)

FROM tmp t2

WHERE t2.dt <= t.dt

) AS val2

FROM tmp t

GROUP BY t.dt

)WITH DATA ON COMMIT PRESERVE ROWS

;

-- 七日移动平均和七日移动汇总求累计值

SELECT

t.dt,

t.val,

t.val2 AS t_val2,

t2.val2 AS t2_val2,

CASE WHEN To_Date(t.dt)>DATE'2022-02-07' THEN t.val2-t2.val2 ELSE t.val2 END AS val3, -- 七日移动汇总

CASE WHEN To_Date(t.dt)>DATE'2022-02-07' THEN t.val2-t2.val2 ELSE t.val2 END/7 AS val4 -- 七日移动平均

FROM tmp39 t

LEFT JOIN tmp39 t2 ON To_Date(t.dt) = To_Date(t2.dt)+7

ORDER BY t.dt

-- TD表写入数据慢解决办法：

CREATE SET TABLE Table_Name,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Company_Id VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Store_Id VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Store_Name VARCHAR(100) CHARACTER SET Latin CaseSpecific,

Zone_Name VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Shipping_Type_Cd CHAR(10) CHARACTER SET Latin CaseSpecific,

Order_Orig_Type_Cd CHAR(10) CHARACTER SET Latin CaseSpecific,

Shipping_Company_Id VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Shipping_Store_Stock_Id VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Shipping_Stock_Name VARCHAR(100) CHARACTER SET Latin CaseSpecific,

Shipping_Zone_Name VARCHAR(20) CHARACTER SET Latin CaseSpecific,

Stock_Prop_Name VARCHAR(100) CHARACTER SET Latin CaseSpecific,

POS_Sale_Type_Name VARCHAR(30) CHARACTER SET Latin CaseSpecific,

Orig_Bill_Dt DATE Format 'yyyy-mm-dd',

Bill_Dt DATE Format 'yyyy-mm-dd',

Brand_Short_Name VARCHAR(100) CHARACTER SET Latin CaseSpecific,

Marketing_Dt DATE Format 'yyyy-mm-dd',

POS_Bill_Id VARCHAR(40) CHARACTER SET Latin CaseSpecific,

Qty INTEGER,

AR_Amt DECIMAL(38,6),

Total_Amt DECIMAL(38,6),

Actu_Price_Val DECIMAL(38,6))

PRIMARY INDEX ( Company_Id ,POS_Bill_Id )

PARTITION BY Range_N(Cast((Bill_Dt ) AS DATE Format 'YYYY-MM-DD') BETWEEN DATE '2004-01-01' AND DATE '2012-12-31' EACH INTERVAL '1' YEAR ,

DATE '2013-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' DAY ,

NO RANGE);

-- TD创建索引 ID作为索引字段

CREATE INDEX Idx (id) ON table_name

相关推荐