일별, 월별 통계 구하기

월별통계 (2009-01 ~ 2010-01)

SELECT 
  COUNT(*) as count, 
  to_char(created_date, 'YYYY-MM') as created_date 
FROM 
  table_name
WHERE 
  created_date>='2009-01-01' AND created_date<'2010-01-01'
GROUP BY 
  to_char(created_date, 'YYYY-MM');

일별통계 (2009-04-01 ~ 2009-04-30)

SELECT 
  COUNT(*) as count, 
  to_char(created_date, 'YYYY-MM-DD') as created_date 
FROM 
  table_name
WHERE 
  created_date>='2009-04-01' AND created_date<'2009-05-01'
GROUP BY 
  to_char(created_date, 'YYYY-MM-DD');

---

postgresql 

 

<select id="custSalesPriceSumList" parameterType="java.util.Map" resultType="hashMap">
    /* custDAO.custSalesPriceSumList */

    select  
        prd_id,
        cust_nm,
        prd_nm,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'01', prd_id , cust_id  ) , 0 ) as month1,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'02', prd_id , cust_id  ) , 0 ) as month2,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'03', prd_id , cust_id  ) , 0 ) as month3,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'04', prd_id , cust_id  ) , 0 ) as month4,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'05', prd_id , cust_id  ) , 0 ) as month5,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'06', prd_id , cust_id  ) , 0 ) as month6,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'07', prd_id , cust_id  ) , 0 ) as month7,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'08', prd_id , cust_id  ) , 0 ) as month8,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'09', prd_id , cust_id  ) , 0 ) as month9,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'10', prd_id , cust_id  ) , 0 ) as month10,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'11', prd_id , cust_id  ) , 0 ) as month11,
        coalesce( fn_get_sales_total('license' ,#{searchYear},'12', prd_id , cust_id  ) , 0 ) as month12
    FROM( 
        SELECT  
            B.cust_id ,
            A.prd_id ,
            fn_get_name('CUST', B.cust_id) as cust_nm,   
            fn_get_name('PRD', A.prd_id) as prd_nm  
        from tn_license as A  
        left outer join tn_contract as B
        on A.sale_cd = B.sale_cd 
        where split_part(A.lic_bgng_de::varchar, '-', 1)  = #{searchYear}
        group by B.cust_id , A.prd_id , split_part(A.lic_bgng_de::varchar, '-', 1) <!-- 라이센스 시작일 기준 -->
    ) as SAL WHERE 1=1  
    <if test='!searchPrd.equals("") and searchPrd != null '>
        <if test='!searchPrd.equals("ALL")'>
          AND prd_id = #{searchPrd}
        </if> 
    </if>  
    <if test='!searchValue.equals("") and searchValue != null'>
        AND ${searchField}::text ILIKE '%${searchValue}%'
    </if>
</select>

프로시져

CREATE OR REPLACE FUNCTION public.fn_get_sales_total(v_type character varying, v_sales_year character varying, v_sales_month character varying, v_prd_id character varying, v_cust_id character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$

DECLARE  
	V_RSLT int4; 
BEGIN
 	IF V_SALES_YEAR IS NULL THEN
		V_RSLT := '';
	ELSE
	
		-- 라이센스 계약금액 합계
		IF V_TYPE = 'license' then 
			select   
				gramt INTO V_RSLT
			from (  
				SELECT   
					sum( coalesce( A.gramt , 0 ) )	 as gramt
				from tn_license as A  
				left outer join tn_contract as B
				on A.sale_cd = B.sale_cd 
				where A.prd_id = v_prd_id and B.cust_id = v_cust_id and to_char(A.frst_reg_dt, 'YYYY-MM')  = concat(v_sales_year, '-', v_sales_month) 
				group by B.cust_id , A.prd_id , to_char(A.frst_reg_dt, 'YYYY-MM') 
			) as SAL;
			 
		ELSE
			V_RSLT := '';
		END IF;
	END IF;

	RETURN V_RSLT;	 
END;
$function$
;

결과