将每月固定值拆分为每天和国家/地区 [英] Split fix value per month to each day and country

问题描述

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-31", "0", "2000"),

("DE", "2020-02-15", "0", "0"),
("NL", "2020-02-15", "0", "0"),
("FR", "2020-02-15", "0", "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        |     37.95  (= 2000/31 = 64.5 x 0.59)
2020-01-03   |     FR       |       350        |     26.57  (= 2000/31 = 64.5 x 0.41)
2020-01-03   |     NL       |       320        |      0.00
-------------|--------------|------------------|------------------------------------------
2020-02-15   |     DE       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     FR       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     NL       |         0        |      0.00
-------------|--------------|------------------|------------------------------------------    
2020-03-27   |     DE       |       180        |     20.20  (= 4000/31 = 129.0 x 0.16) 
2020-03-27   |     FR       |       970        |    108.84  (= 4000/31 = 129.0 x 0.84)   
2020-03-27   |     NL       |       670        |      0.00
-------------|--------------|------------------|-------------------------------------------

---

预期结果中的列fix_costs计算如下:

步骤 1) 获取每月 fix_costs 的每日费率.<代码>(2000/31 = 64.5；5000/29 = 172.4；4000/31 = 129.0)
步骤 2) 根据它们在 sales_volume 中的份额，将每日价值拆分到 DE 和 FR 国家/地区.
步骤 3) 如果 sales_volume 为 0，则每日费率将 50/50 拆分为 DE 和 FR，如您在 2020-12-02 中看到的那样.

Step 1) Get the daily rate of the fix_costs per month. (2000/31 = 64.5; 5000/29 = 172.4; 4000/31 = 129.0)
Step 2) Split the daily value to the countries DE and FR based on their share in the sales_volume.
Step 3) In case the sales_volume is 0 the daily rate gets split 50/50 to DE and FR as you can see for 2020-12-02.

为了实现这一点，我尝试使用此查询，但无法使其工作:

In order to achieve this I tried to go with this query but could not make it work:

SELECT 
    sales_date, 
    country, 
    SUM(sales_volume),
       (CASE WHEN country = 'NL' THEN 0
             WHEN SUM(CASE WHEN country <> 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date) > 0
             THEN ((f.fix_costs/ DAY(LAST_DAY(sales_date))) *
                   sales_volume / NULLIF(SUM(CASE WHEN country <> 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date), 0)
                  )
             ELSE (f.fix_costs / DAY(LAST_DAY(sales_date))) * 1 / SUM(country <> 'NL') OVER (PARTITION by sales_date)
        END) AS imputed_fix_costs
FROM sales s

    CROSS JOIN
     (SELECT
      SUM(fix_costs) as fix_costs
      FROM sales
      WHERE country = 'None'
     ) f

WHERE country <> "None"
GROUP BY 1,2;

我需要在查询中修改什么才能达到预期的结果?

What do I need to modify in the query to achieve the expected result?

推荐答案

你需要修正 FROM 子句，所以你只包括每个月的固定成本:

You need to fix the FROM clause, so you are only including the fixed costs for each month:

FROM sales s CROSS JOIN
     (SELECT LAST_DAY(sales_date) as month_ld, SUM(fix_costs) as fix_costs
      FROM sales
      WHERE country = 'None'
      GROUP BY month_ld
     ) f
     ON f.month_ld = LAST_DAY(s.sales_date)

这篇关于将每月固定值拆分为每天和国家/地区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！