将月份的总价值分配给每月的每一天

Michi 发表于 Dev

美智

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', '0'),
('FR', '2020-01-03', '350', '0'),
('None', '2020-01-31', '0', '2000'),

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

('DE', '2020-03-27', '180', '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       |       2000
2020-01-03    |     FR      |         350       |       2000
2020-02-15    |     DE      |           0       |       5000
2020-02-15    |     FR      |           0       |       5000
2020-03-27    |     DE      |         180       |       4000
2020-03-27    |     FR      |         970       |       4000

如您在表中所看到的，我总共fix_costs分配了每个月的最后一天。

在我的结果我想这个总的分配fix_costs到这个月的每一天。
因此，我尝试使用此查询：

SELECT
s.sales_date, 
s.country,
s.sales_volume,
f.fix_costs
FROM sales s

JOIN

  (SELECT 
  ((date_trunc('MONTH', sales_date) + INTERVAL '1 MONTH - 1 DAY')::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)

WHERE country <> 'None'
GROUP BY 1,2,3;

对于此查询，我收到一个错误，LAST_DAY(s.sales_date)因为PostgresSQL中不存在此表达式。
但是，我不知道如何正确替换它才能获得预期的结果。

你能帮助我吗？

（MariaDB Fiddle作为比较）

男装

演示：db <> fiddle

SELECT
    s1.sales_date,
    s1.country,
    s1.sales_volume,
    s2.fix_costs
FROM sales s1
JOIN sales s2 ON s1.country <> 'None' AND s2.country = 'None' 
    AND date_trunc('month', s1.sales_date) = date_trunc('month', s2.sales_date)

您需要自然的自我加入。加盟条件为：

没有 None记录的第一张表（s1.country <> 'None'）
仅第二表None记录（s2.country = 'None'）
日期：仅考虑年份和月份部分，忽略日期。这可以通过使用将两个表的日期归一化到每月的第一天来实现date_trunc()。因此，例如'2020-02-15'产生'2020-02-01'和'2020-02-29'产生结果'2020-02-01'，也可以作为比较和连接条件。

或者：

SELECT
    *
FROM (
    SELECT
        sales_date,
        country,
        sales_volume,
        SUM(fix_costs) OVER (PARTITION BY date_trunc('month', sales_date)) as fix_costs
    FROM sales 
) s
WHERE country <> 'None'

您可以在如上所述SUM()的组上使用窗口功能date_trunc()。然后您需要过滤None记录

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。