EC3-37 用accumulate或for_each来统计区间

博客介绍了在需要灵活自定义统计区间时,可使用accumulate或for_each。阐述了accumulate的简单用法,强调第三个参数初始值类型要和统计类型相同,还举例说明其使用自定义统计函数的情况,同时提到for_each也能实现相同功能。

需要用一些灵活的自定义的方式统计区间时可以用accumulate或for_each(accumulate在中)。
accumulate的简单用法:

vector<int> v = {1, 2, 3, 4, 5};
int sum = accumulate(v.begin(), v.end(), 0);//求和 sum=15

需要注意的是,第三个参数为初始值,它的类型需要和统计的类型相同,例如:

vector<double> v = {1.1, 2.2};
int sum = accumulate(v.begin(), v.end(), 0.0);//正确 sum = 3.300000

下面是错误的例子,第三个参数为int 0,函数则以int型保存结果,导致最终sum=3.0,且编译运行正常:

vector<double> v = {1.1, 2.2};
int sum = accumulate(v.begin(), v.end(), 0);//错误! sum = 3.000000

accumulate也可以使用自定义的统计函数,例如求区间的积(使用标准
multiplies仿函数类):

vector<double> v = {1.1, 2.0};
double sum = accumulate(v.begin(), v.end(), 1.0, multiplies<double>());

复杂一些的例子,计算二维点的平均值,Point定义如下:

struct Point 
{
	Point(double initX, double initY): x(initX), y(initY) {}
	double x, y;
};

调用accumulate:

vector<Point> v = {Point(1, 2), Point(3, 2), Point(-2, 4)};
Point avg = accumulate(v.begin(), v.end(), Point(0, 0), PointAverage());

PointAverage是仿函数类的对象,定义如下:

class PointAverage
{
	public:
	PointAverage(): numPoints(0), xSum(0), ySum(0) {}
	const Point operator()(const Point& avgSoFar, const Point& p) 
	{
		++numPoints;
		xSum += p.x;
		ySum += p.y;
		return Point(xSum/numPoints, ySum/numPoints);
	}
	private:
	size_t numPoints;
	double xSum;
	double ySum;
};

用for_each也可以实现同样的功能,调用:

Point avg = for_each(v.begin(), v.end(), PointAverage()).result();

PointAverage代码:

