根据每日收入份额将固定价值分配给各个国家 [英] Split fix value to countries based on daily revenue share
问题描述
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
分别划分为每月的每天和每个国家/地区.
In the table above I have fix_costs
per month.
Now, I want to split those monthly fix_costs
to each day of the month and to each country.
因此,我使用以下方法计算每个月的 fix_cost_per_day
: SUM(fix_costs)/DAY(LAST_DAY(sales_date))AS fix_cost_per_day
Therefore, I calculate the fix_cost_per_day
for each month using this:
SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day
但是,我不知道如何将每个国家/地区的每日收入份额
应用于 fix_cost_per_day
.
我需要修改查询以达到预期结果:
However, i do not know how I can apply the daily revenue_share per country
to the fix_cost_per_day
.
What do I need to modify my query to achieve the expected result:
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;
推荐答案
首先,您的currenet查询不会执行您想要的操作.看来您实际上需要每月在 fix_costs
上的窗口总和.所以我将从以下内容开始:
As a starter: your currenet query does not do what you want. It seems like you actually need a monthly window sum on fix_costs
. So I would start from:
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 | DE | 500 | 64.5161
2020-01-03 | FR | 350 | 64.5161
2020-01-03 | NL | 320 | 64.5161
2020-01-30 | None | 0 | 64.5161
2020-02-15 | DE | 700 | 172.4138
2020-02-15 | FR | 180 | 172.4138
2020-02-15 | NL | 420 | 172.4138
2020-02-29 | None | 0 | 172.4138
2020-03-27 | DE | 180 | 129.0323
2020-03-27 | FR | 970 | 129.0323
2020-03-27 | NL | 670 | 129.0323
2020-03-31 | None | 0 | 129.0323
从那里开始,您可以添加考虑每个国家/地区的每日收入份额"的逻辑.据我了解,您的问题是:
From there on, you can add the logic that takes in account the "daily revenue share per country". As I understand your question, that is:
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.57099531
2020-01-03 | FR | 350 | 19.29969672
2020-01-03 | NL | 320 | 17.64543700
2020-01-30 | None | 0 | null
2020-02-15 | DE | 700 | 92.83819629
2020-02-15 | FR | 180 | 23.87267905
2020-02-15 | NL | 420 | 55.70291777
2020-02-29 | None | 0 | null
2020-03-27 | DE | 180 | 12.76143212
2020-03-27 | FR | 970 | 68.76993974
2020-03-27 | NL | 670 | 47.50088621
2020-03-31 | None | 0 | null
Demo on DB Fiddle
如果需要,您可以通过将查询转到子查询并在外部查询中进行过滤来删除国家/地区无" 的记录.
If needed, You can remove the records for country 'None'
by turning the query to a subquery and filtering in an outer query.
这篇关于根据每日收入份额将固定价值分配给各个国家的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!