Greenplum计算每月消耗的基本语法

最新推荐文章于 2024-03-12 10:52:57 发布

Virgil_Ko

最新推荐文章于 2024-03-12 10:52:57 发布

阅读量378

点赞数

分类专栏： Greenplum

本文链接：https://blog.csdn.net/m0_38105675/article/details/86468753

版权

Greenplum 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SELECT
	dtime.sysmonth,
	opos.orderby_person,
CASE
		
		WHEN opos."消耗金额" IS NULL THEN
		0 ELSE opos."消耗金额" 
	END 
	FROM
		(
		SELECT
			DISTINCTto_char ( onday, 'yyyymm' ) sysmonthFROM (
			SELECT cast
				( '2015-01-01 00:00:00' AS TIMESTAMP ) + ( c || 'day' ) :: INTERVAL AS onday 
			FROM
				generate_series ( 0, 10300 ) AS tab ( c ) 
			) tb 
		WHERE
			onday <= CURRENT_DATEORDER BY TO_CHAR( onday, 'yyyymm' ) 
		) dtime
		LEFT OUTER JOIN ( SELECT * FROM dm.dm_operation_per_order_statistics ) opos ON opos.sh_month = dtime.sysmonth
ORDER BY
	dtime.sysmonth