代码之家 › 专栏 › 技术社区 › Shukurullox Komiljonov

从记录中获得相互和解。使用SQL

django-models django sql python

Shukurullox Komiljonov · 技术社区 · 4 月前

如何在SQL中为特定员工生成相互结算?

我有一张表,用于跟踪员工的共同结算。该表包含以下列:

employee_id :员工的ID
amount :交易金额(可以是正数或负数,具体取决于原因)
reason :交易描述
created_at :交易发生的时间戳

我需要生成一份报告,显示员工的余额如何随时间变化,如下所示:

开始余额	更改	最终余额
0	+50	50
50	-15	35
35	+10	45
45	5.	40

我的SQL查询(未按预期工作)

我尝试使用窗口函数计算之前的余额、变化和最终余额,但结果不正确:

SELECT 
    id,
    employee_id,
    COALESCE(LAG(SUM(amount)) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_amount,
    amount AS change,
    SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS final_amount
FROM settlement_settlement
GROUP BY id
ORDER BY created_at;

1 回复 | 直到 4 月前

Kishore Arul 4 月前

LAG(SUM(amount))无法按预期工作,因为SUM(amounts)是一个聚合函数,而LAG()对单个行进行操作。相反,您应该使用LAG(final_balance)OVER(…)来获取前几行的累积总和。

CREATE TABLE employee_settlement ( employee_id int, amount int, created_at date);


insert into employee_settlement values(1,950,'2024-12-01');
insert into employee_settlement values(1,1000,'2025-01-01');
insert into employee_settlement values(1,1050,'2025-02-01');

实际查询:

WITH balance_cte AS (
    SELECT 
        employee_id,
        created_at,
        amount AS change,
        SUM(amount) OVER (PARTITION BY employee_id ORDER BY created_at) AS final_balance
    FROM employee_settlement
    WHERE employee_id = 1 -- Replace with a specific employee_id
)
SELECT 
    employee_id,
    COALESCE(LAG(final_balance) OVER (PARTITION BY employee_id ORDER BY created_at), 0) AS start_balance,
    change,
    final_balance
FROM balance_cte
ORDER BY created_at;

输出: