postgresql实现获取一周数据，没有数据填充0

postgresql实现获取一周订单数据，没有数据填充0

表格原始数据

sql1：
SELECT
to_char( date_trunc( ‘day’, t2.rtime ), ‘yyyy-MM-DD’ ) AS TIME,
SUM (t2.money ) AS “totalMoney”
FROM
aoo t2
WHERE
t2.rtime <= to_date( ‘2018-10-07’, ‘YYYY-MM-DD’ )
AND t2.rtime >= to_date( ‘2018-10-01’, ‘YYYY-MM-DD’ )
GROUP BY
TIME
结果10-02 10-06没有数据，要填充0

实现成如下效果

首先生成一个连续日期的函数用于拼接
sql2：
SELECT
to_char ( b, ‘YYYY-MM-DD’ ) AS time
FROM
generate_series ( to_timestamp ( ‘2018-10-01’, ‘YYYY-MM-DD hh24:mi:ss’ ), to_timestamp ( ‘2018-10-07’, ‘YYYY-MM-DD hh24:mi:ss’ ), ‘1 days’ ) AS b
GROUP BY TIME

此处sql2和sql1使用全连接FULL OUTER JOIN
SELECT a.time,COALESCE(b.“totalMoney”,0) as “totalMoney” from
(
SELECT
to_char ( b, ‘YYYY-MM-DD’ ) AS time
FROM
generate_series ( to_timestamp ( ‘2018-10-01’, ‘YYYY-MM-DD hh24:mi:ss’ ), to_timestamp ( ‘2018-10-07’, ‘YYYY-MM-DD hh24:mi:ss’ ), ‘1 days’ ) AS b
GROUP BY
time

) as a

FULL OUTER JOIN

(
SELECT
to_char( date_trunc( ‘day’, t2.rtime ), ‘yyyy-MM-DD’ ) AS TIME,
SUM (t2.money ) AS “totalMoney”
FROM
aoo t2
WHERE
t2.rtime <= to_date( ‘2018-10-07’, ‘YYYY-MM-DD’ )
AND t2.rtime >= to_date( ‘2018-10-01’, ‘YYYY-MM-DD’ )
GROUP BY
TIME
) as b

on a.time=b.time
order by a.time asc;

即可实现如下效果