SQLite中的数据分析：经常性销售(订阅)的月度收入

我正在为一家以订阅为基础的公司做一个案例，该公司希望了解他们每月的销售额。这意味着我需要汇总所有活跃订阅的收入，按月分组。我已经设法处理完数据，创建了一个类似下面的表。每一行都是一份合同，其中包含开始日期、结束日期、建议(标准或折扣)和相应的每月价格。
ContractStartDate|ContractEndDate|PropostionReference|PropostionPrice
-|-|
2018-01-03|空|标准|4.5
2019-01-17|空|折扣|2
2018-02-09|2019-01-17|标准版|4.5
...|...|...|...
我希望有每个月的收入，从最低合同开始日期(他们第一次收到任何收入)，为标准和折扣建议。所以我想要一个看起来像这样的东西：
月|RevenueStandard|RevenueDiscount
-|-|
2017-07|90|30
2017-08|85.5|80
2017-09|180|60
...||
2022-10|3862|1136
对于每个月和每个建议(标准或折扣)，如果开始日期在该月之后而结束日期在该月之前(或者没有结束日期)，我将需要检查每一份合同并汇总建议价格。
这就是我尝试的代码，但我觉得我真的离解决方案还很远：

SELECT 
MonthYear, PropositionReference, 
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Standard" 
THEN PropositionPrice ELSE 0 END) AS RevenueStandard,
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Discount" 
THEN PropositionPrice ELSE 0 END) AS RevenueDiscount
FROM (SELECT *, STRFTIME("%m %Y", ContractStartDate) AS MonthYear FROM Combined)
GROUP BY MonthYear, PropositionReference
ORDER BY MonthYear, PropositionReference

如果你的问题被正确地解释了，以下可能是基础。

WITH 
    range AS (
        SELECT min(contractstartdate) AS low, max(coalesce(contractenddate,date('now'))) AS high FROM combined
        ),
    t AS (
        SELECT 
            low AS month,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
        FROM range
        UNION ALL 
            SELECT date(month,'+1 month'),
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',date(month,'+1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',date(month,'+1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
            FROM t 
            WHERE date(month,'+1 month') < (SELECT max(coalesce(contractenddate,date('now'))) FROM combined) 
            LIMIT 500 /* just in case to stop continuous loop  */
    ) 
SELECT * FROM t;

所以这就是

首先创建一个CTE(公用表表达式(在执行期间存在的临时表))，它由单行组成，其中包含两个值：最低开始日期和最高结束日期(如果有NULL，则为当前日期)。
第二个创建另一个CTE，但是是递归的，其中第一行是第一个月，第二行是下个月...直到最后一个月(或在本例中为最多500次迭代(以防止意外的无限循环))。
每次迭代从包含被处理日期的组合表中检索相应类型的行的总和。

作为演示，根据您当时的数据：

DROP TABLE IF EXISTS combined;
CREATE TABLE IF NOT EXISTS combined (ContractStartDate TEXT,ContractEndDate TEXT,PropositionReference TEXT,PropositionPrice REAL);
INSERT INTO combined VALUES
    ('2018-01-03',  NULL,   'Standard', 4.5)
    ,('2019-01-17', NULL,   'Discount', 2)
    ,('2018-02-09', '2019-01-17',   'Standard', 4.5)
;
WITH 
    range AS (
        SELECT min(contractstartdate) AS low, max(coalesce(contractenddate,date('now'))) AS high FROM combined
        ),
    t AS (
        SELECT 
            low AS month,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
        FROM range
        UNION ALL 
            SELECT date(month,'+1 month'),
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',date(month,'+1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',date(month,'+1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
            FROM t 
            WHERE date(month,'+1 month') < (SELECT max(coalesce(contractenddate,date('now'))) FROM combined) 
            LIMIT 500 /* just in case to stop continuous loop  */
    ) 
SELECT * FROM t;
DROP TABLE IF EXISTS combined;

结果如下：

SQLite中的数据分析：经常性销售(订阅)的月度收入

1条答案

相关问题

热门标签

最新问答