vertica基础操作

--获取当前时间
select sysdate;
select getdate();
select now()+1;--会显示时区
--格式化日期
select to_char(sysdate(),'YYYY-MM-DD');
select year(sysdate)||'-'||month(sysdate)||'-'||day(sysdate)||'第'||quarter(sysdate)||'季度   第'||week(sysdate)||'周';
select to_char(sysdate,'hh24:mi:ss')
--转换类型
select cast('123' as integer);
--返回字符串位置
select INSTR('sdsq','s',2);
--替换字符串
SELECT Replace('abcdef', 'bcd', 'ijklmn');
--截取字符串
select substr('abcd',2,2);
--取绝对值：
select abs(-1);
--向上取整：
select ceil(-1.001);
--向下取整：
select floor(-1.001);
--取整：
select trunc(-1.002);
--四舍五入：
select round(1.23456,4.0);
--取随机数：
select random();
--数值比较：
select greatest(1,-2,4,3);
--NULL空值
select isnull(NULL ,'123');
--最大值
select max(hurr_id) from ROI_USER_ROLE rr; 
--最小值
select min(hurr_id) from ROI_USER_ROLE rr;
select PRODUCT_ID,count(1) from ROI_USER_PRODUCT rup group by PRODUCT_ID having COUNT(1)>1; 
--字符串长度
select LENGTH('12345');
--大小写转换 lower,upper
select LOWER('ABC');
select UPPER('abc');
--左右补空格
select LPAD('abcd',14, ' ') value,RPAD('abc',5,' ') from dual;
--删空格ltrim,rtrim,trim
select ltrim('   123   '),RTRIM('  12324  '),TRIM('  12343   '); 

--生成内码 如PR0001

select concat('PR',CAST(DENSE_RANK() OVER(ORDER BY PRODLINE_NAME,ROI_PRODUCT ) AS VARCHAR(10)))
from PRODUCT_INFO rpi 

--相同产品排序
select 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

--为空的赋值
select 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
select ROW_NUMBER() over(order by NAME) as ID  from ORDER_CONFIG where CONFIG_CODE='CHANNEL_COMPLETE'

--添加注释

1，先往要添加注释的表里添加一条数据。
2，查询投影分区
select projection_name from projections where anchor_table_name = 'Table';
3,添加注释
COMMENT ON COLUMN 用户.投影分区名称.字段名 IS '注释'; 
4,查询投影分区里的注释
 SELECT t3.anchor_table_name AS Table_name,
       SUBSTR (t1.object_name, INSTR (t1.object_name, '.', 1) + 1) AS Column_name,
       t1.comment AS comment
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;

--查询字段出现的表名
select * from v_catalog.columns where table_name  like  upper('%ROI%')
--查询字段在哪个表中出现
select * from v_catalog.columns 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 set not null;
--更改字段数据类型
alter table test.dim_micro_area_gsm alter column cell_id set 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;
--查询当前资源池的使用情况
select node_name,pool_name,running_query_count as running,memory_size_kb,memory_inuse_kb,general_memory_borrowed_kb,query_budget_kb
from resource_pool_status
where running_query_count > 0
order by pool_name,node_name;
--查询一个表有哪个用户有哪些权限
select grantor,grantee,privileges_description from grants where object_name='ROI_USER'
--杀锁
SELECT transaction_id FROM locks; ---查看transaction_id
SELECT * FROM sessions where transaction_id = <>;
SELECT INTERRUPT_STATEMENT（'<session_id>'，'<statement_id>'）;