我正在try 向我的查询中添加两个窗口函数.一个是计算每个客户的滚动总数,另一个是简单地添加每个客户的行号.
当滚动总和运行得很好时,行号不会正确排序.我想添加一个降序行号,这样我就可以保留每个客户的所有行号1,我想在其中找到该客户的总和.
我在Snowflake SQL工作.
CREATE TABLE transactions (
customer_id INTEGER
,txn_date DATE
,txn_type VARCHAR(10)
,txn_amount INTEGER
);
INSERT INTO transactions (customer_id, txn_date, txn_type, txn_amount)
VALUES
('1', '2020-01-02', 'deposit', '312'),
('1', '2020-01-02', 'deposit', '312'),
('1', '2020-03-05', 'purchase', '612'),
('1', '2020-03-05', 'purchase', '612'),
('1', '2020-03-17', 'deposit', '324'),
('1', '2020-03-17', 'deposit', '324'),
('1', '2020-03-19', 'purchase', '664'),
('1', '2020-03-19', 'purchase', '664');
这是我目前使用的查询:
SELECT
customer_id
,txn_type
,txn_date
,txn_amount
,CASE WHEN txn_type = 'deposit'
THEN txn_amount
ELSE txn_amount * -1
END AS new_amount
,SUM(new_amount) OVER (
PARTITION BY customer_id
ORDER BY customer_id, txn_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
,ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY customer_id, txn_date DESC) AS rn
FROM
transactions
ORDER BY
customer_id
,txn_date;
它给我带来了这些结果:
customer_id | txn_type | txn_date | txn_amount | new_amount | running_total | rn |
---|---|---|---|---|---|---|
1 | deposit | 2020-01-02 | 312 | 312 | 312 | 7 |
1 | deposit | 2020-01-02 | 312 | 312 | 624 | 8 |
1 | purchase | 2020-03-05 | 612 | -612 | 12 | 5 |
1 | purchase | 2020-03-05 | 612 | -612 | -600 | 6 |
1 | deposit | 2020-03-17 | 324 | 324 | -276 | 3 |
1 | deposit | 2020-03-17 | 324 | 324 | 48 | 4 |
1 | purchase | 2020-03-19 | 664 | -664 | -616 | 1 |
1 | purchase | 2020-03-19 | 664 | -664 | -1280 | 2 |
虽然我预计会是这样的:
customer_id | txn_type | txn_date | txn_amount | new_amount | running_total | rn |
---|---|---|---|---|---|---|
1 | deposit | 2020-01-02 | 312 | 312 | 312 | 8 |
1 | deposit | 2020-01-02 | 312 | 312 | 624 | 7 |
1 | purchase | 2020-03-05 | 612 | -612 | 12 | 6 |
1 | purchase | 2020-03-05 | 612 | -612 | -600 | 5 |
1 | deposit | 2020-03-17 | 324 | 324 | -276 | 4 |
1 | deposit | 2020-03-17 | 324 | 324 | 48 | 3 |
1 | purchase | 2020-03-19 | 664 | -664 | -616 | 2 |
1 | purchase | 2020-03-19 | 664 | -664 | -1280 | 1 |
那么我到底做错了什么呢?
最后,我不仅要按客户ID划分行号,还要按月划分行号,这样我就可以获取每个月的最新记录,并查看Running_Total是多少.