sql - SQLite 中的聚合

问题描述

我正在寻找能够按月汇总分期付款值的 SQL 查询。通常这不会太难，因为您只需将分期付款值和group by月份相加即可。然而，问题并不那么容易，在本文的其余部分中，我将说明原因并寻求人们能够提供的任何帮助。

首先，重要的是要注意该installments列。其中installments1 表示购买时支付的总价值。大于 1 的地方installments，表示当月以及接下来的月份支付的总金额。例如，如果我们看到transaction_id9 和 10，这是一笔 100 美元的交易，分两期付款，这意味着 50 美元将在 2 月支付，50 美元将在 3 月支付。

考虑我们想要查看的每月账单credit_card_id = 11111111。如果我们查看该installments列，我们可以看到正确的输出应该如下：

一月：19.99 + 75.3
二月：1337 + 75.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，旨在为可以被视为 1 组的唯一交易提供一个 ID .

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);

我的问题是

是否可以在 SQLite 中获得我上面确定的格式的输出，如果可以，如何？
我必须要有txn专栏才能获得这些信息吗？

谢谢您的帮助。

标签： sqldatabasesqliteaggregate-functionsdata-analysis

假设您运行 SQLite 3.25+ 版本，请考虑使用CTE 和窗口函数，通过相同的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);

使用 PostgreSQL 的Rextester Demo ，因为 AFAIK 没有在线小提琴（SQLFiddle、SQLiteonline、DBFiddle 等）支持带有窗口函数的 SQLite

sql - SQLite 中的聚合

问题描述

解决方案

推荐阅读