根据每日收益分成将固定值分配给各个国家/地区时，排除某些值

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 37.95 (=2000/31 = 64.5 * 0.59)2020-01-03 FR 350 26.57 (=2000/31 = 64.5 * 0.41)2020-01-03 NL 320 0.002020-02-15 DE 700 137.15 (=5000/29 = 172.4 * 0.8) 2020-02-15 FR 180 35.27 (=5000/29 = 172.4 * 0.2) 2020-02-15 NL 420 0.00 2020-03-27 DE 180 20.20 (=4000/31 = 129.0 * 0.16) 2020-03-27 FR 970 108.81 (=4000/31 = 129.0 * 0.84) 2020-03-27 NL 670 0.00参考此问题中的解决方案 我目前使用以下查询将 monthly fix_costs 每天 划分到每个国家/地区:With reference to the solution in this question I currently use the below query to divide the monthly fix_costs on a daily basis to each country: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;一切正常.但是，现在我希望将该国家 NL 从 share_calculation 中排除，并始终保持为 0 ，如您在预期结果中所见.我该如何修改查询才能实现此目的?However, now I want that country NL is excluded from the share_calculation and always remains 0 as you can see in the expected results. How do I have to modify the query to achieve this?推荐答案您可以使用 case 表达式，既可以 计算，也可以使用 in 窗口 sum :You can use case expressions, both around the computation and within the window sum:select sales_date, country, sum(sales_volume), case when country <> 'NL' then sum(sum(fix_costs)) over(partition by year(sales_date), month(sales_date)) / day(last_day(sales_date)) * sum(sales_volume) / sum(case when country <> 'NL' then sum(sales_volume) else 0 end) over(partition by sales_date) else 0 end as fix_cost_per_dayfrom salesgroup by 1,2; DB Fiddle上的演示 :Demo on DB Fiddle:sales_date | country | sum(sales_volume) | fix_cost_per_day:--------- | :------ | ----------------: | ---------------:2020-01-03 | DE | 500 | 37.950664142020-01-03 | FR | 350 | 26.565464902020-01-03 | NL | 320 | null2020-01-30 | None | 0 | null2020-02-15 | DE | 700 | 137.147335422020-02-15 | FR | 180 | 35.266457682020-02-15 | NL | 420 | null2020-02-29 | None | 0 | null2020-03-27 | DE | 180 | 20.196353442020-03-27 | FR | 970 | 108.835904632020-03-27 | NL | 670 | null2020-03-31 | None | 0 | null 这篇关于根据每日收益分成将固定值分配给各个国家/地区时，排除某些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！