1.时间
字符串转时间/时间戳
select to_date(‘2018-03-08 18:55:33’,‘yyyy-MM-dd hh24:mi:ss’)
select to_timestamp(‘2018-03-08 18:55:33’,‘yyyy-MM-dd hh24:mi:ss’) from
2.schema
关系型数据库中存储数据，首先必须定义schema“模式”，也就是用一种预定义结构向数据库说明：要有哪些表格，表中有哪些列，每一列都存放何种类型的数据。必须先定义好模式，然后才能存放数据。nosql型数据库与之相反
3.split集合
1.–select regexp_split_to_table(‘yaosting\test\split’,’\’) ;
| regexp_split_to_table |
|---|
| yaosting |
| test |
| split |
2.–select (regexp_split_to_array(‘yaosting\test\split’,’\’ ));
| regexp_split_to_array |
|---|
| {yaosting,test,split} |
3. --select split_part(‘yaosting\test\split’,’’,2);
| split_part |
|---|
| test |
3.1. --select (regexp_split_to_array(‘yaosting\test\split’,’\’ ))[2];
| regexp_split_to_array |
|---|
| test |
4.coalesce(numbe,0)
coalesce(numbe,0) 函数
numbe不为null 返回原数值，为null时返回 0
解决数值+null为null的问题,当对表进行关联相加时,存在null列.
–select 2+ coalesce(null,0) as add_num
| add_num |
|---|
| 2 |
5.drop、truncate和delete的区别
TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。
参考链接
5.1 高水位线问题 vacuum [full]
频繁地delete和update操作
Postgresql之VACUUM和VACUUM FULL对比
参考链接
6.replace
regexp_replace(customer_name, ‘([a-zA-Z]+)|([@]+)|([#]+)|([*]+)’,’’)
7.数据类型转化和round
select round(cast(a/b as numeric), 2) --保留两位小数.
8.group高阶用法
SELECT
brand_id,
store_id,
round( count(*), 0 )
FROM
bas_buick_repair_order
WHERE
car_model_name IN ( ‘威朗’, ‘全新英朗’ )
GROUP BY
GROUPING SETS ( store_id, brand_id, (store_id, brand_id) );
group by grouping sets(store_id, brand_id, (store_id, brand_id)) 等价于group by store_id+group by brand_id+group by store_id,brand_id.
9. filter()~~case
select brand_id, store_id
,count(*) filter(where mileage_daily_avg_forecast>=0 and mileage_daily_avg_forecast<10) as “0~10”
from dim_dev_buick_premiles
group by
grouping sets(store_id,brand_id)
;
select brand_id,store_id
,sum(case when mileage_daily_avg_forecast>=0 and mileage_daily_avg_forecast<10 then 1 else null end) as “0~10”
from dim_dev_buick_premiles
group by
grouping sets(store_id,brand_id)
;
一次全表 扫描,filter适合所有的聚合函数，不仅仅是PG内置的的聚合函数，还支持安装扩展包的聚合函数 原文链接
10.over()
select store_id
,avg(mileage_daily_avg_forecast) over(partition by store_id)
from dim_dev_buick_premiles
over()子句又称作窗口函数，这个窗口是将where 条件获取的总的集合假想成一个窗口，并且avg(gdp) over()获取的是这个假想集的平均值。
返回行数与原表行数一致,可以简单理解为新增了一列.
原文链接
滑动窗口函数
select country_name,“year”,gdp,array_agg(“year”) over(partition by country_name order by “year” desc rows between unbounded preceding and 1 following) from country_gdp_year_final
rows between unbounded preceding and 1 following
定义的滑动窗口包含三行，当前行，当前行的前一行，当前行的后一行
结合order by 才有意义,将数据先排序,然后计算窗口内的值.
与over()相比,前者是字段级别的,后者更细,某字段的某范围值.书写规范类似于filter()
内建窗口函数
查看sql正在进行的进程、是否锁表、解表
1.查看正在进行的sql进程
SELECT
procpid,
START,
now() - START AS lap,
current_query
FROM
(
SELECT
backendid,
pg_stat_get_backend_pid (S.backendid) AS procpid,
pg_stat_get_backend_activity_start (S.backendid) AS START,
pg_stat_get_backend_activity (S.backendid) AS current_query
FROM
(
SELECT
pg_stat_get_backend_idset () AS backendid
) AS S
) AS S
WHERE
current_query <> ‘’
ORDER BY
lap DESC;
2.查看锁表的进程
select oid from pg_class where relname=‘m_ss_kjcx_tech_inovate_talent’
select pid from pg_locks where relation=‘4384913’
3.对锁表的进程进行释放
select pg_cancel_backend(线程id);
来kill掉指定的SQL语句。（这个函数只能 kill Select 查询，而updae,delete DML不生效）
使用可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作=
select pg_terminate_backend(pid int)
gpsql - 索引
索引大法好
1.需要使用的列：
经常查询的列
排序的列
连接的列
where条件的列
经常出现order by 、group by 、distinct的列
注意：定期删除无用的索引，减少数据操作的负荷
2.索引的创建
唯一索引
create index index_name_id on dbscheme.test0001(name_id);
多字段索引
create index indexs_name_traff on dbscheme.test0001(name_id,traffic);
部分索引
create index index_par_name_id on dbscheme.test0001(name_id) where name_id = ‘kk’;
表达式索引
select trim(nameid) from dbscheme.test0001 where trim(nameid) = ‘ll’
create index index_name_id on dbscheme.test0001(trim(name_id)) ;
创建指定的btree索引
create index index_name_id on dbscheme.test0001 using btree (name_id);
修改索引名称
alter index index_name_id rename to index_name_id_new
删除索引
DROP INDEX index_name_id_new;
事务的ACID属性
从我们最开始了解数据库的时候，我们都知道事务是由一个或者多个SQL语句组成，并且这些语句要么全部执行成功、要门全部执行失败(这就是原子性 Atomic),并且事务将数据库从一个一致的状态转移到另一个一致的状态(这就是一致性：consistency)，但是当数据库并发比较大的时候，多个session对于表记录进行并发操作的时候，Session之间的查询和操作是互不影响的，看上去各个事务像是一前一后串联发生(这就是隔离性:Isolation)，事务在被提交后提供一个确定一致的结果，即使出现硬件故障等，它仍然保持一个一致的状态(这就是持久性：persistence)
