pgsql笔记

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."日期"