根据每日收入份额将固定价值拆分到国家/地区

8wigbo56 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(221)

小提琴

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_costs
2020-01-03        DE              500           27.6  (=2000/31 = 64.5 * 0.42)
2020-01-03        FR              350           19.3  (=2000/31 = 64.5 * 0.30)
2020-01-03        NL              320           17.6  (=2000/31 = 64.5 * 0.28)
2020-02-15        DE              700           92.8  (=5000/29 = 172.4 * 0.54)   
2020-02-15        FR              180           23.9  (=5000/29 = 172.4 * 0.14)  
2020-02-15        NL              420           55.7  (=5000/29 = 172.4 * 0.32)     
2020-03-27        DE              180           12.8  (=4000/31 = 129.0 * 0.10) 
2020-03-27        FR              970           68.8  (=4000/31 = 129.0 * 0.53)   
2020-03-27        NL              670           47.5  (=4000/31 = 129.0 * 0.37)

在上面的表格里我有 fix_costs 每月。
现在，我想每个月分一次 fix_costs 每一天，每一个国家。
因此，我计算 fix_cost_per_day 每个月使用： SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day 但是，我不知道如何才能申请 daily revenue_share per country 到 fix_cost_per_day .
我需要什么来修改查询以获得预期结果：

SELECT 
sales_date, 
country, 
SUM(sales_volume),
SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day
FROM sales
GROUP BY 1,2;

sql mysql window-functions sum mariadb

来源：https://stackoverflow.com/questions/62996363/split-fix-value-to-countries-based-on-daily-revenue-share

2条答案

按热度按时间

rsaldnfx1#

首先：您的currenet查询并不是您想要的。似乎你真的需要每月的窗口金额 fix_costs . 所以我要从：

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)) 
        as fix_cost_per_day
from sales
group by 1,2;

这将产生：

sales_date | country | sum(sales_volume) | fix_cost_per_day
:--------- | :------ | ----------------: | ---------------:
2020-01-03 | DE      |               500 |          64.5161
2020-01-03 | FR      |               350 |          64.5161
2020-01-03 | NL      |               320 |          64.5161
2020-01-30 | None    |                 0 |          64.5161
2020-02-15 | DE      |               700 |         172.4138
2020-02-15 | FR      |               180 |         172.4138
2020-02-15 | NL      |               420 |         172.4138
2020-02-29 | None    |                 0 |         172.4138
2020-03-27 | DE      |               180 |         129.0323
2020-03-27 | FR      |               970 |         129.0323
2020-03-27 | NL      |               670 |         129.0323
2020-03-31 | None    |                 0 |         129.0323

从那以后，您可以添加考虑“每个国家的每日收入份额”的逻辑。据我所知，你的问题是：

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;

退货：

sales_date | country | sum(sales_volume) | fix_cost_per_day
:--------- | :------ | ----------------: | ---------------:
2020-01-03 | DE      |               500 |      27.57099531
2020-01-03 | FR      |               350 |      19.29969672
2020-01-03 | NL      |               320 |      17.64543700
2020-01-30 | None    |                 0 |             null
2020-02-15 | DE      |               700 |      92.83819629
2020-02-15 | FR      |               180 |      23.87267905
2020-02-15 | NL      |               420 |      55.70291777
2020-02-29 | None    |                 0 |             null
2020-03-27 | DE      |               180 |      12.76143212
2020-03-27 | FR      |               970 |      68.76993974
2020-03-27 | NL      |               670 |      47.50088621
2020-03-31 | None    |                 0 |             null

db小提琴演示
如果需要，您可以删除国家/地区的记录 'None' 通过将查询转换为子查询并在外部查询中进行过滤。

赞(0）回复(0）举报 2021-07-24

33qvvth12#

要分配数据，可以使用窗口函数。您需要按月份对各种值求和，然后取总数并除以：

SELECT sales_date, country, 
       SUM(sales_volume),
       SUM(fix_costs) / DAY(LAST_DAY(sales_date)) AS fix_cost_per_day,
       (SUM(SUM(fix_costs))  OVER (PARTITION BY yyyy, mm) / DAY(LAST_DAY(sales_date))) *
        (SUM(sales_volume) / SUM(SUM(sales_volume)) OVER (PARTITION BY yyyy, mm)) as allocated
FROM (SELECT s.*, YEAR(sales_date) as yyyy, MONTH(sales_date) as mm
      FROM sales s
     ) s
GROUP BY 1,2;

这是一把小提琴。
注意，子查询不是严格需要的。它只对每个月的分区工作有帮助。

赞(0）回复(0）举报 2021-07-24

我来回答

根据每日收入份额将固定价值拆分到国家/地区

2条答案

相关问题

热门标签

最新问答