一条SQL实现获取每个月的第一条和最后一条数据

应用场景

假设当前有日结表结构如下

store_id	category_id	trandate	sale
门店id	品类id	交易日期	当日销售额

获取各个门店中各品类每月第一条销售记录和最后一条销售记录
插入测试数据如下

-- 数据库为postgre
SELECT * FROM
	(
	SELECT
		store_id,
		category_id,
		EXTRACT ( MONTH FROM trandate ) AS tranmonth,
		EXTRACT ( YEAR FROM trandate ) AS tranyear,
		ROW_NUMBER ( ) OVER ( PARTITION BY store_id, category_id, EXTRACT ( MONTH FROM trandate ), EXTRACT ( YEAR FROM trandate ) ORDER BY store_id, category_id, trandate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS ranks,
		-- 分组后组内第一条数据
		FIRST_VALUE ( sales ) OVER ( PARTITION BY store_id, category_id, EXTRACT ( MONTH FROM trandate ), EXTRACT ( YEAR FROM trandate ) ORDER BY store_id, category_id,trandate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS f_sale,
		-- 分组后组内最后一条数据
		LAST_VALUE ( sales ) OVER ( PARTITION BY store_id, category_id, EXTRACT ( MONTH FROM trandate ), EXTRACT ( YEAR FROM trandate ) ORDER BY store_id, category_id, trandate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS l_sale 
	FROM
		test_sales 
	) tt 
WHERE
	-- 取组内第一条数据
	tt.ranks =1

执行结果如下

如上，实际应用场景可能更加复杂，提供一个思路，仅供大家参考