gpt4 book ai didi

每天的 SQL 累计总和用每个用户的新余额刷新

转载 作者:行者123 更新时间:2023-11-29 12:39:38 28 4
gpt4 key购买 nike

我有一张 table , table 上有多个用户,我想知道每天的钱的总余额。

每个用户都可以在一天内进行交易,我可以看到他们的总余额,但我想看到他们每天结束时的总余额。

所以如果我今天没有加入但昨天我想在今天添加明天的余额如果我昨天加入并进行交易我想为用户更新余额。

SUM(total) OVER (PARTITION BY user ORDER BY DATE(time) ROWS UNBOUNDED PRECEDING) gain,

我可以进行分区,但我想知道我可以在 SQL 中逻辑地进行分区吗?

| Date | User | Balance
+------+------+--------
| Day1 | A | 100
| Day1 | B | 50
| Day1 | C | 100
| Day2 | A | 150
| Day2 | B | 20
| Day3 | E | 100
| Day4 | F | 200
| Day5 | A | 50
| Day5 | F | 50

所以

  • 第 1 天 -> A、B、C 250
  • 第 2 天 -> 新的 150,B 20 我知道 C(100) 昨天的最后余额所以 270
  • 第 3 天 -> E + 最后一个其他最后余额 (270) -> 370
  • 第 4 天 -> F + 其他最后余额 -> 570
  • 第 5 天 -> A 和 F 更改了新余额 320

等等..

最佳答案

很有意思的问题! :)

step-by-step demo:db<>fiddle

SELECT
the_date,
SUM(balance)
FROM (
SELECT DISTINCT ON (the_date, elems -> 'the_user')
the_date,
elems ->> 'the_user' AS the_user,
(elems ->> 'balance')::int AS balance
FROM (
SELECT
the_date::date AS the_date,
jsonb_agg(
row_to_json(mytable)::jsonb
) OVER (ORDER BY the_date) as agg
FROM
mytable
) s,
jsonb_array_elements(agg) as elems
ORDER BY the_date, elems -> 'the_user', elems -> 'the_date' DESC
) s
GROUP BY the_date

想法的草图:

1 累计聚合所有记录。 (为了以后能够访问每一列,这些记录在查询中存储为 JSON 对象)。

这在

中产生
date   data       cum_data
Day1 (A:100) [(A:100)]
Day1 (B:50) [(A:100),(B:50)],
Day1 (C:100) [(A:100),(B:50),(C:100)],
Day2 (A:150) [(A:100),(B:50),(C:100),(A:150)],
Day2 (B:20) [(A:100),(B:50),(C:100),(A:150),(B:20)]

可以看到,每天的最后一条记录保存了所有相关数据。每个用户的相关数据是数组中的最后一个元素。

2 因此,之后您对 (1.) 每天的最后一条记录以及在此 (2.) 每个用户的最后一条记录感兴趣。所以,你必须先扩展记录:

date   cum_data                                  expansion
Day1 [(A:100)] (A:100)
Day1 [(A:100),(B:50)], (A:100)
(B:50)
Day1 [(A:100),(B:50),(C:100)], (A:100) <- last A day1
(B:50) <- last B day1
(C:100) <- last C day1
Day2 [(A:100),(B:50),(C:100),(A:150)], (A:100)
(B:50)
(C:100)
(A:150)
Day2 [(A:100),(B:50),(C:100),(A:150),(B:20)] (A:100)
(B:50)
(C:100) <- last C day2 (unchanged)
(A:150) <- last A day2 (changed)
(B:20) <- last B day2 (changed)

3 因此,下一步是获取每个用户每天的最后一次出现。这可以通过 DISTINCT ON 来完成,它获取有序组的第一条记录。在您的情况下,组是 (date, user) 并且订单是按用户的日期 DESC 排序的。用户的日期当然存储在 json 中。所以 (A:100) 实际上是 (A:100, day1)(A:150)(A: 150,第 2 天)。顺序由第二个元素决定。当然,要先获取最新的,顺序需要降序。

这产生了

date   cum_data                                  expansion
Day1 [(A:100),(B:50),(C:100)], (A:100) <- last A day1
(B:50) <- last B day1
(C:100) <- last C day1

Day2 [(A:100),(B:50),(C:100),(A:150),(B:20)] (C:100) <- last C day2 (unchanged)
(A:150) <- last A day2 (changed)
(B:20) <- last B day2 (changed)

4 这最终可以简单地按date 列进行分组和总结:

date  sum
Day1 (A:100) + (B:50) + (C:100) = 250
Day2 (C:100) + (A:150) + (B:20) = 270

当然,对于大型数据集,累积的性能会非常低。在这种情况下,我建议编写一个循环遍历所有记录的简单函数;像这样:

date list := empty list of (date, balance)
user list := empty list of (user, balance)

for all records:
get current date
if current date <> previous date
add element (previous date, sum(all balances in user list)) to date list

get current user
if current user already exists in user list then
replace its balance
else
add current user to user list

return date list

编辑:这是一个可能的函数(比查询快得多)。它完全遵循给定的伪代码。这只是第一次抛出,我相信你可以优化代码,所以请把这个也看成草图:

demo:db<>fiddle

CREATE OR REPLACE FUNCTION  foobar() RETURNS SETOF record 
AS $$
DECLARE
_record record;
_date_rec record;
_prev_date date;
_user_balance int;
_date_balance int;
BEGIN
CREATE TEMP TABLE user_recs (the_user text, balance int);

FOR _record IN
SELECT * FROM mytable ORDER BY the_date
LOOP
IF (_prev_date IS NOT NULL AND (_record.the_date::date > _prev_date )) THEN
SELECT
SUM(ur.balance)
FROM
user_recs ur
INTO _date_balance;

_date_rec = (_prev_date , _date_balance);
RETURN NEXT _date_rec;
END IF;

SELECT balance FROM user_recs ur WHERE ur.the_user = _record.the_user
INTO _user_balance;

IF _user_balance IS NULL THEN
INSERT INTO user_recs VALUES (_record.the_user, _record.balance);
ELSE
UPDATE user_recs ur SET balance = _record.balance WHERE ur.the_user = _record.the_user;
END IF;

_prev_date = _record.the_date;
END LOOP;

RETURN QUERY
SELECT
_prev_date,
SUM(ur.balance)::int
FROM
user_recs ur;
END;
$$ LANGUAGE 'plpgsql'

关于每天的 SQL 累计总和用每个用户的新余额刷新,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57583374/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com