求助：如何编写SQL查询筛选拥有至少6个月数据的钻井Well_ID

核心思路 #

你需要的是每个钻井自身的数据采集时间跨度至少6个月，而非仅在某个固定时间段内有数据。核心逻辑是对每个Well_ID分组，计算其最早和最晚received_time的时间差，判断是否满足6个月要求。

不同数据库的实现代码 #

1. MySQL/MariaDB

使用TIMESTAMPDIFF函数直接计算月份差：

SELECT well_id
FROM table1
GROUP BY well_id
HAVING TIMESTAMPDIFF(MONTH, MIN(received_time), MAX(received_time)) >= 6;

2. PostgreSQL

用AGE函数计算时间差，再提取月份数：

SELECT well_id
FROM table1
GROUP BY well_id
HAVING EXTRACT(MONTH FROM AGE(MAX(received_time), MIN(received_time))) >= 6;

或者用DATE_PART实现相同逻辑：

SELECT well_id
FROM table1
GROUP BY well_id
HAVING DATE_PART('month', MAX(received_time) - MIN(received_time)) >= 6;

3. SQL Server

使用DATEDIFF函数计算月份差：

SELECT well_id
FROM table1
GROUP BY well_id
HAVING DATEDIFF(MONTH, MIN(received_time), MAX(received_time)) >= 6;

补充：如果需要筛选「连续6个月都有数据」 #

如果你的需求是该钻井在连续的6个月中每个月都有采集数据（而非总跨度≥6个月），可以用窗口函数结合日期截断来实现（以PostgreSQL为例）：

WITH monthly_data AS (
    SELECT 
        well_id,
        DATE_TRUNC('month', received_time) AS month,
        COUNT(*) AS data_count
    FROM table1
    GROUP BY well_id, DATE_TRUNC('month', received_time)
),
consecutive_months AS (
    SELECT 
        well_id,
        month,
        ROW_NUMBER() OVER (PARTITION BY well_id ORDER BY month) AS rn,
        (month - INTERVAL '1 month' * ROW_NUMBER() OVER (PARTITION BY well_id ORDER BY month)) AS group_key
    FROM monthly_data
)
SELECT DISTINCT well_id
FROM consecutive_months
GROUP BY well_id, group_key
HAVING COUNT(*) >= 6;

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