根据每日收入份额将固定价值分配给各个国家/地区

Question

DB Fiddle

CREATE TABLE sales (
id int auto_increment primary key,
country VARCHAR(255),
sales_date DATE,
sales_volume INT,
fix_costs INT
);
INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 
("DE", "2020-01-03", "500", "0"),
("NL", "2020-01-03", "320", "0"),
("FR", "2020-01-03", "350", "0"),
("None", "2020-01-30", "0", "2000"),
("DE", "2020-02-15", "700", "0"),
("NL", "2020-02-15", "420", "0"),
("FR", "2020-02-15", "180", "0"),
("None", "2020-02-29", "0", "5000"),
("DE", "2020-03-27", "180", "0"),
("NL", "2020-03-27", "670", "0"),
("FR", "2020-03-27", "970", "0"),
("None", "2020-03-31", "0", "4000");

预期结果：

sales_date      country       sales_volume     fix_costs
2020-01-03        DE              500           27.6  (=2000/31 = 64.5 * 0.42)
2020-01-03        FR              350           19.3  (=2000/31 = 64.5 * 0.30)
2020-01-03        NL              320           17.6  (=2000/31 = 64.5 * 0.28)
2020-02-15        DE              700           92.8  (=5000/29 = 172.4 * 0.54)   
2020-02-15        FR              180           23.9  (=5000/29 = 172.4 * 0.14)  
2020-02-15        NL              420           55.7  (=5000/29 = 172.4 * 0.32)     
2020-03-27        DE              180           12.8  (=4000/31 = 129.0 * 0.10) 
2020-03-27        FR              970           68.8  (=4000/31 = 129.0 * 0.53)   
2020-03-27        NL              670           47.5  (=4000/31 = 129.0 * 0.37)   

---

在上表中，我每月有fix_costs
现在，我想将每月的fix_costs划分为该月的每天和每个国家。

因此，我使用以下公式计算每个月的fix_cost_per_day：
SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day

然而，我不知道如何将daily revenue_share per country应用于fix_cost_per_day
我需要什么来修改我的查询以获得预期结果：

SELECT 
sales_date, 
country, 
SUM(sales_volume),
SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day
FROM sales
GROUP BY 1,2;

Answer 1

首先：您的currenet查询不能满足您的要求。看起来你实际上需要fix_costs上的每月窗口金额。所以我会从开始

select 
sales_date, 
country, 
sum(sales_volume),
sum(sum(fix_costs)) over(partition by year(sales_date), month(sales_date))
/ day(last_day(sales_date)) 
as fix_cost_per_day
from sales
group by 1,2;

这产生：

sales_date|country|sum(sales_volume(|fix_cost_per_day：---------------------------------------|：---------------------------------------|----------------：|----------------2020-01-03 |日期|500 |64.51612020-01-03 | FR | 350 | 64.51612020-01-03 | NL | 320 | 64.51612020-01-30|无|0|64.51612020-02-15 |日期|700 | 172.41382020-02-15 | FR|180 | 172.41382020-02-15 | NL | 420 | 172.41382020-02-29|无|0|172.41382020-03-27 |日期|180 | 129.03232020-03-27 | FR | 970 | 129.03232020-03-27 | NL | 670 | 129.03232020-03-31|None|0|129.0323

从那时起，您可以添加考虑"；每个国家的每日收入份额"；。据我所知，你的问题是：

select 
sales_date, 
country, 
sum(sales_volume),
sum(sum(fix_costs)) over(partition by year(sales_date), month(sales_date))
/ day(last_day(sales_date)) 
* sum(sales_volume)
/ sum(sum(sales_volume)) over(partition by sales_date)
as fix_cost_per_day
from sales
group by 1,2;

退货：

sales_date|country|sum(sales_volume(|fix_cost_per_day：---------------------------------------|：---------------------------------------|----------------：|----------------2020-01-03|DE |500 |27.570995312020-01-03 | FR | 350 | 19.299696722020-01-03 | NL | 320 | 17.645437002020-01-30|None|0|null2020-02-15|DE|700|92.838196292020-02-15 | FR|180 | 23.872679052020-02-15 | NL | 420 | 55.702917772020-02-29|None|0|null22020-03-27|DE|180|12.761432122020-03-27 | FR | 970 | 68.769939742020-03-27 | NL | 670 | 47.500886212020-03-31|None|0|null

在DB Fiddle上演示

如果需要，可以通过将查询转为子查询并在外部查询中进行筛选来删除国家/地区'None'的记录。

Answer 2

要分配数据，可以使用窗口函数。你需要按月对各种值求和，然后求和除以：

SELECT sales_date, country, 
SUM(sales_volume),
SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day,
(SUM(SUM(fix_costs))  OVER (PARTITION BY yyyy, mm) / DAY(LAST_DAY(sales_date))) *
(SUM(sales_volume) / SUM(SUM(sales_volume)) OVER (PARTITION BY yyyy, mm)) as allocated
FROM (SELECT s.*, YEAR(sales_date) as yyyy, MONTH(sales_date) as mm
FROM sales s
) s
GROUP BY 1,2;

这里有一个db<gt；不停摆弄

请注意，子查询并不是严格需要的。这对每个月处理分区很有帮助。