写SQL语句计算采购指定日期前的移动加权平均单价

多次采购同一产品，数量不同价格有差异，如何写SQL语句计算采购指定日期前的移动加权平均单价？？

采购日期采购单价采购物料编码采购数量
2021-09-01 00:00:00.000 68.7168140000 11010101010001 1199.2000000000
2021-09-01 00:00:00.000 68.7168140000 11010101010001 1198.2000000000
2021-09-01 00:00:00.000 68.7168140000 11010101010001 1193.2000000000
2021-09-01 00:00:00.000 68.7168140000 11010101010001 1192.2000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 1053.3000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 1058.3000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 1059.0000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 1055.9000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 846.6000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 827.8000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 828.5000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 830.4000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 831.8000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 746.5000000000
2021-09-04 00:00:00.000 75.8761060000 11010101010001 748.9000000000
2021-09-14 00:00:00.000 77.0265490000 11010101010001 711.1000000000
2021-09-14 00:00:00.000 77.0265490000 11010101010001 771.0000000000
2021-09-14 00:00:00.000 77.0265490000 11010101010001 986.8000000000
2021-09-17 00:00:00.000 71.8407080000 11010101010001 1159.2000000000
2021-09-17 00:00:00.000 71.8407080000 11010101010001 1184.2000000000
2021-09-17 00:00:00.000 71.8407080000 11010101010001 1183.2000000000
2021-09-27 00:00:00.000 72.2743360000 11010101010001 401.5000000000
2021-09-27 00:00:00.000 71.8141590000 11010101010001 713.7000000000
2021-09-27 00:00:00.000 71.8141590000 11010101010001 1112.2000000000
2021-09-27 00:00:00.000 71.8141590000 11010101010001 1131.2000000000
2021-09-27 00:00:00.000 71.8141590000 11010101010001 1130.2000000000
2021-10-18 00:00:00.000 71.8407080000 11010101010001 514.2000000000
2021-10-18 00:00:00.000 71.1150440000 11010101010001 1169.2000000000
2021-10-18 00:00:00.000 71.1150440000 11010101010001 879.9000000000
2021-10-18 00:00:00.000 71.1150440000 11010101010001 1210.2000000000
2021-10-18 00:00:00.000 71.1150440000 11010101010001 906.9000000000
2021-11-15 00:00:00.000 0.0000010000 11010101010001 646.0000000000
2021-11-23 00:00:00.000 76.6371680000 11010101010001 4760.5000000000
2021-12-08 00:00:00.000 73.1504420000 11010101010001 1216.7000000000
2021-12-08 00:00:00.000 73.1504420000 11010101010001 1053.7000000000
2021-12-08 00:00:00.000 73.1504420000 11010101010001 1053.2000000000
2021-12-14 00:00:00.000 72.5132740000 11010101010001 1017.4000000000
2021-12-14 00:00:00.000 72.5132740000 11010101010001 1019.8000000000
2021-12-14 00:00:00.000 72.5132740000 11010101010001 1039.6000000000
2021-12-14 00:00:00.000 73.0884960000 11010101010001 1217.2000000000
2021-12-23 00:00:00.000 69.4867260000 11010101010001 691.1000000000
2021-12-28 00:00:00.000 69.4867260000 11010101010001 1614.3500000000
2021-12-28 00:00:00.000 72.2035400000 11010101010001 3.6500000000
2021-12-28 00:00:00.000 69.4867260000 11010101010001 2478.2000000000
2021-12-28 00:00:00.000 69.4867260000 11010101010001 422.7000000000
2021-12-28 00:00:00.000 69.4867260000 11010101010001 235.3000000000
2022-01-12 00:00:00.000 75.6460180000 11010101010001 2799.6000000000
2022-05-04 00:00:00.000 76.2035400000 11010101010001 2905.2200000000
2022-05-05 00:00:00.000 81.2831860000 11010101010001 149.9000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 1355.9000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 1234.0000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 803.4000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 928.7000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 1486.6000000000
2022-05-27 00:00:00.000 76.9469030000 11010101010001 1489.7000000000
2022-05-27 00:00:00.000 76.9469030000 11010101010001 1262.6000000000
2022-05-27 00:00:00.000 76.9469030000 11010101010001 1264.7000000000
2022-05-27 00:00:00.000 78.4867260000 11010101010001 315.8000000000
2022-06-23 00:00:00.000 77.6283190000 11010101010001 310.4500000000
2022-06-23 00:00:00.000 77.6283190000 11010101010001 4221.1000000000
2022-06-23 00:00:00.000 76.9469030000 11010101010001 2795.0500000000
2022-06-23 00:00:00.000 77.6283190000 11010101010001 129.7500000000
2022-08-05 00:00:00.000 70.1769910000 11010101010001 1190.6500000000
2022-09-28 00:00:00.000 70.1769910000 11010101010001 191.4000000000
2022-11-16 00:00:00.000 68.8761060000 11010101010001 2329.1000000000
2023-02-02 00:00:00.000 69.1238940000 11010101010001 4990.2000000000
2023-02-17 00:00:00.000 71.2035400000 11010101010001 1119.4700000000
2023-02-17 00:00:00.000 71.2743360000 11010101010001 1191.1700000000
2023-03-13 00:00:00.000 71.0442480000 11010101010001 8851.6000000000
2023-04-20 00:00:00.000 71.4159290000 11010101010001 634.7400000000
2023-04-20 00:00:00.000 71.4159290000 11010101010001 1153.1000000000
2023-04-20 00:00:00.000 71.4159290000 11010101010001 1159.3000000000
2023-05-11 00:00:00.000 73.8053100000 11010101010001 3740.5000000000
2023-06-02 00:00:00.000 71.7433630000 11010101010001 2802.1000000000
2023-06-14 00:00:00.000 71.4690270000 11010101010001 1084.1000000000
2023-06-20 00:00:00.000 69.4513270000 11010101010001 2067.5000000000
2023-07-18 00:00:00.000 70.6460180000 11010101010001 6186.4000000000
2023-08-14 00:00:00.000 71.6371680000 11010101010001 3643.3000000000
2023-09-01 00:00:00.000 73.6637170000 11010101010001 5077.1900000000
2023-09-05 00:00:00.000 73.6637170000 11010101010001 3107.6000000000
2023-10-05 00:00:00.000 74.0176990000 11010101010001 909.1000000000
2023-10-11 00:00:00.000 73.0176990000 11010101010001 4147.4000000000
2023-10-11 00:00:00.000 73.2123890000 11010101010001 1181.7000000000
2023-11-02 00:00:00.000 72.5309730000 11010101010001 5452.9000000000

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

