vertica基础操作
--获取当前时间
lect sysdate;
lect getdate();
lect now()+1;--会显⽰时区
--格式化⽇期
lect to_char(sysdate(),'YYYY-MM-DD');
lect year(sysdate)||'-'||month(sysdate)||'-'||day(sysdate)||'第'||quarter(sysdate)||'季度 第'||week(sysdate)||'周'; lect to_char(sysdate,'hh24:mi:ss')
--转换类型
余悸什么意思lect cast('123' as integer);
--返回字符串位置
lect INSTR('sdsq','s',2);
--替换字符串
SELECT Replace('abcdef', 'bcd', 'ijklmn');
solitude
--截取字符串
lect substr('abcd',2,2);
--取绝对值：
lect abs(-1);
--向上取整：
lect ceil(-1.001);
--向下取整：
lect floor(-1.001);
-
-取整：
lect trunc(-1.002);
--四舍五⼊：
lect round(1.23456,4.0);
--取随机数：
lect random();
--数值⽐较：
lect greatest(1,-2,4,3);
--NULL空值
lect isnull(NULL ,'123');
--最⼤值
lect max(hurr_id) from ROI_USER_ROLE rr;
--最⼩值
lect min(hurr_id) from ROI_USER_ROLE rr;
ygg
lect PRODUCT_ID,count(1) from ROI_USER_PRODUCT rup group by PRODUCT_ID having COUNT(1)>1;
--字符串长度
lect LENGTH('12345');
--⼤⼩写转换 lower,upper
lect LOWER('ABC');
lect UPPER('abc');
--左右补空格
电影的英文单词lect LPAD('abcd',14, ' ') value,RPAD('abc',5,' ') from dual;
--删空格ltrim,rtrim,trim
lect ltrim(' 123 '),RTRIM(' 12324 '),TRIM(' 12343 ');
--⽣成内码 如PR0001
lect concat('PR',CAST(DENSE_RANK() OVER(ORDER BY PRODLINE_NAME,ROI_PRODUCT ) AS VARCHAR(10))) from PRODUCT_INFO rpi
--相同产品排序
lect PRODUCT ,NULL ,'KPI',RANK () OVER(
ORDER BY PRODUCT ) AS PRODUCT_SORT,'A',NULL,NULL,NULL,PRODLINE_NAME ,NULL,RANK () OVER(
ORDER BY PRODLINE_NAME ) AS PRODLINE_SORT,BIGPRODLINE_NAME,NULL,RANK () OVER(
ORDER BY BIGPRODLINE_NAME ) AS BIGPRODLINE_SORT,NULL from PRODLINE_YEAR
-obang
against是什么意思-为空的赋值
lect IFNULL(NULL,0),NVL(NULL,0)
--字段包含"研发费⽤（含间接" 取值
DECODE(HFM.SUBJECT,'研发费⽤（含间接）',SUM(CALIBER)*100000000)
--分组排序 处理数据
LAG(CAST (DECODE(HFM.SUBJECT,'收⼊',SUM(CALIBER)*100000000) AS FLOAT),1,0) OVER(PARTITION BY
HFM.ROI_PRODUCT ORDER BY HFM.PRODUCT,SUBJECT,HFM.YEARS) AS LAST_BUSINESS_INCOME
--⽣成ID
lect ROW_NUMBER() over(order by NAME) as ID from ORDER_CONFIG where CONFIG_CODE='CHANNEL_COMPLETE' --添加注释
1，先往要添加注释的表⾥添加⼀条数据。computer
2，查询投影分区
lect projection_name from projections where anchor_table_name = 'Table';
3，添加注释
节哀顺变英文
COMMENT ON COLUMN ⽤户.投影分区名称.字段名 IS '注释';
get off4，查询投影分区⾥的注释
SELECT t3.anchor_table_name AS Table_name,
SUBSTR (t1.object_name, INSTR (t1.object_name, '.', 1) + 1) AS Column_name,
FROM comments t1, projections t3
WHERE SUBSTR (t1.object_name, 1, INSTR (t1.object_name, '.', 1) - 1) =
t3.projection_name
AND t1.object_type = 'COLUMN' --and t3.anchor_table_name = 'HFM_PRODUCT'
ORDER BY t3.anchor_table_name;
--查询字段出现的表名
lect * from lumns where table_name like upper('%ROI%')
白色的英文
--查询字段在哪个表中出现
lect * from lumns where column_name = 'PDTNO';
--修改表名
ALTER TABLE test1 RENAME TO table_test;
--修改列名称
alter table IBDS_PDT rename pdtno1 to PDTNO;
-
-删除字段
Alter table表名 drop column字段名;
--查看所有表名
SELECT table_schema, table_name, create_time FROM tables;
--修改字段为⾮空
alter table test.fct_fournet_wlanap_equp_ana_d alter column day_id t not null;
--更改字段数据类型
alter table test.dim_micro_area_gsm alter column cell_id t data type numeric(15,0);
--给表增加字段
alter table test.DIM_DETAIL_SVCTYPE add column if_app numeric(10,0);
--删除表字段
alter table test.DIM_DETAIL_SVCTYPE drop column if_app;
--查询当前资源池的设置情况
SELECT name, memorysize, maxmemorysize, priority, runtimepriority,QUEUETIMEOUT,runtimeprioritythreshold, runtimecap, maxconcurrency
FROM V_CATALOG.RESOURCE_POOLS;
--查询当前资源池的使⽤情况
lect node_name,pool_name,running_query_count as
running,memory_size_kb,memory_inu_kb,general_memory_borrowed_kb,query_budget_kb
from resource_pool_status
where running_query_count > 0
order by pool_name,node_name;
--查询⼀个表有哪个⽤户有哪些权限
lect grantor,grantee,privileges_description from grants where object_name='ROI_USER'
--杀锁
SELECT transaction_id FROM locks; ---查看transaction_id
SELECT * FROM ssions where transaction_id = <>;
SELECT INTERRUPT_STATEMENT（'<ssion_id>'，'<statement_id>'）;
