基于工作日分析的SQL数据入库模式检测及连续天数统计需求

嘿，这个需求很实用，我来给你拆解下实现的核心思路，分三个关键步骤来搞定：

1. 提取每个产品的固定入库模式 #

首先得从过去半年的数据里，识别出每个产品的固定入库工作日集合——也就是那些每周都会稳定入库的工作日（比如你例子里的周一、周三、周五，对应编号2、4、6）。

具体操作可以用分组统计的方式：

• 按产品、周、工作日分组，统计每个工作日在对应周的入库次数
• 筛选出在所有周里都有入库记录的工作日，这些就是该产品的固定模式

举个SQL实现的例子（这里用PostgreSQL语法，不同数据库可调整函数）：

WITH product_patterns AS (
    SELECT 
        product_id,
        ARRAY_AGG(DISTINCT weekday ORDER BY weekday) AS target_weekdays
    FROM (
        SELECT 
            product_id,
            -- 匹配你系统的工作日编号规则：1=周一→2，3=周三→4，5=周五→6
            CASE EXTRACT(ISODOW FROM entry_date)
                WHEN 1 THEN 2
                WHEN 3 THEN 4
                WHEN 5 THEN 6
                ELSE EXTRACT(ISODOW FROM entry_date)
            END AS weekday,
            EXTRACT(WEEK FROM entry_date) AS week_num
        FROM data_entries
        WHERE entry_date >= CURRENT_DATE - INTERVAL '6 months'
    ) AS weekly_entries
    GROUP BY product_id, week_num
    HAVING COUNT(*) = 1  -- 假设每个工作日仅入库一次，按需调整
    GROUP BY product_id
    HAVING COUNT(DISTINCT week_num) = (
        -- 该产品过去半年的总周数
        SELECT COUNT(DISTINCT EXTRACT(WEEK FROM entry_date))
        FROM data_entries
        WHERE product_id = weekly_entries.product_id AND entry_date >= CURRENT_DATE - INTERVAL '6 months'
    )
)

2. 验证连续时间段的模式合规性 #

拿到固定模式后，需要检查每一天是否严格遵循规则：

• 模式内的工作日必须有入库记录
• 模式外的工作日必须无入库记录

我们可以生成过去半年的所有日期，逐一和产品模式比对，标记合规性：

WITH daily_compliance AS (
    SELECT 
        pp.product_id,
        pp.target_weekdays,
        all_dates.entry_date,
        -- 判断当天是否属于模式内的工作日
        CASE WHEN (SELECT CASE EXTRACT(ISODOW FROM all_dates.entry_date)
                WHEN 1 THEN 2 WHEN 3 THEN 4 WHEN 5 THEN 6 ELSE EXTRACT(ISODOW FROM all_dates.entry_date) END) = ANY(pp.target_weekdays)
            THEN TRUE ELSE FALSE END AS is_target_day,
        -- 判断当天是否有入库记录
        CASE WHEN de.entry_date IS NOT NULL THEN TRUE ELSE FALSE END AS has_entry,
        -- 标记是否合规：目标日有记录/非目标日无记录即为合规
        CASE WHEN (is_target_day AND has_entry) OR (NOT is_target_day AND NOT has_entry)
            THEN TRUE ELSE FALSE END AS is_compliant
    FROM generate_series(CURRENT_DATE - INTERVAL '6 months', CURRENT_DATE, INTERVAL '1 day') AS all_dates(entry_date)
    CROSS JOIN product_patterns pp
    LEFT JOIN data_entries de 
        ON de.product_id = pp.product_id AND de.entry_date = all_dates.entry_date
)

3. 统计符合模式的连续天数 #

最后用窗口函数识别连续合规的时间段，计算最长连续天数和总合规天数：

SELECT 
    product_id,
    target_weekdays,
    MAX(continuous_length) AS longest_continuous_compliant_days,
    SUM(CASE WHEN is_compliant THEN 1 ELSE 0 END) AS total_compliant_days
FROM (
    SELECT 
        product_id,
        target_weekdays,
        entry_date,
        is_compliant,
        -- 遇到不合规日期就开启新分组
        SUM(CASE WHEN NOT is_compliant THEN 1 ELSE 0 END) OVER (PARTITION BY product_id ORDER BY entry_date) AS compliance_group,
        -- 计算每个分组的连续天数
        COUNT(*) OVER (PARTITION BY product_id, compliance_group) AS continuous_length
    FROM daily_compliance
) AS continuous_groups
GROUP BY product_id, target_weekdays;

注意事项 #

• 工作日编号的映射要和你的系统完全匹配，记得根据实际规则调整CASE语句
• 如果允许目标工作日多次入库，要修改COUNT(*)的判断阈值
• 若仅需统计工作日范围内的连续天数，可调整日期生成逻辑，只包含工作日

内容的提问来源于stack exchange，提问作者Joseph Jeremiah Noonan