关联sql转开窗函数sql优化案例分享

原始sql：1天12分钟，6天接近1小时

SELECT source.id, source.observation_datetime, source.station_code, MAX(roi.WSPD_10MIN) WSPD_10MIN_MAX FROM fact_wind_per_minute source JOIN fact_wind_per_minute roi ON roi.station_code = source.station_code AND roi.observation_datetime BETWEEN CAST(source.observation_datetime AS DATE) AND source.observation_datetime WHERE source.observation_datetime BETWEEN '2024-07-15 00:00:00' AND '2024-07-16 00:00:00' GROUP BY source.id, source.observation_datetime, source.station_code;

修改后sql：

random_table:

1day:  0.5s

31day: 3.7s

1year: 25s

hash_table 

1day   0.24s

31day: 2.74s

1year: 23.44s

SELECT id, observation_datetime, station_code, MAX(WSPD_10MIN) OVER ( PARTITION BY station_code, DATE(observation_datetime) ORDER BY observation_datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS WSPD_10MIN_MAX FROM fact_wind_per_minute_hash WHERE observation_datetime BETWEEN '2024-06-15 00:00:00' AND '2024-07-16 00:00:00' ORDER BY station_code, observation_datetime;