pgsql自动生成时间序列
按天
SELECT
to_char ( c, 'yyyyMMdd' ) AS AllDate
FROM
generate_series (to_timestamp('2019-08-21', 'yyyy-MM-dd'),to_timestamp('2020-04-12', 'yyyy-MM-dd'),'1 day' ) AS c
按月
SELECT
to_char ( c, 'yyyyMM' ) AS AllDate
FROM
generate_series (CURRENT_TIMESTAMP - INTERVAL '5 MONTH', CURRENT_TIMESTAMP,'1 month' ) AS c
pgsql做累加
SELECT
t1."日期",
SUM(t1."储值金额") as "当日储值金额",
SUM(sum(t1."储值金额")) OVER (ORDER BY t1."日期" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT row) "储值金额"
FROM
(
SELECT
a.AllDate as "日期",
COALESCE(b.normal_recharge_amt,0) AS "储值金额"
FROM
(SELECT
to_char ( c, 'yyyyMMdd' ) AS AllDate
FROM
generate_series (to_timestamp('2019-08-21', 'yyyy-MM-dd'),to_timestamp('2020-04-12', 'yyyy-MM-dd'),'1 day' ) AS c
) a
LEFT JOIN
datav.dv_onebase_realtime_yy_rb_member_balance_record_change as b
on a.AllDate=b.day_id
) t1
group by t1."日期"