SQLite中的資料分析：來自經常性銷售（訂閱）的月收入

我正在為一家基于訂閱的公司做一個案例，該公司希望了解他們的月銷售額。這意味著我需要總結所有活動訂閱的收入，按月分組。我設法通過資料創建了一個如下表所示的表格。每行是一份合同，其中包含開始日期、結束日期、提議（標準或折扣）和相應的每月價格。

對于標準和折扣提議，我希望從最低 ContractStartDate（他們第一次收到任何收入）開始獲得每個月的收入。所以我想要一些看起來像這樣的東西：

對于每個月和每個提議（標準或折扣），如果開始日期在該月之后并且結束日期在該月之前（或者沒有結束日期），我需要檢查每個合同并總結提議價格。

這是我嘗試過的代碼，但我覺得我離解決方案還很遠：

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

uj5u.com熱心網友回復：

如果您的問題得到正確解釋，以下內容可能是基礎。

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;

結果是 ：-

標籤：sqlsqlite循环约会时间总计的

上一篇：如何比較串列中的每個元素并檢查它是否大于右側的元素

下一篇：觸發在另一個表中搜索插入的值