Teradata SQL 常用，
DBC
dbc.tables
dbc.columns
dbc.tablesize
dbc.allrights
select *from dbc.columnswhere upper(databasename)='PP_RISK_OPS_VIEWS' andupper(tablename)='UNIFIED_DATASET_V3' ;
selecttop 10 * from DBC.columnsVX where databasename='pp_access_views' andtablename='dw_payment_sent' ;
select *from dbc.diskspace
selprofile ;
explainselect top 10 * from pp_discovery_views.dim_customer ;
help table pp_discovery_views.dim_customer ;
help column pp_discovery_views.dim_customer.acct_clsd_dt ;
help viewpp_discovery_views.dim_customer ;
Teradata 数据库空间 以及表空间的计算
select a.databasename,
a.tablename,
cast(trim(cast(sum(a.currentperm)/1024/1024
as int)
|| 'MB') as
char(5)) as table_size
from dbc.tablesize
a
join dbc.tables
b on
a.databasename=b.databasename
and a.tablename=b.tablename
where upper(b.databasename)
in ( 'PP_SCRATCH_RISK',
'PP_OAP_WZ3_T') and
upper(b.creatorname)
='WZHAO3'
group by
1,2
order by
1,2
replace macro all_dataspace as (
select cast(trim(databasename) as char(20)) || '|' (title 'name')
,cast(trim(cast(sum(currentperm)/1024/1024/1024 as int) || 'G') as char(5)) || '|' (title 'curr(G)')
,cast(trim(cast(sum(maxperm)/1024/1024/1024 as int) || 'G')as char(6)) || '|' (title 'max(G)')
,cast(trim(cast(sum(maxperm)/1024/1024/1024 as int)-cast(sum(currentperm)/1024/1024/1024 as int) || 'G')as char(6)) || '|' (title 'spare(G)')
,sum(currentperm)*100/nullifzero(sum(maxperm)) (format 'zz9.99%',title 'per(%)')
from dbc.diskspace
groupby 1
order by 4 desc;
);
--计算表空间
sel cast(trim(cast(sum(currentperm)/1024/1024/1024 as int) || 'G') as char(5)),tablename,databasename from dbc.tablesize where databasename='dwpdata' and tablename in
('Atable','Btable','Ctable','Dtable','Etable') group by 2,3
select *from dbc.allrights
where lower(databasename)='pp_oap_wz3_t' and username='wzhao3'
select sndr_id_chr,
trans_id_chr,
cast ( trim(pmt_start_ts)
astimestamp(0)
format'yyyy-mm-ddbhh:mi:ss' )
pmt_start_ts
from pp_scratch_risk.wzhao3_fn_supercookie_sndr
where fn_supercookie
is not
null
sample 10