使用来自日期范围的数据创建多个SUM列，其中一列合并来自已连接的第三个表的数据

Question

我正在创建一个查询，按'实体'列分组，并输出4个新列。

• 这些新列根据日期范围从每个"实体"的"金额"列中求和或减去金额值。
• 我也试图加入第三个表(previous_year_table)，并包括数据。

关于如何使以下查询成为可能的任何指导?或者关于更有效的查询的指导?
我正在构建的实际查询在第二个代码块中。

SELECT
ent.entity,
SUM(data.amount) curr_month /* when data.ds between 1st of current month AND current date)*/
SUM(data.amount) prev_month /* when data.ds between 1st of previous month AND current date)*/
(SUBTRACT prev_month sum from curr_month sum) movement,
(
SUM(prev_data.amount) /* when prev_data.ds between 1st day of Previous year AND current date)*/
+ SUM(data.amount) /* when data.ds between 1st day of Current year AND current date)*/
) previous_year
FROM entity_table ent
JOIN current_year_table data
ON data.unit = ent.unit
JOIN previous_year_table prev_data
ON prev_data.unit = ent.unit
GROUP BY
entity;

我目前实现的上述查询

这是没有'previous_year_table'连接，似乎工作到目前为止，但感觉黑客。我也使用prestodb语法，例如DATE_TRUNC,current_date等

SELECT
ent.entity,
SUM(
CASE
WHEN data.ds BETWEEN CAST(DATE_TRUNC('month', current_date) AS VARCHAR)
AND CAST(current_date AS VARCHAR) THEN data.amount_usd
ELSE 0
END
) curr_month,
SUM(
CASE
WHEN data.ds BETWEEN CAST(
(DATE_TRUNC('month', current_date) - INTERVAL '1' MONTH) AS VARCHAR
) AND CAST(current_date AS VARCHAR) THEN data.amount_usd
ELSE 0
END
) prev_month,
(
SUM(
CASE
WHEN data.ds BETWEEN CAST(DATE_TRUNC('month', current_date) AS VARCHAR)
AND CAST(current_date AS VARCHAR) THEN data.amount_usd
ELSE 0
END
) - SUM(
CASE
WHEN data.ds BETWEEN CAST(
(DATE_TRUNC('month', current_date) - INTERVAL '1' MONTH) AS VARCHAR
) AND CAST(current_date AS VARCHAR) THEN data.amount_usd
ELSE 0
END
)
) movement
FROM entity_table ent
JOIN current_year_table data
ON data.operating_unit = ent.operating_unit
GROUP BY
entity;

期望查询输出

previous_year7090

你对movement的公式很奇怪，因为这两个:

curr_month/* when data。(当前月1日和当前日期之间的数据)/
prev_month/(前一个月1日与当前日期之间)*/

movement将始终只是一些前一个月(1日至最后一天之间)的负号。

同样根据数据，对于previous_year，您实际上想要当前和以前年份的总和。

除此之外，你的方法是好的，我建议只是使用if的小调整，子选择和使用数据函数，使其更具可读性:

--sample data
WITH entity_table  (entity, operating_unit) AS (
VALUES  ('entity_1', 1),
('entity_2', 2)
),
current_year_table (operating_unit, amount_usd, ds) AS (
VALUES  
(1, 20, '2022-05-21'),
(1, 20, '2022-04-19'),
(2, 10, '2022-05-20'),
(2, 40, '2022-04-26')
),
previous_year_table (operating_unit, amount_usd, ds) AS (
VALUES  
(1, 20, '2021-08-29'),
(2, 30, '2021-03-18'),
(2, 10, '2021-01-21'),
(1, 10, '2021-02-13')
)

查询:

-- query
select entity,
curr_month,
prev_month,
curr_month - prev_month as movement,
total_current_year + previous_year as previous_year
from (
select ent.entity,
ent.operating_unit,
sum(
if(
DATE_TRUNC('month', date(data.ds)) = DATE_TRUNC('month', now()),
amount_usd,
0
)
) curr_month,
sum(
if(
// use = instead of >= if you want actual diff between current and previous month
DATE_TRUNC('month', date(data.ds)) >= DATE_TRUNC('month', now()) - interval '1' month,
amount_usd,
0
)
) prev_month,
sum(amount_usd) total_current_year
FROM entity_table ent
JOIN current_year_table data ON data.operating_unit = ent.operating_unit
GROUP BY entity,
ent.operating_unit
) as current_year_data
join (
select operating_unit,
sum(amount_usd) as previous_year
from previous_year_table
group by operating_unit
) previous_year_data on current_year_data.operating_unit = previous_year_data.operating_unit

输出:

tbody> <<tr>

entity	curr_month	prev_month	movement	previous_year
entity_1	20	40	-20	70
entity_2	10	50	-40	90