source

일일 수익 점유율을 기준으로 고정 값을 국가에 분할할 때 특정 값 제외

factcode 2022. 11. 5. 08:56
반응형

일일 수익 점유율을 기준으로 고정 값을 국가에 분할할 때 특정 값 제외

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");

예상 결과:

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

질문의 솔루션을 참조하여 저는 현재 아래 쿼리를 사용하여 다음을 나누고 있습니다.monthly 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)) 
        * sum(sales_volume)
        / sum(sum(sales_volume)) over(partition by sales_date)
        as fix_cost_per_day
from sales
group by 1,2;

이 모든 것이 잘 작동한다.


하지만 지금은 그 나라가NL에서 제외됩니다.share_calculation그리고 항상 남는다0기대한 결과에서 알 수 있듯이이를 위해 쿼리를 수정하려면 어떻게 해야 합니까?

사용할 수 있습니다.case계산 주변과 창 양쪽에서 표현식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;

DB Fielen 데모:

sales_date | country | sum(sales_volume) | fix_cost_per_day:--------- | :------ | ----------------: | ---------------:2020-01-03 | DE | 500 | 37.950664142020-01-03 | FR | 350 | 26.565464902020-01-03 | NL | 320 | 특수한 순서2020-01-30 | 없음 | 0 | null2020-02-15 | DE | 700 | 137.147335422020-02-15 | FR | 180 | 35.266457682020-02-15 | NL | 420 | 특수한 순서2020-02-29 | 없음 | 0 | null2020-03-27 | DE | 180 | 20.196353442020-03-27 | FR | 970 | 108.835904632020-03-27 | NL | 670 | 특수한 순서2020-03-31 | 없음 | 0 | null

조건부 집계를 사용합니다.

select 
    sales_date, 
    country, 
    sum(sales_volume),
    (case when country = 'NL' then 0
          else sum(sum(case when country = 'NL' then 0 else fix_costs end)) 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)
     end)  as fix_cost_per_day
from sales
group by 1,2;

언급URL : https://stackoverflow.com/questions/62997226/exclude-certain-values-when-splitting-a-fix-value-to-countries-based-on-a-daily

반응형