每日归档： 2017年12月8日

BigDecimal计算

加法  a.add(b);

减法 a.subtract(b);

乘法 a.multiply(b);

除法 a.divide(b,需要精确到小数点以后几位,BigDecimal.ROUND_HALF_UP)

//* 保留位数、四舍五入

//1.直接去掉多余的位数 ROUND_DOWN
BigDecimal a = new BigDecimal("1.234").setScale(2, BigDecimal.ROUND_DOWN);
System.out.println(a);//1.23

//2.去掉多余的位数并进位 ROUND_UP
BigDecimal b = new BigDecimal("1.234").setScale(2, BigDecimal.ROUND_UP);
System.out.println(b);//1.24

//3.正数进位，负数舍位 ROUND_CEILING
BigDecimal c = new BigDecimal("1.234").setScale(2, BigDecimal.ROUND_CEILING);
System.out.println(c);//1.24 如果是正数，相当于BigDecimal.ROUND_UP
 
BigDecimal d = new BigDecimal("-1.234").setScale(2, BigDecimal.ROUND_CEILING);
System.out.println(d);//-1.23 如果是负数，相当于BigDecimal.ROUND_DOWN

//4.正数舍位，负数进位 ROUND_FLOOR
BigDecimal e = new BigDecimal("1.234").setScale(2, BigDecimal.ROUND_FLOOR);
System.out.println(e);//1.23 如果是正数，相当于BigDecimal.ROUND_DOWN
 
BigDecimal f = new BigDecimal("-1.234").setScale(2, BigDecimal.ROUND_FLOOR);
System.out.println(f);//-1.24 如果是负数，相当于BigDecimal.ROUND_HALF_UP

//5.四舍五入 （若舍弃部分>=.5，就进位）ROUND_HALF_UP
BigDecimal g = new BigDecimal("1.225").setScale(2, BigDecimal.ROUND_HALF_UP);
System.out.println(g); //1.23 

//6.四舍五入（若舍弃部分>.5,就进位）ROUND_HALF_DOWN
BigDecimal h = new BigDecimal("1.225").setScale(2, BigDecimal.ROUND_HALF_DOWN);
System.out.println(h);//1.22 

//7.ROUND_HALF_EVEN
BigDecimal i = new BigDecimal("1.225").setScale(2, BigDecimal.ROUND_HALF_EVEN);
System.out.println(i);//1.22 如果舍弃部分左边的数字为偶数，则作 ROUND_HALF_DOWN
 
BigDecimal j = new BigDecimal("1.215").setScale(2, BigDecimal.ROUND_HALF_EVEN);
System.out.println(j);//1.22 如果舍弃部分左边的数字为奇数，则作 ROUND_HALF_UP

postgresql创建简单搜索视图

--创建search_company
CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id,
a.ticker,
a.company_status_id,
a.company_name_cn,
a.company_name_en,
a.company_shortname_cn,
a.company_shortname_en,
b.brand_name,
a.year_founded,
c.name_cn AS company_status_name_cn,
c.name_en AS company_status_name_en
FROM ((company_profile a
LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text)))
LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text)))
WHERE a.company_status_id!='3.3'
ORDER BY a.ticker;

--导入自定义函数(扩展包)
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
create extension pg_trgm;
create or replace function record_to_text(anyelement) returns text as $$
select $1::text;
$$ language sql strict immutable;

--创建全文索引
create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops);
create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops);

--检测索引是否正常
/*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;

同步同一个表中的相关字段

--创建函数

CREATE OR REPLACE FUNCTION updateItemDDL(center_show varchar,item_ddl VARCHAR,table_name varchar,item varchar)
RETURNS
void as $$
UPDATE schema_table set center_show=$1,item_ddl=$2 WHERE "table_name"=$3 AND item=$4 AND "version"='v2';
$$ LANGUAGE 'sql' VOLATILE;

--执行函数

SELECT updateItemDDL(center_show,item_ddl,"table_name",item) from schema_table WHERE "version"='v1' AND item_ddl!='';

查询是否执行成功

SELECT "table_name",item,center_show,item_ddl from schema_table WHERE "version"='v2' AND item_ddl!=''