3条回答默认最新

码农阿豪 Java领域优质创作者 2024-04-07 21:18

关注

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

首先，移动加权平均单价的计算公式为：移动加权平均单价 = Σ（采购单价 × 采购数量）/ Σ采购数量所以，我们需要先按照采购日期升序排序，然后依次计算每个采购记录的移动加权平均单价，最后返回指定日期前的移动加权平均单价，即最后一个采购记录的移动加权平均单价。 SQL语句如下：

SELECT 
  SUM(采购单价 * 采购数量) / SUM(采购数量) AS 移动加权平均单价
FROM 
  采购记录表 
WHERE 
  采购日期 <= '指定日期'
ORDER BY 
  采购日期 ASC;

案例代码： Suppose we have a table called purchase_records and the SQL statement to create this table is as follows:

CREATE TABLE purchase_records (
    采购日期 datetime,
    采购单价 decimal(18, 10),
    采购物料编码 varchar(20),
    采购数量 decimal(18, 10)
);
INSERT INTO purchase_records VALUES
    ('2021-09-01 00:00:00.000', 68.7168140000, '11010101010001', 1199.2000000000),
    ('2021-09-01 00:00:00.000', 68.7168140000, '11010101010001', 1198.2000000000),
    ('2021-09-01 00:00:00.000', 68.7168140000, '11010101010001', 1193.2000000000),
    ('2021-09-01 00:00:00.000', 68.7168140000, '11010101010001', 1192.2000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 1053.3000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 1058.3000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 1059.0000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 1055.9000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 846.6000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 827.8000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 828.5000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 830.4000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 831.8000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 746.5000000000),
    ('2021-09-04 00:00:00.000', 75.8761060000, '11010101010001', 748.9000000000),
    ('2021-09-14 00:00:00.000', 77.0265490000, '11010101010001', 711.1000000000),
    ('2021-09-14 00:00:00.000', 77.0265490000, '11010101010001', 771.0000000000),
    ('2021-09-14 00:00:00.000', 77.0265490000, '11010101010001', 986.8000000000),
    ('2021-09-17 00:00:00.000', 71.8407080000, '11010101010001', 1159.2000000000),
    ('2021-09-17 00:00:00.000', 71.8407080000, '11010101010001', 1184.2000000000),
    ('2021-09-17 00:00:00.000', 71.8407080000, '11010101010001', 1183.2000000000),
    ('2021-09-27 00:00:00.000', 72.2743360000, '11010101010001', 401.5000000000),
    ('2021-09-27 00:00:00.000', 71.8141590000, '11010101010001', 713.7000000000),
    ('2021-09-27 00:00:00.000', 71.8141590000, '11010101010001', 1112.2000000000),
    ('2021-09-27 00:00:00.000', 71.8141590000, '11010101010001', 1131.2000000000),
    ('2021-09-27 00:00:00.000', 71.8141590000, '11010101010001', 1130.2000000000),
    ('2021-10-18 00:00:00.000', 71.8407080000, '11010101010001', 514.2000000000),
    ('2021-10-18 00:00:00.000', 71.1150440000, '11010101010001', 1169.2000000000),
    ('2021-10-18 00:00:00.000', 71.1150440000, '11010101010001', 879.9000000000),
    ('2021-10-18 00:00:00.000', 71.1150440000, '11010101010001', 1210.2000000000),
    ('2021-10-18 00:00:00.000', 71.1150440000, '11010101010001', 906.9000000000),
    ('2021-11-15 00:00:00.000', 0.0000010000, '11010101010001', 646.0000000000),
    ('2021-11-23 00:00:00.000', 76.6371680000, '11010101010001', 4760.5000000000),
    ('2021-12-08 00:00:00.000', 73.1504420000, '11010101010001', 1216.7000000000),
    ('2021-12-08 00:00:00.000', 73.1504420000, '11010101010001', 1053.7000000000),
    ('2021-12-08 00:00:00.000', 73.1504420000, '11010101010001', 1053.2000000000),
    ('2021-12-14 00:00:00.000', 72.5132740000, '11010101010001', 1017.4000000000),
    ('2021-12-14 00:00:00.000', 72.5132740000, '11010101010001', 1019.8000000000),
    ('2021-12-14 00:00:00.000', 72.5132740000, '11010101010001', 1039.6000000000),
    ('2021-12-14 00:00:00.000', 73.0884960000, '11010101010001', 1217.2000000000),
    ('2021-12-23 00:00:00.000', 69.4867260000, '11010101010001', 691.1000000000),
    ('2021-12-28 00:00:00.000', 69.4867260000, '11010101010001', 1614.3500000000),
    ('2021-12-28 00:00:00.000', 72.2035400000, '11010101010001', 3.6500000000),
    ('2021-12-28 00:00:00.

报告相同问题？

关注问题

计算出每年在校人数,写出SQL语句 sql 数据库
2023-04-24 21:09

回答 6 已采纳 mysql，我试试吧
项目开发中，用java进行复杂逻辑计算合适呢还是写sql语句计算合适 java sql 数据库算法
2017-11-15 07:56

回答 4 已采纳这个要根据情况而定的，一般取决因素是工作量和可维护性、运行效率等因素，祝你好运。
关于计算当年每个月年累计的SQL语句的写法 sql
2020-07-09 10:16

回答 3 已采纳 select b.month,sum(a.value) as value from table a, table b where a.month <= b.month group by b.mo
无服务器计算：云计算的下一阶段
2021-06-19 20:30

阿川2015的博客该文阐述了对无服务器计算（Serverless Computing）的概念解读及其对于全球云计算产业的意义。该文的9位联合作者认为，无服务器计算将是云计算的下一个阶段或下一个主导形态。 2010年，我们（本文作者）中的一些人...
sql语句怎么显示最后一次记录覆盖到后面所有的日期 sql
2021-07-29 14:40

回答 2 已采纳你就说可以不可以 DROP TABLE #TempA,#TempB,#TempC CREATE TABLE #TempA (SJ Date) CREATE TABLE #TempB (UID INT,
SQL计算两个日期时间差，去除周末和节假日，结果以小时显示 sql
2021-08-06 13:53

回答 2 已采纳像现在做到了去除周末和节假日，但是效果并不是以小时制的显示，改怎么才能以小时制显示呢
SQL 周期计算消除重复日期 sql
2018-04-25 07:39

回答 3 已采纳合不合理要看你对消费周期怎么定义，这个应该不是技术实现讨论的范畴如果你觉得两次消费只计一次比较合理，那就用dinstinct只保留一条记录 select avg(t.消费周期) from( S
数据分析侠A的成长故事
2016-03-09 10:46

代立冬的博客数据分析侠A的成长故事面包君同学A：22岁，男，大四准备实习，计算机专业，迷茫期作为一个很普通的即将迈入职场的他来说，看到周边的同学都找了技术开发的岗位，顿觉自己很迷茫，因为自己不是那么喜欢钻研写代码，...
用sql语句统计学校已开设的课程门数 sql sqlserver
2022-04-30 21:22

回答 1 已采纳 select count(cname) as 开设课程数 from course以上输入运行即可
sql 语句问题查询重复日期的数据 sql
2017-10-30 04:56

回答 4 已采纳三个字段假设名称为name,time,info select t1.* from 表名 t1 inner join (select name,time from 表名 group by nam
SQL语句，错误：指定了非布尔类型的表达式 sql 其他开发语言
2021-05-22 20:17

回答 2 已采纳第一个WHERE后面只有字段名，没有逻辑表达式呀，当然报这个错误了。需要INNER　JOIN SELECT COUNT(DISTINCT Takes.ID) FROM Takes
数据分析面试题（2023.09.08）
2023-09-08 17:31

海拉鲁的小厨娘的博客存储过程在业务比较复杂的时候非常实用，比如很多时候我们完成一个操作可能需要写一大串SQL语句，这时候我们可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，...
SQL里两个count语句相除怎么除啊 sql
2022-03-14 16:27

回答 3 已采纳会不会是因为除出来的结果小于1，才显示0的啊
系统集成项目管理工程师笔记
2021-10-26 22:58

@北海怪兽的博客 460 14.1.2 采购管理的主要过程 460 14.2 编制采购计划 462 14.2.1 编制采购计划的输入、输出 463 14.2.2 用于编制采购计划过程的技术和方法 472 14.2.3 工作说明书 473 14.3 实施采购 474 14.3.1 实施采购的输入 ...
算法面试必备-----数据分析常见面试题
2020-08-06 09:43

Avery123123的博客算法面试必备-----数据分析常见面试题算法面试必备-----数据分析常见...问题：mysql中设置row number问题：如何写SQL求出中位数平均数和众数（除了用count之外的方法）机器学习问题问题：如何避免决策树过拟合？问题
没有解决我的问题, 去提问

问题事件

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
创建了问题 4月7日

悬赏问题

¥16 mybatis的代理对象无法通过@Autowired装填
¥15 可见光定位matlab仿真
¥15 arduino 四自由度机械臂
¥15 wordpress 产品图片 GIF 没法显示
¥15 求三国群英传pl国战时间的修改方法
¥15 matlab代码代写，需写出详细代码，代价私
¥15 ROS系统搭建请教（跨境电商用途）
¥15 AIC3204的示例代码有吗，想用AIC3204测量血氧，找不到相关的代码。
¥20 CST怎么把天线放在座椅环境中并仿真
¥15 任务A：大数据平台搭建（容器环境）怎么做呢？

写SQL语句计算采购指定日期前的移动加权平均单价

3条回答 默认 最新

问题事件

悬赏问题

3条回答默认最新