数据统计用到的一些sql函数(pg)

//日期格式化
to_char(p.real_begin_date, 'YYYY-MM-DD HH:MM:SS')realBeginTime

SELECT p.id, p.project_id AS projectId, p.project_name AS projectName, p.progress AS prjprogress,
	p.customer_name AS customerName, p.leader_man AS leaderMan, u.phone AS phone, 
	u.department AS department, to_char(p.real_begin_date, 'YYYY-MM-DD HH:MM:SS')realBeginTime,
	to_char(p.plan_to_end, 'YYYY-MM-DD HH:MM:SS')planEndTime 
	FROM dd_prj_info p LEFT JOIN dd_user u ON u.id=p.leader_id 
	WHERE p.id=id
	
//geometry转text
ST_ASTEXT(g.geom)

SELECT ST_ASTEXT(g.geom) AS geom 
	FROM dd_prj_info p 
	LEFT JOIN dd_prj_geometry g ON g.project_id = p.project_id AND g.geom IS NOT NULL AND g.del=0 
	WHERE p.id="+id;
	
//获取范围中心点
ST_Centroid(geom)

SELECT ST_ASTEXT(geom) AS geom, ST_ASTEXT(ST_Centroid(geom)) AS center 
	FROM dd_prj_geometry 
	WHERE project_id =
	
//case when
SELECT t1.leader_id as leaderId, t1.workload,t4.username AS createUserName, t1.static_id AS staticId, t7.static_id AS parentStaticId, t1.name,t6.name AS prjType, 
	case when t1.parent_id is NULL then t5.prj_manager_id else t7.leader_id end AS auditerId, 
	case when t1.parent_id is NULL then t8.username else t3.username end AS auditName
	FROM prj_scheme_daily t1 
	LEFT JOIN prj_worker t2 ON t1.id = t2.scheme_daily_id AND t2.del=0 
	LEFT JOIN sys_user t4 ON t4.id = t1.create_user_id 
	LEFT JOIN prj_info t5 ON t5.id = t1.prj_id 
	LEFT JOIN prj_dict t6 ON t5.id = t5.type_id 
	LEFT JOIN prj_scheme_daily t7 ON t7.id = t1.parent_id 
	LEFT JOIN sys_user t3 ON t3.id = t7.leader_id 
	LEFT JOIN sys_user t8 ON t8.id = t5.prj_manager_id
	WHERE t1.prj_id='prjId' AND t1.del=0 
	AND (t2.member_id='userId' OR t1.leader_id = 'userId')

//点是否在范围内

SELECT t0.department, t0.createuser_name, t0.createtime,t0.project_name, ST_Contains(t0.geom, st_geometryfromtext(t0.checkingeom,4326)) from (select t3.department, t1.createuser_name, t1.createtime,t1.project_name, t2.geom, ST_ASTEXT(t1.checkingeom) as checkingeom from dd_hr_check_in t1
left join dd_prj_geometry t2 on t2.project_id = t1.project_id
left join dd_user t3 on t3.id = t1.createuser_id
where t1.project_id in (select DISTINCT(project_id) from dd_prj_geometry where del=0 and geom is not null) and t1.del=0 and t1.createtime>‘2018-01-01 00:00:00’ order by t1.createtime) t0