如何生成Datetime值填补气象站数据缺口？PowerBI/SQL简化方案咨询

一、更简洁的Oracle SQL查询语句 #

1. 基于GENERATE_SERIES的简化写法（Oracle 12cR2及以上版本） #

如果你的Oracle版本支持GENERATE_SERIES，可以直接生成时间序列，语法更直观：

WITH DateSequence AS (
    SELECT 
        TRUNC(MIN(Time), 'HH24') + (value - 1)/48 AS generated_datetime
    FROM WeatherData,
         GENERATE_SERIES(1, (TRUNC(MAX(Time), 'HH24') - TRUNC(MIN(Time), 'HH24'))*48 + 1)
)
SELECT ds.generated_datetime
FROM DateSequence ds
LEFT JOIN WeatherData wd ON ds.generated_datetime = wd.Time
WHERE wd.Time IS NULL
ORDER BY ds.generated_datetime;

2. 低版本兼容的优化写法 #

原语句可以提前计算时间边界，避免重复扫描数据表：

WITH DateBounds AS (
    SELECT 
        TRUNC(MIN(Time), 'HH24') AS start_dt,
        TRUNC(MAX(Time), 'HH24') AS end_dt
    FROM WeatherData
),
DateSequence AS (
    SELECT 
        start_dt + (LEVEL - 1)/48 AS generated_datetime
    FROM DateBounds
    CONNECT BY LEVEL <= (end_dt - start_dt)*48 + 1
)
SELECT ds.generated_datetime
FROM DateSequence ds
LEFT JOIN WeatherData wd ON ds.generated_datetime = wd.Time
WHERE wd.Time IS NULL
ORDER BY ds.generated_datetime;

二、Excel实现方式 #

1. 生成完整时间序列：
   • 在空白单元格输入数据中的最早时间（如2023-04-07 10:30），下一个单元格输入2023-04-07 11:00，选中这两个单元格，拖动填充柄至最晚时间（2023-04-07 16:30），自动生成每30分钟间隔的时间序列。
2. 定位缺失时间：
   • 在相邻列输入公式=IF(COUNTIF(原数据时间列,A2)=0,"缺失","存在")，下拉填充后筛选标记为“缺失”的行，即为需要填补的时间缺口。

三、PowerBI实现方式 #

1. 生成完整时间序列表：
   • 进入数据视图，点击建模选项卡→新建表，输入DAX公式：

     完整时间序列 = 
     ADDCOLUMNS(
         GENERATESERIES(
             MIN('WeatherData'[Time]),
             MAX('WeatherData'[Time]),
             TIME(0,30,0)
         ),
         "时间", [Value]
     )
     

2. 筛选缺失时间点：
   • 建立完整时间序列与WeatherData的左关联（基于[时间]和[Time]字段）。
   • 新建计算列：是否缺失 = IF(ISBLANK(RELATED('WeatherData'[Time])), "是", "否")。
   • 筛选是否缺失为“是”的行，即可得到所有缺失的时间记录。

内容的提问来源于stack exchange，提问作者Musab bin Jamil