根据每日收入分成将固定值拆分到国家/地区时排除某些值 [英] Exclude certain values when splitting a fix value to countries based on a daily revenue share

问题描述

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

这篇关于根据每日收入分成将固定值拆分到国家/地区时排除某些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！