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

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_costs2020-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_dayFROM salesGROUP 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_dayfrom salesgroup by 1,2;这将产生:sales_date | country | sum(sales_volume) | fix_cost_per_day:--------- | :------ | ----------------: | ---------------:2020-01-03 | DE | 500 | 64.51612020-01-03 | FR | 350 | 64.51612020-01-03 | NL | 320 | 64.51612020-01-30 | None | 0 | 64.51612020-02-15 | DE | 700 | 172.41382020-02-15 | FR | 180 | 172.41382020-02-15 | NL | 420 | 172.41382020-02-29 | None | 0 | 172.41382020-03-27 | DE | 180 | 129.03232020-03-27 | FR | 970 | 129.03232020-03-27 | NL | 670 | 129.03232020-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_dayfrom salesgroup 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 | null2020-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上的演示 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. 这篇关于根据每日收入份额将固定价值分配给各个国家的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！