QuestDB中如何找出年度最高汽油均价对应的月份

针对QuestDB中gas_prices表的需求，我们可以通过窗口函数或关联查询实现目标，以下是两种可行方案：

方案一：使用窗口函数（推荐） #

利用窗口函数按年度分组，标记出每个年度内均价最高的月份：

WITH monthly_avg AS (
  SELECT 
    timestamp,
    avg(galon_price) AS avg_per_month,
    date_trunc('year', timestamp) AS year_start
  FROM gas_prices
  SAMPLE BY 1M
)
SELECT
  year_start AS year,
  max_per_year,
  timestamp AS which_month_is_responsible
FROM (
  SELECT
    year_start,
    avg_per_month,
    timestamp,
    -- 计算当前年度的最高均价
    max(avg_per_month) OVER (PARTITION BY year_start) AS max_per_year,
    -- 按均价降序排名，取每个年度的第一名
    ROW_NUMBER() OVER (PARTITION BY year_start ORDER BY avg_per_month DESC) AS rn
  FROM monthly_avg
) ranked_data
WHERE rn = 1;

方案二：使用关联查询 #

先计算年度最高均价，再关联回月度均价表找到对应月份：

WITH monthly_avg AS (
  SELECT 
    timestamp,
    avg(galon_price) AS avg_per_month,
    date_trunc('year', timestamp) AS year_start
  FROM gas_prices
  SAMPLE BY 1M
),
yearly_max AS (
  SELECT
    year_start,
    max(avg_per_month) AS max_per_year
  FROM monthly_avg
  GROUP BY year_start
)
SELECT
  ym.year_start AS year,
  ym.max_per_year,
  ma.timestamp AS which_month_is_responsible
FROM yearly_max ym
JOIN monthly_avg ma 
  ON ym.year_start = ma.year_start 
  AND ym.max_per_year = ma.avg_per_month;

关键说明 #

1. 第一步通过SAMPLE BY 1M计算月度均价，同时用date_trunc('year', timestamp)提取年度起始时间，用于后续分组。
2. 方案一中的ROW_NUMBER()会给每个年度内的月度均价排序，WHERE rn = 1确保只保留均价最高的月份；如果存在多个月份均价相同且都是年度最高，可改用RANK()保留所有符合条件的记录。
3. 方案二通过关联年度最大值和月度均价，直接匹配出对应月份，逻辑更直观。

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