如何在SQLite中关联两个依赖计算结果的SELECT语句

表结构定义 #

首先是customers_tbl的创建语句：

CREATE TABLE customers_tbl 
(
    cust_id INTEGER PRIMARY KEY, 
    account_creation DATE, 
    last_ordr_date DATE
);

需求说明 #

需要关联以下两个SELECT语句，让第二个语句能使用第一个的计算结果calc_date，最终输出calc_date和留存率两列：

生成calc_date的语句 #

该语句将用户注册日期调整到当周周一，生成单列calc_date：

SELECT 
    DATE(account_creation,'weekday 0','-6 days') as calc_date 
FROM 
    "customers_tbl"
WHERE 
    DATE(account_creation) >= DATE('now','localtime','-364 days')
GROUP BY 
    calc_date
ORDER BY 
    account_creation ASC
LIMIT 50000

计算留存率的语句 #

该语句以calc_date为参数，计算对应时间段的用户留存率：

SELECT 
    100 * CAST((SELECT COUNT(*) AS retainedcustcount
                FROM customers_tbl
                WHERE DATE (account_creation) BETWEEN DATE(calc_date, '-21 days') 
                                                  AND DATE(calc_date, '-14 days')
                  AND DATE (last_ordr_date) BETWEEN DATE(calc_date, '-20 days') AND DATE (calc_date)) AS FLOAT) / 
                (SELECT COUNT(*) AS newcustcount
                 FROM customers_tbl
                 WHERE DATE (account_creation) BETWEEN DATE(calc_date, '-21 days') AND DATE(calc_date, '-14 days')) AS "留存率"

关联实现方案 #

方法一：使用CTE（公共表表达式） #

可读性更强，适合复杂场景：

WITH calc_dates AS (
    SELECT 
        DATE(account_creation,'weekday 0','-6 days') as calc_date 
    FROM 
        "customers_tbl"
    WHERE 
        DATE(account_creation) >= DATE('now','localtime','-364 days')
    GROUP BY 
        calc_date
    ORDER BY 
        account_creation ASC
    LIMIT 50000
)
SELECT 
    cd.calc_date,
    100 * CAST((SELECT COUNT(*) AS retainedcustcount
                FROM customers_tbl
                WHERE DATE(account_creation) BETWEEN DATE(cd.calc_date, '-21 days') 
                                                  AND DATE(cd.calc_date, '-14 days')
                  AND DATE(last_ordr_date) BETWEEN DATE(cd.calc_date, '-20 days') AND DATE(cd.calc_date)) AS FLOAT) / 
                (SELECT COUNT(*) AS newcustcount
                 FROM customers_tbl
                 WHERE DATE(account_creation) BETWEEN DATE(cd.calc_date, '-21 days') AND DATE(cd.calc_date, '-14 days')) AS "留存率"
FROM calc_dates cd;

方法二：使用子查询作为数据源 #

直接嵌套子查询，适合简单场景：

SELECT 
    sub.calc_date,
    100 * CAST((SELECT COUNT(*) AS retainedcustcount
                FROM customers_tbl
                WHERE DATE(account_creation) BETWEEN DATE(sub.calc_date, '-21 days') 
                                                  AND DATE(sub.calc_date, '-14 days')
                  AND DATE(last_ordr_date) BETWEEN DATE(sub.calc_date, '-20 days') AND DATE(sub.calc_date)) AS FLOAT) / 
                (SELECT COUNT(*) AS newcustcount
                 FROM customers_tbl
                 WHERE DATE(account_creation) BETWEEN DATE(sub.calc_date, '-21 days') AND DATE(sub.calc_date, '-14 days')) AS "留存率"
FROM (
    SELECT 
        DATE(account_creation,'weekday 0','-6 days') as calc_date 
    FROM 
        "customers_tbl"
    WHERE 
        DATE(account_creation) >= DATE('now','localtime','-364 days')
    GROUP BY 
        calc_date
    ORDER BY 
        account_creation ASC
    LIMIT 50000
) sub;

注意：若某时间段无新用户，会出现除以0的错误，可在分母处添加NULLIF函数处理，如NULLIF(SELECT COUNT(...), 0)，此时结果会返回NULL而非报错。

测试用示例数据 #

INSERT INTO customers_tbl (cust_id, account_creation, last_ordr_date)
VALUES
("9258266601","12/29/2023","1/8/2023"),
("9739199739","12/29/2023","12/31/2023"),
("4086661710","12/19/2023","12/21/2024"),
("8059106540","12/26/2023","12/26/2023"),
("4087182471","12/27/2023","12/27/2023"),
("9257255250","12/21/2023","1/1/2024"),
("2145378403","12/31/2023","1/2/2024"),
("9258092593","12/21/2023","12/21/2023"),
("6507735098","12/21/2023","1/9/2024"),
("5104070728","12/21/2023","12/21/2023"),
("2245002039","12/22/2023","12/22/2023"),
("5105794996","12/22/2023","12/22/2023"),
("9256678370","12/22/2023","12/22/2023"),
("9254905027","12/22/2023","12/22/2023"),
("9256993084","12/29/2023","1/9/2024"),
("9517751555","12/23/2023","12/23/2023"),
("9255205775","12/23/2023","12/23/2023"),
("5104702809","12/23/2023","12/23/2023"),
("5102400977","12/23/2023","12/23/2023"),
("9257898817","12/26/2023","12/26/2023"),
("9257855978","12/26/2023","1/2/2024"),
("2096092929","12/26/2023","12/26/2023"),
("9257194811","12/26/2023","12/26/2023"),
("9256058676","12/27/2023","12/27/2023");

内容的提问来源于stack exchange，提问作者Regis