gp数据库分月查询

with
  mon as(
      select
          mon::date mon
            from generate_series(date_trunc('year', now())::date, date_trunc('year', now())::date + '+1y -1d'::interval,'1mon'::interval) mon
  )
    select
      *
        from mon

（sql实例）

 SELECT 
 A.MONTH,
COALESCE(thnum,0)  thnum,
call_time,
phone_number,
phone_attr,
call_attr,
call_way,
call_number,
office_number,
lac,
ci,
incident_place,
longitude,
latitude,
area_address
FROM
    (
    select SUBSTRING ( '' || mon FROM 6 FOR 2 ) as MONTH from (
with
  mon as(
      select
          mon::date mon
            from generate_series(date_trunc('year', now())::date, date_trunc('year', now())::date + '+1y -1d'::interval,'1mon'::interval) mon
  )
    select
      *
        from mon) b
    ) A LEFT JOIN (
    SELECT COUNT( * ) AS thnum,
        SUBSTRING ( '' || bi.call_time FROM 6 FOR 2 ) as MONTH,
        bi.* 
    FROM
        data_bill bi 
    WHERE
        bi.phone_number = '15278013456' 
        AND bi.call_way != '上网' 
    AND     date_trunc( 'DAY', call_time ) >= to_date('2019-06-11', 'yyyy-mm-dd' ) 
  AND date_trunc( 'DAY', call_time ) <= to_date('2022-03-30', 'yyyy-mm-dd' ) 
  AND call_time >= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || '00:00:00'  AS TIMESTAMP ) 
  AND call_time <= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || '23:59:59'  AS TIMESTAMP ) 
        AND bi.call_number = '16638260556' 
        AND bi.call_duration >= '0' 
    GROUP BY
        date_trunc( 'MONTH', call_time ) ,bi.pk,bi.id,bi.call_number,bi.call_time,bi.phone_number,bi.phone_attr,bi.call_attr,bi.call_duration,bi.office_number,bi.lac,bi.ci,bi.call_way,bi.incident_place,bi.operator,bi.case_number,bi.longitude,bi.latitude,bi.area_address,bi.access_id,bi.is_public,bi.imsi,bi.call_type
    ) b ON A.MONTH = b.MONTH 
ORDER BY
    MONTH;

（在gp中获取每个月，和具有时间的进行join查询，可以获得每个月统计）

 页面展示类似这种效果

判断字段是否为空

select COALESCE(tran.rm_amt,0) rm_amt

select COALESCE(null, 0) as test

结果

test

0