sql - SQLite 中的聚合

Question

我希望构建一个能够按月汇总分期付款值的 SQL 查询。通常这不会太难，因为您只需将分期付款值和 group by 月相加即可。然而，这个问题并没有那么简单，在帖子的其余部分，我将说明原因并征求人们能够提供的任何帮助。

首先请务必注意 installments 列。 installments 为 1 时，表示在购买时支付了总金额。当 installments 大于 1 时，这意味着总值(value)在当月和随后的月份支付。例如，如果我们看到 transaction_id 9 和 10，这是一笔 100 美元的交易，分两次付款，这意味着 50 美元将在 2 月支付，50 美元将在 3 月支付。

假设我们想要查看 credit_card_id = 11111111 的每月账单。如果我们查看 installments 列，我们可以看到正确的输出应该如下所示:

• 1 月:19.99 + 75.3
• 2 月:1337 + 75.3
• 3 月:75.3

再次说明一下，3 月份出现 75.3 是因为我们在 1 月份进行了 3 笔分期付款交易，这意味着客户将在 1 月、2 月和 3 月支付 75.3 的费用。问题是我不知道如何根据给定的数据为三月创建类别。

首先，我在 SQL 中重新创建了表，并使用以下 SQLite 查询轻松地按月获取卡的所有交易

select strftime('%m', transaction_date) as Month, total_value, installment_value, installments 
from transactions 
WHERE credit_card_id = '11111111';

它输出一个看起来像这样的表格

但是，如何将 3 个分期付款期间拆分为 01、02 和 03 并不明显，因此我创建了一个新表，其中包含一个列 txn，旨在为可以被认为是一组的唯一交易。

CREATE TABLE transactions (
    transaction_id int primary key,
    credit_card_id int,
    transaction_date timestamp,
    merchant_name varchar(256),
    total_value decimal(19,4),
    installment_value decimal(19,4),
    installments int,
    txn int
);

insert into transactions values(1,11111111,'2018-01-10T00:00:00','Colorful Soaps', 19.99, 19.99, 1, 1);
insert into transactions values(2,22222222,'2018-01-11T00:01:00','Cantina da Mamma',43.5,43.5,1,2);
insert into transactions values(3,33333333,'2018-01-12T01:02:00','Boulevard Hotel',129,129,1,3);
insert into transactions values(4,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(5,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(6,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(7,22222222,'2018-01-18T22:10:01','IPear Store',9999.99,9999.99,1,5);
insert into transactions values(8,11111111,'2018-02-20T21:08:32','Forrest Paintball',1337,1337,1,6);
insert into transactions values(9,44444444,'2018-02-22T00:05:30','Unicorn Costumes',100,50,2,7);
insert into transactions values(10,44444444,'2018-02-22T00:05:30','Unicorn Costumes',100,50,2,7);

我的问题是

1. 是否可以在 SQLite 中获得我在上面确定的格式的输出，如果可以，如何获得？
2. 我必须要有 txn 列才能获得此信息吗？

感谢您的帮助。

Answer 1

假设你运行 SQLite 3.25+ 版本，考虑使用 CTE and window function它通过相同的 credit_card_id 和 transaction_date 创建一个运行计数，并使用此值将所需的月份添加到交易日期。从那里，根据新计算的日期 install_date 进行聚合。

WITH cte AS
   (SELECT *,
       DATE(transaction_date, 
            '+' || (ROW_NUMBER() 
                      OVER(PARTITION BY transaction_date, credit_card_id 
                           ORDER BY transaction_date) - 1)
                || ' month'
            ) AS install_date       
    FROM transactions)

SELECT credit_card_id, 
       STRFTIME('%Y', install_date) AS install_year, 
       STRFTIME('%m', install_date) AS install_month, 
       SUM(installment_value) AS sum_installment_value
FROM cte 
GROUP BY credit_card_id, 
         STRFTIME('%Y', install_date), 
         STRFTIME('%m', install_date)
ORDER BY credit_card_id, 
         STRFTIME('%Y', install_date), 
         STRFTIME('%m', install_date);

Rextester Demo 使用 PostgreSQL，因为 AFAIK 没有在线 fiddle (SQLFiddle、SQLiteonline、DBFiddle 等)支持带窗口函数的 SQLite