代码之家 › 专栏 › 技术社区 › Michi

如何使用date\u part函数将每月值拆分为每天和国家

postgresql

Michi · 技术社区 · 4 年前

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    country VARCHAR(255),
    sales_date DATE,
    sales_volume DECIMAL,
    fix_costs DECIMAL
);

INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 

('DE', '2020-01-03', '500', '2000'),
('FR', '2020-01-03', '350', '2000'),
('None', '2020-01-31', '0', '2000'),

('DE', '2020-02-15', '0', '5000'),
('FR', '2020-02-15', '0', '5000'),
('None', '2020-02-29', '0', '5000'),

('DE', '2020-03-27', '180', '4000'),
('FR', '2020-03-27', '970', '4000'),
('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-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-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)   
-------------|--------------|------------------|-------------------------------------------

列 fix_costs 预期结果计算如下:

获取每月固定成本的每日费率。 (2000/31 = 64.5; 5000/29 = 172.4; 4000/31 = 129.0)

步骤2) (500/850 = 0.59; 350/850 = 0.41; 180/1150 = 0.16; 970/1150 = 0.84)

步骤3) 如果销售量 0 50/50 如你所见 2020-02-15 .

在 MariaDB

SELECT
s.sales_date, 
s.country,
s.sales_volume,

       (CASE WHEN SUM(sales_volume) OVER (PARTITION BY sales_date) > 0
             THEN ((s.fix_costs/ DAY(LAST_DAY(sales_date))) *
                   sales_volume / NULLIF(SUM(sales_volume) OVER (PARTITION BY sales_date), 0)
                  )
             ELSE (s.fix_costs / DAY(LAST_DAY(sales_date))) * 1 / SUM(country <> 'None') OVER (PARTITION by sales_date)
        END) AS imputed_fix_costs
        
FROM sales s
WHERE country <> 'None'
GROUP BY 1,2,3
ORDER BY 1;

然而,在 PostgresSQL 我的电脑出错了 DAY(LAST_DAY(sales_date)) .
(date_part('DAY', ((date_trunc('MONTH', s.sales_date) + INTERVAL '1 MONTH - 1 DAY')::date)))
但是,这会导致另一个错误。

如何修改查询以获得预期结果?

2 回复 | 直到 4 年前

Stefanov.sm 4 年前

Postgresql等价于 DAY(LAST_DAY(sales_date)) 可能是:

extract(day from (date_trunc('month', sales_date + interval '1 month') - interval '1 day'))

表达式 SUM(country <> 'None') 也需要固定为

SUM(case when country <> 'None' then 1 else 0 end)

最好定义此兼容性函数:

create function last_day(d date) returns date as
$$
  select date_trunc('month', d + interval '1 month') - interval '1 day';
$$ language sql immutable;

那么第一个表达式就变得简单了

extract(day from last_day(sales_date))

a_horse_with_no_name 4 年前

create function month_length(p_input date) 
  returns integer
as
$$
  select extract(day from (date_trunc('month', p_input) + interval '1 month - 1 day'));
$$
language sql 
immutable;

然后可以将查询编写为:

select sales_date, country, 
       sum(sales_volume),
       sum(fix_costs_per_day * cost_factor) 
from (       
  select id, country, sales_date, sales_volume, fix_costs, 
         fix_costs / month_length(sales_date) as fix_costs_per_day,
         case 
            when sum(sales_volume) over (partition by sales_date) > 0 
              then sales_volume::numeric / sum(sales_volume) over (partition by sales_date)
            else sales_volume::numeric / 2
         end as cost_factor
  from sales
  where country <> 'None'
) t
group by sales_date, country
order by sales_date, country