根据每日收入分成将固定值拆分到国家/地区时排除某些值
DB-Fiddle
创建表销售 (id int auto_increment 主键，国家 VARCHAR(255),销售日期日期，sales_volume 整数，fix_costs INT);插入销售(国家，sales_date，sales_volume，fix_costs)价值观(DE"、2020-01-03"、500"、0")，(NL"、2020-01-03"、320"、0")，(FR"、2020-01-03"、350"、0")，(无"、2020-01-30"、0"、2000")，(DE"、2020-02-15"、700"、0")，(NL"、2020-02-15"、420"、0")，(FR"、2020-02-15"、180"、0")，(无"、2020-02-29"、0"、5000")，(DE"、2020-03-27"、180"、0")，(NL"、2020-03-27"、670"、0")，(FR"、2020-03-27"、970"、0")，(无"、2020-03-31"、0"、4000")；预期结果:
sales_date 国家 sales_volume fix_costs2020-01-03 DE 500 37.95 (=2000/31 = 64.5 * 0.59)2020-01-03 法国 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 法国 180 35.27 (=5000/29 = 172.4 * 0.2)2020-02-15 NL 420 0.002020-03-27 DE 180 20.20 (=4000/31 = 129.0 * 0.16)2020-03-27 法国 970 108.81 (=4000/31 = 129.0 * 0.84)2020-03-27 NL 670 0.00参考这个问题 我目前使用以下查询将monthly fix_costs daily 划分为每个国家/地区:
选择销售日期，国家，总和(销售量)，sum(sum(fix_costs)) over(按年分区(sales_date)、month(sales_date))/天(last_day(sales_date))*总和(销售量)/sum(sum(sales_volume)) over(按sales_date划分)作为 fix_cost_per_day从销售按 1,2 分组；这一切都很好.
但是，现在我希望将国家 NL 从 share_calculation 中排除，并且始终保持为 0，正如您在预期结果中看到的那样.我必须如何修改查询才能实现这一点?
你可以使用 case 表达式，围绕计算和within窗口sum:
选择销售日期，国家，总和(销售量)，国家<>时的情况'荷兰'然后 sum(sum(fix_costs)) over(partition by year(sales_date), month(sales_date))/天(last_day(sales_date))*总和(销售量)/sum(case when country <> 'NL' then sum(sales_volume) else 0 end) over(partition by sales_date)否则 0以 fix_cost_per_day 结束从销售按 1,2 分组；DB Fiddle 演示:
<上一页>销售日期 |国家 |总和(销售量) |fix_cost_per_day:--------- |:-------- |----------------: |---------------:2020-01-03 |德 |500 |37.950664142020-01-03 |法语 |350 |26.565464902020-01-03 |荷兰 |320 |空2020-01-30 |无 |0 |空2020-02-15 |德 |700 |137.147335422020-02-15 |法语 |180 |35.266457682020-02-15 |荷兰 |420 |空2020-02-29 |无 |0 |空2020-03-27 |德 |180 |20.196353442020-03-27 |法语 |970 |108.835904632020-03-27 |荷兰 |670 |空2020-03-31 |无 |0 |空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");
Expected Result:
sales_date country sales_volume fix_costs
2020-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.00
2020-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
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_day
from sales
group by 1,2;
All this works fine.
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?
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_day
from sales
group by 1,2;
Demo on DB Fiddle:
sales_date | country | sum(sales_volume) | fix_cost_per_day :--------- | :------ | ----------------: | ---------------: 2020-01-03 | DE | 500 | 37.95066414 2020-01-03 | FR | 350 | 26.56546490 2020-01-03 | NL | 320 | null 2020-01-30 | None | 0 | null 2020-02-15 | DE | 700 | 137.14733542 2020-02-15 | FR | 180 | 35.26645768 2020-02-15 | NL | 420 | null 2020-02-29 | None | 0 | null 2020-03-27 | DE | 180 | 20.19635344 2020-03-27 | FR | 970 | 108.83590463 2020-03-27 | NL | 670 | null 2020-03-31 | None | 0 | null
相关文章