qq-gist

select
	cast((oi * sh.star_rating) as float4) as score,
	-- oi as score, 
	sh.htl_id as htl_id,
    sh.htl_name as htl_name,
    hoteldb.get_htl_enames(cast(sh.htl_id as text)) as htl_ename,
	sh.star_rating as star_rating,
	sh.geo_info -> 'Country' ->> 'Name' as country_name,
	sh.geo_info -> 'Province' ->> 'Name' as provice_name,
	sh.geo_info -> 'City' ->> 'Name' as city_name,
	sh.geo_info -> 'Area' ->> 'Name' as area_name,
	to_json(sh.geo_info -> 'BusinessDistrict') as business_disttrict,
	-- sh.geo_info,
	sh.geo_info ->> 'Address' as addr,
	sh.pics::json as pics,
	ts_headline('highlight_cfg', htl_name, websearch_to_tsquery('highlight_cfg', $2) , 'StartSel = <hit>, StopSel = </hit>')  as htl_name_hits,
	ts_headline('english', ename, phraseto_tsquery('english', $1) , 'StartSel = <hit>, StopSel = </hit>') as htl_ename_hits
	--sh.htl_features::text as htl_features
from
	hoteldb.cts_ctrip_hotels sh,
	to_tsquery('jiebacfg', translate(cast (websearch_to_tsquery('xsyn_jiebacfg', $1) as text), '&', '|')) as ji,
	hoteldb.get_htl_enames(sh.htl_id) ename,
	plainto_tsquery('english', $1) enqry,
	phraseto_tsquery('english', $1) penqry,
	plainto_tsquery('english', '"' || $1 || '"') aenqry,
	GREATEST( 
		(   sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), ji, 1) * 1.2)) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), websearch_to_tsquery('xsyn_jiebacfg', $2), 1)) * 8.0) +
			--((sh.room_qty+1) * 1.25) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.geo_info -> 'Country' ->> 'Name'), to_tsquery('xsyn_jiebacfg', '日本') , 1))) * 1.25) ,
		(   sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), ji, 1) * 2.168)) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), websearch_to_tsquery('xsyn_jiebacfg', $2), 1)) * 8.0) +
			--((sh.room_qty+1) * 1.25) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.geo_info -> 'Province' ->> 'Name'), to_tsquery('xsyn_jiebacfg', '台灣') , 1))) * 1.35), 
		(   sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), ji, 1) * 1.2)) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.htl_name), websearch_to_tsquery('xsyn_jiebacfg', $2), 1)) * 8.0) +
			--((sh.room_qty+1) * 1.25) + 
			sqrt((ts_rank_cd(to_tsvector('xsyn_jiebacfg', sh.geo_info -> 'Country' ->> 'Name'), to_tsquery('xsyn_jiebacfg', '泰國') , 1))) * 1.15),
		( 	sqrt(ts_rank_cd(to_tsvector('english', ename), enqry, 1)) * 3.5 ),
		( 	sqrt(ts_rank_cd(to_tsvector('english', ename), penqry, 1)) * 10 ),
		( 	sqrt(ts_rank_cd(to_tsvector('english', ename), aenqry, 1)) * 10 )
	) as oi
where 
	sh.star_rating >= 3 and 
	((ji @@ to_tsvector('xsyn_jiebacfg', htl_name)) or 
	(enqry @@ to_tsvector('english', hoteldb.get_htl_enames(sh.htl_id))))
order by
	oi * sh.star_rating desc,
	oi desc
--	sh.star_rating desc
limit 50;