关于无值SQL查询的技术咨询及业务数据表说明

Hey there! Let's break down how to handle common "missing value" SQL queries using your provided transaction data. I'll cover a few practical scenarios with code examples tailored to your table structure.

假设你想找出从未有过action_type_id = 1记录的客户，可以用这个查询：

-- 把'your_table_name'替换成你的实际表名
SELECT DISTINCT customer_id
FROM your_table_name
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM your_table_name
    WHERE action_type_id = 1
);

逻辑说明： #

• 子查询先筛选出所有执行过操作类型1的客户
• 外层查询排除这些客户，剩下的就是从未执行过该操作的客户
  注：结合你的示例数据，这个查询会返回空结果——因为客户324和326都有至少一条action_type_id=1的记录。

如果已知系统中所有可能的action_type_id（比如假设是1、2、3），可以找出每个客户还没执行过的操作：

WITH all_action_types AS (
    -- 列出系统中所有可能的操作类型
    SELECT 1 AS action_type_id UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
),
unique_customers AS (
    -- 获取表中所有不重复的客户
    SELECT DISTINCT customer_id FROM your_table_name
)
SELECT 
    uc.customer_id,
    aat.action_type_id AS missing_action_type
FROM unique_customers uc
CROSS JOIN all_action_types aat
LEFT JOIN your_table_name t 
    ON uc.customer_id = t.customer_id 
    AND aat.action_type_id = t.action_type_id
WHERE t.id IS NULL;

针对你的示例数据，查询结果为： #

两个客户都没有action_type_id=2的记录，所以该操作类型会被标记为缺失。

如果需要找出客户在某个时间范围内（比如2013-01-01至2017-12-31）没有任何操作的日期，可以生成日期序列后匹配检查：

WITH date_range AS (
    -- 生成目标范围内的所有日期（PostgreSQL语法）
    SELECT generate_series(
        '2013-01-01'::DATE,
        '2017-12-31'::DATE,
        '1 day'::INTERVAL
    ) AS activity_date
),
unique_customers AS (
    SELECT DISTINCT customer_id FROM your_table_name
)
SELECT 
    uc.customer_id,
    dr.activity_date AS inactive_date
FROM unique_customers uc
CROSS JOIN date_range dr
LEFT JOIN your_table_name t 
    ON uc.customer_id = t.customer_id 
    AND t.日期 = dr.activity_date::DATE
WHERE t.id IS NULL
ORDER BY uc.customer_id, dr.activity_date;

注意事项： #

• 可以根据需求调整日期范围和时间间隔（比如把'1 day'改成'1 week'按周统计）
• 如果用的是其他SQL方言（比如MySQL），需要把generate_series替换成对应数据库的日期生成方法

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