如何使用SQL或BigQuery区分月度与每日接收数据？

可以通过分析每个ID的日期分布规律来区分，以下是两种实用方法：

方法一：通过重复日期+间隔特征判断 #

如果某个ID存在同一天的多条记录，直接判定为每日数据（月度数据不会在同一天多次上报）；对无重复日期的ID，再通过相邻日期的间隔统计值判断类型。

WITH processed_data AS (
  -- 转换字符串日期为标准日期类型，提取纯日期部分
  SELECT 
    Id,
    PARSE_DATE('%d-%m-%Y', SUBSTR(date, 1, 10)) AS event_date
  FROM 
    `your-project.your-dataset.your-table`
),
id_dup_date_mark AS (
  -- 标记存在重复日期的ID
  SELECT 
    Id,
    TRUE AS is_daily_candidate
  FROM processed_data
  GROUP BY Id, event_date
  HAVING COUNT(*) > 1
  GROUP BY Id
),
id_interval_calc AS (
  -- 计算无重复日期ID的相邻日期间隔
  SELECT 
    Id,
    DATE_DIFF(next_date, event_date, DAY) AS interval_days
  FROM (
    SELECT 
      Id,
      event_date,
      LEAD(event_date) OVER (PARTITION BY Id ORDER BY event_date) AS next_date
    FROM processed_data
    WHERE Id NOT IN (SELECT Id FROM id_dup_date_mark)
  )
  WHERE next_date IS NOT NULL
),
id_interval_stats AS (
  -- 统计每个ID的间隔极值与平均值
  SELECT 
    Id,
    MIN(interval_days) AS min_interval,
    AVG(interval_days) AS avg_interval
  FROM id_interval_calc
  GROUP BY Id
)
-- 最终合并结果，标记数据类型
SELECT 
  p.Id,
  CASE 
    WHEN d.is_daily_candidate THEN '每日数据'
    WHEN s.min_interval <= 3 AND s.avg_interval <= 7 THEN '每日数据'
    WHEN s.avg_interval BETWEEN 28 AND 32 THEN '月度数据'
    ELSE '不确定类型'
  END AS data_type
FROM processed_data p
LEFT JOIN id_dup_date_mark d ON p.Id = d.Id
LEFT JOIN id_interval_stats s ON p.Id = s.Id
GROUP BY p.Id, d.is_daily_candidate, s.min_interval, s.avg_interval
ORDER BY p.Id;

方法二：基于日份规律判断 #

月度数据的上报日期通常固定在每月同一天（比如示例中ID66都是15号），而每日数据的日份是连续变化的。可以通过统计每个ID的日份唯一性来判断：

WITH processed_data AS (
  SELECT 
    Id,
    EXTRACT(DAY FROM PARSE_DATE('%d-%m-%Y', SUBSTR(date, 1, 10))) AS day_of_month
  FROM 
    `your-project.your-dataset.your-table`
),
id_day_stats AS (
  SELECT 
    Id,
    COUNT(DISTINCT day_of_month) AS distinct_days,
    COUNT(*) AS total_records
  FROM processed_data
  GROUP BY Id
)
SELECT 
  Id,
  CASE 
    WHEN distinct_days >= total_records * 0.7 THEN '每日数据'
    WHEN distinct_days = 1 THEN '月度数据'
    ELSE '不确定类型'
  END AS data_type
FROM id_day_stats
ORDER BY Id;

针对你的示例数据，运行后会得到：

• ID55：每日数据（日期为11、12号，连续两天）
• ID66：月度数据（均为15号，间隔1个月）
• ID77：每日数据（日期为12、13号，连续两天）

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