mysql - 将每月固定值拆分为每天和国家
问题描述
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)将每日价值拆分到各个国家/地区DE
,并FR
根据它们在sales_volume
.
第 3 步)如果sales_volume
是0
每日费率被拆分为50/50
和DE
,FR
如您所见2020-12-02
。
为了实现这一点,我尝试使用此查询但无法使其工作:
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;
我需要在查询中修改什么才能达到预期的结果?
解决方案
您需要修复该FROM
条款,因此您只包括每个月的固定成本:
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)
推荐阅读
- metamask - 第一次 Metamask ETH 交易 Ropsten 水龙头失败
- swift - 如何从 Firestore 查询数组内的字段
- python - 在 python 中为我的单元测试模拟 self.variable
- reactjs - Next.js 路由器创建查询字符串和查询字符串数组
- async-await - 在嵌套地图反应js之外拉出承诺结果
- puppeteer - click() 方法在隔离环境中无法正常工作
- machine-learning - Crossentropyloss Pytorch:Targetsize 与 Torchsize 不匹配
- c++ - 可变长度数组的高性能“正确”C++ 替代方案
- java - 使用 RESPONSE 作为返回类型时,JAX-RS 不返回异常
- ios - 初始化程序 `init(:_rowContent:)` 要求 `Type` 确认为 `Identifiable`