class PointAverage:
public unary_function<Point, void> 
{
	public:
	PointAverage(): xSum(0), ySum(0), numPoints(0) {}
	void operator()(const Point& p)
	{
		++numPoints;
		xSum += p.x;
		ySum += p.y;
	}
	Point result() const
	{
		return Point(xSum/numPoints, ySum/numPoints);
	}
	private:
	size_t numPoints;
	double xSum;
	double ySum;
};
<think>嗯,用户想在PostgreSQL里给一个表新增两个累计字段,分别是this_quantity_accumulate和total_quantity_accumulate,然后按月份从年初累计到当前月份。这需要怎么做呢?首先,我得确认用户的需求。他们需要根据allocation_month字段按月累计汇总this_quantity和total_quantity,然后把结果存储到新增的两个字段里。也就是说,每个月的累计值是从1月到该月的总和。比如,3月的累计值就是1月、2月、3月的总和。接下来,我需要考虑如何实现这个需求。步骤大概分为三步:添加新字段、计算累计值、更新数据。但具体怎么操作呢?添加字段应该用ALTER TABLE语句,没问题。那计算累计值的话,可能需要窗口函数,比如SUM()OVER,按月份排序来累加。但要注意,年份可能不同,所以需要按年份分区,否则不同年份的月份会混在一起。比如2023年1月和2024年1月应该分开累计。然后,更新数据的时候,需要用UPDATE语句,结合窗口函数来计算每个记录的累计值。这里可能需要用到子查询者CTE(公共表表达式)来生成累计值,然后更新对应的字段。不过,用户提到的是“从年初到当前月份”,所以必须确保allocation_month的格式是能被正确排序的,比如使用日期类型者YYYY-MM格式的字符串,这样按月份排序才会正确。如果allocation_month存储的是字符串,可能需要转换为日期者使用字符串比较来确保排序正确。 还有一个问题,如何按年份分区。假设allocation_month字段包含年份信息,比如&#39;2023-01&#39;,那么可以提取年份部分作为分区依据。否则,如果只有月份而没有年份,可能会导致跨年累计的错误。所以用户需要确保allocation_month包含年份,者在表中另有年份字段。 假设allocation_month是包含年份的,比如&#39;YYYY-MM&#39;格式,那么窗口函数可以按年份分区,按月份排序。例如:SUM(this_quantity) OVER(PARTITION BYyear PARTITION ORDERBY allocation_month)不过,如果allocation_month本身是字符串,可能需要转换为日期来提取年份,者在字段设计时已经包含年份。者,用户可能将allocation_month存储为DATE类型,比如每个月的第一天,这样可以直接提取年份。假设用户的数据结构里allocation_month是DATE类型,存储每个月的第一天,比如&#39;2023-01-01&#39;,那么可以这样提取年份:EXTRACT(YEARFROM allocation_month)。这样窗口函数就可以按年份分区,按月份排序。然后,更新语句可能的结构是:WITH cte AS(SELECT id,SUM(this_quantity) OVER(PARTITION BYEXTRACT(YEARFROM allocation_month)ORDER BYallocation_month) ASthis_acc,SUM(total_quantity) OVER(PARTITION BYEXTRACT(YEARFROM allocation_month)ORDER BYallocation_month) AStotal_accFROM table_name) UPDATEtable_nameSET this_quantity_accumulate =cte.this_acc,total_quantity_accumulate =cte.total_accFROMcteWHEREtable_name.id =cte.id;不过,这里需要注意,如果表没有唯一标识符(如id),可能需要使用其他方式关联。者,使用关联条件基于所有字段,但这样可能效率不高。所以最好表有一个主键。另外,如果用户的数据量很大,这样的UPDATE可能会比较慢,需要考虑分批处理者使用更优化的方法。但用户可能只是需要一次性更新,所以暂时可以忽略性能问题,先实现功能。另外,用户提到的是“存储到新增的字段”,所以可能需要先添加字段,然后计算并更新。者,可以在添加字段的同时设置默认值,但默认值可能无法使用窗口函数,所以还是需要后续的UPDATE。 最后,生成相关的SQL语句,并测试是否正确。比如,添加字段的ALTERTABLE语句是否正确,窗口函数的分区和排序是否正确,UPDATE语句是否正确关联记录。 可能的错误点包括:allocation_month的格式不正确导致排序错误,没有按年份分区导致跨年累计,者UPDATE语句中的关联条件不正确,导致更新错误的数据。 另外,用户可能需要考虑是否需要使用ROWS BETWEENUNBOUNDEDPRECEDINGAND CURRENTROW,但默认的窗口框架就是这样的,所以可能不需要显式指定。总结步骤: 1.添加两个累计字段:ALTERTABLE table_name ADDCOLUMNthis_quantity_accumulatenumeric;ALTER TABLEtable_nameADD COLUMN total_quantity_accumulate numeric; 2.使用窗口函数计算累计值,按年份分区,按月份排序:使用CTE来获取每个记录的累计值,然后通过UPDATE将结果写入新字段。 注意,如果allocation_month是字符串类型,比如&#39;2023-01&#39;,那么排序可能需要转换为日期者确保字符串的排序正确。例如,使用TO_DATE(allocation_month,&#39;YYYY-MM&#39;)来转换为日期,者在ORDER BY子句中直接使用allocation_month,前提是字符串的格式能正确排序。另外,如果allocation_month是整数类型,比如202301代表2023年1月,那么需要先转换为年份和月份,者按数值排序。但这种情况可能需要提取年份,比如用SUBSTRING(CAST(allocation_monthAS text),1,4)来获取年份部分,然后按数值排序。假设用户使用的是日期类型,者能够正确排序的字符串格式,那么窗口函数可以正确工作。最后,给出示例SQL语句,并提醒用户注意数据备份和测试。</think>根据您的要求,可以通过以下步骤实现字段添加和累计值计算: ### 一、添加字段 ```sql ALTER TABLE your_table ADD COLUMN this_quantity_accumulate numeric, ADD COLUMN total_quantity_accumulate numeric; ``` ### 二、计算累计值并更新 ```sql WITH yearly_accumulation AS ( SELECT id, SUM(this_quantity) OVER ( PARTITION BY EXTRACT(YEAR FROM allocation_month) ORDER BY allocation_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS calc_this_acc, SUM(total_quantity) OVER ( PARTITION BY EXTRACT(YEAR FROM allocation_month) ORDER BY allocation_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS calc_total_acc FROM your_table ) UPDATE your_table SET this_quantity_accumulate = y.calc_this_acc, total_quantity_accumulate = y.calc_total_acc FROM yearly_accumulation y WHERE your_table.id = y.id; ``` ### 三、技术要点解析 1. **窗口函数**:`SUM() OVER`配合`PARTITION BY`按年份分组,`ORDER BY allocation_month`确保按月排序 2. **时间处理**:`EXTRACT(YEAR FROM allocation_month)`确保跨年数据独立计算 3. **更新策略**:通过CTE预计算累计值后批量更新,比逐行更新效率更高 4. **排序控制**:`ROWS BETWEEN`子句显式声明累计范围(可省略,默认即此行为) ### 四、数据验证示例 假设原始数据: | allocation_month | this_quantity | total_quantity | |-------------------|---------------|----------------| | 2023-01-01 | 100 | 200 | | 2023-02-01 | 150 | 250 | 更新后结果: | allocation_month | this_quantity_accumulate | total_quantity_accumulate | |-------------------|---------------------------|----------------------------| | 2023-01-01 | 100 | 200 | | 2023-02-01 | 250 | 450 | ### 五、维护建议 1. 新建索引加速查询: ```sql CREATE INDEX idx_year_month ON your_table (EXTRACT(YEAR FROM allocation_month), allocation_month); ``` 2. 定期执行维护: ```sql -- 每月初执行 REFRESH MATERIALIZED VIEW CONCURRENTLY your_mview; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值